Interroger les données dans BigQuery à partir de JupyterLab

Cette page explique comment interroger des données stockées dans BigQuery à partir de l'interface JupyterLab de votre instance de notebooks gérés Vertex AI Workbench.

Méthodes d'interrogation des données BigQuery dans les fichiers de notebook (.ipynb)

Pour interroger des données BigQuery à partir d'un fichier notebook JupyterLab, vous pouvez utiliser la commande magique %%bigquery et la bibliothèque cliente BigQuery pour Python.

Les instances de notebooks gérés incluent également une intégration avec BigQuery, qui vous permet de parcourir et d'interroger des données depuis l'interface JupyterLab.

Cette page explique comment utiliser chacune de ces méthodes.

Avant de commencer

Si vous ne l'avez pas déjà fait, créez une instance de notebooks gérés.

Ouvrir JupyterLab

  1. Dans Google Cloud Console, accédez à la page Notebooks gérés.

    Accéder à la page "Notebooks gérés"

  2. À côté du nom de votre instance de notebooks gérés, cliquez sur Ouvrir JupyterLab.

    Votre instance de notebooks gérés ouvre alors JupyterLab.

Parcourir les ressources BigQuery

L'intégration BigQuery fournit un volet permettant de parcourir les ressources BigQuery auxquelles vous avez accès.

  1. Dans le menu de navigation de JupyterLab, cliquez sur BigQuery BigQuery dans Notebooks.

    Le volet BigQuery répertorie les projets et les ensembles de données disponibles, dans lesquels vous pouvez effectuer des tâches comme suit :

    • Pour afficher la description d'un ensemble de données, double-cliquez sur son nom.
    • Pour afficher les tables, les vues et les modèles d'un ensemble de données, développez celui-ci.
    • Pour ouvrir une description récapitulative en tant qu'onglet dans JupyterLab, double-cliquez sur une table, une vue ou un modèle.

    Remarque : Dans la description récapitulative d'une table, cliquez sur l'onglet Aperçu pour prévisualiser les données d'une table. L'image suivante montre un aperçu de la table international_top_terms disponible dans l'ensemble de données google_trends du projet bigquery-public-data :

    Liste des principaux termes internationaux

Interroger des données à l'aide de la commande magique %%bigquery

Dans cette section, vous allez écrire SQL directement dans les cellules de notebook et lire des données de BigQuery dans le notebook Python.

Les commandes magiques basées sur un caractère unique ou double (% ou %%) vous permettent d'utiliser une syntaxe minimale pour interagir avec BigQuery dans le notebook. La bibliothèque cliente BigQuery pour Python est automatiquement installée dans une instance de notebooks gérés. En arrière-plan, la commande magique %%bigquery utilise la bibliothèque cliente BigQuery pour Python pour exécuter la requête concernée, convertir les résultats en objet DataFrame pandas, les enregistrer éventuellement dans une variable, puis afficher les résultats.

Remarque : Depuis la version 1.26.0 du package Python google-cloud-bigquery, l'API BigQuery Storage est utilisée par défaut pour télécharger les résultats des commandes magiques %%bigquery.

  1. Pour ouvrir un fichier notebook, sélectionnez Fichier > Nouveau > Notebook.

  2. Dans la boîte de dialogue Sélectionner le noyau, sélectionnez Python (Local), puis cliquez sur Sélectionner.

    Votre nouveau fichier IPYNB s'ouvre.

  3. Pour obtenir le nombre de régions par pays dans l'ensemble de données international_top_terms, saisissez l'instruction suivante :

    %%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. Cliquez sur  Exécuter la cellule.

    Le résultat ressemble à ce qui suit :

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

  5. Dans la cellule suivante (sous la sortie de la cellule précédente), saisissez la commande suivante pour exécuter la même requête en enregistrant cette fois les résultats dans un nouveau DataFrame pandas nommé regions_by_country. Vous devez spécifier ce nom à l'aide d'un argument avec la commande magique %%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;

    Remarque : Pour en savoir plus sur les arguments disponibles pour la commande %%bigquery, consultez la documentation sur les commandes magiques de la bibliothèque cliente.

  6. Cliquez sur  Exécuter la cellule.

  7. Dans la cellule suivante, saisissez la commande ci-dessous pour examiner les premières lignes des résultats de la requête que vous venez de lire :

    regions_by_country.head()

  8. Cliquez sur  Exécuter la cellule.

    Le DataFrame pandas regions_by_country est prêt à être représenté.

Interroger les données à l'aide de la bibliothèque cliente BigQuery directement

Dans cette section, vous allez utiliser la bibliothèque cliente BigQuery pour Python directement afin de lire des données dans le notebook Python.

La bibliothèque cliente vous permet de mieux contrôler vos requêtes et d'utiliser des configurations plus complexes pour les requêtes et les tâches. Les intégrations de la bibliothèque avec Pandas vous permettent de combiner la puissance du SQL déclaratif au code impératif (Python) pour vous aider à analyser, visualiser et transformer vos données.

Remarque : Vous pouvez utiliser de nombreuses bibliothèques de wrangling, de visualisation et d'analyse des données Python, telles que numpy, pandas, matplotlib, etc. Plusieurs de ces bibliothèques sont basées sur un objet DataFrame.

  1. Dans la cellule suivante, saisissez le code Python ci-dessous pour importer la bibliothèque cliente BigQuery pour Python et initialiser un client :

    from google.cloud import bigquery

client = bigquery.Client()

    Celui-ci permet d'envoyer des messages à l'API BigQuery et d'en recevoir.

  2. Cliquez sur  Exécuter la cellule.

  3. Dans la cellule suivante, saisissez le code ci-dessous pour récupérer le pourcentage des principaux termes quotidiens parmi les top_terms aux États-Unis qui se chevauchent dans le temps avec le nombre de jours d'intervalle. L'idée est ici d'examiner les principaux termes de chaque jour et d'en déterminer le pourcentage qui se chevauchent avec les principaux termes de la veille, deux jours avant, trois jours avant, etc. (pour toutes les paires de dates sur une période d'environ un mois).

    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()

    Le code SQL utilisé est encapsulé dans une chaîne Python, puis transmis à la méthode query() pour exécuter une requête. La méthode to_dataframe attend la fin de la requête et télécharge les résultats sur un objet DataFrame pandas à l'aide de l'API BigQuery Storage.

  4. Cliquez sur  Exécuter la cellule.

    Les premières lignes de résultats de la requête s'affichent sous la cellule de code.

       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

Pour en savoir plus sur l'utilisation des bibliothèques clientes BigQuery, consultez le guide de démarrage rapide Utiliser des bibliothèques clientes.

Interroger des données à l'aide de l'intégration BigQuery dans des notebooks gérés

L'intégration BigQuery fournit deux méthodes supplémentaires pour interroger des données. Ces méthodes sont différentes de l'utilisation de la commande magique %%bigquery.

  • L'éditeur de requêtes à l'intérieur d'une cellule est un type de cellule que vous pouvez utiliser dans vos fichiers de notebooks.

  • L'éditeur de requête autonome s'ouvre dans un onglet distinct de JupyterLab.

À l'intérieur d'une cellule

Pour interroger les données d'une table BigQuery à l'aide de l'éditeur de requêtes à l'intérieur d'une cellule, procédez comme suit :

  1. Dans JupyterLab, ouvrez un fichier notebook (.ipynb) ou créez-en un.

  2. Pour créer un éditeur de requête à l'intérieur d'une cellule, cliquez sur la cellule, puis à droite, cliquez sur Intégration de BigQuery. Vous pouvez également saisir #@BigQuery dans une cellule Markdown.

    L'intégration BigQuery convertit la cellule dans un éditeur de requêtes à l'intérieur d'une cellule.

  3. Sur une nouvelle ligne sous #@BigQuery, écrivez votre requête à l'aide des instructions compatibles et des dialectes SQL de BigQuery. Si des erreurs sont détectées dans votre requête, un message d'erreur s'affiche en haut à droite de l'éditeur de requête. Si la requête est valide, le nombre estimé d'octets à traiter apparaît.

  4. Cliquez sur Envoyer la requête. Les résultats de votre requête s'affichent. Par défaut, les résultats de la requête sont paginés à 100 lignes par page et sont limités à 1 000 lignes au total, mais vous pouvez modifier ces paramètres en bas du tableau des résultats. Dans l'éditeur de requêtes, limitez la requête aux données dont vous avez besoin pour la valider. Vous exécutez à nouveau cette requête dans une cellule de notebook, où vous pouvez ajuster la limite pour récupérer l'ensemble de résultats complet si vous le souhaitez.

  5. Vous pouvez cliquer sur Interroger et charger en tant que DataFrame pour ajouter automatiquement une nouvelle cellule contenant un segment de code qui importe la bibliothèque cliente BigQuery pour Python, puis exécute votre requête dans une cellule de notebook, et stocke les résultats dans un DataFrame Pandas nommé df.

Autonome

Pour interroger les données d'une table BigQuery à l'aide de l'éditeur de requêtes autonome, procédez comme suit :

  1. Dans JupyterLab, dans le volet BigQuery dans Notebooks, effectuez un clic droit sur une table, puis sélectionnez Interroger la table, ou double-cliquez sur une table pour ouvrir une description dans un onglet distinct, puis cliquez sur le lien Interroger la table.

  2. Écrivez votre requête à l'aide des instructions compatibles et des dialectes SQL de BigQuery. Si des erreurs sont détectées dans votre requête, un message d'erreur s'affiche en haut à droite de l'éditeur de requête. Si la requête est valide, le nombre estimé d'octets à traiter apparaît.

  3. Cliquez sur Envoyer la requête. Les résultats de votre requête s'affichent. Par défaut, les résultats de la requête sont paginés à 100 lignes par page et sont limités à 1 000 lignes au total, mais vous pouvez modifier ces paramètres en bas du tableau des résultats. Dans l'éditeur de requêtes, limitez la requête aux données dont vous avez besoin pour la valider. Vous exécutez à nouveau cette requête dans une cellule de notebook, où vous pouvez ajuster la limite pour récupérer l'ensemble de résultats complet si vous le souhaitez.

  4. Vous pouvez cliquer sur Copier le code pour le DataFrame pour copier un segment de code qui importe la bibliothèque cliente BigQuery pour Python, exécute votre requête dans une cellule de notebook et stocke les résultats dans un DataFrame Pandas nommé df. Collez ce code dans une cellule de notebook dans laquelle vous souhaitez l'exécuter.

Afficher l'historique des requêtes et réutiliser les requêtes

Pour afficher l'historique de vos requêtes sous forme d'onglet dans JupyterLab, procédez comme suit :

  1. Dans le menu de navigation de JupyterLab, cliquez sur BigQuery BigQuery dans Notebooks pour ouvrir le volet BigQuery.

  2. Dans le volet BigQuery, faites défiler la page vers le bas et cliquez sur Historique des requêtes.

    Historique des requêtes mis en surbrillance en bas du menu de navigation de gauche

    Une liste de vos requêtes s'ouvre dans un nouvel onglet, dans lequel vous pouvez effectuer les tâches suivantes :

    • Pour afficher les détails d'une requête, tels que son ID de tâche, sa date d'exécution et sa durée, cliquez sur la requête.
    • Pour modifier la requête, exécutez-la à nouveau ou copiez-la dans votre notebook pour une utilisation ultérieure, puis cliquez sur Ouvrir la requête dans l'éditeur.

Étapes suivantes