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 la funzione REGEXP_CONTAINS
o l'operatore LIKE
per confrontare le stringhe. REGEXP_CONTAINS
fornisce più funzionalità, ma ha anche un tempo di esecuzione più lento. L'utilizzo di LIKE
anziché di REGEXP_CONTAINS
è più veloce, in particolare se non hai bisogno della piena 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%';
Ottimizza 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 stai utilizzando ha una funzione di approssimazione equivalente, la funzione di approssimazione produce prestazioni di query più rapide. Ad esempio, anziché utilizzare COUNT(DISTINCT)
, utilizza APPROX_COUNT_DISTINCT
.
Per saperne di più, consulta Funzioni di aggregazione approssimative.
Puoi anche utilizzare le funzioni HyperLogLog++
per eseguire approssimazioni (incluse le aggregazioni approssimative
personalizzate). Per ulteriori informazioni, consulta 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
.
L'esecuzione di una query che contiene la funzione NTILE
può restituire un errore Resources exceeded
se ci sono troppi elementi in ORDER BY
in una singola partizione, causando un aumento del volume di dati.
La finestra di analisi non è partizionata, quindi il calcolo NTILE
richiede un valore ORDER BY
globale affinché tutte le righe della tabella vengano elaborate da un singolo worker/slot.
Prova a utilizzare APPROX_QUANTILES
. Questa funzione consente un'esecuzione più efficiente della query perché 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 fornisce risultati simili ma non identici alla query originale, perché APPROX_QUANTILES
:
- Fornisce un'aggregazione approssimativa.
- Posiziona i valori rimanenti (il resto del numero di righe diviso per bucket) in modo diverso.
Ottimizza le funzioni definite dall'utente
Best practice: utilizza le funzioni definite dall'utente SQL per i calcoli semplici, poiché lo strumento di ottimizzazione delle query può applicare ottimizzazioni alle definizioni delle funzioni definite dall'utente SQL. Utilizza le funzioni definite dall'utente JavaScript per i calcoli complessi non supportati dalle funzioni definite dall'utente SQL.
La chiamata di una funzione JavaScript definita dall'utente richiede la creazione di un'istanza di un sottoprocesso. L'avvio di questo processo e l'esecuzione della funzione definita dall'utente influisce direttamente sulle prestazioni delle query. Se possibile, utilizza invece una UDF nativa (SQL).
UDF permanenti
È preferibile creare funzioni SQL e JavaScript permanenti definite dall'utente in un set di dati BigQuery centralizzato che può essere richiamato nelle query e nelle viste logiche, anziché creare e chiamare ogni volta una funzione definita dall'utente nel codice. La creazione di librerie di logica di business a livello di organizzazione all'interno di set di dati condivisi consente di ottimizzare le prestazioni 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 funzione definita dall'utente temporanea con una permanente:
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;