BigQuery è un data warehouse versatile che aiuta a trasformare i big data in insight di valore, ma i costi possono lievitare in fretta. Nella prima di una serie di guide approfondite, le mostriamo come usarlo in modo efficiente.

Una nuova guida all'ottimizzazione di costi e prestazioni di BigQuery

Quando si gestiscono più dataset BigQuery interrogati da diversi team di analisti, le impennate improvvise dei costi non sono affatto rare. Ma non sono nemmeno inevitabili.

Nel nostro nuovo ebook, The BigQuery Optimization Handbook: Preparing to Save, Sayle Matthews, Senior Cloud Architect e specialista di dati in DoiT, firma la prima parte di una serie di approfondimenti su come spendere meno e ottenere di più con BigQuery, rendendo al tempo stesso più prevedibili le fatture di Google Cloud.

L'ebook affronta:

1. Le basi da impostare correttamente
2. Gli errori più comuni nelle query da evitare
3. Come individuare le query più costose

Le basi da impostare correttamente

Il data warehouse BigQuery è completamente gestito e mette a disposizione funzionalità integrate come machine learning, analisi geospaziale e business intelligence per gestire e analizzare i big data. Senza un minimo di lavoro preparatorio, però, è facile ritrovarsi con costi inattesi. Prima di intervenire sui costi di BigQuery occorre conoscere alcuni concetti di base, a partire da slot e modelli di pricing.

Slot

Il calcolo in BigQuery poggia su un costrutto chiamato slot, ovvero una vCPU con una certa quantità di memoria associata. In teoria, più slot vengono allocati e resi disponibili, più velocemente girano le query. Gli slot standard si acquistano sempre in gruppi da 100 nell'ambito di un commitment di durata mensile o annuale.

Gli slot svolgono inoltre un compito non documentato: lo shuffling dei dati. In parole semplici, lo shuffling consiste nel ridistribuire i dati elaborati in una nuova posizione, così che lo step corrente o successivo del piano di esecuzione della query risulti più rapido. In ogni momento, fino al 60% degli slot allocati al progetto può fungere da shuffle slot. Lo shuffling migliora l'efficienza delle query, ma impegna slot preziosi in operazioni che non fanno avanzare direttamente l'esecuzione.

Modelli di pricing

I modelli di pricing sono due: on-demand o flat-rate. Quello predefinito è l'on-demand, che applica una tariffa di 5 $ per TB di dati scansionati dalle query. Per la maggior parte delle aziende è proprio questa la voce che traina i costi su BigQuery.

Il pricing flat-rate (o per slot) prevede invece un prezzo fisso per le scansioni in BigQuery, potenzialmente a scapito delle prestazioni: il numero di slot disponibili viene limitato a quelli pre-acquistati e si elimina la tariffa di 5 $ per TB scansionato.

Con il flat-rate è possibile rivedere il design dell'architettura aggiungendo un pool di slot chiamati Flex slot, per ampliare o sostituire quelli esistenti. I Flex slot offrono commitments più brevi e flessibili, con una durata minima di 60 secondi e la possibilità di disattivarli annullandoli in qualsiasi momento.

Per eguagliare le prestazioni dell'on-demand occorre acquistare 20 blocchi da 100 slot, al costo di 40.000 $ al mese o 34.000 $ con impegno annuale. Acquistare 2.000 slot non è conveniente se non si sta già spendendo almeno tale cifra in costi di scansione su BigQuery.

Determinare i costi

Prima di poter ottimizzare i costi, bisogna analizzare l'utilizzo dei dati nei propri progetti. Serve quindi accedere ai dati delle query nei progetti/dataset.

I metodi sono due: gli audit log sink e le tabelle INFORMATION_SCHEMA. La via consigliata è l'audit log sink, perché i dati sono molto più ricchi . I clienti DoiT che utilizzano la funzionalità BigQuery Lens in DoiT Cloud Intelligence™ hanno già l'audit log sink configurato nei propri ambienti. Maggiori informazioni qui.

Gli errori più comuni nelle query da evitare

Prima di passare alle query che le restituiranno i dati di cui ha realmente bisogno, vediamo alcuni errori ricorrenti che si commettono scrivendo query in BigQuery: errori che allungano i tempi di esecuzione e fanno lievitare i costi più del necessario.

1. SELECT *

È probabilmente la principale fonte di costi aggiuntivi inutili nelle query BigQuery. Selezionare tutte le colonne di una tabella o di una vista è quasi sempre superfluo. Ricordi che la fattura di BigQuery si basa sulla quantità di dati scansionati: riducendo la selezione, riduce anche il costo.

2. Join inutili o troppo estesi

Nei data warehouse orientati a una strategia OLAP (come BigQuery) la best practice è denormalizzare gli schemi del database. In sostanza si appiattiscono le strutture dati, riducendo il numero di join necessari rispetto a un database relazionale tradizionale.

3. Cross join

I cross join servono in diversi casi d'uso in BigQuery, ma diventano un problema quando vengono inseriti come operazione più interna della query: si finisce così per leggere molti più dati di quanti ne arriveranno effettivamente all'output.

4. Uso scorretto delle Common Table Expression (CTE)

Le Common Table Expression (CTE) sono strumenti eccellenti che semplificano enormemente il codice SQL. Tuttavia, se in una query si fa riferimento più volte a una stessa CTE, questa viene eseguita ogni volta da capo: significa pagare più volte la lettura degli stessi dati.

5. Non usare le partizioni nelle clausole WHERE

Le partizioni sono tra le funzionalità più importanti di BigQuery per ridurre i costi e ottimizzare le prestazioni in lettura. Eppure vengono spesso tralasciate, con il risultato di aggiungere costi inutili alle query.

6. Viste troppo complesse

Creare viste complesse può degradare le prestazioni. Se la logica di una vista è eccessivamente articolata, potrebbe essere più opportuno pre-calcolarla in un'altra tabella o spostarla in una vista materializzata per migliorare le prestazioni.

7. Insert di piccole dimensioni

Se deve inserire un numero ridotto di record in una tabella, lo faccia in batch anziché tramite numerosi insert di piccole dimensioni.

8. Uso eccessivo di istruzioni DML

Si tende ad abusare delle istruzioni DML quando si tratta BigQuery come un classico RDBMS, ricreando i dati a piacimento. Un'alternativa migliore è adottare un "modello additivo": si inseriscono nuove righe con un timestamp per indicare la versione più recente e si rimuovono periodicamente quelle più vecchie, se non serve mantenerne lo storico.

Come individuare le query più costose

L'ebook rimanda a un repository GitHub con i file SQL necessari per individuare le query più costose. Le tre query principali che utilizzerà sono:

• Le query complessivamente più costose
• Le singole query più costose
• Gli utenti più costosi

Altre query nel repository

Il repository GitHub include anche query aggiuntive per finalità più specifiche: ad esempio individuare le query originate da Looker, contare le esecuzioni di una query o calcolare il costo delle query con etichette specifiche.

Individuare le query con problemi di prestazioni

Una volta identificate le query più costose, occorre scoprire quali consumano più risorse del necessario e non offrono le prestazioni attese. Spesso queste coincidono con le più costose, quindi è normale che ci sia una sovrapposizione.

Il performance tuning sarà approfondito in una prossima parte della serie, con un focus su alcune delle insidie meno note delle prestazioni di BigQuery e sui metodi per superarle.

L'ultimo argomento di questa parte della serie è una raccolta di query, sempre nel repository GitHub, dedicate a informazioni più generali e metadati:

• Query per tipo di job
• Query concorrenti
• Conteggi delle query

I prossimi passi

Scarichi The BigQuery Optimization Handbook: Preparing to Save. Poiché questa e le prossime parti della serie contengono una mole notevole di materiale dettagliato, le consigliamo di attivare fin da subito un audit log sink sui suoi progetti BigQuery: raccoglierà dati nel tempo, rendendo queste query e il resto della serie ancora più utili. Ricordi che i clienti DoiT che utilizzano la funzionalità BigQuery Lens in DoiT Cloud Intelligence hanno già l'audit log sink configurato nei propri ambienti.