Best practice per le funzioni

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

Ottimizza il confronto di stringhe

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

In BigQuery, puoi utilizzare la funzione REGEXP_CONTAINS o l'operatore LIKE per confrontare le stringhe. REGEXP_CONTAINS offre più funzionalità, ma anche un tempo di esecuzione più lento. L'utilizzo di LIKE anziché di REGEXP_CONTAINS è più veloce, soprattutto se non hai bisogno di tutta la potenza delle espressioni regolari fornite 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%';

Ottimizzare le funzioni di aggregazione

Best practice: se il tuo caso d'uso lo supporta, utilizza una funzione di aggregazione approssimativa.

Se la funzione di aggregazione SQL che utilizzi ha una funzione di approssimazione equivalente, quest'ultima produce prestazioni delle query più rapide. Ad esempio, invece di utilizzare COUNT(DISTINCT), utilizza APPROX_COUNT_DISTINCT. Per saperne di più, consulta Funzioni di aggregazione approssimate.

Puoi anche utilizzare le funzioni HyperLogLog++ per eseguire approssimazioni (incluse aggregazioni approssimate personalizzate). Per saperne di più, consulta le funzioni HyperLogLog++ nel riferimento 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 quantile

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

L'esecuzione di una query che contiene la funzione NTILE può non riuscire con un errore Resources exceeded se ci sono troppi elementi da ORDER BY in una singola partizione, il che causa l'aumento del volume di dati. La finestra analitica non è partizionata, quindi il calcolo NTILE richiede un ORDER BY globale per l'elaborazione di tutte le righe della tabella da parte di un singolo worker/slot.

Prova a utilizzare APPROX_QUANTILES. Questa funzione consente di eseguire la query in modo più efficiente perché non richiede un 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 fornisce risultati simili ma non identici alla query originale, perché APPROX_QUANTILES:

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

Ottimizzare le UDF

Best practice:utilizza le UDF SQL per i calcoli semplici, perché l'ottimizzatore di query può applicare ottimizzazioni alle definizioni delle UDF SQL. Utilizza le UDF JavaScript per calcoli complessi non supportati dalle UDF SQL.

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

Funzioni definite dall'utente permanenti

È preferibile creare funzioni SQL e JavaScript persistenti definite dall'utente in un set di dati BigQuery centralizzato che può essere richiamato in query e in viste logiche, anziché creare e chiamare una UDF nel codice ogni volta. La creazione di librerie a livello di organizzazione di logica di business all'interno di set di dati condivisi consente di ottimizzare il rendimento e utilizzare meno risorse.

L'esempio seguente mostra come viene richiamata una funzione definita dall'utente temporanea 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;