Points de contrôle
Create a new dataset
/ 25
Identify a key field in your ecommerce dataset
/ 25
Pitfall: non-unique key
/ 25
Join pitfall solution
/ 25
Résoudre les problèmes et éviter les pièges liés à la jointure des données
- GSP412
- Présentation
- Prérequis
- Tâche 1 : Créer un ensemble de données pour stocker vos tables
- Tâche 2 : Épingler le projet de l'atelier dans BigQuery
- Tâche 3 : Examiner les champs
- Tâche 4 : Identifier un champ de clé dans votre ensemble de données d'e-commerce
- Tâche 5 : Piège : clé non unique
- Tâche 6 : Solution pour éviter le piège lié aux jointures : utiliser des SKU distincts avant de procéder à la jointure
- Félicitations !
GSP412
Présentation
BigQuery est la base de données d'analyse de Google. Économique et entièrement gérée, elle ne nécessite aucune opération (no-ops). 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 :
- Jointure croisée : elle combine chaque ligne du premier ensemble de données avec chaque ligne du deuxième ensemble de données, et toutes les combinaisons sont représentées dans les résultats.
- Jointure interne : elle nécessite la présence de valeurs de clé dans les deux tables pour que les enregistrements apparaissent dans la table de résultats. Les enregistrements n'apparaissent dans la fusion que s'il existe des correspondances dans les deux tables pour les valeurs de clé.
- Jointure gauche : chaque ligne de la table de gauche apparaît dans les résultats, que la table de droite contienne ou non des correspondances.
- Jointure droite : l'inverse d'une jointure gauche. Chaque ligne de la table de droite apparaît dans les résultats, que la table de gauche contienne ou non des correspondances.
Pour en savoir plus sur les jointures, reportez-vous à la page à ce sujet.
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.
Objectifs de l'atelier
Dans cet atelier, vous allez :
- utiliser BigQuery pour explorer un ensemble de données ;
- résoudre les problèmes de lignes en double dans un ensemble de données ;
- créer des jointures entre les tables de données ;
- comprendre chaque type de jointure.
Prérequis
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) ;
- vous disposez d'un temps limité ; une fois l'atelier commencé, vous ne pouvez pas le mettre en pause.
Démarrer l'atelier et se connecter à la console Google Cloud
-
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
- Le temps restant
- Les identifiants temporaires que vous devez utiliser pour cet atelier
- Des informations complémentaires vous permettant d'effectuer l'atelier
-
Cliquez sur Ouvrir la console Google. 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. -
Si nécessaire, copiez le nom d'utilisateur inclus dans le panneau Détails concernant l'atelier et collez-le dans la boîte de dialogue Se connecter. Cliquez sur Suivant.
-
Copiez le mot de passe inclus dans le panneau Détails concernant l'atelier et collez-le dans la boîte de dialogue de bienvenue. Cliquez sur Suivant.
Important : Vous devez utiliser les identifiants fournis dans le panneau de gauche. Ne saisissez pas vos identifiants Google Cloud Skills Boost. Remarque : Si vous utilisez votre propre compte Google Cloud pour cet atelier, des frais supplémentaires peuvent vous être facturés. -
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 aux essais offerts.
Après quelques instants, la console Cloud s'ouvre dans cet onglet.
Ouvrir la console BigQuery
- 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.
- Cliquez sur OK.
La console BigQuery s'ouvre.
Tâche 1 : Créer un ensemble de données pour stocker vos tables
Dans votre projet BigQuery, créez un ensemble de données intitulé ecommerce
.
- Cliquez sur les trois points à côté de l'ID de votre projet et sélectionnez Créer un ensemble de données.
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.
Tâche 2 : Épingler le projet de l'atelier dans BigQuery
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.
- Cliquez sur le menu de navigation > BigQuery.
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".
Tâche 3 : Examiner les champs
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.
Tâche 4 : Identifier un champ de clé dans votre ensemble de données d'e-commerce
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.
Examiner les enregistrements
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.
- Recherchez combien de noms de produits et de SKU sont présents sur le site Web. Copiez et collez la requête ci-dessous dans l'éditeur BigQuery :
- Cliquez sur Exécuter.
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.
- Effacez la requête précédente et exécutez la requête ci-dessous pour lister le nombre de SKU différents à l'aide de la clause
DISTINCT
:
Examiner la relation entre le SKU et le nom
À présent, déterminez quels produits sont associés à plusieurs SKU et quels SKU s'appliquent à plus d'un nom de produit.
- Effacez la requête précédente et exécutez la requête ci-dessous pour déterminer si certains noms de produits sont associés à plus d'un SKU. La fonction STRING_AGG() permet de regrouper tous les SKU associés à un nom de produit en une liste de valeurs séparées par des virgules.
- Cliquez sur Exécuter.
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 ?
- Pour le savoir, effacez la requête précédente et exécutez la requête ci-dessous :
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.
Tâche 5 : Piège : clé non unique
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.
-
Écrivez une requête pour identifier tous les noms de produits associés au SKU
'GGOEGPJC019099'
.
Solution possible :
- Cliquez sur Exécuter.
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 :
Joindre des données du site Web à la liste d'inventaire de vos produits
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.
- Effacez la requête précédente et exécutez la requête suivante :
Piège lié aux jointures : création involontaire d'une relation de SKU de type plusieurs à un
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.
- Effacez la requête précédente et exécutez la requête suivante :
Ensuite, développez la requête précédente pour calculer le stock disponible pour chaque produit à l'aide de la fonction SUM.
- Effacez la requête précédente et exécutez la requête suivante :
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.
Tâche 6 : Solution pour éviter le piège lié aux jointures : utiliser des SKU distincts avant de procéder à la jointure
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.
- Regroupez tous les noms possibles dans un tableau :
Maintenant, au lieu d'avoir une ligne pour chaque nom de produit, vous avez uniquement une ligne pour chaque SKU unique.
- Si vous vouliez dédupliquer les noms de produit, vous pourriez même limiter le tableau comme suit :
Piège lié aux jointures : perte d'enregistrements de données après une jointure
Vous êtes maintenant prêt à refaire la jointure de l'ensemble de données de votre inventaire.
- Effacez la requête précédente et exécutez la requête suivante :
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) :
- Effacez la requête précédente et exécutez la requête suivante :
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 ?
Solution pour éviter le piège lié aux jointures : sélectionner le bon type de jointure et filtrer les valeurs NULL
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.
- Reprenez la requête précédente pour employer un autre type de jointure. Vous pourrez ainsi inclure tous les enregistrements de la table du site Web, qu'il y ait ou non une correspondance avec un enregistrement de SKU de l'inventaire. Types de jointures possibles : INNER JOIN (JOINTURE INTERNE), LEFT JOIN (JOINTURE GAUCHE), RIGHT JOIN (JOINTURE DROITE), FULL JOIN (JOINTURE COMPLÈTE), CROSS JOIN (JOINTURE CROISÉE).
Solution possible :
- Cliquez sur Exécuter.
À 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 ?
- Saisissez une requête pour filtrer les valeurs NULL de la table de l'inventaire.
Solution possible :
- Cliquez sur Exécuter.
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.
- Effacez la requête précédente et exécutez la requête ci-dessous pour confirmer l'utilisation de l'un des SKU spécifiques de l'ensemble de données du site Web :
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 ?
- Saisissez une requête en utilisant un autre type de jointure pour en savoir plus.
Solution possible :
- Cliquez sur Exécuter.
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.
- Effacez la requête précédente et exécutez la requête suivante :
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 :
- Un nouveau produit (aucune commande, rien sous sentimentScore) et un produit "in store only" (disponible en magasin uniquement).
- Un nouveau produit avec 0 commande
Pourquoi le nouveau produit n'apparaîtrait-il pas dans l'ensemble de données de votre site Web ?
- L'ensemble de données du site Web répertorie les commandes passées des clients. Les nouveaux produits qui n'ont jamais été vendus n'apparaîtront pas dans les analyses Web tant qu'ils ne seront pas vus ou achetés.
Comment obtenir une requête répertoriant tous les produits manquants sur le site Web ou dans l'inventaire ?
- Saisissez une requête en utilisant un autre type de jointure.
Solution possible :
- Cliquez sur Exécuter.
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.
Piège lié aux jointures : jointure croisée involontaire
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).
- Effacez la requête précédente et exécutez la requête suivante :
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.
- Effacez la requête précédente et exécutez la requête suivante pour savoir combien de produits sont en liquidation :
Voyons quelles sont les conséquences de l'ajout involontaire de plusieurs enregistrements dans la table de la remise.
- Effacez la requête précédente et exécutez la requête suivante pour insérer deux autres enregistrements dans la table de la promotion :
Examinons ensuite les valeurs de données de la table de la promotion.
- Effacez la requête précédente et exécutez la requête suivante :
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 ?
- Effacez la requête précédente et exécutez la requête suivante :
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.
- Effacez la requête précédente et exécutez la requête suivante :
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.
Félicitations !
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 !
Terminer votre quête
Cet atelier d'auto-formation fait partie de la quête BigQuery for Data Warehousing. Une quête est une série d'ateliers associés qui constituent un parcours de formation. Si vous terminez cette quête, vous obtenez un badge attestant de votre réussite. Vous pouvez rendre publics les badges que vous recevez et ajouter leur lien dans votre CV en ligne ou sur vos comptes de réseaux sociaux. Inscrivez-vous à cette quête pour obtenir immédiatement les crédits associés à cet atelier si vous l'avez suivi. Découvrez toutes les quêtes disponibles dans le catalogue Google Cloud Skills Boost.
Atelier suivant
Continuez sur votre lancée avec un autre atelier, par exemple Utiliser des objets JSON, ARRAY et STRUCT dans BigQuery ou découvrez ces ateliers :
- Présentation de SQL pour BigQuery et Cloud SQL
- Prédire le prix d'une course en taxi à l'aide d'un modèle de ML BigQuery
Étapes suivantes et informations supplémentaires
-
Vous possédez un compte Google Analytics et souhaitez interroger vos propres ensembles de données BigQuery ? Suivez ce guide d'exportation.
-
Consultez les bonnes pratiques pour'optimiser le calcul des requêtes.
-
Si vous souhaitez en savoir plus sur la syntaxe SQL pour les opérations JOIN, consultez la documentation sur les types de jointures de BigQuery.
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 : 10 mai 2023
Dernier test de l'atelier : 10 mai 2023
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.