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 interrogare terabyte e terabyte di dati senza dover gestire alcuna infrastruttura e non hai bisogno di un amministratore di database. BigQuery utilizza un ambiente SQL familiare e può sfruttare i vantaggi di un modello di pagamento a consumo. BigQuery permette di concentrarsi sull'analisi dei dati per trovare informazioni significative.

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 insight 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 Livello intermedio Eccellente

2. Configurazione e requisiti

Abilitare BigQuery

Se non disponi già di un Account Google, devi crearne uno.

1. Accedi alla console Google Cloud e vai a BigQuery. Puoi anche aprire direttamente la UI web di BigQuery inserendo il seguente URL nel tuo 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 prendi nota dell'ID.

L'ID progetto è un nome univoco tra tutti i progetti Google Cloud. Verrà indicato più avanti in questo codelab come 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 sotto il 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. Dalla console Cloud, fai clic su Attiva Cloud Shell .

Se non hai mai avviato Cloud Shell, ti viene mostrata una schermata intermedia (below the fold) che descrive di cosa si tratta. In tal caso, fai clic su Continua (e non la vedrai più). Ecco come appare quella singola schermata:

Il provisioning e la connessione a Cloud Shell dovrebbero richiedere solo qualche istante.

Questa macchina virtuale viene caricata con tutti gli strumenti di sviluppo di cui hai bisogno. Offre una home directory permanente da 5 GB e viene eseguita in Google Cloud, migliorando notevolmente le prestazioni di rete e l'autenticazione. Gran parte, se non tutto, del lavoro in questo codelab può essere svolto semplicemente con un browser o 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 è uno strumento a riga di comando potente e unificato disponibile in Google Cloud. È già installato in Cloud Shell. Noterai che supporta il completamento della scheda. Per ulteriori informazioni, consulta la panoramica dello strumento a riga di comando gcloud.

3. Esegui questo comando in Cloud Shell per confermare che il comando gcloud è a conoscenza del 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 praticità, alcuni dati del 10 aprile 2019 del set di dati relativi alle visualizzazioni di pagina di Wikipedia sono disponibili in Google Cloud Storage all'indirizzo gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-140000.gz. Il file di dati è in formato CSV con 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 nella riga di comando: "gcloud config list" e cerca il valore di "project".

Inoltre, quando esegui bq per la prima volta, potrebbe esserti chiesto di selezionare un progetto predefinito. Dopo la selezione, puoi eseguire nuovamente il comando riportato sopra 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 dell'operazione.

Per caricare il file delle visualizzazioni di pagina, hai utilizzato alcune opzioni avanzate:

• Imposta --source_format CSV per indicare che il file deve essere analizzato come file CSV. Questo passaggio è facoltativo perché il formato predefinito è CSV.
• Imposta --field_delimiter " " per indicare che viene utilizzato un singolo spazio per delimitare i campi.
• Imposta --allow_jagged_rows in modo da 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 racchiuse tra virgolette.
• Imposta --max_bad_records 3 per ignorare al massimo tre errori durante l'analisi del file CSV.

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

5. Visualizza 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 conoscere la quantità di dati contenuti nella tabella, vai alla scheda Dettagli:

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

6. Scrivere una query

1. Fai clic su Crea nuova query:

Viene visualizzato l'Editor di query:

2. Trova il numero totale di visualizzazioni 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:

In pochi secondi, il risultato viene elencato in basso e indica anche quanti dati sono stati 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, quindi la quantità totale di dati elaborati può essere notevolmente inferiore a quella della tabella. Con il clustering e il partizionamento, la quantità di dati elaborati può essere ulteriormente ridotta.

Puoi elaborare/eseguire query su un massimo di 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 Wikipedia

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

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

Nota 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 familiari, come CONTAINS, GROUP BY, ORDER BY, e varie funzioni di aggregazione. Inoltre, puoi utilizzare anche espressioni regolari per eseguire query sui campi di testo. Provane una:

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 di BigQuery nella documentazione di riferimento per le query BigQuery.

Eseguire query su più tabelle

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

1. Per prima cosa, crea una seconda tabella su cui eseguire la 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 limita le 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. esegui la pulizia

Se vuoi, elimina il set di dati 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 reale delle visualizzazioni di pagina di Wikipedia. Puoi eseguire query su set di dati dell'ordine dei petabyte.

Scopri di più

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