Esegui query sui dati in BigQuery da JupyterLab

Esegui query sui dati in BigQuery da JupyterLab

Questa pagina mostra come eseguire query sui dati archiviati in BigQuery dall'interfaccia JupyterLab della tua istanza di Vertex AI Workbench.

Metodi per eseguire query sui dati BigQuery nei file del blocco note (IPYNB)

Per eseguire query sui dati di BigQuery da un file di blocco note JupyterLab, puoi utilizzare il comando magic %%bigquery e la libreria client di BigQuery per Python.

Le istanze di Vertex AI Workbench includono anche un'integrazione di BigQuery che consente di sfogliare ed eseguire query sui dati dall'interfaccia JupyterLab.

In questa pagina viene descritto come utilizzare ognuno di questi metodi.

Prima di iniziare

Se non l'hai ancora fatto, crea un'istanza di Vertex AI Workbench.

Ruoli obbligatori

Per assicurarti che l'account di servizio dell'istanza disponga delle autorizzazioni necessarie per eseguire query sui dati in BigQuery, chiedi all'amministratore di concedere all'account di servizio dell'istanza il ruolo IAM Service Usage Consumer (roles/serviceusage.serviceUsageConsumer) sul progetto. Per saperne di più sulla concessione dei ruoli, consulta Gestire l'accesso.

L'amministratore potrebbe anche essere in grado di concedere all'account di servizio dell'istanza le autorizzazioni richieste tramite ruoli personalizzati o altri ruoli predefiniti.

Apri JupyterLab

1. Nella console Google Cloud, vai alla pagina Istanze.

   Vai a Istanze

2. Accanto al nome dell'istanza di Vertex AI Workbench, fai clic su Apri JupyterLab.

   L'istanza di Vertex AI Workbench apre JupyterLab.

Esplora le risorse BigQuery

L'integrazione di BigQuery fornisce un riquadro da cui esplorare le risorse BigQuery a cui hai accesso.

1. Nel menu di navigazione di JupyterLab, fai clic su  BigQuery in Notebooks.

   Il riquadro BigQuery elenca i progetti e i set di dati disponibili in cui puoi eseguire le attività come segue:

   • Per visualizzare la descrizione di un set di dati, fai doppio clic sul suo nome.
   • Per mostrare tabelle, viste e modelli di un set di dati, espandi il set di dati.
   • Per aprire una descrizione riepilogativa come scheda in JupyterLab, fai doppio clic su una tabella, una vista o un modello.

   Nota: nella descrizione riepilogativa di una tabella, fai clic sulla scheda Anteprima per visualizzare l'anteprima dei dati della tabella. L'immagine seguente mostra un'anteprima della tabella international_top_terms trovata nel set di dati google_trends nel progetto bigquery-public-data:

   

Query sui dati utilizzando il comando magic %%bigquery

In questa sezione scriverai SQL direttamente nelle celle del blocco note e leggi i dati da BigQuery nel blocco note Python.

I comandi magici che utilizzano un carattere di percentuale singola o doppia (% o %%) consentono di utilizzare una sintassi minima per interagire con BigQuery all'interno del blocco note. La libreria client di BigQuery per Python viene installata automaticamente in un'istanza di Vertex AI Workbench. Dietro le quinte, il comando magico %%bigquery utilizza la libreria client di BigQuery per Python per eseguire la query specificata, convertire i risultati in un DataFrame pandas, salvare facoltativamente i risultati in una variabile e poi visualizzare i risultati.

Nota: a partire dalla versione 1.26.0 del pacchetto Python google-cloud-bigquery, l'API BigQuery Storage viene utilizzata per impostazione predefinita per scaricare i risultati dal pacchetto magico %%bigquery.

1. Per aprire un file di blocco note, seleziona File > Nuovo > Blocco note.

2. Nella finestra di dialogo Seleziona kernel, seleziona Python 3, quindi fai clic su Seleziona.

   Si apre il nuovo file IPYNB.

3. Per ottenere il numero di regioni per paese nel set di dati international_top_terms, inserisci la seguente istruzione:

   %%bigquery
   SELECT
     country_code,
     country_name,
     COUNT(DISTINCT region_code) AS num_regions
   FROM
     `bigquery-public-data.google_trends.international_top_terms`
   WHERE
     refresh_date = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)
   GROUP BY
     country_code,
     country_name
   ORDER BY
     num_regions DESC;
   

4. Fai clic su  Esegui cella.

   L'output è simile al seguente:

   Query complete after 0.07s: 100%|██████████| 4/4 [00:00<00:00, 1440.60query/s]
   Downloading: 100%|██████████| 41/41 [00:02><00:00, 20.21rows/s]
   ... country_code country_name num_regions 0 TR Turkey 81 1 TH Thailand 77 2 VN Vietnam 63 3 JP Japan 47 4 RO Romania 42 5 NG Nigeria 37 6 IN India 36 7 ID Indonesia 34 8 CO Colombia 33 9 MX Mexico 32 10 BR Brazil 27 11 EG Egypt 27 12 UA Ukraine 27 13 CH Switzerland 26 14 AR Argentina 24 15 FR France 22 16 SE Sweden 21 17 HU Hungary 20 18 IT Italy 20 19 PT Portugal 20 20 NO Norway 19 21 FI Finland 18 22 NZ New Zealand 17 23 PH Philippines 17>

   Nota: i tuoi risultati potrebbero essere diversi da quelli sopra riportati perché il google_trends set di dati oggetto della query viene aggiornato costantemente con nuovi dati.

5. Nella cella successiva (sotto l'output della cella precedente), inserisci il comando seguente per eseguire la stessa query, ma questa volta salva i risultati in un nuovo DataFrame pandas denominato regions_by_country. Devi fornire questo nome utilizzando un argomento con il comando magic %%bigquery.

   %%bigquery regions_by_country
   SELECT
     country_code,
     country_name,
     COUNT(DISTINCT region_code) AS num_regions
   FROM
     `bigquery-public-data.google_trends.international_top_terms`
   WHERE
     refresh_date = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)
   GROUP BY
     country_code, country_name
   ORDER BY
     num_regions DESC;
   

   Nota: per ulteriori informazioni sugli argomenti disponibili per il comando %%bigquery, consulta la documentazione relativa alla magia della libreria client.

6. Fai clic su  Esegui cella.

7. Nella cella successiva, inserisci il seguente comando per esaminare le prime righe dei risultati della query che hai appena letto:

   regions_by_country.head()
   

8. Fai clic su  Esegui cella.

   Il DataFrame regions_by_country di pandas è pronto per essere tracciato.

Eseguire query sui dati utilizzando direttamente la libreria client di BigQuery

In questa sezione utilizzerai la libreria client di BigQuery per Python per leggere direttamente i dati nel blocco note Python.

La libreria client offre un maggiore controllo sulle query e consente di utilizzare configurazioni più complesse per query e job. Le integrazioni della libreria con panda ti consentono di combinare la potenza dell'SQL dichiarativo con il codice imperativo (Python) per analizzare, visualizzare e trasformare i dati.

Nota: puoi utilizzare varie librerie di visualizzazione, analisi dei dati e data wrangling in Python, ad esempio numpy, pandas, matplotlib e molte altre. Molte di queste librerie sono basate su un oggetto DataFrame.

1. Nella cella successiva, inserisci il seguente codice Python per importare la libreria client BigQuery per Python e inizializzare un client:

   from google.cloud import bigquery
   
   client = bigquery.Client()
   

   Il client BigQuery viene utilizzato per inviare e ricevere messaggi dall'API BigQuery.

2. Fai clic su  Esegui cella.

3. Nella cella successiva, inserisci il codice seguente per recuperare la percentuale di termini più popolari giornalieri negli Stati Uniti top_terms che si sovrappongono nel tempo in base al numero di giorni di distanza. L'idea qui è esaminare i termini principali di ogni giorno e vedere quale percentuale si sovrappone ai termini principali del giorno precedente, dei due giorni prima, dei tre giorni prima e così via (per tutte le coppie di date in un intervallo di circa un mese).

   sql = """
   WITH
     TopTermsByDate AS (
       SELECT DISTINCT refresh_date AS date, term
       FROM `bigquery-public-data.google_trends.top_terms`
     ),
     DistinctDates AS (
       SELECT DISTINCT date
       FROM TopTermsByDate
     )
   SELECT
     DATE_DIFF(Dates2.date, Date1Terms.date, DAY)
       AS days_apart,
     COUNT(DISTINCT (Dates2.date || Date1Terms.date))
       AS num_date_pairs,
     COUNT(Date1Terms.term) AS num_date1_terms,
     SUM(IF(Date2Terms.term IS NOT NULL, 1, 0))
       AS overlap_terms,
     SAFE_DIVIDE(
       SUM(IF(Date2Terms.term IS NOT NULL, 1, 0)),
       COUNT(Date1Terms.term)
       ) AS pct_overlap_terms
   FROM
     TopTermsByDate AS Date1Terms
   CROSS JOIN
     DistinctDates AS Dates2
   LEFT JOIN
     TopTermsByDate AS Date2Terms
     ON
       Dates2.date = Date2Terms.date
       AND Date1Terms.term = Date2Terms.term
   WHERE
     Date1Terms.date <= Dates2.date
   GROUP BY
     days_apart
   
   ORDER BY
     days_apart;
   """
   pct_overlap_terms_by_days_apart = client.query(sql).to_dataframe()
   
   pct_overlap_terms_by_days_apart.head()
   

   Il codice SQL utilizzato viene incapsulato in una stringa Python e quindi passato al metodo query() per eseguire una query. Il metodo to_dataframe attende il completamento della query e scarica i risultati in un DataFrame pandas utilizzando l'API BigQuery Storage.

4. Fai clic su  Esegui cella.

   Le prime righe di risultati della query vengono visualizzate sotto la cella di codice.

      days_apart   num_date_pairs  num_date1_terms overlap_terms   pct_overlap_terms
    0          0             32               800            800            1.000000
    1          1             31               775            203            0.261935
    2          2             30               750             73            0.097333
    3          3             29               725             31            0.042759
    4          4             28               700             23            0.032857
   

   Nota: i tuoi risultati potrebbero essere diversi da quelli sopra riportati perché il google_trends set di dati oggetto della query viene aggiornato costantemente con nuovi dati.

Per ulteriori informazioni sull'utilizzo delle librerie client di BigQuery, consulta la guida rapida Utilizzo delle librerie client.

Esegui query sui dati utilizzando l'integrazione di BigQuery in Vertex AI Workbench

L'integrazione di BigQuery offre due metodi aggiuntivi per eseguire query sui dati. Questi metodi sono diversi dall'utilizzo del comando magic %%bigquery.

• L'Editor query nella cella è un tipo di cella che puoi utilizzare all'interno dei file del blocco note.

• L'editor di query autonomo si apre come scheda separata in JupyterLab.

All'interno della cella

Per utilizzare l'editor delle query all'interno della cella per eseguire query sui dati di una tabella BigQuery, segui questi passaggi:

1. In JupyterLab, apri un file blocco note (IPYNB) o creane uno nuovo.

2. Per creare un editor di query all'interno della cella, fai clic sulla cella, quindi fai clic sul pulsante  Integrazione BigQuery a destra della cella. In alternativa, in una cella di markdown, inserisci #@BigQuery.

   L'integrazione di BigQuery converte la cella in un editor di query all'interno della cella.

3. In una nuova riga sotto #@BigQuery, scrivi la query utilizzando le istruzioni e i dialetti SQL supportati di BigQuery. Se vengono rilevati errori nella query, nell'angolo in alto a destra di Editor query viene visualizzato un messaggio di errore. Se la query è valida, viene visualizzato il numero stimato di byte da elaborare.

4. Fai clic su Invia query. Vengono visualizzati i risultati della query. Per impostazione predefinita, i risultati della query vengono impaginati in base a 100 righe per pagina e sono limitati a un totale di 1000 righe, ma puoi modificare queste impostazioni in fondo alla tabella dei risultati. Nell'editor query, limita la query ai soli dati necessari per verificare la query. Eseguirai di nuovo questa query in una cella del blocco note in cui, se vuoi, potrai modificare il limite per recuperare il set di risultati completo.

   Nota: il testo e i risultati della query vengono mantenuti dopo la chiusura e la riapertura del file del blocco note.

5. Puoi fare clic su Esegui una query e carica come DataFrame per aggiungere automaticamente una nuova cella contenente un segmento di codice che importa la libreria client di BigQuery per Python, esegue la query in una cella del blocco note e archivia i risultati in un dataframe pandas denominato df.

Indipendente

Per utilizzare l'editor di query autonomo per eseguire query sui dati in una tabella BigQuery, completa i seguenti passaggi:

1. In JupyterLab, nel riquadro BigQuery in Notebooks, fai clic con il pulsante destro del mouse su una tabella e seleziona Tabella delle query oppure fai doppio clic su una tabella per aprire una descrizione in una scheda separata, poi fai clic sul link Tabella delle query.

2. Scrivi la query utilizzando le istruzioni e i dialetti SQL supportati di BigQuery. Se vengono rilevati errori nella query, nell'angolo in alto a destra di Editor query viene visualizzato un messaggio di errore. Se la query è valida, viene visualizzato il numero stimato di byte da elaborare.

3. Fai clic su Invia query. Vengono visualizzati i risultati della query. Per impostazione predefinita, i risultati della query vengono impaginati in base a 100 righe per pagina e sono limitati a un totale di 1000 righe, ma puoi modificare queste impostazioni in fondo alla tabella dei risultati. Nell'editor query, limita la query ai soli dati necessari per verificare la query. Eseguirai di nuovo questa query in una cella del blocco note in cui, se vuoi, potrai modificare il limite per recuperare il set di risultati completo.

4. Puoi fare clic su Copia codice per DataFrame per copiare un segmento di codice che importa la libreria client BigQuery per Python, esegue la query in una cella del blocco note e archivia i risultati in un dataframe pandas denominato df. Incolla questo codice nella cella del blocco note in cui vuoi eseguirlo.

Visualizzare la cronologia delle query e riutilizzare le query

Per visualizzare la cronologia delle query come scheda in JupyterLab, procedi nel seguente modo:

1. Nel menu di navigazione di JupyterLab, fai clic su  BigQuery in Notebooks per aprire il riquadro BigQuery.

2. Nel riquadro BigQuery, scorri verso il basso e fai clic su Cronologia delle query.

   

   Si apre un elenco delle query in una nuova scheda in cui puoi eseguire attività quali:

   • Per visualizzare i dettagli di una query, come l'ID job, la data di esecuzione e il tempo necessario, fai clic sulla query.
   • Per rivedere la query, eseguirla di nuovo o copiarla nel blocco note per un utilizzo futuro, fai clic su Apri query nell'editor.

Nota: se esegui query dopo aver aperto questa scheda, fai clic su  Aggiorna per visualizzare le query più recenti.

Passaggi successivi

• Per esempi su come visualizzare i dati dalle tabelle BigQuery, vedi Esplorare e visualizzare i dati in BigQuery da JupyterLab.

• Per scoprire di più sulla scrittura di query per BigQuery, consulta Esecuzione di job di query interattivi e batch.

• Scopri come controllare l'accesso ai set di dati BigQuery.