Risolvere i problemi relativi alle query

Questo documento ha lo scopo di aiutarti a risolvere i problemi comuni relativi all'esecuzione di query, ad esempio identificare i motivi delle query lente o fornire passaggi di risoluzione per gli errori comuni restituiti dalle query non riuscite.

Risolvere i problemi relativi alle query lente

Quando risolvi i problemi di prestazioni lente delle query, considera le seguenti cause comuni:

  1. Controlla la pagina Google Cloud Service Health per interruzioni note del servizio BigQuery che potrebbero influire sulle prestazioni delle query.

  2. Esamina la cronologia del job per la query nella pagina dei dettagli del job per vedere quanto tempo è stato necessario per l'esecuzione di ogni fase della query.

    • Se la maggior parte del tempo trascorso è dovuta a tempi di creazione lunghi, contatta l'assistenza clienti Google Cloud per ricevere assistenza.

    • Se la maggior parte del tempo trascorso è dovuta a tempi di esecuzione lunghi, esamina gli insight sulle prestazioni delle query. Gli insight sulle prestazioni delle query possono informarti se la query è stata eseguita più a lungo del tempo di esecuzione medio e suggerire possibili cause. Le possibili cause potrebbero includere la contesa degli slot di query o una quota di shuffle insufficiente. Per ulteriori informazioni su ogni problema di prestazioni delle query e sulle possibili soluzioni, consulta Interpretare gli approfondimenti sulle prestazioni delle query.

  3. Controlla il campo finalExecutionDurationMs in JobStatistics per il job relativo alla query. La query potrebbe essere stata ritentata. Il campo finalExecutionDurationMs contiene la durata in millisecondi dell'esecuzione dell'ultimo tentativo di questo job.

  4. Esamina i byte elaborati nella pagina dei dettagli del job di query per verificare se sono superiori al previsto. Puoi farlo confrontando il numero di byte elaborati dalla query corrente con un altro job di query completato in un periodo di tempo accettabile. Se si verifica una grande discrepanza tra i byte elaborati tra le due query, è possibile che la query sia stata lenta a causa di un grande volume di dati. Per informazioni sull'ottimizzazione delle query per gestire grandi volumi di dati, vedi Ottimizzazione del calcolo delle query.

    Puoi anche identificare le query nel tuo progetto che elaborano una grande quantità di dati cercando le query più costose utilizzando la visualizzazione INFORMATION_SCHEMA.JOBS.

Se ancora non riesci a trovare il motivo per cui le prestazioni delle query sono più lente del previsto, contatta l'assistenza clienti Google Cloud per ricevere assistenza.

Risoluzione dello schema Avro

Error string: Cannot skip stream

Questo errore può verificarsi durante il caricamento di più file Avro con schemi diversi, con conseguente problema di risoluzione dello schema e interruzione del job di importazione in un file casuale.

Per risolvere questo errore, assicurati che l'ultimo file in ordine alfabetico nel job di caricamento contenga il superset (unione) degli schemi diversi. Questo è un requisito basato su come Avro gestisce la risoluzione dello schema.

Query simultanee in conflitto

Error string: Concurrent jobs in the same session are not allowed

Questo errore può verificarsi quando vengono eseguite più query contemporaneamente in una sessione, il che non è supportato. Consulta le limitazioni della sessione.

Istruzioni DML in conflitto

Error string: Could not serialize access to table due to concurrent update

Questo errore può verificarsi quando le istruzioni DML (Data Manipulation Language) di mutazione che vengono eseguite contemporaneamente sulla stessa tabella sono in conflitto tra loro oppure quando la tabella viene troncata durante un'istruzione DML di mutazione. Per ulteriori informazioni, consulta Conflitti di istruzioni DML.

Per risolvere questo errore, esegui operazioni DML che interessano una singola tabella in modo che non si sovrappongano.

Sottoquery correlate

Error string: Correlated subqueries that reference other tables are not supported unless they can be de-correlated

Questo errore può verificarsi quando la query contiene una sottoquery che fa riferimento a una colonna esterna alla sottoquery, chiamata colonna di correlazione. La sottoquery correlata viene valutata utilizzando una strategia di esecuzione nidificata inefficiente, in cui la sottoquery viene valutata per ogni riga della query esterna che produce le colonne di correlazione. A volte, BigQuery può riscrivere internamente le query con sottoquery correlate in modo che vengano eseguite in modo più efficiente. L'errore relativo alle sottoquery correlate si verifica quando BigQuery non riesce a ottimizzare sufficientemente la query.

Per risolvere questo errore, prova quanto segue:

  • Rimuovi eventuali clausole ORDER BY, LIMIT, EXISTS, NOT EXISTS o IN dalla sottoquery.
  • Utilizza una query con più istruzioni per creare una tabella temporanea a cui fare riferimento nella sottoquery.
  • Riscrivi la query in modo che utilizzi CROSS JOIN.

Autorizzazioni di controllo dell'accesso a livello di colonna insufficienti

Error string: Requires raw access permissions on the read columns to execute the DML statements

Questo errore si verifica quando tenti un'istruzione DML DELETE, UPDATE o MERGE senza disporre dell'autorizzazione Lettore granulare per le colonne scansionate che utilizzano il controllo dell'accesso a livello di colonna per limitare l'accesso a livello di colonna. Per ulteriori informazioni, consulta Impatto sulle scritture del controllo dell'accesso a livello di colonna.

Credenziali non valide per le query pianificate

Stringhe di errore:

  • Error code: INVALID_USERID
  • Error code 5: Authentication failure: User Id not found
  • PERMISSION_DENIED: BigQuery: Permission denied while getting Drive credentials

Questo errore può verificarsi quando una query pianificata non va a buon fine a causa di credenziali obsolete, in particolare quando si esegue una query sui dati di Google Drive.

Per risolvere questo errore:

Credenziali del account di servizio non valide

Error string: HttpError 403 when requesting returned: The caller does not have permission

Questo errore potrebbe essere visualizzato quando tenti di configurare una query pianificata con un account di serviziot. Per risolvere questo errore, consulta i passaggi per la risoluzione dei problemi nella sezione Problemi di autorizzazione e permessi.

Ora dello snapshot non valida

Error string: Invalid snapshot time

Questo errore può verificarsi quando si tenta di eseguire una query sui dati storici al di fuori della finestra di spostamento nel tempo per il set di dati. Per risolvere questo errore, modifica la query in modo da accedere ai dati storici all'interno della finestra di spostamento nel tempo del set di dati.

Questo errore può essere visualizzato anche se una delle tabelle utilizzate nella query viene eliminata e ricreata dopo l'avvio della query. Controlla se esiste una query o un'applicazione pianificata che esegue questa operazione e che è stata eseguita contemporaneamente alla query non riuscita. In questo caso, prova a spostare il processo che esegue l'operazione di eliminazione e ricreazione in un momento che non sia in conflitto con le query che leggono la tabella.

Il job esiste già

Error string: Already Exists: Job <job name>

Questo errore può verificarsi per i job di query che devono valutare array di grandi dimensioni, in modo che la creazione di un job di query richieda più tempo del solito. Ad esempio, una query con una clausola WHERE come WHERE column IN (<2000+ elements array>).

Per risolvere questo errore:

Job non trovato

Error string: Job not found

Questo errore può verificarsi in risposta a una chiamata getQueryResults, in cui non è specificato alcun valore per il campo location. In questo caso, riprova a chiamare e fornisci un valore location.

Per ulteriori informazioni, consulta Evitare più valutazioni delle stesse espressioni di tabella comuni (CTE).

Posizione non trovata

Error string: Dataset [project_id]:[dataset_id] was not found in location [region]

Questo errore viene restituito quando fai riferimento a una risorsa del set di dati che non esiste o quando la località nella richiesta non corrisponde a quella del set di dati.

Per risolvere il problema, specifica la posizione del set di dati nella query o verifica che il set di dati sia disponibile nella stessa posizione.

La query supera il limite di tempo di esecuzione

Error string: Query fails due to reaching the execution time limit

Se la query raggiunge il limite di tempo di esecuzione delle query, controlla il tempo di esecuzione delle esecuzioni precedenti della query eseguendo una query sulla vista INFORMATION_SCHEMA.JOBS con una query simile all'esempio seguente:

SELECT TIMESTAMP_DIFF(end_time, start_time, SECOND) AS runtime_in_seconds
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE statement_type = 'QUERY'
AND query = "my query string";

Se le esecuzioni precedenti della query hanno richiesto molto meno tempo, utilizza gli approfondimenti sulle prestazioni delle query per determinare e risolvere il problema sottostante.

La risposta alla query è troppo grande

Error string: responseTooLarge

Questo errore si verifica quando i risultati della query sono maggiori della dimensione massima della risposta.

Per risolvere questo errore, segui le indicazioni fornite per il messaggio di errore responseTooLarge.

Troppe istruzioni DML

Error string: Too many DML statements outstanding against <table-name>, limit is 20

Questo errore si verifica quando superi il limite di 20 istruzioni DML con stato PENDING in una coda per una singola tabella. Questo errore si verifica in genere quando invii job DML a una singola tabella più velocemente di quanto BigQuery possa elaborare.

Una possibile soluzione è raggruppare più operazioni DML più piccole in un numero inferiore di job più grandi, ad esempio raggruppando gli aggiornamenti e gli inserimenti. Quando raggruppi job più piccoli in job più grandi, il costo di esecuzione dei job più grandi viene ammortizzato e l'esecuzione è più rapida. Il consolidamento delle istruzioni DML che interessano gli stessi dati migliora in genere l'efficienza dei job DML ed è meno probabile che superi il limite della quota delle dimensioni della coda. Per saperne di più sull'ottimizzazione delle operazioni DML, consulta Evitare istruzioni DML che aggiornano o inseriscono singole righe.

Altre soluzioni per migliorare l'efficienza del linguaggio DML potrebbero essere il partizionamento o il clustering delle tabelle. Per saperne di più, consulta le best practice.

L'utente non dispone dell'autorizzazione

Stringhe di errore:

  • Access Denied: Project [project_id]: User does not have bigquery.jobs.create permission in project [project_id].
  • User does not have permission to query table project-id:dataset.table.
  • Access Denied: User does not have permission to query table or perhaps it does not exist.

Questi errori possono verificarsi quando esegui una query senza l'autorizzazione bigquery.jobs.create sul progetto da cui la esegui, indipendentemente dalle tue autorizzazioni sul progetto che contiene i dati.

Potresti ricevere questi errori anche se il tuo account di servizio, utente o gruppo non dispone dell'autorizzazione bigquery.tables.getData su tutte le tabelle e le viste a cui fa riferimento la query. Per ulteriori informazioni sulle autorizzazioni richieste per l'esecuzione di una query, consulta Ruoli richiesti.

Questi errori possono verificarsi anche se la tabella non esiste nella regione sottoposta a query, ad esempio asia-south1. Puoi verificare la regione esaminando la posizione del set di dati.

Quando risolvi questi errori, tieni presente quanto segue:

  • Service account: i service account devono disporre dell'autorizzazione bigquery.jobs.create sul progetto da cui vengono eseguiti e dell'autorizzazione bigquery.tables.getData su tutte le tabelle e le viste a cui fa riferimento la query.

  • Ruoli personalizzati: i ruoli IAM personalizzati devono includere esplicitamente l'autorizzazione bigquery.jobs.create nel ruolo pertinente e devono disporre dell'autorizzazione bigquery.tables.getData su tutte le tabelle e le viste a cui fa riferimento la query.

  • Set di dati condivisi: quando lavori con set di dati condivisi in un progetto separato, potresti comunque aver bisogno dell'autorizzazione bigquery.jobs.create nel progetto per eseguire query o job nel set di dati.

Per concedere l'autorizzazione ad accedere a una tabella o a una vista, vedi Concedere l'accesso a una tabella o a una vista.

Problemi relativi al superamento delle risorse

I seguenti problemi si verificano quando BigQuery non dispone di risorse sufficienti per completare la query.

La query supera le risorse della CPU

Error string: Query exceeded resource limits

Questo errore si verifica quando le query on demand utilizzano troppa CPU rispetto alla quantità di dati scansionati. Per informazioni su come risolvere questi problemi, vedi Risolvere i problemi relativi al superamento delle risorse.

La query supera le risorse di memoria

Error string: Resources exceeded during query execution: The query could not be executed in the allotted memory

Per le istruzioni SELECT, questo errore si verifica quando la query utilizza troppe risorse. Per risolvere questo errore, vedi Risolvere i problemi relativi all'esaurimento delle risorse.

Spazio esaurito nello stack

Error string: Out of stack space due to deeply nested query expression during query resolution.

Questo errore può verificarsi quando una query contiene troppe chiamate di funzioni nidificate. A volte, durante l'analisi, alcune parti di una query vengono tradotte in chiamate di funzioni. Ad esempio, un'espressione con operatori di concatenazione ripetuti, come A || B || C || ..., diventa CONCAT(A, CONCAT(B, CONCAT(C, ...))).

Per risolvere questo errore, riscrivi la query in modo da ridurre il livello di nidificazione.

Risorse superate durante l'esecuzione della query

Error string: Resources exceeded during query execution: The query could not be executed in the allotted memory. Peak usage: [percentage]% of limit. Top memory consumer(s): ORDER BY operations.

Ciò può accadere con le query ORDER BY ... LIMIT ... OFFSET .... A causa dei dettagli di implementazione, l'ordinamento potrebbe avvenire su una singola unità di calcolo, che potrebbe esaurire la memoria se deve elaborare troppe righe prima dell'applicazione di LIMIT e OFFSET, in particolare con un OFFSET di grandi dimensioni.

Per risolvere questo errore, evita valori di OFFSET elevati nelle query ORDER BY… LIMIT. In alternativa, utilizza la funzione finestra scalabile ROW_NUMBER() per assegnare ranghi in base all'ordine scelto, quindi filtra questi ranghi in una clausola WHERE. Ad esempio:

SELECT ...
FROM (
  SELECT ROW_NUMBER() OVER (ORDER BY ...) AS rn
  FROM ...
)
WHERE rn > @start_index AND rn <= @page_size + @start_index  -- note that row_number() starts with 1

La query supera le risorse di shuffling

Error string: Resources exceeded during query execution: Your project or organization exceeded the maximum disk and memory limit available for shuffle operations

Questo errore si verifica quando una query non può accedere a risorse di shuffle sufficienti.

Per risolvere questo errore, esegui il provisioning di più slot o riduci la quantità di dati elaborati dalla query. Per ulteriori informazioni su come farlo, vedi Quota di shuffle insufficiente.

Per ulteriori informazioni su come risolvere questi problemi, vedi Risolvere i problemi relativi all'esaurimento delle risorse.

La query è troppo complessa

Error string: Resources exceeded during query execution: Not enough resources for query planning - too many subqueries or query is too complex

Questo errore si verifica quando una query è troppo complessa. Le cause principali della complessità sono:

  • Clausole WITH nidificate in profondità o utilizzate ripetutamente.
  • Visualizzazioni nidificate in profondità o utilizzate ripetutamente.
  • Utilizzo ripetuto dell'operatore UNION ALL.

Per risolvere questo errore, prova le seguenti opzioni:

  • Dividi la query in più query, quindi utilizza il linguaggio procedurale per eseguirle in sequenza con uno stato condiviso.
  • Utilizza tabelle temporanee anziché clausole WITH.
  • Riscrivi la query per ridurre il numero di oggetti e confronti a cui viene fatto riferimento.

Puoi monitorare in modo proattivo le query che si avvicinano al limite di complessità utilizzando il campo query_info.resource_warning nella INFORMATION_SCHEMA.JOBS vista. L'esempio seguente restituisce query con un utilizzo elevato delle risorse per gli ultimi tre giorni:

SELECT
  ANY_VALUE(query) AS query,
  MAX(query_info.resource_warning) AS resource_warning
FROM
  <your_project_id>.`region-us`.INFORMATION_SCHEMA.JOBS
WHERE
  creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 DAY)
  AND query_info.resource_warning IS NOT NULL
GROUP BY
  query_info.query_hashes.normalized_literals
LIMIT
  1000

Per ulteriori informazioni su come risolvere questi problemi, vedi Risolvere i problemi relativi all'esaurimento delle risorse.

Risolvere i problemi relativi al superamento delle risorse

Per i job di query:

Per ottimizzare le query, prova a svolgere i seguenti passaggi:

  • Prova a rimuovere una clausola ORDER BY.
  • Se la query utilizza JOIN, assicurati che la tabella più grande si trovi sul lato sinistro della clausola. Assicurati inoltre che i dati non contengano chiavi di join duplicate.
  • Se la query utilizza FLATTEN, determina se è necessario per il tuo caso d'uso. Per maggiori informazioni, vedi dati nidificati e ripetuti.
  • Se la query utilizza EXACT_COUNT_DISTINCT, valuta la possibilità di utilizzare COUNT(DISTINCT).
  • Se la query utilizza COUNT(DISTINCT <value>, <n>) con un valore <n> elevato, valuta l'utilizzo di GROUP BY in alternativa. Per ulteriori informazioni, vedi COUNT(DISTINCT).
  • Se la query utilizza UNIQUE, valuta la possibilità di utilizzare GROUP BY o una funzione finestra all'interno di una sottoquery.
  • Se la query materializza molte righe utilizzando una clausola LIMIT, valuta la possibilità di filtrare in base a un'altra colonna, ad esempio ROW_NUMBER(), o di rimuovere completamente la clausola LIMIT per consentire la parallelizzazione della scrittura.
  • Se la query utilizzava viste nidificate in profondità e una clausola WITH, ciò può causare una crescita esponenziale della complessità, raggiungendo così i limiti.
  • Non sostituire le tabelle temporanee con clausole WITH. La clausola potrebbe dover essere ricalcolata più volte, il che può rendere la query complessa e quindi lenta. La persistenza dei risultati intermedi nelle tabelle temporanee aiuta a gestire la complessità.
  • Evita di utilizzare query UNION ALL.

Per maggiori informazioni, consulta le seguenti risorse:

Per i job di caricamento:

Se carichi file Avro o Parquet, riduci le dimensioni delle righe nei file. Controlla le limitazioni specifiche delle dimensioni per il formato del file che stai caricando:

Se ricevi questo errore durante il caricamento dei file ORC, contatta l'assistenza.

Per l'API Storage:

Error string: Stream memory usage exceeded

Durante una chiamata all'API Storage Read ReadRows, alcuni stream con un utilizzo elevato della memoria potrebbero generare un errore RESOURCE_EXHAUSTED con questo messaggio. Ciò può accadere durante la lettura di tabelle ampie o con uno schema complesso. Come soluzione, riduci le dimensioni della riga dei risultati selezionando meno colonne da leggere (utilizzando il parametro selected_fields) o semplificando lo schema della tabella.

Risolvere i problemi di connettività

Le sezioni seguenti descrivono come risolvere i problemi di connettività quando tenti di interagire con BigQuery:

Inserire nella lista consentita Google DNS

Utilizza lo strumento Google IP Dig per risolvere l'endpoint DNS di BigQuery bigquery.googleapis.com in un singolo IP del record "A". Assicurati che questo IP non sia bloccato nelle impostazioni del firewall.

In generale, consigliamo di inserire i nomi DNS di Google nella lista consentita. Gli intervalli IP condivisi nei file https://www.gstatic.com/ipranges/goog.json e https://www.gstatic.com/ipranges/cloud.json cambiano spesso, pertanto ti consigliamo di inserire nella lista consentita i nomi DNS di Google. Ecco un elenco di nomi DNS comuni che ti consigliamo di aggiungere alla lista consentita:

  • *.1e100.net
  • *.google.com
  • *.gstatic.com
  • *.googleapis.com
  • *.googleusercontent.com
  • *.appspot.com
  • *.gvt1.com

Identifica il proxy o il firewall che elimina i pacchetti

Per identificare tutti gli hop dei pacchetti tra il client e Google Front End (GFE), esegui un comando traceroute sulla macchina client che potrebbe evidenziare il server che rilascia i pacchetti indirizzati a GFE. Ecco un comando traceroute di esempio:

traceroute -T -p 443 bigquery.googleapis.com

È anche possibile identificare gli hop dei pacchetti per indirizzi IP GFE specifici se il problema è correlato a un indirizzo IP particolare:

traceroute -T -p 443 142.250.178.138

Se si verifica un problema di timeout lato Google, vedrai la richiesta arrivare fino al GFE.

Se noti che i pacchetti non raggiungono mai GFE, contatta l'amministratore di rete per risolvere il problema.

Generare un file PCAP e analizzare il firewall o il proxy

Genera un file di acquisizione pacchetti (PCAP) e analizzalo per assicurarti che il firewall o il proxy non filtri i pacchetti verso gli IP di Google e che consenta ai pacchetti di raggiungere GFE.

Ecco un comando di esempio che può essere eseguito con lo strumento tcpdump:

tcpdump -s 0 -w debug.pcap -K -n host bigquery.googleapis.com

Configurare i tentativi per problemi di connettività intermittenti

Esistono situazioni in cui i bilanciatori del carico GFE potrebbero interrompere le connessioni da un IP client, ad esempio se rilevano pattern di traffico DDOS o se l'istanza del bilanciatore del carico viene ridimensionata, il che potrebbe comportare il riciclo dell'IP dell'endpoint. Se i bilanciatori del carico GFE interrompono la connessione, il client deve intercettare la richiesta scaduta e riprovare a inviare la richiesta all'endpoint DNS. Assicurati di non utilizzare lo stesso indirizzo IP finché la richiesta non va a buon fine, perché l'indirizzo IP potrebbe essere cambiato.

Se hai identificato un problema con timeout coerenti lato Google in cui i tentativi non sono utili, contatta l'assistenza clienti Google Cloud e assicurati di includere un nuovo file PCAP generato eseguendo uno strumento di acquisizione dei pacchetti come tcpdump.

Passaggi successivi