Best practice per le funzioni

Questo documento descrive come ottimizzare le query che utilizzano le funzioni SQL.

Ottimizza il confronto delle stringhe

Best practice:se possibile, utilizza LIKE anziché REGEXP_CONTAINS.

In BigQuery, puoi utilizzare REGEXP_CONTAINS o la funzione LIKE per confrontare le stringhe. REGEXP_CONTAINS offre più funzionalità, ma ha anche un tempo di esecuzione più lento. Utilizzo di LIKE anziché di REGEXP_CONTAINS è più veloce, soprattutto se non hai bisogno della piena potenza delle espressioni regolari fornita da REGEXP_CONTAINS, ad esempio la corrispondenza con caratteri jolly.

Considera il seguente utilizzo della funzione REGEXP_CONTAINS:

SELECT
  dim1
FROM
  `dataset.table1`
WHERE
  REGEXP_CONTAINS(dim1, '.*test.*');

Puoi ottimizzare questa query nel seguente modo:

SELECT
  dim1
FROM
  `dataset.table`
WHERE
  dim1 LIKE '%test%';

Ottimizza le funzioni di aggregazione

Best practice: se il tuo caso d'uso lo supporta, utilizza un'aggregazione approssimativa personalizzata.

Se la funzione di aggregazione SQL che stai utilizzando ha un'approssimazione equivalente , la funzione di approssimazione offre prestazioni più veloci nelle query. Per esempio, invece di utilizzare COUNT(DISTINCT), utilizzare APPROX_COUNT_DISTINCT. Per ulteriori informazioni, vedi approssimative alle funzioni di aggregazione.

Puoi anche utilizzare le funzioni HyperLogLog++ per fare approssimazioni (incluse le aggregazioni approssimative). Per ulteriori informazioni, vedi Funzioni HyperLogLog++ nel riferimento di GoogleSQL.

Considera il seguente utilizzo della funzione COUNT:

SELECT
  dim1,
  COUNT(DISTINCT dim2)
FROM
  `dataset.table`
GROUP BY 1;

Puoi ottimizzare questa query nel seguente modo:

SELECT
  dim1,
  APPROX_COUNT_DISTINCT(dim2)
FROM
  `dataset.table`
GROUP BY 1;

Ottimizza le funzioni dei quantili

Best practice:se possibile, utilizza APPROX_QUANTILE anziché NTILE.

Esecuzione di una query contenente NTILE può avere esito negativo con Resources exceeded se sono presenti troppi a ORDER BY in una singola partizione, con conseguente aumento del volume di dati. La finestra di analisi non è partizionata, pertanto il calcolo di NTILE richiede un ORDER BY globale per tutte le righe della tabella da elaborare da un singolo worker/slot.

Prova a usare APPROX_QUANTILES . Questa funzione consente di eseguire la query in modo più efficiente non richiede un valore ORDER BY globale per tutte le righe della tabella.

Considera il seguente utilizzo della funzione NTILE:

SELECT
  individual_id,
  NTILE(nbuckets) OVER (ORDER BY sales desc) AS sales_third
FROM
  `dataset.table`;

Puoi ottimizzare questa query nel seguente modo:

WITH QuantInfo AS (
  SELECT
    o, qval
  FROM UNNEST((
     SELECT APPROX_QUANTILES(sales, nbuckets)
     FROM `dataset.table`
    )) AS qval
  WITH offset o
  WHERE o > 0
)
SELECT
  individual_id,
  (SELECT
     (nbuckets + 1) - MIN(o)
   FROM QuantInfo
   WHERE sales <= QuantInfo.qval
  ) AS sales_third
FROM `dataset.table`;

La versione ottimizzata restituisce risultati simili, ma non identici, all'originale perché APPROX_QUANTILES:

1. Fornisce un'aggregazione approssimativa.
2. Posiziona i valori rimanenti (il resto del numero di righe divisi per bucket) in modo diverso.

Ottimizza le funzioni definite dall'utente

Best practice: utilizza le funzioni definite dall'utente per i calcoli semplici, in quanto lo strumento di ottimizzazione delle query può applicare ottimizzazioni alle definizioni delle funzioni SQL. Utilizza le funzioni JavaScript definite dall'utente per i calcoli complessi non supportati dalle funzioni SQL.

La chiamata di una funzione JavaScript definita dall'utente richiede la creazione di un'istanza di un processo secondario. L'avvio di questo processo e l'esecuzione della funzione definita dall'utente influiscono direttamente sulle prestazioni delle query. Se possibile, utilizza un UDF nativa (SQL) .

UDF permanenti

È preferibile creare funzioni SQL e JavaScript definite dall'utente permanenti in un set di dati centralizzato di BigQuery che può essere richiamato query e nelle viste logiche, invece di creare e chiamare una funzione definita dall'utente nel codice ogni volta. Creazione di librerie a livello di organizzazione della logica di business all'interno di set di dati condivisi consente di ottimizzare le prestazioni e usare meno risorse.

L'esempio seguente mostra come una funzione definita dall'utente temporanea viene richiamata in una query:

CREATE TEMP FUNCTION addFourAndDivide(x INT64, y INT64) AS ((x + 4) / y);

WITH numbers AS
  (SELECT 1 as val
  UNION ALL
  SELECT 3 as val
  UNION ALL
  SELECT 4 as val
  UNION ALL
  SELECT 5 as val)
SELECT val, addFourAndDivide(val, 2) AS result
FROM numbers;

Puoi ottimizzare questa query sostituendo la funzione definita dall'utente temporanea con una persistente:

WITH numbers AS
  (SELECT 1 as val
  UNION ALL
  SELECT 3 as val
  UNION ALL
  SELECT 4 as val
  UNION ALL
  SELECT 5 as val)
SELECT val, `your_project.your_dataset.addFourAndDivide`(val, 2) AS result
FROM numbers;