Esegui query sui dati in BigQuery da JupyterLab

Esegui query sui dati in BigQuery da JupyterLab

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

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

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

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

In questa pagina viene descritto come utilizzare ciascuno 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 della tua istanza abbia le autorizzazioni necessarie per eseguire query sui dati in BigQuery, chiedi all'amministratore di concedere all'account di servizio della tua istanza il ruolo IAM Consumer di utilizzo del servizio (roles/serviceusage.serviceUsageConsumer) nel progetto. Per saperne di più sulla concessione dei ruoli, consulta Gestire l'accesso a progetti, cartelle e organizzazioni.

L'amministratore potrebbe anche essere in grado di fornire l'account di servizio dell'istanza le autorizzazioni richieste tramite la ruoli o altri ruoli predefiniti ruoli.

Apri JupyterLab

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

   Vai a Istanze

2. Fai clic su Apri JupyterLab accanto al nome dell'istanza di Vertex AI Workbench.

   L'istanza di Vertex AI Workbench apre JupyterLab.

Esplora le risorse BigQuery

L'integrazione di BigQuery fornisce un riquadro per 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 seguenti attività:

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

   Nota:nella descrizione di riepilogo di una tabella, fai clic sul pulsante Anteprima. per visualizzare l'anteprima dei dati di una tabella. L'immagine seguente mostra un'anteprima del Tabella international_top_terms trovato nel set di dati google_trends nel progetto bigquery-public-data:

   

Esegui query sui dati utilizzando il comando magico %%bigquery

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

I comandi magici che utilizzano uno o due caratteri percentuali (% o %%) consentono di utilizzare una sintassi minima per interagire con BigQuery all'interno un blocco note personalizzato. La libreria client BigQuery per Python viene automaticamente installato in un'istanza di Vertex AI Workbench. Dietro le quinte, la magia di %%bigquery utilizza la libreria client di BigQuery per Python al fine di eseguire una data query, converti i risultati in un DataFrame pandas e, facoltativamente, salva i risultati in una variabile, quindi mostra i risultati.

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

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

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

   Si apre il nuovo file IPYNB.

3. Per ottenere il numero di regioni per paese in 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 risultati potrebbero essere diversi da quelli riportati sopra, in quanto il set di dati google_trends su cui viene eseguita la query viene aggiornato con nuovi dati in modo continuativo.

5. Nella cella successiva (sotto l'output della cella precedente), inserisci il seguente comando per eseguire la stessa query, ma questa volta salva i risultati in un nuovo DataFrame Pandas denominato regions_by_country. Sei tu a garantire che utilizzando un argomento con il comando magico %%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 la classe %%bigquery, consulta la documentazione relativa alle librerie client relative alle funzioni magiche.

6. Fai clic su  Esegui cella.

7. Nella cella successiva, inserisci il comando seguente 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 pandas regions_by_country è pronto per il grafico.

Query sui dati utilizzando direttamente la libreria client di BigQuery

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

La libreria client ti offre un maggiore controllo sulle query e ti consente di utilizzare configurazioni più complesse per query e job. Integrazioni della libreria con Pandas puoi combinare la potenza del linguaggio SQL dichiarativo con di codice (Python) per aiutarti ad analizzare, visualizzare e trasformare i tuoi dati.

Nota:puoi utilizzare varie funzionalità di analisi dei dati, data wrangling e librerie di visualizzazione, come numpy, pandas, matplotlib e molte altri. Molte di queste librerie sono basate su un oggetto DataFrame.

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

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

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

2. Fai clic su  Esegui cella.

3. Nella cella successiva, inserisci il seguente codice per recuperare la percentuale di termini principali giornalieri negli Stati Uniti top_terms che si sovrappongono nel tempo in base al numero di giorni di distanza. L'idea è esaminare i termini principali di ogni giorno e vedere quale percentuale si sovrappone ai termini principali del giorno precedente, di 2 giorni prima, di 3 giorni prima e così via (per tutte le coppie di date nell'arco 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 pandas tramite l'API BigQuery Storage.

4. Fai clic su  Esegui cella.

   Le prime righe dei 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 differire da quanto sopra in quanto google_trends il set di dati su cui viene eseguita la query viene costantemente aggiornato 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 magico %%bigquery.

• L'editor di query in-cell è un tipo di cella che puoi utilizzare all'interno dei file del tuo notebook.

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

In-cell

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

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

2. Per creare un editor di query in-cell, fai clic sulla cella, quindi a destra della cella fai clic sul pulsante  Integrazione BigQuery. Oppure in una cella di markdown, inserisci #@BigQuery.

   L'integrazione di BigQuery converte la cella in un editor di query in-cell.

3. In una nuova riga sotto #@BigQuery, scrivi la query utilizzando le istruzioni e i dialetti SQL supportati e BigQuery. Se vengono rilevati errori nella query, viene visualizzato un messaggio di errore. nell'angolo in alto a destra dell'editor di query. 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 sono suddivisi in pagine da 100 righe ciascuna e sono limitati a 1000 righe in totale, ma puoi modificare queste impostazioni nella parte inferiore della tabella dei risultati. Nella editor di query, limita la query ai soli dati necessari per verificare la query. Eseguirai di nuovo questa query in una cella del notebook, dove potrai modificare il limite per recuperare l'intero insieme di risultati, se vuoi.

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

5. Puoi fare clic su Esegui query e carica come DataFrame per aggiungere automaticamente una nuova cella contenente 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.

Autonomo

Come utilizzare l'editor di query autonomo per eseguire query sui dati in una tabella BigQuery, segui questi passaggi:

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

2. Scrivi la query utilizzando le istruzioni supportate e i dialetti SQL di BigQuery. Se vengono rilevati errori nella query, viene visualizzato un messaggio di errore nell'angolo in alto a destra dell'editor query. 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 sono suddivisi in pagine da 100 righe ciascuna e sono limitati a 1000 righe in totale, ma puoi modificare queste impostazioni nella parte inferiore della tabella dei risultati. Nell'editor delle query, limita la query solo ai dati necessari per verificare la query. Eseguirai di nuovo questa query in una cella del blocco note dove puoi regolare il limite per recuperare è possibile impostare risultati completi.

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 notebook e archivia i risultati in un dataframe pandas denominato df. Incolla questo codice in una cella del notebook in cui vuoi eseguirlo.

Visualizzare la cronologia delle query e riutilizzarle

Per visualizzare la cronologia delle query come scheda in JupyterLab:

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

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

   

   In una nuova scheda si apre un elenco delle tue query, in cui puoi eseguire operazioni come:

   • Per visualizzare i dettagli di una query, come l'ID job, quando è stata dell'esecuzione e il tempo impiegato, fai clic sulla query.
   • Per rivedere la query, eseguila di nuovo o copiala nel tuo blocco note per per usi futuri, 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 vedere esempi di come visualizzare i dati delle tue tabelle BigQuery, consulta Esplorare e visualizzare i dati in BigQuery da JupyterLab.

• Per scoprire di più sulla scrittura di query per BigQuery, consulta Eseguire job di query interattive e in batch.

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