Panoramica

L'ottimizzazione delle prestazioni di BigQuery viene solitamente eseguita perché vogliamo ridurre i tempi o i costi di esecuzione delle query. In questo lab vedremo una serie di ottimizzazioni delle prestazioni che potrebbero funzionare per il tuo caso d'uso. L'ottimizzazione delle prestazioni dovrebbe essere effettuata solo alla fine della fase di sviluppo e solo se si osserva che le query tipiche richiedono troppo tempo.

È molto meglio avere schemi di tabella flessibili e query eleganti, leggibili e gestibili piuttosto che offuscare layout di tabella e query alla ricerca di un minimo di prestazioni. Tuttavia, ci saranno casi in cui sarà necessario migliorare le prestazioni delle query, forse perché vengono eseguite così spesso che piccoli miglioramenti sono significativi. Un altro aspetto è che la conoscenza dei compromessi prestazionali può aiutare a decidere tra progetti alternativi.

Obiettivi

In questo lab apprenderai le seguenti tecniche per ridurre i tempi e i costi di esecuzione di BigQuery:

• Ridurre al minimo l'I/O
• Inserire nella cache i risultati delle query precedenti
• Eseguire join efficienti
• Evitare di sovraccaricare i singoli worker
• Utilizzare funzioni di aggregazione approssimate

Configurazione e requisiti

Per ciascun lab, riceverai un nuovo progetto Google Cloud e un insieme di risorse per un periodo di tempo limitato senza alcun costo aggiuntivo.

1. Accedi a Qwiklabs utilizzando una finestra di navigazione in incognito.

2. Tieni presente la durata dell'accesso al lab (ad esempio, 1:15:00) e assicurati di finire entro quell'intervallo di tempo.
   Non è disponibile una funzionalità di pausa. Se necessario, puoi riavviare il lab ma dovrai ricominciare dall'inizio.

3. Quando è tutto pronto, fai clic su Inizia lab.

4. Annota le tue credenziali del lab (Nome utente e Password). Le userai per accedere a Google Cloud Console.

5. Fai clic su Apri console Google.

6. Fai clic su Utilizza un altro account e copia/incolla le credenziali per questo lab nei prompt.
   Se utilizzi altre credenziali, compariranno errori oppure ti verranno addebitati dei costi.

7. Accetta i termini e salta la pagina di ripristino delle risorse.

Apri la console di BigQuery

1. Nella console Google Cloud, seleziona Menu di navigazione > BigQuery.

Si aprirà la finestra con il messaggio Ti diamo il benvenuto in BigQuery nella console Cloud. Questa finestra fornisce un link alla guida rapida ed elenca gli aggiornamenti dell'interfaccia utente.

2. Fai clic su Fine.

Attività 1: riduci al minimo l'I/O

Una query che calcola la somma di tre colonne sarà più lenta di una query che calcola la somma di due colonne, ma la maggior parte della differenza di prestazioni sarà dovuta alla lettura di più dati, non all'aggiunta di extra. Pertanto, una query che calcola la media di una colonna sarà veloce quasi quanto una query il cui metodo di aggregazione consiste nel calcolare la varianza dei dati (anche se il calcolo della varianza richiede che BigQuery tenga traccia sia della somma che della somma dei quadrati) perché la maggior parte dell'overhead delle query semplici è causato dall'I/O, non dal calcolo.

Utilizza le istruzioni SELECT in modo mirato

Poiché BigQuery utilizza formati di file a colonne, minore è il numero di colonne lette in un'istruzione SELECT, minore è la quantità di dati da leggere. In particolare, un'istruzione SELECT * legge ogni colonna di ogni riga della tabella, il che rende l'operazione piuttosto lenta e costosa.

Si ha un'eccezione quando si utilizza un'istruzione SELECT * in una sottoquery, quindi si fa riferimento solo ad alcuni campi nella query esterna; l'ottimizzatore BigQuery sarà abbastanza intelligente da leggere solo le colonne assolutamente necessarie.

1. Esegui questa query nella finestra EDITOR di BigQuery:

SELECT bike_id, duration FROM `bigquery-public-data`.london_bicycles.cycle_hire ORDER BY duration DESC LIMIT 1

Nella finestra Risultati delle query nota che la query è stata completata in circa 1,2 secondi e ha elaborato circa 372 MB di dati.

2. Esegui questa query nella finestra EDITOR di BigQuery:

SELECT * FROM `bigquery-public-data`.london_bicycles.cycle_hire ORDER BY duration DESC LIMIT 1

Nella finestra Risultati delle query nota che questa query è stata completata in circa 4,5 secondi e ha utilizzato circa 2,6 GB di dati. È stata molto più lenta della prima.

Se hai bisogno di quasi tutte le colonne di una tabella, valuta la possibilità di utilizzare SELECT * EXCEPT in modo da non leggere quelle che non ti servono.

Nota: BigQuery memorizzerà nella cache i risultati delle query per velocizzarne la ripetizione. Disattiva questa cache per visualizzare le prestazioni effettive di elaborazione delle query facendo clic su Altro > Impostazioni query e deselezionando Utilizza risultati memorizzati nella cache.

Riduci i dati da leggere

Quando si ottimizza una query, è importante iniziare con i dati che vengono letti e considerare se è possibile ridurli. Supponiamo di voler trovare la durata tipica dei più comuni noleggi di sola andata.

1. Esegui questa query nella finestra dell'editor di BigQuery:

SELECT MIN(start_station_name) AS start_station_name, MIN(end_station_name) AS end_station_name, APPROX_QUANTILES(duration, 10)[OFFSET (5)] AS typical_duration, COUNT(duration) AS num_trips FROM `bigquery-public-data`.london_bicycles.cycle_hire WHERE start_station_id != end_station_id GROUP BY start_station_id, end_station_id ORDER BY num_trips DESC LIMIT 10

2. Fai clic sulla scheda Dettagli esecuzione della finestra Risultati delle query.

I dettagli della query indicano che l'ordinamento (per i quantili approssimati per ogni coppia di stazioni) richiede una ripartizione degli output della fase di input, ma la maggior parte del tempo viene spesa durante il calcolo.

3. Possiamo ridurre l'overhead I/O della query se filtriamo ed eseguiamo il raggruppamento utilizzando il nome della stazione anziché l'ID della stazione poiché avremo bisogno di leggere meno colonne. Esegui questa query:

SELECT start_station_name, end_station_name, APPROX_QUANTILES(duration, 10)[OFFSET(5)] AS typical_duration, COUNT(duration) AS num_trips FROM `bigquery-public-data`.london_bicycles.cycle_hire WHERE start_station_name != end_station_name GROUP BY start_station_name, end_station_name ORDER BY num_trips DESC LIMIT 10

La query precedente permette di non leggere le due colonne id e termina in circa 10,8 secondi. Questa accelerazione è causata dagli effetti downstream della lettura di meno dati.

Il risultato della query rimane lo stesso poiché esiste una relazione 1:1 tra il nome e l'ID della stazione.

Riduci il numero di calcoli dispendiosi

Supponiamo di voler trovare la distanza totale percorsa da ciascuna bicicletta nel nostro set di dati.

1. Un modo ingenuo per farlo sarebbe quello di trovare le distanze percorse in ogni viaggio intrapreso da ciascuna bicicletta e sommarle:

WITH trip_distance AS ( SELECT bike_id, ST_Distance(ST_GeogPoint(s.longitude, s.latitude), ST_GeogPoint(e.longitude, e.latitude)) AS distance FROM `bigquery-public-data`.london_bicycles.cycle_hire, `bigquery-public-data`.london_bicycles.cycle_stations s, `bigquery-public-data`.london_bicycles.cycle_stations e WHERE start_station_id = s.id AND end_station_id = e.id ) SELECT bike_id, SUM(distance)/1000 AS total_distance FROM trip_distance GROUP BY bike_id ORDER BY total_distance DESC LIMIT 5

La query precedente richiede circa 9,8 secondi (circa 55 secondi di tempo di slot) ed esegue lo shuffling di circa 1,22 MB di dati. Il risultato è che alcune biciclette hanno percorso quasi 6000 chilometri.

2. Calcolare la distanza è un'operazione piuttosto costosa e possiamo evitare di eseguire il join della tabella cycle_stations con la tabella cycle_hire table se precalcoliamo le distanze tra tutte le coppie di stazioni di noleggio:

WITH stations AS ( SELECT s.id AS start_id, e.id AS end_id, ST_Distance(ST_GeogPoint(s.longitude, s.latitude), ST_GeogPoint(e.longitude, e.latitude)) AS distance FROM `bigquery-public-data`.london_bicycles.cycle_stations s, `bigquery-public-data`.london_bicycles.cycle_stations e ), trip_distance AS ( SELECT bike_id, distance FROM `bigquery-public-data`.london_bicycles.cycle_hire, stations WHERE start_station_id = start_id AND end_station_id = end_id ) SELECT bike_id, SUM(distance)/1000 AS total_distance FROM trip_distance GROUP BY bike_id ORDER BY total_distance DESC LIMIT 5

Questa query effettua solo 600.000 calcoli di distanza geografica rispetto ai 24 milioni precedenti. Ora richiede circa 31,5 secondi di tempo di slot (una velocità del 30% maggiore), nonostante venga eseguito lo shuffling di 33,05 MB di dati.

Fai clic su Controlla i miei progressi per verificare l'obiettivo.

Riduci al minimo l'I/O

Attività 2: memorizza nella cache i risultati delle query precedenti

Il servizio BigQuery memorizza automaticamente nella cache i risultati delle query in una tabella temporanea. Se viene inviata una query identica entro circa 24 ore, i risultati vengono forniti da questa tabella temporanea senza alcun ricalcolo. I risultati memorizzati nella cache sono estremamente rapidi e non comportano costi.

Ci sono, tuttavia, alcune avvertenze di cui tenere conto. La memorizzazione nella cache delle query si basa sul confronto tra stringhe esatte. Quindi anche gli spazi vuoti possono causare un fallimento della cache. Le query non vengono mai memorizzate nella cache se mostrano un comportamento non deterministico (ad esempio, utilizzano CURRENT_TIMESTAMP o RAND), se la tabella o la vista su cui viene eseguita la query è cambiata (anche se le colonne/righe di interesse per la query sono invariate), se la tabella è associata a un buffer dei flussi di dati (anche se non sono presenti nuove righe) o se la query utilizza istruzioni DML oppure viene eseguita su origini dati esterne.

Memorizza nella cache i risultati intermedi

È possibile migliorare le prestazioni complessive a scapito di un aumento di I/O sfruttando tabelle temporanee e viste materializzate.

Ad esempio, supponiamo di avere una serie di query che iniziano trovando la durata tipica degli spostamenti tra due stazioni di noleggio. La clausola WITH (chiamata anche espressione di tabella comune) migliora la leggibilità ma non aumenta la velocità né riduce il costo delle query poiché i risultati non vengono memorizzati nella cache. Lo stesso vale anche per le viste e le sottoquery. Se ti ritrovi a utilizzare spesso una clausola WITH, una vista o una sottoquery, un modo per migliorare potenzialmente le prestazioni è archiviare il risultato in una tabella (o vista materializzata).

1. Per prima cosa dovrai creare un set di dati denominato mydataset nella regione eu (multiple regions in European Union) (dove risiedono i dati sulle biciclette) nel tuo progetto in BigQuery.

• Nel riquadro a sinistra nella sezione Explorer, fai clic sull'icona con i tre puntini Visualizza azione accanto al tuo progetto BigQuery (qwiklabs-gcp-xxxx) e seleziona Crea set di dati.

Nella finestra di dialogo Crea set di dati:

• Imposta ID set di dati su mydataset.

• Imposta Tipo di località su eu (multiple regions in European Union).

• Lascia invariate tutte le altre impostazioni predefinite.

• Per finire, fai clic sul pulsante blu Crea set di dati.

• Ora puoi eseguire questa query:

  CREATE OR REPLACE TABLE mydataset.typical_trip AS SELECT start_station_name, end_station_name, APPROX_QUANTILES(duration, 10)[OFFSET (5)] AS typical_duration, COUNT(duration) AS num_trips FROM `bigquery-public-data`.london_bicycles.cycle_hire GROUP BY start_station_name, end_station_name

2. Utilizza la tabella creata per trovare i giorni in cui gli spostamenti in bicicletta sono molto più lunghi del solito:

SELECT EXTRACT (DATE FROM start_date) AS trip_date, APPROX_QUANTILES(duration / typical_duration, 10)[OFFSET(5)] AS ratio, COUNT(*) AS num_trips_on_day FROM `bigquery-public-data`.london_bicycles.cycle_hire AS hire JOIN mydataset.typical_trip AS trip ON hire.start_station_name = trip.start_station_name AND hire.end_station_name = trip.end_station_name AND num_trips > 10 GROUP BY trip_date HAVING num_trips_on_day > 10 ORDER BY ratio DESC LIMIT 10

3. Utilizza la clausola WITH per trovare i giorni in cui gli spostamenti in bicicletta sono molto più lunghi del solito:

WITH typical_trip AS ( SELECT start_station_name, end_station_name, APPROX_QUANTILES(duration, 10)[OFFSET (5)] AS typical_duration, COUNT(duration) AS num_trips FROM `bigquery-public-data`.london_bicycles.cycle_hire GROUP BY start_station_name, end_station_name ) SELECT EXTRACT (DATE FROM start_date) AS trip_date, APPROX_QUANTILES(duration / typical_duration, 10)[ OFFSET (5)] AS ratio, COUNT(*) AS num_trips_on_day FROM `bigquery-public-data`.london_bicycles.cycle_hire AS hire JOIN typical_trip AS trip ON hire.start_station_name = trip.start_station_name AND hire.end_station_name = trip.end_station_name AND num_trips > 10 GROUP BY trip_date HAVING num_trips_on_day > 10 ORDER BY ratio DESC LIMIT 10

Osserva l'aumento di velocità di circa il 50% poiché viene evitato il calcolo della durata media degli spostamenti. Entrambe le query restituiscono lo stesso risultato, ovvero che gli spostamenti sotto Natale richiedono più tempo del solito. Tieni presente che la tabella mydataset.typical_trip non viene aggiornata quando vengono aggiunti nuovi dati alla tabella cycle_hire.

Un modo per risolvere questo problema dei dati non aggiornati consiste nell'utilizzare una vista materializzata o pianificare le query per aggiornare periodicamente la tabella. Devi misurare il costo di questi aggiornamenti per verificare se il miglioramento delle prestazioni delle query compensa il costo aggiuntivo derivante dal mantenere aggiornata la tabella o la vista materializzata.

Accelera le query con BI Engine

Se sono presenti tabelle a cui accedi frequentemente nelle impostazioni di business intelligence (BI) come dashboard con aggregazioni e filtri, un modo per velocizzare le query è utilizzare BI Engine, che archivierà automaticamente i dati pertinenti in memoria (colonne effettive dalla tabella o risultati derivati) e utilizzerà uno specifico processore di query ottimizzato per lavorare principalmente con dati in memoria. Puoi prenotare la quantità di memoria (fino a un massimo attuale di 10 GB) che BigQuery dovrebbe utilizzare per la cache dalla console di amministrazione di BigQuery, in BI Engine.

Assicurati di prenotare questa memoria nella stessa regione del set di dati su cui esegui le query. Successivamente, BigQuery inizierà a memorizzare nella cache tabelle, parti di tabelle e aggregazioni in memoria e fornirà i risultati più velocemente.

Un caso d'uso primario per BI Engine riguarda le tabelle a cui si accede da strumenti di dashboard come Google Data Studio. Fornendo l'allocazione della memoria per una prenotazione di BI Engine, possiamo rendere molto più adattabili le dashboard che si basano su un backend BigQuery.

Fai clic su Controlla i miei progressi per verificare l'obiettivo.

Memorizza nella cache i risultati delle query precedenti

Attività 3: esegui join efficienti

Il join di due tabelle richiede il coordinamento dei dati ed è soggetto alle limitazioni imposte dalla larghezza di banda della comunicazione tra gli slot. Se è possibile, evita i join o riduci la quantità di dati di cui eseguire il join.

Denormalizzazione

Un modo per migliorare le prestazioni di lettura ed evitare i join è rinunciare ad archiviare i dati in modo efficiente, aggiungendo invece copie ridondanti dei dati. Questa si chiama denormalizzazione.

• Pertanto, anziché archiviare le latitudini e le longitudini delle stazioni di noleggio delle biciclette separatamente dalle informazioni sul noleggio delle biciclette, potremmo creare una tabella denormalizzata:

  CREATE OR REPLACE TABLE mydataset.london_bicycles_denorm AS SELECT start_station_id, s.latitude AS start_latitude, s.longitude AS start_longitude, end_station_id, e.latitude AS end_latitude, e.longitude AS end_longitude FROM `bigquery-public-data`.london_bicycles.cycle_hire AS h JOIN `bigquery-public-data`.london_bicycles.cycle_stations AS s ON h.start_station_id = s.id JOIN `bigquery-public-data`.london_bicycles.cycle_stations AS e ON h.end_station_id = e.id

  Quindi, per tutte le query successive non servirà eseguire il join poiché la tabella conterrà i dati sulla posizione necessari per tutti gli spostamenti.

  In questo caso, stai preferendo l'archiviazione e la lettura di più dati rispetto al costo dell'elaborazione di un join. È del tutto possibile che il costo della lettura di più dati dal disco superi il costo del join: dovresti misurare se la denormalizzazione apporta vantaggi in termini di prestazioni.

  Fai clic su Controlla i miei progressi per verificare l'obiettivo.

  Denormalizzazione

Evita i self-join di tabelle di grandi dimensioni

I self-join si verificano quando viene eseguito il join di una tabella con se stessa. Sebbene BigQuery supporti i self-join, questi possono portare a un peggioramento delle prestazioni se la tabella di cui viene eseguito il join con se stessa è molto grande. In molti casi, è possibile evitare il self-join sfruttando le funzionalità SQL come l'aggregazione e le funzioni finestra.

Vediamo un esempio. Uno dei set di dati pubblici di BigQuery è il set di dati dei nomi di bambini fornito dalla Social Security Administration degli Stati Uniti.

1. È possibile eseguire una query sul set di dati per trovare i nomi maschili più comuni nel 2015 nello stato del Massachusetts (assicurati che la query sia in esecuzione nella regione US (più regioni negli Stati Uniti). Fai clic su +Crea query SQL, quindi seleziona Altro > Impostazioni query > Opzioni avanzate, deseleziona Selezione automatica della località e seleziona Più regioni e quindi USA (più regioni negli Stati Uniti) oppure lascia la query per utilizzare la selezione Località automatica e fai clic su Salva:

SELECT name, number AS num_babies FROM `bigquery-public-data`.usa_names.usa_1910_current WHERE gender = 'M' AND year = 2015 AND state = 'MA' ORDER BY num_babies DESC LIMIT 5

2. Allo stesso modo, esegui una query sul set di dati per trovare i nomi femminili più comuni nel 2015 nello stato del Massachusetts:

SELECT name, number AS num_babies FROM `bigquery-public-data`.usa_names.usa_1910_current WHERE gender = 'F' AND year = 2015 AND state = 'MA' ORDER BY num_babies DESC LIMIT 5

3. Quali sono i nomi di bambini sia maschi che femmine più comuni nel paese nel corso degli anni nel set di dati? Un modo ingenuo per risolvere questo problema prevede la lettura della tabella di input due volte e l'esecuzione di un self-join:

WITH male_babies AS ( SELECT name, number AS num_babies FROM `bigquery-public-data`.usa_names.usa_1910_current WHERE gender = 'M' ), female_babies AS ( SELECT name, number AS num_babies FROM `bigquery-public-data`.usa_names.usa_1910_current WHERE gender = 'F' ), both_genders AS ( SELECT name, SUM(m.num_babies) + SUM(f.num_babies) AS num_babies, SUM(m.num_babies) / (SUM(m.num_babies) + SUM(f.num_babies)) AS frac_male FROM male_babies AS m JOIN female_babies AS f USING (name) GROUP BY name ) SELECT * FROM both_genders WHERE frac_male BETWEEN 0.3 AND 0.7 ORDER BY num_babies DESC LIMIT 5

4. Una soluzione più rapida, elegante (e corretta!) consiste nel riformulare la query per leggere l'input solo una volta ed evitare del tutto il self-join:

WITH all_babies AS ( SELECT name, SUM( IF (gender = 'M', number, 0)) AS male_babies, SUM( IF (gender = 'F', number, 0)) AS female_babies FROM `bigquery-public-data.usa_names.usa_1910_current` GROUP BY name ), both_genders AS ( SELECT name, (male_babies + female_babies) AS num_babies, SAFE_DIVIDE(male_babies, male_babies + female_babies) AS frac_male FROM all_babies WHERE male_babies > 0 AND female_babies > 0 ) SELECT * FROM both_genders WHERE frac_male BETWEEN 0.3 AND 0.7 ORDER BY num_babies DESC LIMIT 5

L'operazione ha richiesto circa 2,4 secondi, un miglioramento di circa 30 volte.

Riduci i dati di cui eseguire il join

È possibile eseguire la query precedente con un join efficiente a patto di ridurre la quantità di dati di cui eseguire il join raggruppando inizialmente i dati per nome e sesso:

• Prova la seguente query:

  WITH all_names AS ( SELECT name, gender, SUM(number) AS num_babies FROM `bigquery-public-data`.usa_names.usa_1910_current GROUP BY name, gender ), male_names AS ( SELECT name, num_babies FROM all_names WHERE gender = 'M' ), female_names AS ( SELECT name, num_babies FROM all_names WHERE gender = 'F' ), ratio AS ( SELECT name, (f.num_babies + m.num_babies) AS num_babies, m.num_babies / (f.num_babies + m.num_babies) AS frac_male FROM male_names AS m JOIN female_names AS f USING (name) ) SELECT * FROM ratio WHERE frac_male BETWEEN 0.3 AND 0.7 ORDER BY num_babies DESC LIMIT 5

  Il raggruppamento iniziale consente di ridurre i dati all'inizio della query, prima che quest'ultima esegua un JOIN. In questo modo, lo shuffling e altre operazioni complesse vengono eseguite solo su dati notevolmente ridotti e rimangono abbastanza efficienti. La query precedente è stata completata in circa 2 secondi e ha restituito il risultato corretto.

Utilizza una funzione finestra al posto di un self-join

Supponiamo di voler trovare il tempo che intercorre tra la riconsegna di una bicicletta e il suo successivo noleggio, ovvero il tempo di permanenza della bicicletta presso la stazione di noleggio. Questo è un esempio di relazione dipendente tra righe. Potrebbe sembrare che l'unico modo per risolvere questo problema sia eseguire il join della tabella con se stessa, confrontando la data di fine (end_date) di un noleggio con la data di inizio (start_date) di quello successivo (assicurati che la query sia in esecuzione nella regione eu (multiple regions in European Union). Fai clic su +Crea query SQL, quindi seleziona Altro > Impostazioni query > Opzioni aggiuntivi e verifica che sia selezionata la selezione Località automatica.

1. Tuttavia, puoi evitare un self-join utilizzando una funzione finestra:

SELECT bike_id, start_date, end_date, TIMESTAMP_DIFF( start_date, LAG(end_date) OVER (PARTITION BY bike_id ORDER BY start_date), SECOND) AS time_at_station FROM `bigquery-public-data`.london_bicycles.cycle_hire LIMIT 5

2. Utilizzando questa query, possiamo calcolare il tempo medio di inutilizzo di una bicicletta in ciascuna stazione di noleggio e classificare le stazioni in base a questa misura:

WITH unused AS ( SELECT bike_id, start_station_name, start_date, end_date, TIMESTAMP_DIFF(start_date, LAG(end_date) OVER (PARTITION BY bike_id ORDER BY start_date), SECOND) AS time_at_station FROM `bigquery-public-data`.london_bicycles.cycle_hire ) SELECT start_station_name, AVG(time_at_station) AS unused_seconds FROM unused GROUP BY start_station_name ORDER BY unused_seconds ASC LIMIT 5

Esegui join con valori precalcolati

A volte può essere utile precalcolare le funzioni su tabelle più piccole e quindi eseguirne il join con i valori precalcolati anziché ripetere ogni volta un calcolo costoso.

Ad esempio, supponiamo di voler trovare le due stazioni di noleggio tra cui i nostri clienti si spostano in bicicletta più velocemente. Per calcolare il ritmo (minuti per chilometro) di pedalata, dobbiamo dividere la durata dello spostamento per la distanza tra le stazioni di noleggio.

1. Potremmo creare una tabella denormalizzata con le distanze tra le stazioni di noleggio e quindi calcolare il ritmo medio:

WITH denormalized_table AS ( SELECT start_station_name, end_station_name, ST_DISTANCE(ST_GeogPoint(s1.longitude, s1.latitude), ST_GeogPoint(s2.longitude, s2.latitude)) AS distance, duration FROM `bigquery-public-data`.london_bicycles.cycle_hire AS h JOIN `bigquery-public-data`.london_bicycles.cycle_stations AS s1 ON h.start_station_id = s1.id JOIN `bigquery-public-data`.london_bicycles.cycle_stations AS s2 ON h.end_station_id = s2.id ), durations AS ( SELECT start_station_name, end_station_name, MIN(distance) AS distance, AVG(duration) AS duration, COUNT(*) AS num_rides FROM denormalized_table WHERE duration > 0 AND distance > 0 GROUP BY start_station_name, end_station_name HAVING num_rides > 100 ) SELECT start_station_name, end_station_name, distance, duration, duration/distance AS pace FROM durations ORDER BY pace ASC LIMIT 5

La query precedente richiama la funzione geospaziale ST_DISTANCE una volta per ciascuna riga nella tabella cycle_hire (24 milioni di volte), richiede circa 14,7 secondi ed elabora circa 1,9 GB di dati.

2. In alternativa, possiamo utilizzare la tabella cycle_stations per precalcolare la distanza tra ogni coppia di stazioni (questo è un self-join) e quindi eseguirne il join con la tabella di dimensioni ridotte della durata media tra le stazioni di noleggio:

WITH distances AS ( SELECT a.id AS start_station_id, a.name AS start_station_name, b.id AS end_station_id, b.name AS end_station_name, ST_DISTANCE(ST_GeogPoint(a.longitude, a.latitude), ST_GeogPoint(b.longitude, b.latitude)) AS distance FROM `bigquery-public-data`.london_bicycles.cycle_stations a CROSS JOIN `bigquery-public-data`.london_bicycles.cycle_stations b WHERE a.id != b.id ), durations AS ( SELECT start_station_id, end_station_id, AVG(duration) AS duration, COUNT(*) AS num_rides FROM `bigquery-public-data`.london_bicycles.cycle_hire WHERE duration > 0 GROUP BY start_station_id, end_station_id HAVING num_rides > 100 ) SELECT start_station_name, end_station_name, distance, duration, duration/distance AS pace FROM distances JOIN durations USING (start_station_id, end_station_id) ORDER BY pace ASC LIMIT 5

La query riformulata con i join più efficienti richiede solo circa 8,2 secondi (una velocità di circa 1,8 volte maggiore) ed elabora circa 554 MB di dati con costi ridotti di quasi 4 volte.

Fai clic su Controlla i miei progressi per verificare l'obiettivo.

Esegui join efficienti

Attività 4: evita di sovraccaricare un worker

Alcune operazioni (ad esempio ordinare dati) devono essere eseguite su un singolo worker. Dover ordinare troppi dati può sovraccaricare la memoria di un worker e provocare un errore di "risorse superate". Evita di sovraccaricare il worker con troppi dati. Man mano che viene eseguito l'upgrade dell'hardware dei data center Google, il significato di "troppo" in questo contesto si espande nel tempo. Attualmente, siamo nell'ordine di 1 GB.

Limita gli ordinamenti di grandi dimensioni

1. Supponiamo di voler scorrere i noleggi e numerarli 1, 2, 3 e così via nell'ordine in base a cui termina il noleggio. Potremmo farlo utilizzando la funzione ROW_NUMBER():

SELECT rental_id, ROW_NUMBER() OVER(ORDER BY end_date) AS rental_number FROM `bigquery-public-data.london_bicycles.cycle_hire` ORDER BY rental_number ASC LIMIT 5

Occorrono 34,5 secondi per elaborare solo 372 MB perché è necessario ordinare l'intero set di dati sulle biciclette di Londra in un singolo worker. Se avessimo elaborato un set di dati più grande, avremmo sovraccaricato il worker.

2. Potremmo valutare se sia possibile limitare gli ordinamenti di grandi dimensioni e distribuirli. In effetti, è possibile estrarre la data dai noleggi e poi ordinare gli spostamenti di ciascun giorno:

WITH rentals_on_day AS ( SELECT rental_id, end_date, EXTRACT(DATE FROM end_date) AS rental_date FROM `bigquery-public-data.london_bicycles.cycle_hire` ) SELECT rental_id, rental_date, ROW_NUMBER() OVER(PARTITION BY rental_date ORDER BY end_date) AS rental_number_on_day FROM rentals_on_day ORDER BY rental_date ASC, rental_number_on_day ASC LIMIT 5

La query richiede circa 15,1 secondi (una velocità di circa 2 volte maggiore) perché l'ordinamento può essere eseguito solo su un singolo giorno di dati alla volta.

Fai clic su Controlla i miei progressi per verificare l'obiettivo.

Evita di sovraccaricare un worker

Distorsione dei dati

Lo stesso problema del sovraccaricamento di un worker (in questo caso, sovraccaricamento della memoria di un worker) può verificarsi durante una funzione ARRAY_AGG con GROUP BY se una delle chiavi è molto più comune delle altre.

1. Poiché esistono più di 3 milioni di repository GitHub e i commit sono ben distribuiti tra loro, questa query ha esito positivo (assicurati di eseguire la query nel centro di elaborazione us (multiple regions in United States)):

SELECT repo_name, ARRAY_AGG(STRUCT(author, committer, subject, message, trailer, difference, encoding) ORDER BY author.date.seconds) FROM `bigquery-public-data.github_repos.commits`, UNNEST(repo_name) AS repo_name GROUP BY repo_name

Nota: anche se avrà esito positivo, la query potrebbe richiedere fino a 30 minuti. Se comprendi la query, vai avanti nel lab.

2. La maggior parte delle persone che utilizzano GitHub vivono in zone che rientrano in pochi fusi orari, quindi il raggruppamento in base al fuso orario ha esito negativo. Chiediamo a un singolo worker di ordinare una frazione significativa di 750 GB:

SELECT author.tz_offset, ARRAY_AGG(STRUCT(author, committer, subject, message, trailer, difference, encoding) ORDER BY author.date.seconds) FROM `bigquery-public-data.github_repos.commits` GROUP BY author.tz_offset

3. Se è necessario ordinare tutti i dati, utilizza chiavi più granulari (distribuendo cioè i dati del gruppo su più worker) e quindi aggrega i risultati corrispondenti alla chiave desiderata. Ad esempio, invece di eseguire il raggruppamento solo in base al fuso orario, è possibile eseguirlo sia in base a timezone che a repo_name e quindi aggregare i repository per ottenere la risposta effettiva per ciascun fuso orario:

SELECT repo_name, author.tz_offset, ARRAY_AGG(STRUCT(author, committer, subject, message, trailer, difference, encoding) ORDER BY author.date.seconds) FROM `bigquery-public-data.github_repos.commits`, UNNEST(repo_name) AS repo_name GROUP BY repo_name, author.tz_offset

Nota: anche se avrà esito positivo, la query potrebbe richiedere più di 15 minuti. Se comprendi la query, vai avanti nel lab.

Attività 5: utilizza funzioni di aggregazione approssimate

BigQuery fornisce approssimazioni rapide delle funzioni aggregate utilizzando poca memoria. Invece di utilizzare COUNT(DISTINCT…), possiamo utilizzare APPROX_COUNT_DISTINCT su flussi di dati di grandi dimensioni quando una piccola incertezza statistica nel risultato è tollerabile.

Conteggio approssimato

1. Possiamo trovare il numero di repository GitHub univoci utilizzando:

SELECT COUNT(DISTINCT repo_name) AS num_repos FROM `bigquery-public-data`.github_repos.commits, UNNEST(repo_name) AS repo_name

La query precedente richiede 8,3 secondi per calcolare il risultato corretto di 3.347.770.

2. Utilizzando la funzione approssimata:

SELECT APPROX_COUNT_DISTINCT(repo_name) AS num_repos FROM `bigquery-public-data`.github_repos.commits, UNNEST(repo_name) AS repo_name

La query precedente richiede circa 3,9 secondi (una velocità di circa 2 volte maggiore) e restituisce un risultato approssimato di 3.399.473, che sovrastima la risposta corretta di circa l'1,5%.

L'algoritmo approssimato è molto più efficiente dell'algoritmo esatto solo su set di dati di grandi dimensioni ed è consigliato nei casi d'uso in cui sono tollerabili errori di circa l'1%. Prima di utilizzare la funzione approssimata, esegui la misurazione del tuo caso d'uso.

Altre funzioni approssimate disponibili includono APPROX_QUANTILES per calcolare i percentili, APPROX_TOP_COUNT per trovare gli elementi principali e APPROX_TOP_SUM per calcolare gli elementi principali in base alla somma di un elemento.

Fai clic su Controlla i miei progressi per verificare l'obiettivo.

Utilizza funzioni di aggregazione approssimate

Complimenti!

Hai appreso una serie di tecniche per migliorare potenzialmente le prestazioni delle query. Quando prendi in considerazione alcune di queste tecniche, ricorda la citazione del celebre informatico Donald Knuth: "L'ottimizzazione prematura è la radice di tutti i mali".

Prossimi passi/Scopri di più

• Documentazione sull'ottimizzazione delle prestazioni delle query della piattaforma Google Cloud.
• Best practice per il controllo dei costi di BigQuery.

Termina il lab

Una volta completato il lab, fai clic su Termina lab. Google Cloud Skills Boost rimuove le risorse che hai utilizzato ed esegue la pulizia dell'account.

Avrai la possibilità di inserire una valutazione in merito alla tua esperienza. Seleziona il numero di stelle applicabile, inserisci un commento, quindi fai clic su Invia.

Il numero di stelle corrisponde alle seguenti valutazioni:

• 1 stella = molto insoddisfatto
• 2 stelle = insoddisfatto
• 3 stelle = esperienza neutra
• 4 stelle = soddisfatto
• 5 stelle = molto soddisfatto

Se non vuoi lasciare un feedback, chiudi la finestra di dialogo.

Per feedback, suggerimenti o correzioni, utilizza la scheda Assistenza.

Copyright 2020 Google LLC Tutti i diritti riservati. Google e il logo Google sono marchi di Google LLC. Tutti gli altri nomi di società e prodotti sono marchi delle rispettive società a cui sono associati.