Eseguire query sul set di dati di Wikipedia in BigQuery

1. Panoramica

BigQuery è il database di analisi NoOps a basso costo e completamente gestito di Google. Con BigQuery puoi eseguire query su molti terabyte di dati senza dover gestire alcuna infrastruttura e non hai 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.

In questo codelab esplorerai il set di dati di Wikipedia utilizzando BigQuery.

Obiettivi didattici

• Come utilizzare BigQuery
• Come caricare un set di dati reale in BigQuery
• Come scrivere una query per ottenere informazioni dettagliate su un set di dati di grandi dimensioni

Che cosa ti serve

• Un progetto Google Cloud
• Un browser, ad esempio Google Chrome o Firefox

Sondaggio

Come utilizzerai questo tutorial?

Sola lettura Leggilo e completa gli esercizi

Come valuteresti la tua esperienza con Google Cloud?

Principiante Intermedio Avanzato

2. Configurazione e requisiti

Attivare BigQuery

Se non hai ancora un Account Google, devi crearne uno.

1. Accedi alla console Google Cloud e vai a BigQuery. Puoi anche aprire l'interfaccia utente web di BigQuery direttamente inserendo il seguente URL nel browser.

https://console.cloud.google.com/bigquery

2. Accetta i Termini di servizio.
3. Prima di poter utilizzare BigQuery, devi creare un progetto. Segui le istruzioni per creare il nuovo progetto.

Scegli un nome per il progetto e annota l'ID progetto.

L'ID progetto è un nome univoco tra tutti i progetti Google Cloud. In questo codelab verrà chiamato PROJECT_ID.

Questo codelab utilizza le risorse BigQuery entro i limiti della sandbox di BigQuery. Non è richiesto un account di fatturazione. Se in un secondo momento vuoi rimuovere i limiti della sandbox, puoi aggiungere un account di fatturazione registrandoti alla prova senza costi di Google Cloud.

Caricherai il set di dati di Wikipedia nella sezione successiva.

3. Crea un set di dati

Innanzitutto, crea un nuovo set di dati nel progetto. Un set di dati è composto da più tabelle.

1. Per creare un set di dati, fai clic sul nome del progetto nel riquadro delle risorse, quindi fai clic su Crea set di dati:

2. Inserisci lab come ID set di dati:

3. Fai clic su Crea set di dati per creare un set di dati vuoto.

4. Carica i dati con il programma a riga di comando bq

Attiva Cloud Shell

1. Nella console Cloud, fai clic su Attiva Cloud Shell .

Se non hai mai avviato Cloud Shell, viene visualizzata una schermata intermedia (sotto la piega) che ne descrive le funzionalità. In questo caso, fai clic su Continua e non comparirà più. Ecco come si presenta la schermata intermedia:

Bastano pochi istanti per eseguire il provisioning e connettersi a Cloud Shell.

Questa macchina virtuale è caricata con tutti gli strumenti per sviluppatori di cui hai bisogno. Offre una home directory permanente da 5 GB e viene eseguita in Google Cloud, migliorando notevolmente le prestazioni e l'autenticazione della rete. Gran parte del lavoro per questo codelab, se non tutto, può essere svolto semplicemente con un browser o con Chromebook.

Una volta eseguita la connessione a Cloud Shell, dovresti vedere che il tuo account è già autenticato e il progetto è già impostato sul tuo ID progetto.

2. Esegui questo comando in Cloud Shell per verificare che l'account sia autenticato:

gcloud auth list

Output comando

 Credentialed Accounts
ACTIVE  ACCOUNT
*       <my_account>@<my_domain.com>

To set the active account, run:
    $ gcloud config set account `ACCOUNT`

Nota:lo strumento a riga di comando gcloud è il potente strumento a riga di comando unificato di Google Cloud. È già installato in Cloud Shell. Noterai il supporto del completamento automatico. Per saperne di più, consulta la panoramica dello strumento a riga di comando gcloud.

3. Esegui questo comando in Cloud Shell per verificare che il comando gcloud conosca il tuo progetto:

gcloud config list project

Output comando

[core]
project = <PROJECT_ID>

In caso contrario, puoi impostarlo con questo comando:

gcloud config set project <PROJECT_ID>

Output comando

Updated property [core/project].

Caricare dati in BigQuery

Per comodità, alcuni dati del 10 aprile 2019 del set di dati sulle visualizzazioni di pagina di Wikimedia sono disponibili su Google Cloud Storage all'indirizzo gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-140000.gz. Il file di dati è un file CSV compresso in formato GZip. Puoi caricare questo file direttamente utilizzando l'utilità a riga di comando bq. Nell'ambito del comando di caricamento, descrivi anche lo schema del file.

Puoi trovare l'ID progetto nella console API o dalla riga di comando "gcloud config list" e cercare il valore di "project".

Inoltre, quando esegui bq per la prima volta, potrebbe chiederti di selezionare un progetto predefinito. Una volta selezionato, puoi eseguire di nuovo il comando precedente per caricare i dati.

bq load \
  --source_format CSV \
  --field_delimiter " " \
  --allow_jagged_rows \
  --quote "" \
  --max_bad_records 3 \
  $GOOGLE_CLOUD_PROJECT:lab.pageviews_20190410_140000 \
  gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-140000.gz \
  wiki,title,requests:integer,zero:integer

Il caricamento dei dati potrebbe richiedere alcuni minuti. Attendi il completamento.

Hai utilizzato alcune opzioni avanzate per caricare il file delle visualizzazioni di pagina:

• Imposta --source_format CSV per indicare che il file deve essere analizzato come file CSV. Questo passaggio è facoltativo perché il formato CSV è quello predefinito.
• Imposta --field_delimiter " " per indicare che un singolo spazio viene utilizzato per delimitare i campi.
• Imposta --allow_jagged_rows per includere le righe con un numero inferiore di colonne e ignorare gli errori durante il caricamento del file CSV.
• Imposta --quote "" per indicare che le stringhe non sono tra virgolette.
• Imposta --max_bad_records 3 in modo che ignori al massimo tre errori durante l'analisi del file CSV.

Puoi scoprire di più sulla riga di comando bq nella documentazione.

5. Visualizzare l'anteprima del set di dati

Nella console BigQuery, apri una delle tabelle che hai appena caricato.

1. Espandi il progetto.
2. Espandi il set di dati.
3. Seleziona la tabella.

Puoi visualizzare lo schema della tabella nella scheda Schema. 4. Per scoprire la quantità di dati presenti nella tabella, vai alla scheda Dettagli:

5. Apri la scheda Anteprima per visualizzare una selezione di righe della tabella.

6. Comporre una query

1. Fai clic su Crea nuova query:

Viene visualizzato l'editor di query:

2. Trova il numero totale di visualizzazioni di Wikimedia tra le 14:00 e le 15:00 del 10 aprile 2019 scrivendo questa query:

SELECT SUM(requests)
FROM `lab.pageviews_20190410_140000`

3. Fai clic su Esegui:

Dopo pochi secondi, il risultato viene elencato in basso e indica anche la quantità di dati elaborati:

Questa query ha elaborato 123,9 MB, anche se la tabella è di 691,4 MB. BigQuery elabora solo i byte delle colonne utilizzate nella query, pertanto la quantità totale di dati elaborati può essere notevolmente inferiore alle dimensioni della tabella. Con il clustering e il partizionamento, la quantità di dati elaborati può essere ridotta ulteriormente.

Puoi elaborare/interrogare fino a 1 TB di dati senza costi ogni mese. Per ulteriori dettagli, consulta la pagina dei prezzi di BigQuery.

7. Query più avanzate

Trovare le visualizzazioni di pagina di Wikipedia

Il set di dati Wikimedia contiene le visualizzazioni di pagina di tutti i progetti Wikimedia (inclusi Wikipedia, Wiktionary, Wikibooks e Wikiquote). Restringi la query alle sole pagine di Wikipedia in inglese aggiungendo un'istruzione WHERE:

SELECT SUM(requests), wiki
FROM `lab.pageviews_20190410_140000`
WHERE wiki = "en"
GROUP BY wiki

Tieni presente che, eseguendo una query su una colonna aggiuntiva, wiki, la quantità di dati elaborati è aumentata da 124 MB a 204 MB.

BigQuery supporta molte delle clausole SQL più comuni, come CONTAINS, GROUP BY, ORDER BY e una serie di funzioni di aggregazione. Inoltre, puoi anche utilizzare le espressioni regolari per eseguire query sui campi di testo. Prova:

SELECT title, SUM(requests) requests
FROM `lab.pageviews_20190410_140000`
WHERE
  wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

Scopri di più sulla sintassi BigQuery nella documentazione di riferimento delle query BigQuery.

Eseguire query su più tabelle

Puoi selezionare un intervallo di tabelle per formare l'unione utilizzando una tabella con funzione jolly.

1. Innanzitutto, crea una seconda tabella su cui eseguire query caricando le visualizzazioni di pagina dell'ora successiva in una nuova tabella:

bq load \
  --source_format CSV \
  --field_delimiter " " \
  --quote "" \
  $GOOGLE_CLOUD_PROJECT:lab.pageviews_20190410_150000 \
  gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-150000.gz \
  wiki,title,requests:integer,zero:integer

2. Nell'editor di query, esegui query su entrambe le tabelle caricate eseguendo query sulle tabelle con "pageviews_2019" come prefisso:

SELECT title, SUM(requests) requests
FROM `lab.pageviews_2019*`
WHERE
  wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

Puoi filtrare le tabelle in modo più selettivo con la pseudocolonna _TABLE_SUFFIX. Questa query è limitata alle tabelle corrispondenti al 10 aprile.

SELECT title, SUM(requests) requests
FROM `lab.pageviews_2019*`
WHERE
  _TABLE_SUFFIX BETWEEN '0410' AND '0410_9999999'
  AND wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

8. Pulizia

(Facoltativo) Elimina il set di dati che hai creato con il comando bq rm. Utilizza il flag -r per rimuovere le tabelle che contiene.

bq rm -r lab

9. Complimenti!

Hai utilizzato BigQuery e SQL per eseguire query sul set di dati sulle visualizzazioni di pagine di Wikipedia nel mondo reale. Hai la possibilità di eseguire query su set di dati dell'ordine dei petabyte.

Scopri di più

• Consulta il subreddit di BigQuery per scoprire come altri utilizzano BigQuery oggi.
• Trova i set di dati pubblici disponibili in BigQuery.
• Scopri come caricare dati in BigQuery.