Quando ha senso utilizzare Google BigQuery?

Nel corso della mia carriera, ho dovuto scrivere complesse query analitiche per diversi tipi di report e grafici. Molto spesso, si trattava di un grafico che mostrava i dati aggregati per data, settimana, trimestre e così via. Di solito, tali report vengono creati per aiutare i clienti a identificare le tendenze e illustrare come la loro attività sta andando ad alto livello. Ma cosa succede quando data scientist e ingegneri devono creare un report molto più ampio, basato su un grande set di dati?

Nel caso in cui il report sia basato su un piccolo insieme di dati, il compito può essere risolto scrivendo una query SQL in un database relazionale. In questo passaggio, è importante conoscere le basi per scrivere le query e come renderle più veloci ed efficienti. Tuttavia, a volte il report dipende da un insieme più ampio di dati (ad es. milioni e più righe in una tabella), ma il report non dipende dalle variabili di input (parametri), oppure potresti scoprire che il numero di valori è piuttosto piccolo. In tali scenari, una query SQL può essere lenta, quindi non sarebbe ottimale per gli utenti attendere fino all'esecuzione della query. La pratica più comune in questi casi è eseguire una query in anticipo, prima che il client richieda un rapporto.

Inoltre, richiede l'implementazione di alcune funzionalità di memorizzazione nella cache, in modo che il client possa prelevare dati dalla cache invece di eseguire una query in tempo reale. Questo approccio funziona perfettamente, a condizione che non sia necessario mostrare i dati in tempo reale. Può mostrare i dati calcolati un'ora o anche un giorno prima. Pertanto, il rapporto/grafico effettivo viene visualizzato utilizzando i dati memorizzati nella cache, non basati su dati in tempo reale.

Passando a Google BigQuery

Mentre stavo lavorando a un progetto analitico nel settore farmaceutico, avevo bisogno di grafici che prendessero il codice postale e il nome del farmaco come parametri di input. Avevo anche bisogno di mostrare alcuni confronti tra farmaci in specifiche regioni degli Stati Uniti.

La query analitica è stata molto complessa e ha finito per durare circa 50 minuti sul nostro server Postgres (CPU quad-core con 16 GB di RAM). Non sono stato in grado di eseguirlo in anticipo e memorizzare nella cache i risultati, poiché la query utilizzava codici postali e farmaci come parametri di input, quindi c'erano migliaia di combinazioni ed era impossibile prevedere quale client avrebbe scelto.

Anche se volessi provare a eseguire tutte le combinazioni di parametri di input, molto probabilmente il mio database si sarebbe bloccato. Quindi è arrivato il momento di scegliere un approccio diverso e scegliere una soluzione facile da usare. Quel grafico era importante per il client, tuttavia, il client non era pronto a impegnarsi per apportare grandi modifiche all'architettura o migrare completamente a un altro DB.

Su quel particolare progetto abbiamo provato alcuni approcci diversi:

• Ridimensionamento verticale del server (aggiunta di RAM e CPU al server Postgres)
• Utilizzo di DB alternativi come Amazon Redshift e altri.
• Abbiamo anche ricercato una soluzione NoSQL, ma la maggior parte di esse è piuttosto complessa e richiede molti cambiamenti nell'architettura, molti dei quali sarebbero stati troppo grandi per il cliente.

Infine, abbiamo provato Google BigQuery. Ha soddisfatto le nostre aspettative e ci ha permesso di portare a termine il lavoro senza apportare enormi modifiche che il cliente sarebbe riluttante ad approvare. Ma cos'è Google BigQuery e come si comporta?

BigQuery è un servizio Web basato su REST che consente di eseguire query analitiche complesse basate su SQL su grandi set di dati. Dopo aver caricato i dati su BigQuery ed eseguito la stessa query di Postgres (la sintassi è stranamente simile), la nostra query è stata eseguita molto più velocemente e il completamento ha richiesto circa un minuto. Alla fine, abbiamo ottenuto un aumento delle prestazioni di 50 volte semplicemente utilizzando un servizio diverso. Vale la pena notare che gli altri DB non stavano offrendo lo stesso guadagno in termini di prestazioni, e siamo generosi e diciamo semplicemente che non erano nemmeno vicini. Ad essere onesti, sono rimasto davvero colpito dal miglioramento delle prestazioni fornito da BigQuery, poiché le cifre erano migliori di quanto chiunque di noi avesse sperato.

Nonostante ciò, non pubblicizzerei BigQuery come la migliore soluzione di database al mondo. Sebbene abbia funzionato bene per il nostro progetto, presenta ancora molte limitazioni, come un numero limitato di aggiornamenti nella tabella al giorno, limitazioni sulla dimensione dei dati per richiesta e altro. Devi capire che BigQuery non può essere utilizzato per sostituire un database relazionale ed è orientato all'esecuzione di query analitiche, non per semplici operazioni e query CRUD.

In questo articolo, proverò a confrontare l'utilizzo di Postgres (il mio database relazionale preferito) e BigQuery per scenari di casi d'uso del mondo reale. Inoltre, fornirei alcuni suggerimenti lungo il percorso, in particolare la mia opinione su quando ha effettivamente senso utilizzare BigQuery.

Dati di esempio

Per confrontare Postgres e Google BigQuery, ho preso le informazioni demografiche pubbliche per ogni paese raggruppate per paese, età, anno e sesso (puoi scaricare gli stessi dati da questo link).

Ho aggiunto i dati a quattro tabelle:

1. populations
2. locations
3. age_groups
4. populations_aggregated

L'ultima tabella è solo dati aggregati delle tre tabelle precedenti. Ecco lo schema del DB:

La tabella delle populations che ho trovato contiene più di 6,9 milioni di righe. Non è troppo, ma è stato sufficiente per il mio test.

Sulla base di dati di esempio, ho cercato di creare query che possono essere utilizzate per creare report e grafici analitici di vita reale. Quindi ho preparato le domande per i prossimi rapporti:

1. Popolazione negli Stati Uniti aggregata per anni.
2. Popolazione nel 2019 per tutti i paesi a partire dai paesi più grandi.
3. Le prime cinque nazioni "più antiche" ogni anno. "Più vecchio" indica i paesi in cui la percentuale di persone con più di 60 anni rispetto al numero totale di persone è la più alta. La query dovrebbe fornire cinque risultati all'anno.
4. Le prime cinque nazioni aggregate per anno, dove la differenza tra popolazione maschile e femminile è la più grande.
5. Ottieni l'età media (media) per paese per ogni anno a partire dai paesi "più vecchi" a quelli "più giovani".
6. Trova i primi cinque paesi "che muoiono" ogni anno. “Morire” significa paesi in cui la popolazione sta diminuendo (lo spopolamento è il più alto).

Le query n. 1, n. 2 e n. 6 sono abbastanza facili e dirette, ma le query n. 3, n. 4 e n. 5 non erano così facili da scrivere, almeno per me. Tieni presente che sono un ingegnere di back-end e scrivere query SQL complesse non è esattamente una mia specialità, quindi qualcuno con più esperienza SQL potrebbe probabilmente creare query più intelligenti. Tuttavia, al momento dobbiamo verificare come Postgres e BigQuery elaborano le stesse query con gli stessi dati.

Ho creato 24 query in totale:

• 6 per Postgres DB, che utilizza tabelle non aggregate ( populations , locations , age_groups di età)
• 6 per Postgres DB, che utilizza la tabella populations_aggregated
• 6+6 query per BigQuery che utilizzano tabelle aggregate e non aggregate.

Consentitemi di condividere le query BigQuery n. 1 e n. 5 per i dati aggregati in modo da poter comprendere la complessità delle query semplici (n. 1) e complesse n. 5.

Popolazione negli Stati Uniti aggregata per anni query:

 select sum (value), year from world_population.populations_aggregated where location_name = 'United States of America' group by 2 order by year asc

Query per l'età media per paese per ogni anno ordinata dal più vecchio al più giovane:

 --converts age to number with population_by_age_year_and_location as( select sum (value) as value, cast (regexp_replace(age_group_name, '\\+', '') as int64) as age, year, location_name from world_population.populations_aggregated where location_type = 'COUNTRY' group by 2,3,4), --calculates total population per country per year total_population_by_year_and_locations as ( select sum(value) as value, year, location_name from population_by_age_year_and_location group by 2,3 ), --calculates total number of people in country per year age_multiplied_by_population_temp as ( select sum(value * age) as value, year, location_name from population_by_age_year_and_location group by 2,3 ), median_per_year_country as ( select a.value / b.value as median, a.year, a.location_name from age_multiplied_by_population_temp a inner join total_population_by_year_and_locations b on a.location_name = b.location_name and a.year = b.year ) select * from median_per_year_country order by year desc, median desc

Nota: puoi trovare tutte le query nel mio repository bitbucket (il link è alla fine dell'articolo).

Risultati del test

Per eseguire le query, ho utilizzato due diversi server Postgres. Il primo ha 1 core CPU e 4 GB di RAM, supportato da un'unità SSD. Il secondo ha 16 core CPU, 64 GB di RAM e utilizzava anche un'unità SSD (il secondo server ha 16 volte il potenziale di CPU e RAM).

Inoltre, prendere nota che non c'è stato alcun carico sui database durante l'esecuzione del test. Li ho creati solo per eseguire query. Nelle situazioni reali, le query impiegheranno più tempo, poiché altre query potrebbero essere eseguite contemporaneamente e, inoltre, quelle query eseguite in parallelo possono bloccare le tabelle. Per controllare la velocità delle query stavo usando pgAdmin3 e l'interfaccia web di BigQuery.

Nel mio test ho ottenuto questi risultati:

Permettetemi di mostrare quei risultati in un grafico a barre per la query n. 1 e la query n. 5.

Nota: il database di Postgres si trovava su un server con sede negli Stati Uniti e io risiedo in Europa, quindi si è verificato un ulteriore ritardo nella trasmissione dei dati di Postgres.

Prestazioni e conclusioni di BigQuery

Sulla base dei risultati che ho ottenuto, ho tratto le seguenti conclusioni:

• In caso di ridimensionamento verticale di Postgres, anche 16 volte, fornisce solo il 10-25% delle prestazioni nell'esecuzione di una singola query. In altre parole, un server Postgres con un solo core CPU e 4 GB di RAM eseguiva query con un tempo molto simile al tempo necessario per un server con 16 core CPU e 64 GB di RAM. Naturalmente, i server più grandi possono elaborare set di dati molto più grandi, tuttavia, ciò non offre un notevole miglioramento del tempo di esecuzione delle query.
• Per Postgres, i join con tabelle piccole (la tabella locations ha circa 400 righe e age_groups ha 100 righe) non producono enormi differenze rispetto all'esecuzione di query con dati aggregati situati in una tabella. Inoltre, ho scoperto che per le query in esecuzione da uno a due secondi, le query con inner join sono più veloci, ma per le query di lunga durata la situazione è diversa.
• In BigQuery la situazione con i join è completamente diversa. A BigQuery non piacciono i join. La differenza di tempo tra le query, che utilizzano dati aggregati e non aggregati, è piuttosto grande (per le query n. 3 e $ 5 era di circa due secondi). Significa che, per BigQuery, puoi eseguire tutte le sottoquery che desideri, ma per ottenere buone prestazioni, la query dovrebbe utilizzare una tabella.
• Postgres è più veloce per le query che utilizzano semplici aggregazioni o filtri o utilizzano un piccolo set di dati. Ho scoperto che le query che richiedono meno di cinque secondi in Postgres funzionano più lentamente in BigQuery.
• BigQuery ha prestazioni molto migliori per le query di lunga durata. All'aumentare della differenza nella dimensione del set di dati, aumenta anche la differenza nel tempo necessario per il completamento di queste query.

Quando ha senso utilizzare BigQuery

Ora, torniamo alla questione centrale discussa in questo articolo: quando dovresti effettivamente utilizzare Google BigQuery? Sulla base delle mie conclusioni, suggerirei di utilizzare BigQuery quando sono soddisfatte le seguenti condizioni:

• Usalo quando hai query che vengono eseguite per più di cinque secondi in un database relazionale. L'idea di BigQuery è eseguire query analitiche complesse, il che significa che non ha senso eseguire query che eseguono semplici aggregazioni o filtri. BigQuery è adatto per query "pesanti", quelle che operano utilizzando un grande set di dati. Più grande è il set di dati, più è probabile che tu ottenga prestazioni utilizzando BigQuery. Il set di dati che ho usato era di soli 330 MB (megabyte, nemmeno gigabyte).
• A BigQuery non piacciono i join, quindi dovresti unire i tuoi dati in un'unica tabella per ottenere tempi di esecuzione migliori. BigQuery consente di salvare i risultati delle query in una nuova tabella, quindi per creare una nuova tabella aggregata, è sufficiente caricare tutti i dati su BigQuery, eseguire una query che consoliderà tutti i dati e salvarli semplicemente in una nuova tabella.
• BigQuery è utile per scenari in cui i dati non cambiano spesso e desideri utilizzare la cache, poiché ha una cache integrata. Cosa significa questo? Se esegui la stessa query e i dati nelle tabelle non vengono modificati (aggiornati), BigQuery utilizzerà solo i risultati memorizzati nella cache e non tenterà di eseguire nuovamente la query. Inoltre, BigQuery non addebita denaro per le query memorizzate nella cache. Nota: anche le query memorizzate nella cache impiegano 1-1,2 secondi per restituire i risultati.
• Puoi anche utilizzare BigQuery quando vuoi ridurre il carico sul tuo database relazionale. Le query analitiche sono "pesanti" e l'uso eccessivo di esse in un database relazionale può causare problemi di prestazioni. Quindi, alla fine potresti essere costretto a pensare a ridimensionare il tuo server. Tuttavia, con BigQuery puoi spostare queste query in esecuzione su un servizio di terze parti, in modo che non influiscano sul tuo database relazionale principale.

Infine, qualche parola in più sull'utilizzo di BigQuery nella vita reale. Nel nostro progetto nel mondo reale, i dati per i rapporti cambiavano su base settimanale o mensile, quindi potevamo caricare manualmente i dati su BigQuery. Tuttavia, se i tuoi dati cambiano frequentemente, sincronizzare i dati tra il tuo database relazionale e BigQuery potrebbe non essere così semplice e questo è un avvertimento da tenere a mente.

Collegamenti

Puoi trovare i dati di esempio utilizzati in questo articolo qui, mentre le query e i dati in formato CSV sono accessibili qui.