Piano di query e sequenza temporale

Incorporato nei job di query, BigQuery include informazioni sulle tempistiche e sul piano di query diagnostiche. Sono simili alle informazioni fornite da dichiarazioni come EXPLAIN in altri database e sistemi analitici. Queste informazioni possono essere recuperate dalle risposte dell'API di metodi come jobs.get.

Per le query a lunga esecuzione, BigQuery aggiornerà periodicamente queste statistiche. Questi aggiornamenti vengono eseguiti indipendentemente dalla frequenza con cui viene eseguito il polling dello stato del job, ma in genere non avvengono più di ogni 30 secondi. Inoltre, i job di query che non utilizzano risorse di esecuzione, ad esempio le richieste di prova o i risultati che possono essere forniti dai risultati memorizzati nella cache, non includeranno le informazioni diagnostiche aggiuntive, anche se potrebbero essere presenti altre statistiche.

Contesto

Quando BigQuery esegue un job di query, converte l'istruzione SQL dichiarativa in un grafico di esecuzione, suddiviso in una serie di fasi di query, composte a loro volta da insiemi più granulari di passaggi di esecuzione. BigQuery usa un'architettura parallela con grande distribuzione per eseguire queste query. Le fasi modellano le unità di lavoro che molti potenziali worker possono eseguire in parallelo. Le fasi comunicano tra loro utilizzando un'architettura di shuffle distribuita e veloce.

Nel piano di query, i termini unità di lavoro e worker vengono utilizzati per trasmettere informazioni specifiche sul parallelismo. Altrove, in BigQuery, potresti trovare il termine slot, che è una rappresentazione astratta di più facet dell'esecuzione delle query, tra cui risorse di calcolo, memoria e I/O. Le statistiche dei job di primo livello forniscono una stima dei costi delle singole query utilizzando la stima totalSlotMs della query utilizzando questa contabilità astratta.

Un'altra proprietà importante dell'architettura di esecuzione delle query è che è dinamica, il che significa che il piano di query può essere modificato mentre la query è in esecuzione. Le fasi introdotte durante l'esecuzione di una query vengono spesso utilizzate per migliorare la distribuzione dei dati tra i worker di query. Nei piani di query in cui si verifica questa situazione, queste fasi sono in genere etichettate come fasi di ripartizionamento.

Oltre al piano di query, i job di query espongono anche una sequenza temporale di esecuzione, che fornisce una contabilità delle unità di lavoro completate, in attesa e attive all'interno dei worker di query. Una query può avere più fasi con worker attivi contemporaneamente e la sequenza temporale ha lo scopo di mostrare l'avanzamento complessivo della query.

Visualizzazione delle informazioni con la console Google Cloud

Nella console Google Cloud, puoi visualizzare i dettagli del piano di query per una query completata facendo clic sul pulsante Dettagli esecuzione (vicino al pulsante Risultati).

Informazioni sul piano di query

All'interno della risposta dell'API, i piani di query sono rappresentati come un elenco di fasi di query. Ogni voce nell'elenco mostra statistiche di panoramica per fase, informazioni dettagliate sui passaggi e classificazioni delle tempistiche delle fasi. Non tutti i dettagli vengono visualizzati all'interno della console Google Cloud, ma possono essere tutti presenti nelle risposte dell'API.

Panoramica della fase

I campi della panoramica per ogni fase possono includere quanto segue:

Campo API	Descrizione
id	ID numerico univoco per lo stage.
name	Nome di riepilogo semplice della fase. Il steps all'interno della fase fornisce ulteriori dettagli sui passaggi di esecuzione.
status	Stato di esecuzione della fase. Gli stati possibili sono PENDING, RUNNING, COMPLETE, FAILED e CANCELLED.
inputStages	Un elenco degli ID che formano il grafico delle dipendenze della fase. Ad esempio, una fase JOIN richiede spesso due fasi dipendenti che preparano i dati sul lato sinistro e destro della relazione JOIN.
startMs	Timestamp in millisecondi dell'epoca, che rappresenta il momento in cui è iniziato l'esecuzione del primo worker della fase.
endMs	Timestamp, in millisecondi, che rappresenta il momento in cui l'ultimo worker ha completato l'esecuzione.
steps	Elenco più dettagliato dei passaggi di esecuzione nella fase. Per saperne di più, consulta la prossima sezione.
recordsRead	Dimensioni di input della fase come numero di record, per tutti i worker della fase.
recordsWritten	Dimensioni di output della fase come numero di record, per tutti i worker della fase.
parallelInputs	Numero di unità di lavoro caricabili in contemporanea per lo stage. A seconda della fase e della query, questo valore può rappresentare il numero di segmenti a colonna all'interno di una tabella o il numero di partizioni all'interno di uno shuffling intermedio.
completedParallelInputs	Numero di unità presenti nella fase che sono state completate. Per alcune query, non è necessario completare tutti gli input all'interno di una fase per completare la fase.
shuffleOutputBytes	Rappresenta i byte totali scritti su tutti i worker all'interno di una fase di query.
shuffleOutputBytesSpilled	Per le query che trasmettono dati significativi tra le fasi potrebbe essere necessario ricorrere alla trasmissione basata su disco. La statistica dei byte versati comunica la quantità di dati distribuiti sul disco. Dipende da un algoritmo di ottimizzazione, che non è deterministico per una determinata query.

Informazioni sui passaggi per fase

I passaggi rappresentano le operazioni più granulari che ogni worker in una fase deve eseguire, presentate come un elenco ordinato di operazioni. I passaggi sono categorizzati e alcune operazioni forniscono informazioni più dettagliate. Le categorie operative presenti nel piano di query includono:

Passaggio	Descrizione
LEGGI	Lettura di una o più colonne da una tabella di input o da uno shuffling intermedio. Nei dettagli del passaggio vengono restituite solo le prime sedici colonne lette.
SCRIVI	Una scrittura di una o più colonne in una tabella di output o in un risultato intermedio. Per gli output partizionati HASH di una fase, sono incluse anche le colonne utilizzate come chiave di partizione.
COMPUTING	Operazioni come la valutazione delle espressioni e le funzioni SQL.
FILTRO	Operatore che implementa le clausole WHERE, OMIT IF e HAVING.
ORDINA	L'operazione di ordinamento o Ordina per, include le chiavi di colonna e la direzione di ordinamento.
COLLEGA	Un'operazione di aggregazione, ad esempio GROUP BY o COUNT.
LIMIT	Operatore che implementa la clausola LIMIT.
JOIN	Un'operazione JOIN, che include il tipo di join e le colonne utilizzate.
ANALYTIC_FUNCTION	Chiamata a una funzione finestra (detta anche "funzione analitica").
USER_DEFINED_FUNCTION	Una chiamata a una funzione definita dall'utente.

Classificazione dei tempi per fase

Le fasi di query forniscono anche classificazioni dei tempi di esecuzione delle fasi, in forma relativa e assoluta. Poiché ogni fase di esecuzione rappresenta il lavoro svolto da uno o più lavoratori indipendenti, le informazioni vengono fornite in tempi medi e peggiori. Questi tempi rappresentano le prestazioni medie di tutti i worker in una fase e le prestazioni dei worker più lente nella coda lunga per una determinata classificazione. I tempi medio e massimo sono inoltre suddivisi in rappresentazioni assolute e relative. Per le statistiche basate su rapporto, i dati vengono forniti come frazione del tempo più lungo impiegato da un worker in qualsiasi segmento.

La console Google Cloud presenta le tempistiche della fase utilizzando le relative rappresentazioni.

Le informazioni sulle tempistiche della fase sono riportate come segue:

Tempi relativi	Tempistica assoluta	Rapporto numeratore
waitRatioAvg	waitMsAvg	Tempo medio trascorso dal worker in attesa di essere pianificato.
waitRatioMax	waitMsMax	Tempo trascorso dal worker più lento in attesa di pianificazione.
readRatioAvg	readMsAvg	Tempo medio trascorso dal worker nella lettura dei dati di input.
readRatioMax	readMsMax	Tempo trascorso dal worker più lento a leggere i dati di input.
computeRatioAvg	computeMsAvg	Tempo medio di utilizzo della CPU da parte del worker medio.
computeRatioMax	computeMsMax	Tempo in cui il worker più lento ha utilizzato il limite della CPU.
writeRatioAvg	writeMsAvg	Tempo medio trascorso dal worker nella scrittura dei dati di output.
writeRatioMax	writeMsMax	Tempo impiegato dal worker più lento nella scrittura dei dati di output.

Spiegazione per le query federate

Le query federate ti consentono di inviare un'istruzione di query a un'origine dati esterna utilizzando la funzione EXTERNAL_QUERY. Le query federate sono soggette alla tecnica di ottimizzazione nota come pushdown SQL e il piano di query mostra le operazioni push-down all'origine dati esterna, se presente. Ad esempio, se esegui la query seguente:

SELECT id, name
FROM EXTERNAL_QUERY("<connection>", "SELECT * FROM company")
WHERE country_code IN ('ee', 'hu') AND name like '%TV%'

Il piano di query mostrerà i seguenti passaggi di fase:

$1:id, $2:name, $3:country_code
FROM table_for_external_query_$_0(
  SELECT id, name, country_code
  FROM (
    /*native_query*/
    SELECT * FROM company
  )
  WHERE in(country_code, 'ee', 'hu')
)
WHERE and(in($3, 'ee', 'hu'), like($2, '%TV%'))
$1, $2
TO __stage00_output

In questo piano, table_for_external_query_$_0(...) rappresenta la funzione EXTERNAL_QUERY. Tra parentesi puoi vedere la query eseguita dall'origine dati esterna. Sulla base di questi dati, puoi notare che:

• Un'origine dati esterna restituisce solo 3 colonne selezionate.
• Un'origine dati esterna restituisce solo le righe per le quali country_code è 'ee' o 'hu'.
• L'operatore LIKE non è push-down e viene valutato da BigQuery.

Per confronto, se non sono presenti push push, il piano di query mostrerà i seguenti passaggi di fase:

$1:id, $2:name, $3:country_code
FROM table_for_external_query_$_0(
  SELECT id, name, description, country_code, primary_address, secondary address
  FROM (
    /*native_query*/
    SELECT * FROM company
  )
)
WHERE and(in($3, 'ee', 'hu'), like($2, '%TV%'))
$1, $2
TO __stage00_output

Questa volta un'origine dati esterna restituisce tutte le colonne e tutte le righe della tabella company e BigQuery esegue i filtri.

Metadati Timeline

La sequenza temporale delle query segnala l'avanzamento in momenti specifici, fornendo visualizzazioni istantanee dell'avanzamento complessivo della query. La cronologia è rappresentata da una serie di esempi che riportano i seguenti dettagli:

Campo	Descrizione
elapsedMs	Sono trascorsi millisecondi dall'inizio dell'esecuzione della query.
totalSlotMs	Una rappresentazione cumulativa dei millisecondi di slot utilizzati dalla query.
pendingUnits	Unità totali di lavoro pianificate e in attesa di esecuzione.
activeUnits	Unità di lavoro attive totali elaborate dai worker.
completedUnits	Unità totali di lavoro completate durante l'esecuzione di questa query.

Una query di esempio

La seguente query conta il numero di righe nel set di dati pubblico di Shakespeare e ha un secondo conteggio condizionale che limita i risultati alle righe che fanno riferimento ad "hamlet":

SELECT
  COUNT(1) as rowcount,
  COUNTIF(corpus = 'hamlet') as rowcount_hamlet
FROM `publicdata.samples.shakespeare`

Fai clic su Dettagli esecuzione per visualizzare il piano di query:

Gli indicatori colorati mostrano la tempistica relativa di tutti i passaggi in tutte le fasi.

Per scoprire di più sui passaggi delle fasi di esecuzione, fai clic su per espandere i dettagli della fase:

In questo esempio, il tempo più lungo in un segmento è stato il tempo che il singolo worker nella Fase 01 ha trascorso in attesa del completamento della Fase 00. Questo perché la fase 01 dipendeva dall'input della fase 00 e non è riuscita ad avviarsi finché la prima fase non ha scritto l'output nello shuffling intermedio.

Error Reporting

È possibile che i job di query non vadano a buon fine durante l'esecuzione. Poiché le informazioni del piano vengono aggiornate periodicamente, puoi osservare dove si è verificato il errore all'interno del grafico di esecuzione. All'interno della console Google Cloud, le fasi riuscite o non riuscite sono etichettate con un segno di spunta o un punto esclamativo accanto al nome della fase.

Per ulteriori informazioni sull'interpretazione e sulla risoluzione degli errori, consulta la guida alla risoluzione dei problemi.

Rappresentazione di esempio API

Le informazioni sul piano di query sono incorporate nelle informazioni di risposta del job e puoi recuperarle chiamando jobs.get. Ad esempio, il seguente estratto di una risposta JSON per un job che restituisce la query Amleto di esempio mostra le informazioni sul piano di query e sulla sequenza temporale.

"statistics": {
  "creationTime": "1576544129234",
  "startTime": "1576544129348",
  "endTime": "1576544129681",
  "totalBytesProcessed": "2464625",
  "query": {
    "queryPlan": [
      {
        "name": "S00: Input",
        "id": "0",
        "startMs": "1576544129436",
        "endMs": "1576544129465",
        "waitRatioAvg": 0.04,
        "waitMsAvg": "1",
        "waitRatioMax": 0.04,
        "waitMsMax": "1",
        "readRatioAvg": 0.32,
        "readMsAvg": "8",
        "readRatioMax": 0.32,
        "readMsMax": "8",
        "computeRatioAvg": 1,
        "computeMsAvg": "25",
        "computeRatioMax": 1,
        "computeMsMax": "25",
        "writeRatioAvg": 0.08,
        "writeMsAvg": "2",
        "writeRatioMax": 0.08,
        "writeMsMax": "2",
        "shuffleOutputBytes": "18",
        "shuffleOutputBytesSpilled": "0",
        "recordsRead": "164656",
        "recordsWritten": "1",
        "parallelInputs": "1",
        "completedParallelInputs": "1",
        "status": "COMPLETE",
        "steps": [
          {
            "kind": "READ",
            "substeps": [
              "$1:corpus",
              "FROM publicdata.samples.shakespeare"
            ]
          },
          {
            "kind": "AGGREGATE",
            "substeps": [
              "$20 := COUNT($30)",
              "$21 := COUNTIF($31)"
            ]
          },
          {
            "kind": "COMPUTE",
            "substeps": [
              "$30 := 1",
              "$31 := equal($1, 'hamlet')"
            ]
          },
          {
            "kind": "WRITE",
            "substeps": [
              "$20, $21",
              "TO __stage00_output"
            ]
          }
        ]
      },
      {
        "name": "S01: Output",
        "id": "1",
        "startMs": "1576544129465",
        "endMs": "1576544129480",
        "inputStages": [
          "0"
        ],
        "waitRatioAvg": 0.44,
        "waitMsAvg": "11",
        "waitRatioMax": 0.44,
        "waitMsMax": "11",
        "readRatioAvg": 0,
        "readMsAvg": "0",
        "readRatioMax": 0,
        "readMsMax": "0",
        "computeRatioAvg": 0.2,
        "computeMsAvg": "5",
        "computeRatioMax": 0.2,
        "computeMsMax": "5",
        "writeRatioAvg": 0.16,
        "writeMsAvg": "4",
        "writeRatioMax": 0.16,
        "writeMsMax": "4",
        "shuffleOutputBytes": "17",
        "shuffleOutputBytesSpilled": "0",
        "recordsRead": "1",
        "recordsWritten": "1",
        "parallelInputs": "1",
        "completedParallelInputs": "1",
        "status": "COMPLETE",
        "steps": [
          {
            "kind": "READ",
            "substeps": [
              "$20, $21",
              "FROM __stage00_output"
            ]
          },
          {
            "kind": "AGGREGATE",
            "substeps": [
              "$10 := SUM_OF_COUNTS($20)",
              "$11 := SUM_OF_COUNTS($21)"
            ]
          },
          {
            "kind": "WRITE",
            "substeps": [
              "$10, $11",
              "TO __stage01_output"
            ]
          }
        ]
      }
    ],
    "estimatedBytesProcessed": "2464625",
    "timeline": [
      {
        "elapsedMs": "304",
        "totalSlotMs": "50",
        "pendingUnits": "0",
        "completedUnits": "2"
      }
    ],
    "totalPartitionsProcessed": "0",
    "totalBytesProcessed": "2464625",
    "totalBytesBilled": "10485760",
    "billingTier": 1,
    "totalSlotMs": "50",
    "cacheHit": false,
    "referencedTables": [
      {
        "projectId": "publicdata",
        "datasetId": "samples",
        "tableId": "shakespeare"
      }
    ],
    "statementType": "SELECT"
  },
  "totalSlotMs": "50"
},

Utilizzo delle informazioni sull'esecuzione

I piani di query BigQuery forniscono informazioni sul modo in cui il servizio esegue le query, ma la natura gestita del servizio limita l'utilizzo diretto di alcuni dettagli. Molte ottimizzazioni vengono eseguite automaticamente tramite il servizio, che può differire da altri ambienti in cui l'ottimizzazione, il provisioning e il monitoraggio possono richiedere personale dedicato e competente.

Per tecniche specifiche in grado di migliorare l'esecuzione e le prestazioni delle query, consulta la documentazione sulle best practice. Le statistiche relative al piano di query e alle tempistiche possono aiutarti a capire se determinate fasi dominano l'utilizzo delle risorse. Ad esempio, una fase JOIN che genera molte più righe di output che righe di input può indicare un'opportunità di filtrare in precedenza nella query.

Inoltre, le informazioni sulla cronologia possono essere utili per identificare se una determinata query è lenta in isolamento o a causa degli effetti di altre query che si contendono le stesse risorse. Se noti che il numero di unità attive rimane limitato per tutta la durata della query, ma la quantità di unità di lavoro in coda rimane elevata, è possibile che si verifichino casi in cui la riduzione del numero di query in parallelo può migliorare notevolmente il tempo di esecuzione complessivo di determinate query.