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 la procedura 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, tieni conto delle seguenti cause comuni:

  1. Controlla la pagina Integrità del servizio Google Cloud per verificare la presenza di interruzioni del servizio BigQuery note che potrebbero influire sul rendimento delle query.

  2. Esamina la sequenza temporale del job per la tua query nella pagina dei dettagli del job per vedere il tempo impiegato 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 approfondimenti sul rendimento delle query. Gli insight sulle prestazioni delle query possono informarti se la query ha richiesto più tempo rispetto al tempo di esecuzione medio e suggerire possibili cause. Le possibili cause potrebbero includere la contesa degli slot di query o una quota di smistamento insufficiente. Per ulteriori informazioni su ciascun problema di prestazioni delle query e sulle possibili risoluzioni, consulta Interpreta gli approfondimenti sul rendimento delle query.

  3. Esamina i byte elaborati nella pagina dei dettagli del job di query per verificare se sono superiori a quanto previsto. Per farlo, puoi confrontare il numero di byte elaborati dalla query corrente con un altro job di query completato in un periodo di tempo accettabile. Se esiste una grande discrepanza di byte elaborati tra le due query, è possibile che la query sia stata lenta a causa di un volume elevato di dati. Per informazioni su come ottimizzare le query per gestire grandi volumi di dati, consulta Ottimizzare il 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 non riesci ancora a trovare il motivo del rendimento delle query inferiore alle aspettative, contatta l'assistenza clienti Google Cloud per ricevere assistenza.

Risoluzione dello schema Avro

Stringa di errore: Cannot skip stream

Questo errore può verificarsi quando vengono caricati più file Avro con schemi diversi, provocando un problema di risoluzione dello schema e causando il fallimento del job di importazione in un file random.

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

Query concorrenti in conflitto

Stringa di errore: Concurrent jobs in the same session are not allowed

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

Istruzioni DML in conflitto

Stringa di errore: Could not serialize access to table due to concurrent update

Questo errore può verificarsi quando le istruzioni DML (Data Manipulation Language) con mutazioni eseguite contemporaneamente nella stessa tabella sono in conflitto tra loro o quando la tabella viene troncata durante un'istruzione DML con mutazioni. 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

Stringa di errore: 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 delle 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 subquery.
  • Utilizza una query con più istruzioni per creare una tabella temporanea a cui fare riferimento nella sottoquery.
  • Riscrivi la query in modo da utilizzare un CROSS JOIN.

Autorizzazioni di controllo dell'accesso a livello di colonna insufficienti

Stringa di errore: Requires raw access permissions on the read columns to execute the DML statements

Questo errore si verifica quando provi a eseguire un'istruzione DML DELETE, UPDATE o MERGE senza disporre dell'autorizzazione Lettore granulare per le colonne sottoposte a scansione che utilizzano controllo dell'accesso a livello di colonna per limitare l'accesso a livello di colonna. Per ulteriori informazioni, consulta Impatto sulle scritture dal 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 esegui query sui dati di Google Drive.

Per risolvere questo errore:

Credenziali dell'account di servizio non valide

Stringa di errore: HttpError 403 when requesting returned: The caller does not have permission

Questo errore potrebbe verificarsi quando provi a configurare una query pianificata con un account di servizio. Per risolvere questo errore, consulta i passaggi per la risoluzione dei problemi descritti nella sezione Problemi di autorizzazione e autorizzazioni.

Ora istantanea non valida

Stringa di errore: Invalid snapshot time

Questo errore può verificarsi quando si tenta di eseguire query sui dati storici che non rientrano nella finestra di viaggio nel tempo per il set di dati. Per risolvere questo errore, modifica la query per accedere ai dati storici all'interno della finestra di viaggio nel tempo del set di dati.

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

Il job esiste già

Stringa di errore: 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 della media. Ad esempio, una query con una clausola WHERE come WHERE column IN (<2000+ elements array>).

Per risolvere questo errore:

Job non trovato

Stringa di errore: Job not found

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

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

Posizione non trovata

Stringa di errore: Dataset [project_id]:[dataset_id] was not found in location [region]

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

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

La query supera il limite di tempo di esecuzione

Stringa di errore: 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 visualizzazione INFORMATION_SCHEMA.JOBS con una query simile al seguente esempio:

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 sul rendimento delle query per determinare e risolvere il problema sottostante.

La risposta alla query è troppo grande

Stringa di errore: responseTooLarge

Questo errore si verifica quando i risultati della query sono più grandi della dimensione massima della risposta.

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

Troppe istruzioni DML

Stringa di errore: 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 job più grandi, ma meno numerosi, ad esempio raggruppando gli aggiornamenti e gli inserimenti. Quando raggruppi i job più piccoli in altri 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 in genere migliora l'efficienza dei job DML ed è meno probabile che superi il limite di quota delle dimensioni della coda. Per ulteriori informazioni sull'ottimizzazione delle operazioni DML, consulta Evitare istruzioni DML che aggiornano o inseriscono singole righe.

Altre soluzioni per migliorare l'efficienza della DML potrebbero essere la partizione o il clustering delle tabelle. Per ulteriori informazioni, 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.

Questo errore si verifica quando esegui una query senza l'autorizzazione bigquery.jobs.create per il progetto da cui esegui la query, indipendentemente dalle autorizzazioni per il progetto che contiene i dati. Devi inoltre disporre dell'autorizzazione bigquery.tables.getData su tutte le tabelle e le viste a cui fa riferimento la query.

Questo errore può verificarsi anche se la tabella non esiste nella regione interrogata, come asia-south1. Per eseguire query sulle visualizzazioni, devi disporre di questa autorizzazione anche su tutte le tabelle e le visualizzazioni sottostanti. Per ulteriori informazioni sulle autorizzazioni richieste, consulta Eseguire una query.

Per risolvere questo errore, tieni presente quanto segue:

  • Account di servizio: gli account di servizio devono disporre dell'autorizzazione bigquery.jobs.create per il progetto da cui vengono eseguiti.

  • Ruoli personalizzati: i ruoli IAM personalizzati devono avere l'autorizzazionebigquery.jobs.create inclusa esplicitamente nel ruolo pertinente.

  • 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 in quel set di dati.

Per concedere l'autorizzazione ad accedere alla tabella

Per concedere l'autorizzazione di accesso a una tabella a un principale:

  1. Vai alla pagina BigQuery.

    Vai a BigQuery

  2. In Explorer, vai alla tabella a cui devi accedere, seleziona Visualizza azioni, seleziona Condividi e poi fai clic su Gestisci autorizzazioni.

  3. In Aggiungi entità, inserisci il nome degli utenti, dei gruppi, dei domini o degli account di servizio che vuoi aggiungere.

  4. In Assegna i ruoli, seleziona l'autorizzazione bigquery.jobs.create. In alternativa, puoi concedere il ruolo roles/bigquery.jobUser nel progetto da cui viene eseguita la query per ottenere le autorizzazioni necessarie.

  5. Fai clic su Salva.

Problemi relativi alle risorse superate

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

La query supera le risorse della CPU

Stringa di errore: Query exceeded resource limits

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

La query supera le risorse di memoria

Stringa di errore: Resources exceeded during query execution: The query could not be executed in the allotted memory

Per le istruzioneSELECT, questo errore si verifica quando la query utilizza troppe risorse. Per risolvere questo errore, consulta Risolvere i problemi relativi al superamento delle risorse.

La query supera le risorse di ordinamento casuale

Stringa di errore: 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 riesce ad accedere a risorse di ordinamento casuale 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 eseguire questa operazione, consulta Quota di smistamento insufficiente.

Per ulteriori informazioni su come risolvere questi problemi, vedi Risolvere i problemi relativi al superamento delle risorse.

La query è troppo complessa

Stringa di errore: 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 modo molto approfondito o utilizzate ripetutamente.
  • Visualizzazioni nidificate in modo molto approfondito o utilizzate ripetutamente.
  • Utilizzo ripetuto dell'operatore UNION ALL.

Per risolvere questo errore, prova le seguenti opzioni:

  • Suddividi la query in più query, quindi utilizza un linguaggio procedurale per eseguirle in sequenza con 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 stanno avvicinando al limite di complessità utilizzando il campo query_info.resource_warning nella vista INFORMATION_SCHEMA.JOBS. L'esempio seguente restituisce query con un elevato utilizzo 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 al superamento 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.
  • Se la query utilizza FLATTEN, determina se è necessaria per il tuo caso d'uso. Per ulteriori informazioni, consulta 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 la possibilità di utilizzare GROUP BY. 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 un sottoselettore.
  • Se la query materializza molte righe utilizzando una clausola LIMIT, valuta la possibilità di applicare un filtro su un'altra colonna, ad esempio ROW_NUMBER(), o di rimuovere del tutto la clausola LIMIT per consentire la parallizzazione delle scritture.
  • Se la query utilizza viste nidificate in modo molto approfondito e una clausola WITH, può verificarsi un aumento esponenziale della complessità, raggiungendo così i limiti.
  • Non sostituire le tabelle temporanee con clausole WITH. La clausola potrebbe dover essere calcolata più volte, il che può rendere la query complessa e quindi lenta. La persistenza dei risultati intermedi in tabelle temporanee aiuta invece 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. Verifica le limitazioni specifiche relative alle 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:

Stringa di errore: Stream memory usage exceeded

Durante una chiamata ReadRows dell'API Storage Read, alcuni stream con un elevato utilizzo della memoria potrebbero generare un errore RESOURCE_EXHAUSTED con questo messaggio. Ciò può accadere durante la lettura da tabelle ampie o con uno schema complesso. Come soluzione, riduci le dimensioni delle righe del risultato 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 si tenta di interagire con BigQuery:

Inserire nella lista consentita Google DNS

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

In generale, consigliamo di inserire nella lista consentita i nomi DNS di Google. Gli intervalli IP condivisi nei file https://www.gstatic.com/ipranges/goog.json e https://www.gstatic.com/ipranges/cloud.json cambiano spesso, pertanto consigliamo di inserire nella lista consentita i nomi DNS di Google. Ecco un elenco di nomi DNS comuni che 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 perde pacchetti

Per identificare tutti i salti dei pacchetti tra il client e il Google Front End (GFE), esegui un comando traceroute sulla tua macchina client che possa evidenziare il server che sta ignorando i pacchetti diretti al GFE. Ecco un comando traceroute di esempio:

traceroute -T -p 443 bigquery.googleapis.com

È anche possibile identificare i salti dei pacchetti per indirizzi IP GFE specifici se il problema è correlato a un determinato indirizzo IP:

traceroute -T -p 443 142.250.178.138

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

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

Genera un file PCAP e analizza il firewall o il proxy

Genera un file di acquisizione dei pacchetti (PCAP) e analizzalo per assicurarti che il firewall o il proxy non filtri i pacchetti destinati agli IP di Google e consenta ai pacchetti di raggiungere la 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 di ripetizione per problemi di connettività intermittenti

Esistono situazioni in cui i bilanciatori del carico GFE potrebbero interrompere le connessioni da un indirizzo IP client, ad esempio se rilevano pattern di traffico DDOS o se l'istanza del bilanciatore del carico viene ridotta, il che potrebbe comportare il riciclo dell'IP endpoint. Se i bilanciatori del carico GFE interrompono la connessione, il client deve rilevare la richiesta con timeout e riprovare a inviarla 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 rilevato un problema con timeout costanti lato Google in cui i tentativi di nuovo non sono utili, contatta l'assistenza clienti di Cloud e assicurati di includere un file PCAP aggiornato generato eseguendo uno strumento di acquisizione dei pacchetti come tcpdump.

Passaggi successivi