GSP414

Présentation

BigQuery est la base de données d'analyse NoOps, économique et entièrement gérée de Google. Avec BigQuery, vous pouvez interroger plusieurs téraoctets de données sans avoir à gérer d'infrastructure ni faire appel à un administrateur de base de données. Basé sur le langage SQL et le modèle de paiement à l'usage, BigQuery vous permet de vous concentrer sur l'analyse des données pour en dégager des informations pertinentes.

Dans cet atelier, vous allez apprendre à interroger et créer des tables partitionnées dans BigQuery pour améliorer les performances des requêtes et réduire l'utilisation des ressources. Les données utilisées pour cet atelier font partie d'un ensemble de données d'e-commerce comprenant des millions d'enregistrements Google Analytics pour le Google Merchandise Store chargé dans BigQuery.

Objectifs de l'atelier

Dans cet atelier, vous allez apprendre à effectuer les tâches suivantes :

• Interroger des tables partitionnées
• Créer vos propres tables partitionnées

Préparation

Avant de cliquer sur le bouton "Démarrer l'atelier"

Lisez ces instructions. Les ateliers sont minutés, et vous ne pouvez pas les mettre en pause. Le minuteur, qui démarre lorsque vous cliquez sur Démarrer l'atelier, indique combien de temps les ressources Google Cloud resteront accessibles.

Cet atelier pratique vous permet de suivre vous-même les activités dans un véritable environnement cloud, et non dans un environnement de simulation ou de démonstration. Nous vous fournissons des identifiants temporaires pour vous connecter à Google Cloud le temps de l'atelier.

Pour réaliser cet atelier :

• vous devez avoir accès à un navigateur Internet standard (nous vous recommandons d'utiliser Chrome) ;

Remarque : Ouvrez une fenêtre de navigateur en mode incognito/navigation privée pour effectuer cet atelier. Vous éviterez ainsi les conflits entre votre compte personnel et le temporaire étudiant, qui pourraient entraîner des frais supplémentaires facturés sur votre compte personnel.

• vous disposez d'un temps limité ; une fois l'atelier commencé, vous ne pouvez pas le mettre en pause.

Remarque : Si vous possédez déjà votre propre compte ou projet Google Cloud, veillez à ne pas l'utiliser pour réaliser cet atelier afin d'éviter que des frais supplémentaires ne vous soient facturés.

Démarrer l'atelier et se connecter à la console Google Cloud

1. Cliquez sur le bouton Démarrer l'atelier. Si l'atelier est payant, un pop-up s'affiche pour vous permettre de sélectionner un mode de paiement. Sur la gauche, vous trouverez le panneau Détails concernant l'atelier, qui contient les éléments suivants :

   • Le bouton Ouvrir la console Google Cloud
   • Le temps restant
   • Les identifiants temporaires que vous devez utiliser pour cet atelier
   • Des informations complémentaires vous permettant d'effectuer l'atelier

2. Cliquez sur Ouvrir la console Google Cloud (ou effectuez un clic droit et sélectionnez Ouvrir le lien dans la fenêtre de navigation privée si vous utilisez le navigateur Chrome).

   L'atelier lance les ressources, puis ouvre la page Se connecter dans un nouvel onglet.

   Conseil : Réorganisez les onglets dans des fenêtres distinctes, placées côte à côte.

   Remarque : Si la boîte de dialogue Sélectionner un compte s'affiche, cliquez sur Utiliser un autre compte.

3. Si nécessaire, copiez le nom d'utilisateur ci-dessous et collez-le dans la boîte de dialogue Se connecter.

   {{{user_0.username | "Username"}}}

   Vous trouverez également le nom d'utilisateur dans le panneau Détails concernant l'atelier.

4. Cliquez sur Suivant.

5. Copiez le mot de passe ci-dessous et collez-le dans la boîte de dialogue Bienvenue.

   {{{user_0.password | "Password"}}}

   Vous trouverez également le mot de passe dans le panneau Détails concernant l'atelier.

6. Cliquez sur Suivant.

   Important : Vous devez utiliser les identifiants fournis pour l'atelier. Ne saisissez pas ceux de votre compte Google Cloud. Remarque : Si vous utilisez votre propre compte Google Cloud pour cet atelier, des frais supplémentaires peuvent vous être facturés.

7. Accédez aux pages suivantes :

   • Acceptez les conditions d'utilisation.
   • N'ajoutez pas d'options de récupération ni d'authentification à deux facteurs (ce compte est temporaire).
   • Ne vous inscrivez pas à des essais gratuits.

Après quelques instants, la console Cloud s'ouvre dans cet onglet.

Remarque : Pour afficher un menu contenant la liste des produits et services Google Cloud, cliquez sur le menu de navigation en haut à gauche.

Ouvrir la console BigQuery

1. Dans la console Google Cloud, sélectionnez le menu de navigation > BigQuery.

Le message Bienvenue sur BigQuery dans Cloud Console s'affiche. Il contient un lien vers le guide de démarrage rapide et les notes de version.

2. Cliquez sur OK.

La console BigQuery s'ouvre.

Tâche 1 : Créer un ensemble de données

1. Commencez par créer un ensemble de données dans lequel stocker vos tables.

2. Dans le volet Explorateur, à côté de l'ID de votre projet, cliquez sur Afficher les actions, puis sur Créer un ensemble de données.

3. Définissez le champ ID de l'ensemble de données sur e-commerce.

Laissez les valeurs par défaut des autres options, "Emplacement des données" et "Expiration de la table par défaut".

4. Cliquez sur Créer un ensemble de données.

Cliquez sur Vérifier ma progression pour valider l'objectif.

Créer un ensemble de données nommé "e-commerce"

Tâche 2 : Créer des tables partitionnées par date

Une table partitionnée est une table divisée en segments, appelés partitions, qui facilitent la gestion et l'interrogation des données. Diviser une grande table en partitions plus petites permet d'améliorer les performances des requêtes et de maîtriser les coûts en réduisant le nombre d'octets traités par une requête.

Vous allez maintenant créer une table et lui associer une colonne de date ou de code temporel en tant que partition. Auparavant, examinons les données de la table non partitionnée.

Interroger des données analytiques de page Web pour un échantillon de visiteurs en 2017

1. Cliquez sur + Saisir une nouvelle requête et ajoutez la requête suivante :

#standardSQL SELECT DISTINCT fullVisitorId, date, city, pageTitle FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE date = '20170708' LIMIT 5

Avant de l'exécuter, notez la quantité totale de données à traiter, comme indiqué à côté de l'icône de l'outil de validation des requêtes : "Cette requête traitera 1,74 Go lors de son exécution".

2. Cliquez sur Exécuter.

La requête renvoie cinq résultats.

Interroger des données analytiques de page Web pour un échantillon de visiteurs en 2018

Modifions à présent la requête pour qu'elle s'intéresse aux visiteurs en 2018.

1. Cliquez sur SAISIR UNE NOUVELLE REQUÊTE pour effacer l'éditeur de requête, puis ajoutez cette nouvelle requête. Notez que le paramètre WHERE date (Où la date) est remplacé par 20180708 :

#standardSQL SELECT DISTINCT fullVisitorId, date, city, pageTitle FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE date = '20180708' LIMIT 5

L'outil de validation des requêtes vous indique la quantité de données qui sera traitée par cette requête.

2. Cliquez sur Exécuter.

Notez que la requête traite toujours 1,74 Go de données, même si elle ne renvoie aucun résultat. Pourquoi ? Le moteur de requêtes doit analyser tous les enregistrements de l'ensemble de données pour vérifier s'ils respectent la condition de correspondance de date dans la clause WHERE. Il doit examiner chaque enregistrement afin de comparer sa date à la condition "20180708".

De plus, contrairement à l'idée reçue, la clause LIMIT 5 (Limite 5) ne réduit pas la quantité totale de données traitée.

Cas d'utilisation courants des tables partitionnées par date

L'analyse systématique de l'ensemble de données complet afin de comparer les lignes à une condition WHERE est inefficace, surtout si seuls les enregistrements concernant une période spécifique vous intéressent, par exemple :

• toutes les transactions de l'année passée ;
• toutes les interactions des visiteurs au cours des sept derniers jours ;
• tous les produits vendus au cours du mois écoulé.

Au lieu d'analyser l'ensemble de données complet et de le filtrer selon un champ de date comme pour les requêtes précédentes, configurez à présent une table partitionnée par date. Cela vous permet d'ignorer complètement les enregistrements de certaines partitions s'ils sont sans rapport avec la requête.

Créer une table partitionnée en fonction de la date

1. Cliquez sur SAISIR UNE NOUVELLE REQUÊTE, ajoutez la requête ci-dessous, puis cliquez sur Exécuter :

#standardSQL CREATE OR REPLACE TABLE ecommerce.partition_by_day PARTITION BY date_formatted OPTIONS( description="a table partitioned by date" ) AS SELECT DISTINCT PARSE_DATE("%Y%m%d", date) AS date_formatted, fullvisitorId FROM `data-to-insights.ecommerce.all_sessions_raw`

Dans cette requête, notez la nouvelle option PARTITION BY (Partitionner par) suivie d'un champ. Les deux options de partition disponibles sont DATE et TIMESTAMP (Code temporel). La fonction PARSE_DATE (Analyser la date) est utilisée pour le champ de date (stocké en tant que chaîne) afin de l'associer au type de DATE correct en vue du partitionnement.

2. Cliquez sur l'ensemble de données e-commerce, puis sélectionnez la nouvelle table partition_by_day (partition par jour) :

3. Cliquez sur l'onglet Détails.

Vérifiez que les données suivantes apparaissent :

• Partitionnée par : jour
• Partitionnement le : date_formatted

Remarque : Les partitions au sein des tables partitionnées liées à votre compte d'atelier expireront automatiquement au bout de 60 jours à compter de la valeur indiquée dans votre colonne de date. Les tables partitionnées que vous créerez dans votre compte Google Cloud personnel avec facturation activée n'expireront pas. Dans le cadre de cet atelier, les requêtes restantes seront exécutées sur des tables partitionnées qui ont déjà été créées.

Cliquez sur Vérifier ma progression pour valider l'objectif.

Créer une table partitionnée en fonction de la date

Tâche 3 : Examiner les résultats de requêtes sur une table partitionnée

1. Exécutez la requête suivante, et notez la quantité totale d'octets à traiter :

#standardSQL SELECT * FROM `data-to-insights.ecommerce.partition_by_day` WHERE date_formatted = '2016-08-01'

Cette fois, 25 ko ou 0,025 Mo sont traités, ce qui représente une petite fraction de la quantité de données analysée par la requête précédente.

2. À présent, exécutez la requête suivante, et notez la quantité totale d'octets à traiter :

#standardSQL SELECT * FROM `data-to-insights.ecommerce.partition_by_day` WHERE date_formatted = '2018-07-08'

Le message suivant doit s'afficher : Cette requête traitera 0 octet lors de son exécution.

Tâche 4 : Créer une table partitionnée à expiration automatique

Les tables partitionnées à expiration automatique sont utilisées pour assurer la conformité avec les règles de confidentialité des données. Elles permettent aussi d'éviter tout stockage superflu (facturé dans un environnement de production). Si vous souhaitez créer une période glissante de données, ajoutez une date d'expiration pour que la partition disparaisse une fois que vous avez fini de l'utiliser.

Explorer les tables de données météorologiques mises à disposition par l'Agence américaine d'observation océanique et atmosphérique (NOAA)

1. Dans le menu de gauche, sous "Explorateur", cliquez sur + Ajouter et sélectionnez Ensembles de données publics.

2. Recherchez GSOD NOAA, puis sélectionnez l'ensemble de données.

3. Cliquez sur Afficher l'ensemble de données.

4. Faites défiler les tables de l'ensemble de données noaa_gsod (elles sont segmentées manuellement et non partitionnées) :

L'objectif est de créer une table qui :

• interroge les données météorologiques à partir de 2018 ;
• filtre les données pour n'inclure que les jours avec des précipitations (pluie, neige, etc.) ;
• stocke chaque partition de données pendant 90 jours uniquement, à compter de sa date de création (période glissante).

1. Tout d'abord, copiez et collez la requête ci-dessous :

#standardSQL SELECT DATE(CAST(year AS INT64), CAST(mo AS INT64), CAST(da AS INT64)) AS date, (SELECT ANY_VALUE(name) FROM `bigquery-public-data.noaa_gsod.stations` AS stations WHERE stations.usaf = stn) AS station_name, -- Stations may have multiple names prcp FROM `bigquery-public-data.noaa_gsod.gsod*` AS weather WHERE prcp < 99.9 -- Filter unknown values AND prcp > 0 -- Filter stations/days with no precipitation AND _TABLE_SUFFIX >= '2018' ORDER BY date DESC -- Where has it rained/snowed recently LIMIT 10 Remarque : Le caractère générique de table "*" est utilisé dans la clause FROM (À partir de) pour limiter la quantité de tables traitées par le filtre TABLE_SUFFIX (Suffixe de table). Remarque : Bien qu'une clause LIMIT 10 ait été ajoutée, la quantité totale de données analysées reste la même (environ 1,83 Go) étant donné qu'aucune partition n'a encore été créée.

2. Cliquez sur Exécuter.

3. Vérifiez que le format de date est correct et que le champ des précipitations indique des valeurs non nulles.

Tâche 5 : À vous de créer une table partitionnée

• Modifiez la requête précédente afin de créer une table correspondant aux spécifications ci-dessous :

  • Nom de la table : ecommerce.days_with_rain
  • Utilisez le champ de date comme clause PARTITION BY
  • Pour OPTIONS, spécifiez partition_expiration_days = 60
  • Ajoutez la description de la table = "weather stations with precipitation, partitioned by day"

Votre requête doit se présenter comme suit :

#standardSQL CREATE OR REPLACE TABLE ecommerce.days_with_rain PARTITION BY date OPTIONS ( partition_expiration_days=60, description="weather stations with precipitation, partitioned by day" ) AS SELECT DATE(CAST(year AS INT64), CAST(mo AS INT64), CAST(da AS INT64)) AS date, (SELECT ANY_VALUE(name) FROM `bigquery-public-data.noaa_gsod.stations` AS stations WHERE stations.usaf = stn) AS station_name, -- Stations may have multiple names prcp FROM `bigquery-public-data.noaa_gsod.gsod*` AS weather WHERE prcp < 99.9 -- Filter unknown values AND prcp > 0 -- Filter AND _TABLE_SUFFIX >= '2018'

Cliquez sur Vérifier ma progression pour valider l'objectif.

À vous de créer une table partitionnée

Vérifier que l'expiration de la partition de données fonctionne

Pour vérifier que vous ne stockez que des données datant de 60 jours ou moins, exécutez la requête DATE_DIFF pour obtenir l'âge de vos partitions, qui sont configurées pour expirer au bout de 60 jours.

Voici une requête qui enregistre les précipitations moyennes pour la station météorologique de la NOAA à Wakayama, au Japon, connue pour ses précipitations abondantes.

• Ajoutez cette requête, puis exécutez-la :

#standardSQL # avg monthly precipitation SELECT AVG(prcp) AS average, station_name, date, CURRENT_DATE() AS today, DATE_DIFF(CURRENT_DATE(), date, DAY) AS partition_age, EXTRACT(MONTH FROM date) AS month FROM ecommerce.days_with_rain WHERE station_name = 'WAKAYAMA' #Japan GROUP BY station_name, date, today, month, partition_age ORDER BY date DESC; # most recent days first

Tâche 6 : Vérifier que la partition la plus ancienne (partition_age) date de 60 jours ou moins

Mettez à jour la clause ORDER BY (Classer par) pour afficher les partitions les plus anciennes en premier.

• Ajoutez cette requête, puis exécutez-la :

#standardSQL # avg monthly precipitation SELECT AVG(prcp) AS average, station_name, date, CURRENT_DATE() AS today, DATE_DIFF(CURRENT_DATE(), date, DAY) AS partition_age, EXTRACT(MONTH FROM date) AS month FROM ecommerce.days_with_rain WHERE station_name = 'WAKAYAMA' #Japan GROUP BY station_name, date, today, month, partition_age ORDER BY partition_age DESC Remarque : Vos résultats varieront si vous réexécutez la requête ultérieurement, puisque les données météorologiques, ainsi que vos partitions, sont mises à jour en permanence.

Félicitations !

Vous avez créé et interrogé des tables partitionnées dans BigQuery.

Étapes suivantes et informations supplémentaires

• Si vous souhaitez savoir comment créer des tables partitionnées par date d'ingestion qui ne sont pas liées à une colonne de date ou de code temporel spécifique, consultez la documentation sur le partitionnement dans BigQuery (qui fournit également des exemples).
• Vous possédez un compte Google Analytics et souhaitez interroger vos propres ensembles de données BigQuery ? Suivez ce guide d'exportation.
• Consultez ces autres ateliers BigQuery :
  • Résoudre les problèmes et éviter les pièges liés à la jointure des données
  • Utiliser des objets JSON, ARRAY et STRUCT dans BigQuery
  • Prédire le prix d'une course en taxi à l'aide d'un modèle de prévision BigQuery ML

Formations et certifications Google Cloud

Les formations et certifications Google Cloud vous aident à tirer pleinement parti des technologies Google Cloud. Nos cours portent sur les compétences techniques et les bonnes pratiques à suivre pour être rapidement opérationnel et poursuivre votre apprentissage. Nous proposons des formations pour tous les niveaux, à la demande, en salle et à distance, pour nous adapter aux emplois du temps de chacun. Les certifications vous permettent de valider et de démontrer vos compétences et votre expérience en matière de technologies Google Cloud.

Dernière mise à jour du manuel : 3 février 2024

Dernier test de l'atelier : 1er janvier 2024

Copyright 2024 Google LLC Tous droits réservés. Google et le logo Google sont des marques de Google LLC. Tous les autres noms d'entreprises et de produits peuvent être des marques des entreprises auxquelles ils sont associés.