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 blocchi note gestiti da Vertex AI Workbench.
Metodi per eseguire query sui dati di BigQuery nei file del notebook (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.
Anche le istanze di blocchi note gestiti includono un'integrazione BigQuery che ti consente sfogliare ed eseguire query sui dati dall'interfaccia JupyterLab.
Questa pagina descrive come utilizzare ciascuno di questi metodi.
Prima di iniziare
Se non l'hai ancora fatto, crea un'istanza di blocchi note gestiti.
Apri JupyterLab
Nella console Google Cloud, vai alla pagina Blocchi note gestiti.
Fai clic su Apri JupyterLab accanto al nome dell'istanza di blocchi note gestiti.
L'istanza di blocco note gestita apre JupyterLab.
Sfogliare le risorse BigQuery
L'integrazione di BigQuery fornisce un riquadro per sfogliare le risorse BigQuery a cui hai accesso.
Nel menu di navigazione JupyterLab, fai clic su BigQuery nei blocchi note.
Il riquadro BigQuery elenca i progetti e i set di dati disponibili, in cui può eseguire attività come segue:
- Per visualizzare la descrizione di un set di dati, fai doppio clic sul nome del set di dati.
- Per visualizzare le tabelle, le visualizzazioni e i modelli di un set di dati, espandi il set di dati.
- Per aprire una descrizione di riepilogo come scheda in JupyterLab, fai doppio clic su una tabella, una vista o un modello.
Nota: nella descrizione di riepilogo di una tabella, fai clic sulla scheda 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 datigoogle_trends
nel progettobigquery-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 un carattere percentuale singolo o doppio (%
o %%
)
ti consentono di utilizzare una sintassi minima per interagire con BigQuery all'interno del
notebook. La libreria client BigQuery per Python viene automaticamente
in un'istanza di blocchi note gestiti. 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 utilizzata per impostazione predefinita per scaricare i risultati dalle magie %%bigquery
.
Per aprire un file del blocco note, seleziona File > Nuovo > Blocco note.
Nella finestra di dialogo Seleziona kernel, seleziona Python (locale), quindi fai clic su Seleziona.
Si apre il nuovo file IPYNB.
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;
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 ...
Nella cella successiva (sotto l'output della cella precedente), inserisci il comando seguente per eseguire la stessa query, ma questa volta salva i risultati 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 il comando
%%bigquery
, consulta la documentazione sui comandi magici della libreria client.Fai clic su
Esegui cella.Nella cella successiva, inserisci il comando seguente per esaminare le prime righe dei risultati della query che hai appena letto:
regions_by_country.head()
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 di BigQuery per Python direttamente per leggere i dati nel notebook Python.
La libreria client ti offre un maggiore controllo sulle query e ti consente di utilizzare configurazioni più complesse per query e job. Le integrazioni della libreria con pandas ti consentono di combinare la potenza del linguaggio SQL dichiarativo con il codice imperativo (Python) per aiutarti ad analizzare, visualizzare e trasformare i 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.
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 dall'API BigQuery.
Fai clic su
Esegui cella.Nella cella successiva, inserisci il codice seguente per recuperare la percentuale di termini più frequenti del giorno negli Stati Uniti
top_terms
che si sovrappongono nel tempo per numero di giorni di distanza. L'idea è quella di cercare ai termini principali di ogni giorno e scopri in che percentuale si sovrappongono termini principali del giorno prima, 2 giorni prima, 3 giorni prima e così via (ad 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 metodoto_dataframe
attende il completamento della query e scarica i risultati in un pandas tramite l'API BigQuery Storage.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
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 nei notebook gestiti
L'integrazione di BigQuery offre due metodi aggiuntivi
per eseguire query sui dati. Questi metodi sono diversi dall'utilizzo
il comando magico %%bigquery
.
L'Editor di query all'interno delle celle è un tipo di cella che puoi utilizzare all'interno dai file del blocco note.
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:
In JupyterLab, apri un file di blocco note (IPYNB) o crea un uno nuovo.
Per creare un editor di query all'interno delle celle, fai clic sulla cella, e a destra della cella fai clic sull'icona Pulsante Integrazione con BigQuery. Oppure in una cella di markdown, inserisci
#@BigQuery
.L'integrazione di BigQuery converte la cella in un editor di query in-cell.
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.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.
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
Per utilizzare l'editor di query autonomo per eseguire query sui dati in una tabella BigQuery, svolgi i seguenti passaggi:
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.
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 di query. Se la query è valida, viene visualizzato il numero stimato di byte da elaborare.
Fai clic su Invia query. Vengono visualizzati i risultati della query. Per impostazione predefinita, i risultati delle query vengono impaginati a 100 righe per pagina e un massimo di 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.
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.
Visualizzazione della cronologia delle query e riutilizzo delle query
Per visualizzare la cronologia delle query come scheda in JupyterLab:
Nel menu di navigazione JupyterLab, fai clic su BigQuery in Notebooks per aprire lo riquadro BigQuery.
Nel riquadro BigQuery, scorri verso il basso e fai clic su Cronologia delle query.
Un elenco delle query si apre in una nuova scheda in cui puoi eseguire attività come come segue:
- Per visualizzare i dettagli di una query, ad esempio l'ID job, la data di esecuzione e il tempo impiegato, fai clic sulla query.
- Per rivedere la query, eseguirla di nuovo o copiarla nel tuo notebook per usarla in futuro, fai clic su Apri query nell'editor.
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 saperne di più sulla scrittura di query per BigQuery, consulta Esecuzione di job di query interattivi e batch.
Scopri come controllare l'accesso a Set di dati BigQuery.
Scopri come accedere ai bucket e ai file di Cloud Storage da JupyterLab.