Panoramica

BigQuery è il database di analisi NoOps a basso costo e completamente gestito di Google. Con BigQuery puoi interrogare molti terabyte di dati senza dover gestire alcuna infrastruttura o aver bisogno di un amministratore del database. BigQuery utilizza SQL e sfrutta i vantaggi offerti dal modello di pagamento a consumo. Inoltre, ti permette di concentrarti sull'analisi dei dati per trovare insight significativi.

Il set di dati che utilizzerai è un set di dati di e-commerce che ha milioni di record di Google Analytics per il Google Merchandise Store caricati su BigQuery. In questo lab avrai a disposizione una copia del set di dati ed esplorerai i campi e le righe disponibili per ricavare insight.

In questo lab eseguirai query su set di dati partizionati e creerai le tue partizioni del set di dati per migliorare le prestazioni delle query e ridurre il costo.

Configurazione

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: crea un nuovo set di dati

Come prima cosa, creerai un set di dati in cui archiviare le tue tabelle.

1. Crea un nuovo set di dati all'interno del progetto facendo clic sull'icona Visualizza azioni accanto all'ID progetto nella sezione Explorer, poi seleziona Crea set di dati.

2. Imposta l'ID set di dati su ecommerce. Mantieni i valori predefiniti per tutte le altre opzioni (Località dei dati, Scadenza tabella predefinita).

3. Fai clic su Crea set di dati.

Attività 2: crea tabelle partizionate in base alle date

Una tabella partizionata è una tabella divisa in segmenti, denominati partizioni, che semplificano la gestione e l'esecuzione di query sui dati. Dividendo una tabella grande in partizioni più piccole puoi migliorare le prestazioni delle query e controllare i costi riducendo il numero di byte letti da una query.

Ora creerai una nuova tabella e definirai la partizione ponendo come vincolo una colonna con data o timestamp. Come prima cosa, esaminiamo i dati presenti nella tabella non partizionata.

Esegui una query sull'analisi della pagina web per un campione dei visitatori del 2017

1. In Query senza titolo, aggiungi la query seguente. Prima di avviare l'esecuzione, nota che di fianco all'icona dello strumento di convalida query è indicata la quantità di dati che verrà elaborata: "Questa query elaborerà 1,74 GB quando verrà eseguita".

#standardSQL SELECT DISTINCT fullVisitorId, date, city, pageTitle FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE date = '20170708' LIMIT 5

2. Fai clic su Esegui.

La query restituisce 5 risultati.

Esegui una query sull'analisi della pagina web per un campione dei visitatori del 2018

Ora modifichiamo la query per vedere i visitatori del 2018.

1. In Query senza titolo, aggiungi la query seguente:

#standardSQL SELECT DISTINCT fullVisitorId, date, city, pageTitle FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE date = '20180708' LIMIT 5

I Risultati delle query ti mostreranno quanti dati verranno elaborati da questa query.

2. Fai clic su ESEGUI.

Nota che la query elabora nuovamente 1,74 GB anche se restituisce 0 risultati. Perché? Il motore di query deve analizzare tutti i record del set di dati per verificare se soddisfano la condizione di corrispondenza della data espressa nella clausola WHERE. Viene quindi controllato ogni record per confrontare la data con la condizione "20180708".

Inoltre, l'istruzione LIMIT 5 non riduce il totale dei dati elaborati, anche se questo è un equivoco comune.

Casi d'uso comuni per le tabelle partizionate in base alle date

Analizzare tutto il set di dati ogni volta che è necessario confrontare le righe con una condizione WHERE comporta uno spreco di risorse. Ciò è ancora più vero nei casi in cui siamo interessati ai record relativi a un periodo di tempo specifico, come ad esempio:

• Tutte le transazioni dello scorso anno
• Tutte le interazioni dei visitatori negli ultimi 7 giorni
• Tutti i prodotti venduti nell'ultimo mese

Invece di analizzare l'intero set di dati e filtrare in base al campo della data, come abbiamo fatto nelle query precedenti, creeremo una tabella partizionata in base alle date. In questo modo potremo ignorare completamente i record delle partizioni non pertinenti per la nostra query.

Crea una nuova tabella partizionata in base alle date

1. Fai clic su + Query SQL per comporre una nuova query e aggiungi la query seguente, quindi fai clic su Esegui:

#standardSQL CREATE OR REPLACE TABLE ecommerce.partition_by_day PARTITION BY date_formatted OPTIONS( description="a table partitioned by date" ) AS SELECT DISTINCT PARSE_DATE("%Y%m%d", date) AS date_formatted, fullvisitorId FROM `data-to-insights.ecommerce.all_sessions_raw`

Nota che in questa query è presente una nuova opzione: PARTITION BY + un campo. Le due opzioni disponibili per la partizione sono DATE e TIMESTAMP. Viene utilizzata la funzione PARSE_DATE nel campo della data (archiviato come stringa) per inserirlo nel tipo DATE corretto per il partizionamento.

2. Fai clic sul set di dati ecommerce, quindi seleziona la nuova tabella partiton_by_day:

3. Fai clic sulla scheda Dettagli.

4. Assicurati che nella sezione Informazioni tabella sia visualizzato quanto segue:

• Partizionata in base a: giorno
• Partizionamento in data: date_formatted

Nota: le partizioni nelle tabelle partizionate nel tuo account Qwiklabs scadranno automaticamente 60 giorni dopo il valore riportato nella colonna della data. Il tuo account Google Cloud personale abilitato per la fatturazione ti consente di avere tabelle partizionate senza scadenza.

Ai fini di questo lab, le query restanti verranno eseguite su tabelle partizionate che sono già state create.

Attività 3: visualizza i dati elaborati con una tabella partizionata

1. Esegui questa query facendo attenzione ai byte totali che verranno elaborati:

#standardSQL SELECT * FROM `data-to-insights.ecommerce.partition_by_day` WHERE date_formatted = '2016-08-01'

Questa volta vengono elaborati circa 25 KB o 0,025 MB, che rappresentano solo una frazione dei dati interessati dalla query.

2. Ora esegui questa query, facendo attenzione ai byte totali che verranno elaborati:

#standardSQL SELECT * FROM `data-to-insights.ecommerce.partition_by_day` WHERE date_formatted = '2018-07-08'

Dovresti vedere il messaggio Questa query elaborerà 0 B quando verrà eseguita.

Perché vengono elaborati 0 byte?

Attività 4: crea una tabella partizionata con scadenza automatica

Le tabelle partizionate con scadenza automatica vengono usate per rispettare le norme sulla privacy dei dati e per evitare di utilizzare spazio di archiviazione non necessario (che in un ambiente di produzione sarebbe a pagamento). Se vuoi creare una finestra temporale continua di dati, aggiungi una data di scadenza: in questo modo, quando avrai finito di usarla, la partizione scomparirà.

Esplora le tabelle di dati disponibili sul meteo NOAA

1. Nel riquadro a sinistra, fai clic su + Aggiungi dati e seleziona Set di dati pubblici.

2. Cerca GSOD NOAA e poi seleziona il set di dati.

3. Fai clic su Visualizza set di dati.

4. Scorri le tabelle nel set di dati noaa_gsod (con sharding automatico e non partizionate).

5. A questo punto, copia e incolla la query seguente in Query senza titolo:

#standardSQL SELECT DATE(CAST(year AS INT64), CAST(mo AS INT64), CAST(da AS INT64)) AS date, (SELECT ANY_VALUE(name) FROM `bigquery-public-data.noaa_gsod.stations` AS stations WHERE stations.usaf = stn) AS station_name, -- Stations may have multiple names prcp FROM `bigquery-public-data.noaa_gsod.gsod*` AS weather WHERE prcp < 99.9 -- Filter unknown values AND prcp > 0 -- Filter stations/days with no precipitation AND _TABLE_SUFFIX >= '2021' ORDER BY date DESC -- Where has it rained/snowed recently LIMIT 10

6. Nota la funzione carattere jolly nella tabella * usata nella clausola FROM per limitare la quantità di tabelle a cui fa riferimento il filtro TABLE_SUFFIX.

7. Nota che, nonostante l'aggiunta di LIMIT 10, il totale dei dati analizzati (circa 141,6 MB) non viene ridotto, poiché non sono ancora presenti partizioni.

8. Fai clic su Esegui.

9. Verifica che la data sia formattata correttamente e che il campo precipitation non contenga valori uguali a zero.

Attività 5: crea la tua tabella partizionata

• Modifica la query precedente per creare una tabella con le seguenti specifiche:

  • Nome della tabella: ecommerce.days_with_rain
  • Usa il campo date come PARTITION BY
  • Per OPTIONS, specifica partition_expiration_days = 60
  • Aggiungi la descrizione tabella = "weather stations with precipitation, partitioned by day"

La tua query dovrebbe essere simile alla seguente:

#standardSQL CREATE OR REPLACE TABLE ecommerce.days_with_rain PARTITION BY date OPTIONS ( partition_expiration_days=60, description="weather stations with precipitation, partitioned by day" ) AS SELECT DATE(CAST(year AS INT64), CAST(mo AS INT64), CAST(da AS INT64)) AS date, (SELECT ANY_VALUE(name) FROM `bigquery-public-data.noaa_gsod.stations` AS stations WHERE stations.usaf = stn) AS station_name, -- Stations may have multiple names prcp FROM `bigquery-public-data.noaa_gsod.gsod*` AS weather WHERE prcp < 99.9 -- Filter unknown values AND prcp > 0 -- Filter AND _TABLE_SUFFIX >= '2021'

Verifica il funzionamento della scadenza della partizione dei dati

Per verificare che stai archiviando solo i dati degli ultimi 60 giorni fino ad oggi, esegui la query DATE_DIFF per ottenere l'età delle tue partizioni, impostate per scadere dopo 60 giorni.

La query seguente tiene traccia delle precipitazioni medie della stazione meteo NOAA a Wakayama, in Giappone, dove le piogge sono abbondanti.

• Aggiungi questa query ed eseguila:

#standardSQL # avg monthly precipitation SELECT AVG(prcp) AS average, station_name, date, CURRENT_DATE() AS today, DATE_DIFF(CURRENT_DATE(), date, DAY) AS partition_age, EXTRACT(MONTH FROM date) AS month FROM ecommerce.days_with_rain WHERE station_name = 'WAKAYAMA' #Japan GROUP BY station_name, date, today, month, partition_age ORDER BY date DESC; # most recent days first

Attività 6: verifica che partition_age sia sempre pari o inferiore a 60 giorni

Aggiorna la clausola ORDER BY in modo che mostri per prime le partizioni più vecchie. Usa la data che vedi lì.

• Aggiungi questa query ed eseguila:

#standardSQL # avg monthly precipitation SELECT AVG(prcp) AS average, station_name, date, CURRENT_DATE() AS today, DATE_DIFF(CURRENT_DATE(), date, DAY) AS partition_age, EXTRACT(MONTH FROM date) AS month FROM ecommerce.days_with_rain WHERE station_name = 'WAKAYAMA' #Japan GROUP BY station_name, date, today, month, partition_age ORDER BY partition_age DESC Nota: se in futuro esegui nuovamente la query, i risultati varieranno, poiché i dati del meteo e le tue partizioni vengono aggiornati continuamente.

Complimenti!

Hai creato delle tabelle partizionate ed eseguito query in 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.