GSP413

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.

Vous allez utiliser un ensemble de données d'e-commerce qui comprend des millions d'enregistrements Google Analytics issus du Google Merchandise Store, et explorer les champs et lignes qu'il contient afin d'obtenir des insights.

Cet atelier va vous apprendre à créer des tables de rapports à l'aide des opérateurs SQL JOIN et UNION.

Scénario : votre équipe marketing vous a fourni, à vous et votre équipe de data scientists, tous les avis sur les produits recueillis sur votre site Web d'e-commerce. Vous collaborez avec elle pour créer dans BigQuery un entrepôt de données qui regroupe des données des trois sources suivantes :

• Données d'e-commerce du site Web
• Niveau des stocks et délais de livraison des produits
• Analyse des sentiments dans les avis sur les produits

Objectifs de l'atelier

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

• Explorer les nouvelles données d'e-commerce sur l'analyse des sentiments
• Joindre des ensembles de données et créer des tables
• Ajouter des données historiques à l'aide d'unions et de caractères génériques de table

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 pour stocker vos tables

Pour commencer, créez dans BigQuery un ensemble de données intitulé ecommerce pour stocker vos tables.

1. Dans le volet de gauche, cliquez sur le nom de votre projet BigQuery (qwiklabs-gcp-xxxx).

2. Cliquez sur les trois points à côté du nom de votre projet, puis sur Créer un ensemble de données.

La boîte de dialogue Créer un ensemble de données s'ouvre.

3. Définissez le champ ID de l'ensemble de données sur ecommerce et conservez la valeur par défaut de toutes les autres options.

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 pour stocker les tables

Tâche 2 : Explorer l'ensemble de données de sentiments sur les produits

Votre équipe de data scientists a analysé la totalité des avis sur vos produits à l'aide de l'API, et vous a fourni un score et une magnitude de sentiment moyens pour chacun de vos produits.

Le projet portant sur l'ensemble de données de votre équipe marketing se nomme data-to-insights. Les ensembles de données publics BigQuery ne sont pas affichés par défaut dans BigQuery. Les requêtes de cet atelier vont utiliser l'ensemble de données data-to-insights, même s'il n'est pas visible.

1. Pour commencer, faites une copie de la table créée par l'équipe de data scientists afin de la lire :

create or replace TABLE ecommerce.products AS SELECT * FROM `data-to-insights.ecommerce.products` Remarque : Cette table vous est fournie uniquement pour que vous puissiez la lire. Les requêtes de cet atelier utiliseront le projet data-to-insights.

2. Cliquez sur l'ensemble de données ecommerce pour afficher la table products.

Examiner les données à l'aide des onglets "Aperçu" et "Schéma"

1. Accédez à l'ensemble de données ecommerce > products et cliquez sur l'onglet Aperçu pour afficher les données.

2. Cliquez sur l'onglet Schéma.

Créer une requête qui affiche les cinq principaux produits recueillant le sentiment le plus positif

1. Dans l'éditeur de requête, rédigez votre requête SQL.

Solution possible :

SELECT SKU, name, sentimentScore, sentimentMagnitude FROM `data-to-insights.ecommerce.products` ORDER BY sentimentScore DESC LIMIT 5

2. Modifiez votre requête pour afficher les cinq principaux produits recueillant le sentiment le plus négatif et filtrer les valeurs NULL.

Solution possible :

SELECT SKU, name, sentimentScore, sentimentMagnitude FROM `data-to-insights.ecommerce.products` WHERE sentimentScore IS NOT NULL ORDER BY sentimentScore LIMIT 5

Quel produit recueille le sentiment le plus négatif ?

Cliquez sur Vérifier ma progression pour valider l'objectif. Explorer l'ensemble de données de sentiments sur les produits

Tâche 3 : Joindre des ensembles de données pour obtenir des insights

Scénario : C'est le premier jour du mois, et l'équipe chargée de l'inventaire vous a informé que le champ orderedQuantity de l'ensemble de données de l'inventaire des produits était obsolète. Elle a besoin de votre aide pour obtenir le montant total des ventes par produit pour le 01/08/2017 et comparer ce chiffre au niveau actuel des stocks dans l'inventaire, afin de déterminer quels produits réapprovisionner en priorité.

Calculer le volume de ventes journalier par code produit (SKU)

1. Créez une table dans votre ensemble de données ecommerce en respectant les spécifications suivantes :

• Intitulez la table sales_by_sku_20170801
• Extrayez les données de data-to-insights.ecommerce.all_sessions_raw
• Incluez uniquement les résultats distincts
• Renvoyez productSKU
• Renvoyez la quantité totale commandée (productQuantity). Conseil : utilisez un opérateur SUM() avec une condition IFNULL
• Filtrez uniquement les ventes effectuées à la date 20170801
• Utilisez ORDER BY pour trier les SKU dans l'ordre décroissant du nombre de commandes

Solution possible :

# pull what sold on 08/01/2017 CREATE OR REPLACE TABLE ecommerce.sales_by_sku_20170801 AS SELECT productSKU, SUM(IFNULL(productQuantity,0)) AS total_ordered FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE date = '20170801' GROUP BY productSKU ORDER BY total_ordered DESC #462 skus sold

2. Cliquez sur la table sales_by_sku, puis sur l'onglet Aperçu.

Combien de codes produit distincts ont été vendus ?

Réponse : 462

Enrichissez ensuite vos données de ventes à l'aide des informations de l'inventaire des produits en joignant les deux ensembles de données.

Joindre des données de ventes et des données d'inventaire

1. À l'aide d'une jointure, enrichissez les données d'e-commerce du site Web avec les champs suivants de l'ensemble de données de l'inventaire des produits :

• name
• stockLevel
• restockingLeadTime
• sentimentScore
• sentimentMagnitude

2. Complétez la requête partiellement rédigée :

# join against product inventory to get name SELECT DISTINCT website.productSKU, website.total_ordered, inventory.name, inventory.stockLevel, inventory.restockingLeadTime, inventory.sentimentScore, inventory.sentimentMagnitude FROM ecommerce.sales_by_sku_20170801 AS website LEFT JOIN `data-to-insights.ecommerce.products` AS inventory ORDER BY total_ordered DESC

Solution possible :

# join against product inventory to get name SELECT DISTINCT website.productSKU, website.total_ordered, inventory.name, inventory.stockLevel, inventory.restockingLeadTime, inventory.sentimentScore, inventory.sentimentMagnitude FROM ecommerce.sales_by_sku_20170801 AS website LEFT JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU ORDER BY total_ordered DESC

3. Modifiez la requête que vous avez rédigée pour inclure les éléments suivants :

• Un champ calculé (total_ordered / stockLevel) auquel vous associez l'alias "ratio". Conseil : utilisez SAFE_DIVIDE(field1,field2) pour éviter les erreurs dues à la division par 0 lorsque le niveau de stock est de 0.
• Filtrez les résultats pour inclure uniquement les produits qui ont déjà écoulé au moins 50 % de leur stock au début du mois.

Solution possible :

# calculate ratio and filter SELECT DISTINCT website.productSKU, website.total_ordered, inventory.name, inventory.stockLevel, inventory.restockingLeadTime, inventory.sentimentScore, inventory.sentimentMagnitude, SAFE_DIVIDE(website.total_ordered, inventory.stockLevel) AS ratio FROM ecommerce.sales_by_sku_20170801 AS website LEFT JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU # gone through more than 50% of inventory for the month WHERE SAFE_DIVIDE(website.total_ordered,inventory.stockLevel) >= .50 ORDER BY total_ordered DESC

Cliquez sur Vérifier ma progression pour valider l'objectif. Joindre des ensembles de données pour obtenir des insights

Tâche 4 : Ajouter des enregistrements

Votre équipe internationale a déjà réalisé à la date du 02/08/2017 des ventes en magasin que vous voulez enregistrer dans vos tables de ventes journalières.

Créer une table vide pour stocker les ventes par code produit pour le 02/08/2017

1. Spécifiez les champs suivants pour le schéma :

• nom de la table : ecommerce.sales_by_sku_20170802
• productSKU STRING
• total_ordered en tant que champ INT64

Solution possible :

CREATE OR REPLACE TABLE ecommerce.sales_by_sku_20170802 ( productSKU STRING, total_ordered INT64 );

2. Vérifiez que vous avez maintenant des tables de ventes partagées pour deux dates. Pour cela, cliquez sur le menu déroulant situé à côté du nom de la table Sales_by_sku dans les résultats de la table, ou actualisez votre navigateur pour les voir s'afficher dans le menu de gauche :

3. Insérez l'enregistrement de ventes qui vous a été fourni par votre équipe commerciale :

INSERT INTO ecommerce.sales_by_sku_20170802 (productSKU, total_ordered) VALUES('GGOEGHPA002910', 101)

4. Affichez l'aperçu de la table pour vérifier que l'enregistrement apparaît : cliquez sur le nom de la table pour afficher les résultats.

Combiner des données historiques

Il existe plusieurs manières de combiner des données possédant le même schéma. Deux moyens courants sont les opérateurs UNION et les caractères génériques de table.

• Union est un opérateur SQL qui permet de combiner des lignes issues d'ensembles de résultats différents.
• Les caractères génériques de table permettent d'interroger plusieurs tables à l'aide d'instructions SQL concises. Les tables génériques sont disponibles uniquement en langage SQL standard.

1. Rédigez une requête UNION qui renverra tous les enregistrements des deux tables ci-dessous :

• ecommerce.sales_by_sku_20170801
• ecommerce.sales_by_sku_20170802

SELECT * FROM ecommerce.sales_by_sku_20170801 UNION ALL SELECT * FROM ecommerce.sales_by_sku_20170802 Remarque : La différence entre UNION et UNION ALL est qu'une instruction UNION n'inclut pas les enregistrements en double.

Quel est l'inconvénient d'utiliser plusieurs tables de ventes journalières ? Vous devrez coder plusieurs instructions UNION enchaînées ensemble.

Il est préférable d'utiliser le filtre générique de table et le filtre _TABLE_SUFFIX.

2. Rédigez une requête qui utilise le caractère générique de table (*) pour sélectionner tous les enregistrements de ecommerce.sales_by_sku_ pour l'année 2017.

Solution possible :

SELECT * FROM `ecommerce.sales_by_sku_2017*`

3. Modifiez la requête précédente en ajoutant un filtre afin de limiter les résultats à la date du 02/08/2017 uniquement.

Solution possible :

SELECT * FROM `ecommerce.sales_by_sku_2017*` WHERE _TABLE_SUFFIX = '0802' Remarque : Vous pouvez également créer une table partitionnée qui peut ingérer automatiquement les données de ventes quotidiennes dans la partition appropriée.

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

Félicitations !

Vous avez exploré des échantillons de données d'e-commerce en créant des tables de rapports, puis en manipulant des vues à l'aide des opérateurs SQL JOIN et UNION.

É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.
• Si vous souhaitez en savoir plus sur la syntaxe SQL pour les types d'opérateurs JOIN, consultez la documentation BigQuery sur les types d'opérateurs JOIN.
• Essayez Google Recherche d'ensembles de données en tant que ressource.
• Découvrez ces autres ateliers :
  • Explorer les données NCAA à l'aide de BigQuery
  • Cloud Composer : Copier des tables BigQuery à différents emplacements

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 : 31 octobre 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.