Carica ed esegui query sui dati con lo strumento a riga di comando bq per BigQuery

1. Introduzione

BigQuery è il database di analisi NoOps a basso costo e completamente gestito di Google. Con BigQuery, puoi eseguire query su terabyte di dati senza un amministratore del database o un'infrastruttura. BigQuery utilizza il noto SQL e un modello di addebito che prevede il pagamento solo per l'utilizzo. Inoltre, ti permette di concentrarti sull'analisi dei dati per trovare insight significativi. In questo codelab, utilizzerai lo strumento a riga di comando bq per caricare un file CSV locale in una nuova tabella BigQuery.

Cosa imparerai a fare

• Come utilizzare lo strumento a riga di comando bq per BigQuery
• Come caricare file di dati locali in una tabella BigQuery

Che cosa ti serve

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

2. Configurazione

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.

Cloud Shell

Utilizzerai Cloud Shell, un ambiente a riga di comando in esecuzione in Google Cloud.

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].

3. Crea un set di dati

Crea un set di dati che contenga le tue tabelle.

Che cos'è un set di dati?

Un set di dati BigQuery è una raccolta di tabelle. Tutte le tabelle di un set di dati vengono archiviate nella stessa posizione dei dati. Puoi anche allegare controlli dell'accesso personalizzati per limitare l'accesso a un set di dati e alle relative tabelle.

Creare un set di dati

In Cloud Shell, utilizza il comando bq mk per creare un set di dati chiamato "bq_load_codelab".

bq mk bq_load_codelab

Visualizzare le proprietà del set di dati

Verifica di aver creato il set di dati visualizzandone le proprietà con il comando bq show.

bq show bq_load_codelab

Dovresti vedere un output simile al seguente:

Dataset my-project:bq_load_codelab

   Last modified           ACLs          Labels
 ----------------- -------------------- --------
  15 Jun 14:12:49   Owners:
                      projectOwners,
                      [e-mail]
                    Writers:
                      projectWriters
                    Readers:
                      projectReaders

4. Creare il file di dati

BigQuery può caricare dati da diversi formati, tra cui JSON delimitato da nuova riga, Avro e CSV. Per semplicità, utilizzerai il formato CSV.

Creare un file CSV

In Cloud Shell, crea un file CSV vuoto.

touch customer_transactions.csv

Apri il file CSV nell'editor di codice in Cloud Shell eseguendo il comando cloudshell edit, che aprirà una nuova finestra del browser con un editor di codice e il riquadro di Cloud Shell.

cloudshell edit customer_transactions.csv

Nota:l'editor di codice richiede i cookie di terze parti per funzionare perché viene caricato da un dominio diverso da quello della console Google Cloud.

Nell'editor di codice, inserisci alcuni valori separati da virgole da caricare in BigQuery.

ID,Zipcode,Timestamp,Amount,Feedback,SKU
c123,78757,2018-02-14 17:01:39Z,1.20,4.7,he4rt5
c456,10012,2018-03-14 15:09:26Z,53.60,3.1,ppiieee
c123,78741,2018-04-01 05:59:47Z,5.98,2.0,ch0c0

Salva il file CSV facendo clic su File > Modifica.

5. Carica dati

Utilizza il comando bq load per caricare il file CSV in una tabella BigQuery.

bq load \
    --source_format=CSV \
    --skip_leading_rows=1 \
    bq_load_codelab.customer_transactions \
    ./customer_transactions.csv \
    id:string,zip:string,ttime:timestamp,amount:numeric,fdbk:float,sku:string

Hai utilizzato le seguenti opzioni:

• --source_format=CSV utilizza il formato dati CSV durante l'analisi del file di dati.
• --skip_leading_rows=1 salta la prima riga del file CSV perché è una riga di intestazione.
• Bq_load_codelab.customer_transactions—the first positional argument— definisce la tabella in cui devono essere caricati i dati.
• ./customer_transactions.csv, il secondo argomento posizionale, definisce il file da caricare. Oltre ai file locali, il comando bq load può caricare file da Cloud Storage con gs://my_bucket/path/to/file URIs.
• Uno schema, che può essere definito in un file di schema JSON o come elenco separato da virgole. (Hai utilizzato un elenco separato da virgole per semplicità.)

Nella tabella customer_transactions hai utilizzato lo schema seguente:

• Id:string: un identificatore cliente
• Zip:string: Un codice postale degli Stati Uniti
• Ttime:timestamp: la data e l'ora in cui è stata eseguita la transazione
• Amount:numeric: l'importo di una transazione (una colonna numerica memorizza i dati in formato decimale, utile per i valori monetari).
• Fdbk:float: La valutazione di un sondaggio di feedback sulla transazione
• Sku:string: un identificatore dell'articolo acquistato

Visualizzare i dettagli della tabella

Verifica che la tabella sia stata caricata mostrando le proprietà della tabella.

bq show bq_load_codelab.customer_transactions

Output:

Table my-project:bq_load_codelab.customer_transactions

   Last modified          Schema          Total Rows   Total Bytes
 ----------------- --------------------- ------------ -------------
  15 Jun 15:13:55   |- id: string         3            159
                    |- zip: string
                    |- ttime: timestamp
                    |- amount: numeric
                    |- fdbk: float
                    |- sku: string

6. Eseguire query sui dati

Ora che i dati sono caricati, puoi eseguire query utilizzando l'interfaccia utente web di BigQuery, il comando bq o l'API. Le query possono unire i tuoi dati a qualsiasi set di dati (o set di dati, purché si trovino nella stessa posizione) per cui disponi dell'autorizzazione di lettura.

Esegui una query SQL standard che unisce il tuo set di dati al set di dati US ZIP codes e somma le transazioni per stato degli Stati Uniti. Utilizza il comando bq query per eseguire la query.

bq query --nouse_legacy_sql '
SELECT SUM(c.amount) AS amount_total, z.state_code AS state_code
FROM `bq_load_codelab.customer_transactions` c
JOIN `bigquery-public-data.utility_us.zipcode_area` z
ON c.zip = z.zipcode
GROUP BY state_code
'

L'output del comando dovrebbe essere simile al seguente:

Waiting on bqjob_r26...05a15b38_1 ... (1s) Current status: DONE   
+--------------+------------+
| amount_total | state_code |
+--------------+------------+
|         53.6 | NY         |
|         7.18 | TX         |
+--------------+------------+

La query che hai eseguito utilizzava un set di dati pubblico e il tuo set di dati privato. Scopri di più leggendo questa versione commentata della stessa query:

#standardSQL
SELECT
  /* Total of all transactions in the state. */
  SUM(c.amount) AS amount_total,

  /* State corresponding to the transaction's zipcode. */
  z.state_code AS state_code

/* Query the table you just constructed.
 * Note: If you omit the project from the table ID,
 *       the dataset is read from your project. */
FROM `bq_load_codelab.customer_transactions` c

/* Join the table to the zipcode public dataset. */
JOIN `bigquery-public-data.utility_us.zipcode_area` z

/* Find the state corresponding to the transaction's zipcode. */
ON c.zip = z.zipcode

/* Group over all transactions by state. */
GROUP BY state_code

7. Esegui la pulizia

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 bq_load_codelab

8. Complimenti!

Hai caricato una tabella in BigQuery ed eseguito una query.

Argomenti trattati

• Utilizzo dello strumento a riga di comando bq per interagire con BigQuery.
• Unire i tuoi dati e un set di dati pubblico con una query BigQuery.

Passaggi successivi

Scopri di più su:

• Lo strumento a riga di comando bq
• Altri modi per caricare i dati in BigQuery.
• Altri set di dati pubblici disponibili tramite BigQuery.
• Dati meteorologici, criminali e di altro tipo in TIL con BigQuery.