Ottimizza il calcolo delle query

Questo documento fornisce le best practice per ottimizzare il rendimento delle query.

Quando esegui una query, puoi visualizzare il piano di query nella console Trusted Cloud . Puoi anche richiedere i dettagli di esecuzione utilizzando le visualizzazioni INFORMATION_SCHEMA.JOBS* o il metodo jobs.get API REST.

Il piano di query include dettagli su fasi e passaggi della query. Questi dettagli possono aiutarti a identificare i modi per migliorare le prestazioni delle query. Ad esempio, se noti una fase che scrive molti più output rispetto alle altre, potrebbe significare che devi filtrare prima nella query.

Per scoprire di più sul piano di query e vedere esempi di come le informazioni sul piano di query possono aiutarti a migliorare le prestazioni delle query, consulta Ottenere insight sulle prestazioni delle query. Dopo aver esaminato gli approfondimenti sul rendimento delle query, puoi ottimizzare ulteriormente la query eseguendo le seguenti operazioni:

• Ridurre i dati da elaborare
• Ottimizzare le operazioni di query
• Ridurre l'output della query
• Evitare gli anti-pattern SQL

Riduci i dati elaborati

Puoi ridurre i dati da elaborare utilizzando le opzioni descritte nelle sezioni seguenti.

Evita SELECT *

Best practice: controlla la proiezione eseguendo query solo sulle colonne che ti servono.

La proiezione si riferisce al numero di colonne lette dalla query. La proiezione di colonne in eccesso comporta I/O e materializzazione aggiuntivi (sprecati) (scrittura dei risultati).

• Utilizza le opzioni di anteprima dei dati. Se stai sperimentando o esplorando i dati, utilizza una delle opzioni di anteprima dei dati anziché SELECT *.
• Esegui query su colonne specifiche. L'applicazione di una clausola LIMIT a una query SELECT * non influisce sulla quantità di dati letti. Ti viene addebitato il costo della lettura di tutti i byte dell'intera tabella e la query viene conteggiata ai fini della quota del livello gratuito. Esegui invece query solo per le colonne che ti servono. Ad esempio, utilizza SELECT * EXCEPT per escludere una o più colonne dai risultati.
• Utilizza tabelle partizionate. Se hai bisogno di query su ogni colonna di una tabella, ma solo su un sottoinsieme di dati, valuta la possibilità di:
  • Materializzare i risultati in una tabella di destinazione ed eseguire query su quella tabella.
  • Partizionamento delle tabelle ed esecuzione di query sulla partizione pertinente. Ad esempio, utilizza WHERE _PARTITIONDATE="2017-01-01" per eseguire query solo sulla partizione del 1° gennaio 2017.

• Utilizzare SELECT * EXCEPT. L'esecuzione di query su un sottoinsieme di dati o l'utilizzo di SELECT * EXCEPT può ridurre notevolmente la quantità di dati letti da una query. Oltre al risparmio sui costi, le prestazioni vengono migliorate riducendo la quantità di I/O dei dati e la quantità di materializzazione necessaria per i risultati della query.

  SELECT * EXCEPT (col1, col2, col5)
  FROM mydataset.newtable

Evita tabelle con caratteri jolly eccessivi

Best practice: quando esegui query su tabelle con caratteri jolly, devi utilizzare il prefisso più granulare.

Utilizza i caratteri jolly per eseguire query su più tabelle utilizzando istruzioni SQL concise. Le tabelle con caratteri jolly sono un'unione di tabelle che corrispondono all'espressione con caratteri jolly. Le tabelle con caratteri jolly sono utili se il tuo set di dati contiene le seguenti risorse:

• Più tabelle con nomi simili e schemi compatibili
• Tabelle con sharding

Quando esegui una query su una tabella con funzione carattere jolly, specifica un carattere jolly (*) dopo il prefisso della tabella comune. Ad esempio, FROM bigquery-public-data.noaa_gsod.gsod194* esegue una query su tutte le tabelle degli anni '40.

I prefissi più granulari hanno un rendimento migliore rispetto a quelli più brevi. Ad esempio, FROM bigquery-public-data.noaa_gsod.gsod194* ha un rendimento migliore di FROM bigquery-public-data.noaa_gsod.* perché meno tabelle corrispondono al carattere jolly.

Evita le tabelle partizionate per data

Best practice: non utilizzare tabelle partizionate per data (chiamate anche tabelle con nome data) al posto di tabelle partizionate per ora.

Le tabelle partizionate hanno prestazioni migliori rispetto alle tabelle denominate in base alla data. Quando crei tabelle partizionate per data, BigQuery deve mantenere una copia dello schema e dei metadati per ogni tabella denominata in base alla data. Inoltre, quando vengono utilizzate tabelle denominate in base alla data, BigQuery potrebbe dover verificare le autorizzazioni per ogni tabella sottoposta a query. Questa pratica aumenta anche l'overhead delle query e influisce sulle prestazioni delle query.

Evita di partizionare eccessivamente le tabelle

Best practice: evita di creare troppi shard di tabelle. Se esegui lo sharding delle tabelle per data, utilizza invece tabelle partizionate in base al tempo.

Lo sharding delle tabelle si riferisce alla suddivisione di set di dati di grandi dimensioni in tabelle separate e all'aggiunta di un suffisso a ogni nome di tabella. Se esegui lo sharding delle tabelle per data, utilizza tabelle partizionate in base al tempo.

Grazie al basso costo dell'archiviazione BigQuery, non è necessario ottimizzare le tabelle per ridurre i costi come faresti in un sistema di database relazionale. La creazione di un numero elevato di partizioni di tabelle ha un impatto sulle prestazioni che supera qualsiasi vantaggio in termini di costi.

Le tabelle suddivise in shard richiedono che BigQuery gestisca schema, metadati e autorizzazioni per ogni shard. A causa del sovraccarico aggiuntivo necessario per gestire le informazioni su ogni shard, la suddivisione eccessiva delle tabelle può influire sulle prestazioni delle query.

La quantità e l'origine dei dati letti da una query possono influire sulle prestazioni e sui costi della query.

Eliminare le query partizionate

Best practice:quando esegui una query su una tabella partizionata, per filtrare con le partizioni nelle tabelle partizionate, utilizza le seguenti colonne:

• Per le tabelle partizionate per data di importazione, utilizza la pseudocolonna _PARTITIONTIME.
• Per le tabelle partizionate, ad esempio quelle basate su colonne di unità di tempo e intervalli di numeri interi, utilizza la colonna di partizionamento.

Per le tabelle partizionate per unità di tempo, il filtraggio dei dati con _PARTITIONTIME o colonna di partizionamento ti consente di specificare una data o un intervallo di date. Ad esempio, la seguente clausola WHERE utilizza la pseudocolonna _PARTITIONTIME per specificare le partizioni tra il 1° gennaio 2016 e il 31 gennaio 2016:

WHERE _PARTITIONTIME
BETWEEN TIMESTAMP("20160101")
AND TIMESTAMP("20160131")

La query elabora i dati solo nelle partizioni indicate dall'intervallo di date. Filtrare le partizioni migliora le prestazioni delle query e riduce i costi.

Ridurre i dati prima di utilizzare un JOIN

Best practice:riduci la quantità di dati elaborati prima di una clausola JOIN eseguendo aggregazioni.

L'utilizzo di una clausola GROUP BY con funzioni di aggregazione richiede molte risorse di calcolo, perché questi tipi di query utilizzano shuffle. Poiché queste query richiedono un elevato utilizzo di risorse di calcolo, devi utilizzare una clausola GROUP BY solo quando necessario.

Per le query con GROUP BY e JOIN, esegui l'aggregazione all'inizio della query per ridurre la quantità di dati elaborati. Ad esempio, la seguente query esegue un JOIN su due tabelle di grandi dimensioni senza alcun filtro preliminare:

WITH
  users_posts AS (
  SELECT *
  FROM
    `bigquery-public-data`.stackoverflow.comments AS c
  JOIN
    `bigquery-public-data`.stackoverflow.users AS u
  ON
    c.user_id = u.id
  )
SELECT
  user_id,
  ANY_VALUE(display_name) AS display_name,
  ANY_VALUE(reputation) AS reputation,
  COUNT(text) AS comments_count
FROM users_posts
GROUP BY user_id
ORDER BY comments_count DESC
LIMIT 20;

Questa query pre-aggrega i conteggi dei commenti, riducendo la quantità di dati letti per JOIN:

WITH
  comments AS (
  SELECT
    user_id,
    COUNT(text) AS comments_count
  FROM
    `bigquery-public-data`.stackoverflow.comments
  WHERE
    user_id IS NOT NULL
  GROUP BY user_id
  ORDER BY comments_count DESC
  LIMIT 20
  )
SELECT
  user_id,
  display_name,
  reputation,
  comments_count
FROM comments
JOIN
  `bigquery-public-data`.stackoverflow.users AS u
ON
  user_id = u.id
ORDER BY comments_count DESC;

Utilizzare la clausola WHERE

Best practice: utilizza una clausola WHERE per limitare la quantità di dati restituita da una query. Se possibile, utilizza le colonne BOOL, INT64, FLOAT64 o DATE nella clausola WHERE.

Le operazioni sulle colonne BOOL, INT64, FLOAT64 e DATE sono in genere più veloci rispetto a quelle sulle colonne STRING o BYTE. Se possibile, utilizza una colonna che utilizza uno di questi tipi di dati nella clausola WHERE per ridurre la quantità di dati restituiti dalla query.

Utilizzare le viste materializzate

Best practice: utilizza le viste materializzate per precalcolare i risultati di una query per migliorare le prestazioni e l'efficienza.

Le viste materializzate sono viste precalcolate che memorizzano nella cache i risultati di una query periodicamente per migliorare le prestazioni e l'efficienza. BigQuery sfrutta i risultati precalcolati dalle viste materializzate e ogni volta che è possibile legge solo le modifiche dalle tabelle di base per calcolare i risultati aggiornati. È possibile eseguire query direttamente sulle viste materializzate oppure l'ottimizzatore BigQuery può usare le viste materializzate per elaborare le query sulle tabelle di base.

Utilizzare BI Engine

Best practice: utilizza BigQuery BI Engine per velocizzare le query memorizzando nella cache i dati che utilizzi più spesso.

Valuta la possibilità di aggiungere una prenotazione BI Engine al progetto in cui vengono calcolate le query. BigQuery BI Engine utilizza un motore di query vettoriale per accelerare le prestazioni delle query SELECT.

Utilizzare gli indici di ricerca

Best practice: utilizza gli indici di ricerca per ricerche efficienti di righe quando devi trovare singole righe di dati in tabelle di grandi dimensioni.

Un indice di ricerca è una struttura di dati progettata per consentire una ricerca molto efficiente con la funzione SEARCH, ma può anche accelerare le query utilizzando altri operatori e funzioni, come gli operatori di uguaglianza (=), IN o LIKE e determinate funzioni di stringa e JSON.

Ottimizza le operazioni di query

Puoi ottimizzare le operazioni di query utilizzando le opzioni descritte nelle sezioni seguenti.

Evitare di trasformare ripetutamente i dati

Best practice:se utilizzi SQL per eseguire operazioni ETL, evita situazioni in cui trasformi ripetutamente gli stessi dati.

Ad esempio, se utilizzi SQL per tagliare le stringhe o estrarre dati utilizzando espressioni regolari, è più efficiente materializzare i risultati trasformati in una tabella di destinazione. Funzioni come le espressioni regolari richiedono calcoli aggiuntivi. L'esecuzione di query sulla tabella di destinazione senza l'overhead di trasformazione aggiuntivo è molto più efficiente.

Evita più valutazioni degli stessi CTE

Best practice: utilizza il linguaggio procedurale, le variabili, le tabelle temporanee e le tabelle a scadenza automatica per conservare i calcoli e utilizzarli in un secondo momento nella query.

Quando la query contiene espressioni di tabella comuni (CTE) utilizzate in più punti della query, potrebbero essere valutate ogni volta che vengono citate. L'ottimizzatore di query tenta di rilevare le parti della query che potrebbero essere eseguite una sola volta, ma questo potrebbe non essere sempre possibile. Di conseguenza, l'utilizzo di un'espressione di tabella comune potrebbe non contribuire a ridurre la complessità delle query interne e il consumo di risorse.

Puoi archiviare il risultato di una CTE in una variabile scalare o in una tabella temporanea a seconda dei dati restituiti dalla CTE.

Evita unioni e sottoquery ripetute

Best practice: evita di unire ripetutamente le stesse tabelle e di utilizzare le stesse query secondarie.

Anziché unire ripetutamente i dati, potrebbe essere più efficiente utilizzare dati ripetuti nidificati per rappresentare le relazioni. I dati ripetuti nidificati ti evitano l'impatto sulle prestazioni della larghezza di banda di comunicazione richiesta da un join. Inoltre, ti consente di risparmiare i costi di I/O che sostieni leggendo e scrivendo ripetutamente gli stessi dati. Per ulteriori informazioni, vedi Utilizzare campi nidificati e ripetuti.

Allo stesso modo, la ripetizione delle stesse sottoquery influisce sulle prestazioni tramite l'elaborazione ripetitiva delle query. Se utilizzi le stesse sottoquery in più query, valuta la possibilità di materializzare i risultati della sottoquery in una tabella. poi utilizza i dati materializzati nelle query.

La materializzazione dei risultati della sottoquery migliora le prestazioni e riduce la quantità complessiva di dati letti e scritti da BigQuery. Il piccolo costo di archiviazione dei dati materializzati supera l'impatto sulle prestazioni dell'elaborazione ripetuta di I/O e query.

Ottimizzare i pattern di unione

Best practice: per le query che uniscono dati di più tabelle, ottimizza i pattern di unione iniziando dalla tabella più grande.

Quando crei una query utilizzando una clausola JOIN, considera l'ordine in cui unisci i dati. L'ottimizzatore di query GoogleSQL determina quale tabella deve trovarsi su quale lato del join. Come best practice, inserisci prima la tabella con il maggior numero di righe, seguita dalla tabella con il minor numero di righe, quindi inserisci le tabelle rimanenti in ordine decrescente di dimensioni.

Quando hai una tabella grande sul lato sinistro di JOIN e una piccola sul lato destro di JOIN, viene creata un'unione di trasmissione. Un broadcast join invia tutti i dati della tabella più piccola a ogni slot che elabora la tabella più grande. È consigliabile eseguire prima l'unione alla trasmissione.

Per visualizzare le dimensioni delle tabelle nel tuo JOIN, consulta Ottenere informazioni sulle tabelle.

Specifica i vincoli di chiave primaria e chiave esterna

Best practice: specifica i vincoli di chiave nello schema della tabella quando i dati della tabella soddisfano i requisiti di integrità dei dati dei vincoli di chiave primaria o chiave esterna. Il motore di query può utilizzare i vincoli di chiave per ottimizzare i piani di query.

BigQuery non verifica automaticamente l'integrità dei dati, quindi devi assicurarti che i dati soddisfino i vincoli specificati nello schema della tabella. Se non mantieni l'integrità dei dati nelle tabelle con vincoli specificati, i risultati della query potrebbero essere imprecisi.

Ottimizzare la clausola ORDER BY

Best practice: quando utilizzi la clausola ORDER BY, assicurati di seguire le best practice:

• Utilizza ORDER BY nella query più esterna o all'interno delle clausole finestra. Sposta le operazioni complesse alla fine della query. L'inserimento di una clausola ORDER BY al centro di una query influisce notevolmente sulle prestazioni, a meno che non venga utilizzata in una funzione finestra.

  Un'altra tecnica per ordinare la query consiste nel spostare le operazioni complesse, come le espressioni regolari e le funzioni matematiche, alla fine della query. Questa tecnica riduce i dati da elaborare prima che vengano eseguite le operazioni complesse.

• Utilizza una clausola LIMIT. Se stai ordinando un numero molto elevato di valori ma non devi restituirli tutti, utilizza una clausola LIMIT. Ad esempio, la seguente query ordina un set di risultati molto grande e genera un errore Resources exceeded. La query esegue l'ordinamento in base alla colonna title in mytable. La colonna title contiene milioni di valori.

  SELECT
  title
  FROM
  `my-project.mydataset.mytable`
  ORDER BY
  title;

  Per rimuovere l'errore, utilizza una query come la seguente:

  SELECT
  title
  FROM
  `my-project.mydataset.mytable`
  ORDER BY
  title DESC
  LIMIT
  1000;

• Utilizza una funzione finestra. Se ordini un numero molto elevato di valori, utilizza una funzione finestra e limita i dati prima di chiamarla. Ad esempio, la seguente query elenca i dieci utenti di Stack Overflow più anziani e la loro classificazione, con l'account più vecchio classificato al livello più basso:

  SELECT
  id,
  reputation,
  creation_date,
  DENSE_RANK() OVER (ORDER BY creation_date) AS user_rank
  FROM bigquery-public-data.stackoverflow.users
  ORDER BY user_rank ASC
  LIMIT 10;

  L'esecuzione di questa query richiede circa 15 secondi. Questa query utilizza LIMIT alla fine della query, ma non nella funzione finestra DENSE_RANK() OVER. Per questo motivo, la query richiede che tutti i dati vengano ordinati in un unico nodo worker.

  Per migliorare il rendimento, devi limitare il set di dati prima di calcolare la funzione finestra:

  WITH users AS (
  SELECT
  id,
  reputation,
  creation_date,
  FROM bigquery-public-data.stackoverflow.users
  ORDER BY creation_date ASC
  LIMIT 10)
  SELECT
  id,
  reputation,
  creation_date,
  DENSE_RANK() OVER (ORDER BY creation_date) AS user_rank
  FROM users
  ORDER BY user_rank;

  L'esecuzione di questa query richiede circa 2 secondi, restituendo gli stessi risultati della query precedente.

  Un avvertimento è che la funzione DENSE_RANK() classifica i dati all'interno degli anni, quindi per la classificazione dei dati che si estendono su più anni, queste query non forniscono risultati identici.

Suddividi le query complesse in query più piccole

Best practice: utilizza le funzionalità di query con più istruzioni e le stored procedure per eseguire i calcoli progettati come una query complessa come più query più piccole e semplici.

L'esecuzione di query complesse, funzioni REGEX e sottoquery o join a più livelli può essere lenta e richiedere molte risorse. Tentare di inserire tutti i calcoli in un'unica istruzione SELECT di grandi dimensioni, ad esempio per creare una vista, a volte è un antipattern e può comportare una query lenta e che richiede molte risorse. In casi estremi, il piano di query interno diventa così complesso che BigQuery non è in grado di eseguirlo.

La suddivisione di una query complessa consente di materializzare i risultati intermedi in variabili o tabelle temporanee. Puoi quindi utilizzare questi risultati intermedi in altre parti della query. Diventa sempre più utile quando questi risultati sono necessari in più di un punto della query.

Spesso ti consente di esprimere meglio la vera intenzione di parti della query con le tabelle temporanee come punti di materializzazione dei dati.

Utilizzare campi nidificati e ripetuti

Per informazioni su come denormalizzare l'archiviazione dei dati utilizzando campi nidificati e ripetuti, consulta Utilizzare campi nidificati e ripetuti.

Utilizzare i tipi di dati INT64 nelle unioni

Best practice:utilizza i tipi di dati INT64 nelle unioni anziché i tipi di dati STRING per ridurre i costi e migliorare le prestazioni di confronto.

BigQuery non indicizza le chiavi primarie come i database tradizionali, quindi più ampia è la colonna di join, più tempo richiede il confronto. Pertanto, l'utilizzo dei tipi di dati INT64 nei join è più economico ed efficiente rispetto all'utilizzo dei tipi di dati STRING.

Ridurre gli output delle query

Puoi ridurre gli output delle query utilizzando le opzioni descritte nelle sezioni seguenti.

Materializzare set di risultati di grandi dimensioni

Best practice: valuta la possibilità di materializzare grandi set di risultati in una tabella di destinazione. La scrittura di set di risultati di grandi dimensioni ha un impatto su prestazioni e costi.

BigQuery limita i risultati memorizzati nella cache a circa 10 GB compressi. Le query che restituiscono risultati più grandi superano questo limite e spesso generano il seguente errore: Response too large.

Questo errore si verifica spesso quando selezioni un numero elevato di campi da una tabella con una notevole quantità di dati. Possono verificarsi problemi di scrittura dei risultati memorizzati nella cache anche nelle query in stile ETL che normalizzano i dati senza riduzione o aggregazione.

Puoi superare la limitazione delle dimensioni dei risultati memorizzati nella cache utilizzando le seguenti opzioni:

• Utilizzare i filtri per limitare il set di risultati
• Utilizza una clausola LIMIT per ridurre il set di risultati, soprattutto se utilizzi una clausola ORDER BY
• Scrivere i dati di output in una tabella di destinazione

Puoi scorrere i risultati utilizzando l'API REST BigQuery. Per ulteriori informazioni, consulta Scorrere i dati della tabella.

UPDATE
  dataset.t t
SET
  my_column = u.my_column
FROM
  dataset.u u
WHERE
  t.my_key = u.my_key