Affidabilità: dati delle query

Questo documento spiega come creare soluzioni affidabili con BigQuery e spiega come eseguire query sui dati in modo affidabile nel tuo ambiente BigQuery.

Informazioni sugli slot

Quando BigQuery esegue un job di query, converte l'istruzione SQL dichiarativa inviata in un grafico di esecuzione, suddiviso in una serie di fasi di query, che a loro volta sono composte da set di passaggi di esecuzione più granulari. Per eseguire queste query, BigQuery utilizza un'architettura parallela molto distribuita e le fasi modellano le unità di lavoro che molti potenziali worker possono eseguire in parallelo. Le risorse di questi worker sono chiamate slot.

Nel più semplice dei termini, uno slot BigQuery è una CPU virtuale utilizzata da BigQuery per eseguire query SQL. Per ulteriori informazioni sugli slot e sul loro funzionamento, consulta la sezione Informazioni sugli slot.

BigQuery è in grado di raggiungere uno SLA del 99,99% per le query fornendo una capacità ridondante in due zone separate. Questa distribuzione protegge i clienti da errori a livello di zona.

Le risorse di calcolo utilizzate per l'esecuzione delle query vengono acquistate in base a un modello di prezzi a richiesta o a costo fisso. Con il modello on demand, le risorse di calcolo vengono addebitate in base al numero di byte elaborati dalle query inviate. Con il modello a costo fisso acquisti una quantità dedicata di capacità di elaborazione delle query che fornisce un modello di costo stabile.

Modello di analisi on demand

Con il modello di prezzi on demand di BigQuery, l'addebito viene effettuato esclusivamente in base all'utilizzo in base alla quantità di byte elaborati, i cosiddetti "byte letti", dalle query inviate. I progetti che utilizzano il modello di prezzi on demand di BigQuery hanno accesso a una capacità predefinita di slot, che in genere è più che sufficiente, e sono soggetti a quote per slot per progetto.

Modello a costo fisso

I clienti che preferiscono un costo fisso per le query anziché pagare per il modello di prezzi on demand devono prendere in considerazione il modello di prezzi a costo fisso. Se hai optato per il modello di prezzi a costo fisso, acquisti una capacità di elaborazione delle query dedicata, misurata in slot BigQuery. Le tue query consumano questa capacità e non ti saranno fatturati i byte elaborati.

La struttura a costo fisso consente ai clienti di acquistare slot con durate di impegno diverse (annuale, mensile o senza impegno), assegnare la priorità agli slot per progetti o cartelle specificati con prenotazioni di slot e condividere slot inattivi tra più prenotazioni. Per ulteriori informazioni, consulta la sezione Introduzione alle prenotazioni.

Il modello a costo fisso imposta in modo esplicito un numero massimo di risorse di slot disponibili per cartelle o progetti, quindi l'acquisto di un numero troppo basso di slot potrebbe influire sulle prestazioni delle query, mentre l'acquisto di troppi slot potrebbe causare una capacità di elaborazione inattiva, con risorse sottoutilizzate e costi inutili.

Considerazioni sui modelli di slot a costo fisso

Sebbene le considerazioni sui costi siano spesso il fattore principale alla base della scelta del modello on demand e del modello a costo fisso, questa decisione può influire sull'affidabilità. Ad esempio, la scalabilità può variare tra il modello di prezzi on demand, che fornisce una base di 2000 slot disponibili per progetto durante il carico di picco, e il modello a costo fisso che può essere configurato con un centinaio di migliaia di slot. Il numero di slot consumati dal progetto dipende dalla complessità delle query, dalla quantità di dati scansionati, dall'utilizzo di funzioni speciali come le funzioni definite dall'utente e dal numero di query in parallelo inviate. Tuttavia,i 2000 slot disponibili per i clienti on demand sono in genere più che sufficienti per gli utenti che iniziano con BigQuery. Per ulteriori informazioni su come scegliere tra i modelli on-demand e a costo fisso di BigQuery, consulta la pagina Scegliere tra prezzi on demand e a costo fisso di BigQuery.

Lo SLA (accordo sul livello del servizio) con disponibilità del 99,99% di BigQuery è applicabile sia ai modelli on demand che a costo fisso, ma il numero di risorse slot è garantito solo in base al modello a costo fisso poiché il modello on demand utilizza un pool di risorse slot condivise. Tieni presente che BigQuery non fornisce alcuna garanzia sulla disponibilità degli slot riservati fino a quando non vengono acquistati e forniti. Per questo motivo, Google consiglia di non affidarsi all'acquisizione di slot riservati just-in-time per carichi di lavoro business critical. Più maggiore è la richiesta di slot, maggiore è la probabilità di richiedere tempi di risposta per il provisioning delle risorse slot.

È inoltre possibile combinare il modello on demand e a costo fisso all'interno della tua organizzazione o del tuo progetto per soddisfare al meglio le tue esigenze. Ciò è possibile grazie al concetto di prenotazioni di BigQuery di BigQuery. Un buon esempio può essere l'utilizzo di una prenotazione a costo fisso per un set di dati in più aree geografiche negli Stati Uniti per la produzione e una capacità on demand per un set di dati di sviluppo più piccolo basato su un'area geografica alternativa.

Prenotazioni

Le prenotazioni di BigQuery ti consentono di personalizzare le allocazioni della capacità degli slot in tutta l'organizzazione e, in questo modo, di assegnare le priorità alle risorse in base ai carichi di lavoro. Ad esempio, puoi creare una prenotazione denominata prod per i carichi di lavoro di produzione, e una prenotazione separata denominata test a scopo di test. In questo modo, i job di test non competeranno per le risorse richieste dai carichi di lavoro di produzione. Una prenotazione specifica la priorità delle risorse slot per il progetto, la cartella o l'organizzazione assegnati. Ogni livello della gerarchia di risorse eredita l'assegnazione dal livello superiore, a meno che non venga sostituito. In altre parole, un progetto eredita l'assegnazione della cartella principale, mentre una cartella eredita l'assegnazione della relativa organizzazione.

Per impostazione predefinita, le prenotazioni BigQuery ottimizzano l'utilizzo delle risorse tramite la condivisione delle risorse degli slot inattivi tra le altre prenotazioni. Ciò consente ai carichi di lavoro non prioritari come ambienti di sviluppo/test di beneficiare di migliori prestazioni delle query durante i periodi in cui le prenotazioni di produzione potrebbero non essere completamente utilizzate. Nel caso in cui un carico di lavoro di sviluppo/test stia consumando slot inutilizzati da una prenotazione di produzione e tale prenotazione ora sia completamente utilizzata, il programma di pianificazione degli slot BigQuery estrarrà in modo intelligente le risorse degli slot dalle query dev/test in corso e le renderà disponibili per la prenotazione di produzione. Le query di sviluppo/test continueranno a essere eseguite, anche se più lentamente, perché hanno meno risorse di calcolo da utilizzare.

A seconda della natura delle tue esigenze di analisi, spesso è consigliabile creare più prenotazioni BigQuery all'interno del tuo ambiente, separando i carichi di lavoro in base a unità aziendali, funzioni (prod o dev/test) o tipi di applicazioni (dashboard di business intelligence o query programmate e query ad hoc degli utenti).

Tieni presente che gli slot inattivi vengono condivisi solo all'interno delle prenotazioni create all'interno di un singolo progetto di amministrazione di BigQuery.

Prenotazione di una prenotazione

Il dimensionamento accurato di una prenotazione è generalmente consigliato tramite il monitoraggio dell'utilizzo corrente e storico degli slot BigQuery tramite i grafici delle risorse di amministrazione BigQuery o la dashboard di Cloud Monitoring. I grafici delle risorse di amministrazione forniscono dati cronologici e in tempo reale 14 giorni dopo, con dettagli sull'utilizzo degli slot, sulla contemporaneità e sulle prestazioni dei job a livello di organizzazione, cartella, progetto, prenotazione, utente o job.

Ottimizza affidabilità job

È possibile inviare due tipi di query per analizzare i dati:

  • Query interattive: una query interattiva inviata viene immediatamente inviata per l'esecuzione.
  • Query batch: una query batch inviata viene inserita in coda e avviata non appena sono disponibili sufficienti risorse slot. Se BigQuery non ha avviato la query entro 24 ore, BigQuery imposta la priorità del job su Interattiva e invia immediatamente la query.

Le query interattive e batch utilizzano le stesse risorse di slot. Infatti, dopo l'avvio di una query batch da parte dello strumento di pianificazione di BigQuery, non c'è alcuna differenza di priorità tra query interattive o batch. Tuttavia, le query interattive e batch influiscono in modo diverso su limiti e quote. A differenza delle query batch, le query interattive vengono conteggiate ai fini del limite di frequenza per le query simultanee.

Se viene raggiunto il limite di query in parallelo, ulteriori query interattive non andranno a buon fine e verrà visualizzato un errore quotaExceeded. Questa quota è stata implementata per la tua protezione, pertanto finché gli slot disponibili non sono stati consumati completamente, generalmente questa quota può essere aumentata contattando l'assistenza clienti o il team di vendita. Tuttavia, gli elevati gradi di esecuzione simultanea delle query riducono la disponibilità complessiva delle risorse di calcolo per ciascuna query e possono pertanto introdurre contesa delle risorse e riduzione della velocità effettiva di elaborazione per query. Pertanto, non devi aumentare la quota di query in parallelo oltre il punto di saturazione delle risorse di slot. Se si verificano errori di query simultanei, puoi provare a ripetere la query con una logica di backoff esponenziale per evitare ripercussioni negative sulla latenza dei job di query.

Le possibili opzioni per ridurre il numero di query in parallelo nel tuo ambiente includono:

  • Esegui le query in modalità di prova per stimare il numero di byte letti, ma non di elaborazione.
  • Durante l'esperimento o l'esplorazione dei dati, anziché eseguire le query autonomamente, visualizza l'anteprima dei dati delle tabelle con la funzionalità di anteprima delle tabelle di BigQuery.
  • Utilizza i risultati delle query memorizzate nella cache. Tutti i risultati delle query, comprese le query interattive e batch, vengono memorizzati nella cache nelle tabelle temporanee per circa 24 ore con alcune eccezioni. Anche se l'esecuzione di una query memorizzata nella cache viene conteggiata nel calcolo del limite di query in parallelo, le query che utilizzano risultati memorizzati nella cache sono molto più veloci, in quanto BigQuery non ha bisogno di calcolare il set di risultati.
  • Usa il servizio di analisi in memoria veloce di BI Engine. BI Engine consente di creare dashboard e rapporti interattivi con strumenti come Google Data Studio e interfaccia SQL e offre una contemporaneità migliorata per i dati archiviati in BigQuery. Questa soluzione è particolarmente efficace per un numero elevato di query di piccole dimensioni.

Altre considerazioni relative alla contemporaneità delle query e alle prestazioni dei job sono l'isolamento dei job e i job temporanei. Isolare i job di query in base al caso d'uso dell'applicazione tra progetti o prenotazioni può ridurre i problemi di rumore dell'area circostante, dove una query utilizza una grande quantità di risorse e questo può influire negativamente sulle altre query. Questo riduce anche la probabilità di raggiungere limiti di query simultanei per progetto.

La creazione di scaglioni di job, o l'invio di più job in sequenza anziché l'invio di più job contemporaneamente, può portare a vantaggi nel complesso, e ridurre i tassi di errore. Consideriamo un insieme di cinque job complessi. Supponiamo che l'esecuzione di queste cinque query contemporaneamente consumerà un numero elevato di slot e completerà l'esecuzione in un'ora. L'invio degli stessi cinque job in sequenza potrebbe continuare a essere eseguito in un'ora, ma libererà risorse di slot che verranno utilizzate da altre query e ridurrà la possibilità di raggiungere limiti di query in parallelo.

BigQuery utilizza la programmazione corretta per assegnare le risorse alle query concorrenti all'interno di un progetto o di una prenotazione. Ciò significa che ogni query ha lo stesso accesso a tutti gli slot disponibili in qualsiasi momento, mentre la capacità viene riassegnata in modo dinamico e automatico tra le query attive man mano che la capacità di ogni query cambia. Per questo motivo, l'esecuzione di query di produzione business critical e di sviluppo/test all'interno dello stesso progetto o prenotazione potrebbe non essere l'ideale, poiché ogni query ha lo stesso accesso agli slot, quindi le query non critiche potrebbero influire sulle prestazioni delle query critiche in corso. È buona norma dare la priorità alle query prima dell'esecuzione.

Considerazioni sul DML per l'ottimizzazione dell'affidabilità

Le istruzioni BigQuery Data Manipulation Language (DML) consentono di aggiornare, inserire ed eliminare dati dalle tabelle BigQuery e di avere linee guida specifiche sull'affidabilità.

  • BigQuery è completamente atomico, per cui ogni istruzione DML avvia una transazione implicita. Ciò significa che le modifiche apportate dall'istruzione vengono automaticamente impegnate alla fine di ogni istruzione DML riuscita.
  • Non è possibile utilizzare le istruzioni DML UPDATE, DELETE o Banelco su una tabella con un buffer di flussi di dati attivo. Tentare di farlo comporterà un errore della query.
  • Le istruzioni DML INSERT, UPDATE, DELETE e Banelco non hanno limiti di quota simultanei. Tuttavia, quando si raggiunge una determinata soglia di job simultanei, i nuovi job vengono messi in coda in stato di attesa.
  • Nel caso in cui vengano eseguite contemporaneamente istruzioni DML mutanti nella stessa tabella, BigQuery determina se i DML possono mutare gli stessi file di backend e consente un solo DML di procedere, mentre gli altri vengono ritentati fino a tre volte. Se i tre nuovi tentativi di DML non riescono, le istruzioni DML non riusciranno a causa di conflitti nelle modifiche che hanno tentato di eseguire.
  • Sebbene BigQuery supporti completamente le operazioni DML UPDATE, DELETE e MERGE, non è destinato a essere un database transazionale in stile OLTP.

Come best practice, evita di inviare un numero elevato di singoli inserimenti o aggiornamenti di righe DML. Se possibile, raggruppa le operazioni DML quando possibile.

Considerazioni sull'UDF per l'ottimizzazione dell'affidabilità

Le funzioni definite dall'utente (UDF) di BigQuery sono funzioni permanenti o temporanee create da un'espressione SQL o da un codice JavaScript che restituisce risultati basati sull'esecuzione del codice fornito. Le funzioni definite dall'utente hanno norme specifiche sull'affidabilità.

  • Le funzioni definite dall'utente utilizzano in genere più risorse slot rispetto alle query SQL standard e possono avere un impatto sulle prestazioni dei job. Se la funzione può essere espressa in SQL, spesso è più ottimale eseguire il codice come job di query SQL standard.
  • Le funzioni definite dall'utente di JavaScript possono causare il timeout e impedire il completamento delle query. I timeout possono corrispondere a soli 5 minuti, ma possono variare in base a diversi fattori, tra cui il tempo di utilizzo della CPU da parte dell'utente e l'entità degli input e degli output nella funzione JavaScript.
  • Le funzioni definite dall'utente sono soggette alle proprie quote e ai limiti di query simultanee. Per ulteriori informazioni, consulta i limiti delle funzioni definite dall'utente.

Gestire quote e limiti

Come spiegato in precedenza, i job di query sono soggetti a una serie di limiti e quote. Questi limiti variano in base al tipo di query e ad altri fattori, ma il limite massimo di tempo di esecuzione della query o dello script è di 6 ore. Questo limite non può essere modificato. In alcuni casi, è possibile eseguire nuovamente le query. In tal caso, la query tentata nuovamente può essere eseguita per altre sei ore e la sua esecuzione può essere ritentata fino a tre volte.

Monitora job di query

Il monitoraggio dei job BigQuery è fondamentale per l'esecuzione di applicazioni affidabili. È possibile eseguire il monitoraggio tramite i grafici in risorse di amministrazione di BigQuery, le dashboard di Cloud Monitoring o le tabelle Information_Schema di BigQuery.
I grafici delle risorse di amministrazione e di Cloud Monitoring di BigQuery consentono il monitoraggio nativo dei byte delle query, del numero di query in parallelo eseguite, del consumo di slot, della latenza delle query e così via. Mentre le tabelle INFORMATION_SCHEMA forniscono metadati aggiuntivi sui tipi di job, messaggi di errore specifici, tariffe della cache, complessità del job e così via. Le tabelle INFORMATION_SCHEMA forniscono livelli aggiuntivi di personalizzazione con l'elenco delle query in esecuzione attualmente in attesa dell'ordine in corso:

SELECT
    creation_time,
    project_id,
    user_email,
    job_id,
    job_type,
    priority,
    state,
    TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), start_time,second) as running_time_sec
 FROM
   region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT
 WHERE
    creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY) AND CURRENT_TIMESTAMP()
    AND state != "DONE"
ORDER BY
    running_time_sec DESC

Casi d'uso per l'esecuzione di query sui dati

Analisi in tempo reale

In questo caso d'uso, i dati di flussi di dati vengono utilizzati per fornire dati a una dashboard di BI in tempo reale, che viene utilizzata da un numero elevato di utenti che eseguono query interattive.

In questo caso d'uso, devi tenere le query della dashboard di BI isolate da altre query per uso generico utilizzando progetti o prenotazioni separati al fine di evitare problemi con il passaggio della dashboard BI a un vicino rumoroso e influire sulle prestazioni di altri job di query per uso generico. Inoltre, il monitoraggio delle query in corso di esecuzione, le query costose e le query SQL che presentano anti-pattern BigQuery di BigQuery dovrebbero essere ottenuti esaminando le tabelle INFORMATION_SCHEMA e corrette per evitare spese non necessarie e latenza delle query.

Prendi in considerazione l'utilizzo di BI Engine se viene inviato un numero elevato di job di query simultanei alla dashboard di BI o se il problema di latenza delle query rappresenta un problema.

Valuta la possibilità di creare limiti di controllo dei costi personalizzati per limitare la quantità di dati che può essere elaborata per utente, per giorno o per tabella.

Elaborazione batch dei dati

In questo caso d'uso, i job batch notturni complessi vengono elaborati per eseguire un'analisi approfondita dell'intera giornata di dati e vengono uniti ad altre origini dati per l'arricchimento dei dati.

Analogamente alle indicazioni per i casi d'uso in tempo reale, consigliamo di tenere isolate queste query batch complesse da altre query per uso generico tramite progetti o prenotazioni separati, al fine di evitare problemi con questi job che comportano un comportamento rumoroso nelle vicinanze e un impatto sulle prestazioni di altri job di query. Inoltre, le query lunghe in fase di elaborazione, le query costose e le query SQL che presentano anti-pattern BigQuery di BigQuery devono essere monitorate con le tabelle INFORMATION_SCHEMA e corrette per evitare spese non necessarie e/o latenza delle query.

Prendi in considerazione la creazione di avvisi in Cloud Monitoring per ricevere avvisi in caso di scarsa disponibilità di slot, tempi di query elevati e byte elevati per analizzare i job con prestazioni inadeguate.

Passaggi successivi