Before you begin
- Labs create a Google Cloud project and resources for a fixed time
- Labs have a time limit and no pause feature. If you restart it, you'll have to start from the beginning.
- Click Start lab to begin
Create a new dataset
/ 25
Identify a key field in your ecommerce dataset
/ 25
Pitfall: non-unique key
/ 25
Join pitfall solution
/ 25
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.
La jointure des tables de données peut vous fournir des renseignements très utiles sur votre ensemble de données. Toutefois, lorsque vous joignez des données, il se peut que vous vous heurtiez à des pièges courants qui peuvent altérer vos résultats. Cet atelier a pour objectif de vous apprendre à éviter ces pièges. Voici tout d'abord les types de jointures :
Pour en savoir plus, reportez-vous à la page sur les jointures.
Vous allez utiliser un ensemble de données d'e-commerce comprenant des millions d'enregistrements Google Analytics pour le Google Merchandise Store, chargé dans BigQuery. Vous disposez d'une copie de cet ensemble de données pour cet atelier, et vous allez explorer les champs et lignes qu'il contient afin d'obtenir des informations.
Pour en savoir plus sur la syntaxe permettant de suivre et de mettre à jour vos requêtes, consultez la section Syntaxe des requêtes en SQL standard.
Dans cet atelier, vous allez apprendre à effectuer les tâches suivantes :
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 :
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 :
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.
Si nécessaire, copiez le nom d'utilisateur ci-dessous et collez-le dans la boîte de dialogue Se connecter.
Vous trouverez également le nom d'utilisateur dans le panneau Détails concernant l'atelier.
Cliquez sur Suivant.
Copiez le mot de passe ci-dessous et collez-le dans la boîte de dialogue Bienvenue.
Vous trouverez également le mot de passe dans le panneau Détails concernant l'atelier.
Cliquez sur Suivant.
Accédez aux pages suivantes :
Après quelques instants, la console Cloud s'ouvre dans cet onglet.
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.
La console BigQuery s'ouvre.
Dans votre projet BigQuery, créez un ensemble de données intitulé ecommerce
.
La boîte de dialogue Créer un ensemble de données s'ouvre.
Définissez l'ID de l'ensemble de données sur ecommerce
.
Laissez la valeur par défaut des autres options et cliquez sur Créer un ensemble de données.
Dans le volet de gauche, vous voyez une table ecommerce
sous votre projet.
Cliquez sur Vérifier ma progression pour valider l'objectif.
Scénario : votre équipe vous fournit un nouvel ensemble de données recensant le stock de chacun de vos produits en vente sur votre site d'e-commerce. Vous souhaitez vous familiariser avec les produits du site Web et avec les champs que vous pouvez utiliser pour effectuer une jointure avec d'autres ensembles de données.
Le projet avec le nouvel ensemble de données s'intitule data-to-insights.
Le message "Bienvenue sur BigQuery dans la console Cloud" s'affiche.
Cliquez sur OK.
Les ensembles de données publics BigQuery ne sont pas affichés par défaut. Pour ouvrir le projet d'ensembles de données publics, copiez data-to-insights.
Cliquez sur Ajouter > Ajouter un projet aux favoris en saisissant son nom, puis collez le nom "data-to-insights".
Cliquez sur Ajouter aux favoris.
Le projet data-to-insights
apparaît désormais dans la section "Explorateur".
Ensuite, familiarisez-vous avec les produits et les champs du site Web que vous pouvez utiliser pour créer des requêtes d'analyse de l'ensemble de données.
Dans le volet de gauche de la section "Ressources", accédez à data-to-insights
> ecommerce
> all_sessions_raw
.
À droite, sous l'éditeur de requête, cliquez sur l'onglet Schéma pour afficher les champs et des informations sur chacun d'eux.
Examinez de plus près les produits et les champs. Vous pourrez ainsi vous familiariser avec les produits du site Web et avec les champs que vous pouvez utiliser pour effectuer une jointure avec d'autres ensembles de données.
Dans cette section, vous verrez combien de noms de produits et de SKU figurent sur votre site Web, et si certains de ces champs sont uniques.
Consultez les résultats de la pagination dans la console pour connaître le nombre total d'enregistrements renvoyés.
Les résultats signifient-ils pour autant qu'il existe un aussi grand nombre de SKU uniques ? L'une des premières requêtes que vous effectuerez en tant qu'analyste de données consistera à vérifier le caractère unique des valeurs de vos données.
DISTINCT
:À présent, déterminez quels produits sont associés à plusieurs SKU et quels SKU s'appliquent à plus d'un nom de produit.
Voici ce que vous obtiendrez :
Le catalogue du site Web d'e-commerce indique que chaque nom de produit peut avoir plusieurs options (taille, couleur, etc.), qui sont vendues avec un SKU distinct.
Vous avez constaté qu'un produit était associé à 12 SKU. Qu'en est-il d'un seul SKU ? Peut-il être associé à plus d'un produit ?
Dans la section suivante, vous comprendrez pourquoi cette relation de données plusieurs à plusieurs est problématique.
Cliquez sur Vérifier ma progression pour valider l'objectif.
Dans le suivi de l'inventaire, un SKU est conçu pour identifier un produit de façon unique. Ici, il constitue la base de votre condition de jointure lorsque vous joignez d'autres tables. Comme nous allons le voir, une clé non unique peut provoquer de graves problèmes pour les données.
'GGOEGPJC019099'
.Solution possible :
v2ProductName |
productSKU |
7" Dog Frisbee |
GGOEGPJC019099 |
7" Dog Frisbee |
GGOEGPJC019099 |
Google 7-inch Dog Flying Disc Blue |
GGOEGPJC019099 |
D'après les résultats de la requête, il existe apparemment trois noms différents pour le même produit. Dans l'exemple, nous notons un caractère spécial dans l'un des noms et une formulation légèrement différente pour un autre :
Découvrez maintenant l'effet d'une jointure sur un ensemble de données contenant plusieurs produits associés à un même SKU. Explorez d’abord l'ensemble de données de l’inventaire de produits (la table products
) pour voir si ce SKU est unique.
Vous disposez maintenant de deux ensembles de données : un pour le niveau d'inventaire et un autre pour les analyses de site Web. Joignez l’ensemble de données de l’inventaire aux noms de produits et SKU de votre site Web pour associer le niveau d'inventaire à chaque produit en vente sur le site Web.
Ensuite, développez la requête précédente pour calculer le stock disponible pour chaque produit à l'aide de la fonction SUM.
Malheureusement, le résultat correspond à trois fois 154, c'est-à-dire 462 au total. Il s'agit d'un triple comptage. C'est ce qu'on appelle une jointure croisée involontaire (nous reviendrons plus tard sur ce sujet).
Cliquez sur Vérifier ma progression pour valider l'objectif.
Quelles sont les options qui permettraient de résoudre votre problème de triple comptage ? Vous devez tout d'abord sélectionner uniquement des SKU distincts sur le site Web avant de joindre d'autres ensembles de données.
Vous savez que plusieurs noms de produit (ex. : 7" Dog Frisbee) peuvent partager un seul SKU.
Maintenant, au lieu d'avoir une ligne pour chaque nom de produit, vous avez uniquement une ligne pour chaque SKU unique.
Vous êtes maintenant prêt à refaire la jointure de l'ensemble de données de votre inventaire.
Il semblerait que 819 SKU aient été perdus après la jointure des ensembles de données. Enquêtez sur cette disparition en affinant vos champs (une colonne SKU de chaque ensemble de données) :
Il semblerait que les SKU apparaissent dans les deux ensembles de données après la jointure pour ces 1 090 enregistrements. Comment trouver les enregistrements manquants ?
Le type de jointure par défaut est INNER JOIN (JOINTURE INTERNE), qui renvoie des enregistrements uniquement s'il existe une correspondance de SKU dans les tables de gauche et de droite qui ont été jointes.
Solution possible :
À l'aide du type LEFT JOIN (JOINTURE GAUCHE), vous obtenez l'intégralité des 1 909 SKU d'origine du site Web dans vos résultats.
Combien de SKU manque-t-il dans l'inventaire de vos produits ?
Solution possible :
Question : combien de produits manque-t-il ?
Réponse : il manque 819 produits (SKU IS NULL [SKU est NULL]) dans l'ensemble de données de l'inventaire.
Maintenant, que se passe-t-il si l'on inverse la situation ? Y a-t-il des produits qui se trouvent dans l'ensemble de données de l'inventaire de produits, mais qui sont absents du site Web ?
Solution possible :
Réponse : Oui. Il manque deux SKU dans l'ensemble de données du site Web.
Ensuite, ajoutez d'autres champs de l'ensemble de données de l'inventaire de produits pour afficher plus de détails.
Pourquoi les produits ci-dessous seraient-ils absents de l'ensemble de données du site Web d'e-commerce ?
website_SKU |
SKU |
name |
orderedQuantity |
stockLevel |
restockingLeadTime |
sentimentScore |
sentimentMagnitude |
null |
GGOBJGOWUSG69402 |
USB wired soundbar - in store only |
10 |
15 |
2 |
1 |
1 |
null |
GGADFBSBKS42347 |
PC gaming speakers |
0 |
100 |
1 |
null |
null |
Réponses possibles :
Pourquoi le nouveau produit n'apparaîtrait-il pas dans l'ensemble de données de votre site Web ?
Comment obtenir une requête répertoriant tous les produits manquants sur le site Web ou dans l'inventaire ?
Solution possible :
Vous obtenez 819 + 2 = 821 SKU.
LEFT JOIN + RIGHT JOIN = FULL JOIN. Cette opération renvoie tous les enregistrements des deux tables, qu'il y ait ou non correspondance entre les clés de jointure. Ensuite, vous filtrez les résultats pour identifier les enregistrements pour lesquels il n'y a pas de correspondance d'un côté ou de l'autre.
Ne pas savoir quelle est la relation entre les clés de table de données (1:1, 1:N, N:N) peut générer des résultats inattendus et réduire considérablement les performances des requêtes.
Le dernier type de jointure est CROSS JOIN (JOINTURE CROISÉE).
Créez une table avec un pourcentage de remise valable sur l'ensemble du site que vous souhaitez appliquer à tous les produits de la catégorie "Clearance" (Liquidation).
Dans le volet de gauche, site_wide_promotion
est désormais répertorié dans la section "Ressources" sous votre projet et ensemble de données.
Voyons quelles sont les conséquences de l'ajout involontaire de plusieurs enregistrements dans la table de la remise.
Examinons ensuite les valeurs de données de la table de la promotion.
Combien d'enregistrements ont été renvoyés ?
Réponse : 3
Que se passe-t-il lorsque vous appliquez à nouveau la remise sur les 82 produits en liquidation ?
Combien de produits sont renvoyés ?
Réponse : au lieu de 82, vous en obtenez maintenant 246, ce qui représente plus d'enregistrements que n'en contient votre table d'origine.
Penchons-nous sur la cause sous-jacente en examinant un SKU.
Quel a été l'effet de la jointure croisée ?
Réponse : Dans la mesure où il existe trois codes de réduction à associer, vous multipliez l'ensemble de données d'origine par 3.
Vous devez donc connaître les relations entre vos données avant de procéder à une jointure et ne pas partir du principe que les clés sont uniques.
Cliquez sur Vérifier ma progression pour valider l'objectif.
Vous avez terminé cet atelier et évité les pièges que peuvent poser les jointures SQL en identifiant les enregistrements en double et en apprenant quand utiliser chaque type de jointure. Bon travail !
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 : 20 septembre 2023
Copyright 2025 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.