GSP408

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

Un nouvel ensemble de données d'e-commerce contenant des millions d'enregistrements Google Analytics pour Google Merchandise Store a été importé dans BigQuery. Pour cet atelier, vous disposez d'une copie de cet ensemble de données, et vous allez explorer les champs et les lignes qu'il contient afin d'obtenir des renseignements.

Cet atelier vous aide à comprendre la logique à suivre pour corriger des requêtes. Les activités sont réalisées dans le contexte d'un scénario réel. Tout au long de cet atelier, imaginez que vous travaillez avec un analyste de données qui a récemment intégré votre équipe. Celui-ci vous a transmis les requêtes ci-après devant permettre de répondre à certaines questions sur votre ensemble de données d'e-commerce. À l'aide des réponses, corrigez ces requêtes afin d'obtenir un résultat pertinent.

Points abordés

Dans cet atelier, vous allez apprendre à :

• épingler des projets à l'arborescence des ressources BigQuery ;
• utiliser l'éditeur de requête et validateur de requête BigQuery pour identifier et corriger des erreurs de syntaxe et de logique SQL courantes.

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
   • 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. 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 inclus dans le panneau Détails concernant l'atelier et collez-le dans la boîte de dialogue Se connecter. Cliquez sur Suivant.

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

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

Remarque : Vous pouvez afficher le menu qui contient la liste des produits et services Google Cloud en cliquant sur le menu de navigation en haut à gauche.

Tâche 1 : Épingler un projet à l'arborescence des ressources BigQuery

1. Cliquez sur le menu de navigation  > BigQuery.

Le message "Bienvenue sur BigQuery dans la console Cloud" s'affiche.

Remarque : Il contient un lien vers le guide de démarrage rapide et les nouveautés de l'interface utilisateur.

2. Cliquez sur OK.

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

4. Cliquez sur Ajouter > Ajouter un projet aux favoris en saisissant son nom, puis collez le nom "data-to-insights".

5. Cliquez sur Ajouter aux favoris.

Le projet data-to-insights apparaît désormais dans la section "Explorateur".

Éditeur de requête et validateur de requête BigQuery

Dans chaque activité des sections suivantes de cet atelier, les requêtes contiennent des erreurs courantes que vous devez corriger. L'atelier vous explique la marche à suivre pour identifier l'erreur et corriger la syntaxe afin d'obtenir des résultats utiles.

Pour suivre la procédure de correction et voir les suggestions, copiez la requête et collez-la dans l'éditeur de requête BigQuery. Si celle-ci contient des erreurs, un point d'exclamation rouge s'affiche sur les lignes concernées ainsi que dans l'outil de validation de requête (dans le coin inférieur).

Si vous exécutez la requête alors qu'elle contient une erreur, elle n'aboutit pas, et l'erreur apparaît dans les informations sur le job.

Si la requête est correcte, une coche verte est visible dans l'outil de validation des requêtes. Dans ce cas, cliquez sur Exécuter pour exécuter la requête et afficher les résultats.

Remarque : Pour obtenir des informations sur la syntaxe, consultez l'article sur la syntaxe des requêtes en SQL standard.

Tâche 2 : Trouver le nombre total de clients qui ont effectué un achat

Dans cette section, votre objectif est de créer une requête qui vous donne le nombre de visiteurs uniques ayant effectué un achat sur votre site Web. Les données se trouvent dans la table rev_transactions fournie par votre équipe d'analystes de données. Celle-ci vous a également donné des exemples de requêtes pour vous aider à faire votre analyse, mais vous n'êtes pas certain que le code soit correct.

Corriger des requêtes contenant des erreurs de validation, d'alias et de virgule

• Examinez la requête ci-dessous et répondez à la question suivante :

#standardSQL SELECT FROM `data-to-inghts.ecommerce.rev_transactions` LIMIT 1000

• Que pensez-vous de cette requête modifiée ?

#standardSQL SELECT * FROM [data-to-insights:ecommerce.rev_transactions] LIMIT 1000

• Que pensez-vous de cette requête utilisant le langage SQL standard ?

#standardSQL SELECT FROM `data-to-insights.ecommerce.rev_transactions`

• Et maintenant ? Cette requête fait référence à une colonne :

#standardSQL SELECT fullVisitorId FROM `data-to-insights.ecommerce.rev_transactions`

• Et maintenant ? La requête suivante fait référence à un titre de page :

#standardSQL SELECT fullVisitorId hits_page_pageTitle FROM `data-to-insights.ecommerce.rev_transactions` LIMIT 1000

• Et maintenant ? La virgule manquante a été ajoutée :

#standardSQL SELECT fullVisitorId , hits_page_pageTitle FROM `data-to-insights.ecommerce.rev_transactions` LIMIT 1000

Réponse : cette requête renvoie des résultats, mais êtes-vous certain que les visiteurs ne sont pas comptabilisés deux fois ? De plus, avec une réponse d'une seule ligne, vous savez combien de visiteurs uniques sont arrivés jusqu'au paiement. Dans la section suivante, vous allez apprendre à agréger vos résultats.

Corriger des requêtes contenant des erreurs de logique, des instructions GROUP BY et des filtres utilisant des caractères génériques

• Agrégez la requête suivante pour répondre à la question "Combien de visiteurs uniques ont effectué un achat ?".

#standardSQL SELECT fullVisitorId , hits_page_pageTitle FROM `data-to-insights.ecommerce.rev_transactions` LIMIT 1000

• Que pensez-vous de la requête suivante ? La fonction d'agrégation COUNT() a été ajoutée :

#standardSQL SELECT COUNT(fullVisitorId) AS visitor_count , hits_page_pageTitle FROM `data-to-insights.ecommerce.rev_transactions`

• Dans la requête suivante, des instructions GROUP BY et DISTINCT ont été ajoutées :

#standardSQL SELECT COUNT(DISTINCT fullVisitorId) AS visitor_count , hits_page_pageTitle FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY hits_page_pageTitle

Résultats

Parfait ! Les résultats sont bons, mais ils ont l'air étrange.

• Filtrez les résultats pour n'afficher que les confirmations de paiement ("Checkout Confirmation") :

#standardSQL SELECT COUNT(DISTINCT fullVisitorId) AS visitor_count , hits_page_pageTitle FROM `data-to-insights.ecommerce.rev_transactions` WHERE hits_page_pageTitle = "Checkout Confirmation" GROUP BY hits_page_pageTitle

Cliquez sur Vérifier ma progression pour valider l'objectif. Trouver le nombre total de clients qui ont effectué un achat

Tâche 3 : Lister les villes comptant le plus de transactions sur votre site e-commerce

Résoudre les problèmes de classement, de champs calculés et de filtrage après agrégation des erreurs

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

SELECT geoNetwork_city, totals_transactions, COUNT( DISTINCT fullVisitorId) AS distinct_visitors FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY

Solution possible :

#standardSQL SELECT geoNetwork_city, SUM(totals_transactions) AS totals_transactions, COUNT( DISTINCT fullVisitorId) AS distinct_visitors FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY geoNetwork_city

2. Modifiez la requête précédente pour classer les villes selon un ordre décroissant pour le nombre de transactions.

Solution possible :

#standardSQL SELECT geoNetwork_city, SUM(totals_transactions) AS totals_transactions, COUNT( DISTINCT fullVisitorId) AS distinct_visitors FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY geoNetwork_city ORDER BY distinct_visitors DESC

3. Modifiez la requête en créant un autre champ calculé destiné à renvoyer le nombre moyen de produits par commande et par ville.

Solution possible :

#standardSQL SELECT geoNetwork_city, SUM(totals_transactions) AS total_products_ordered, COUNT( DISTINCT fullVisitorId) AS distinct_visitors, SUM(totals_transactions) / COUNT( DISTINCT fullVisitorId) AS avg_products_ordered FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY geoNetwork_city ORDER BY avg_products_ordered DESC

Résultats

Filtrez vos résultats agrégés pour ne renvoyer que les villes présentant une valeur supérieure à 20 pour avg_products_ordered.

• Quel est le problème avec la requête suivante ?

#standardSQL SELECT geoNetwork_city, SUM(totals_transactions) AS total_products_ordered, COUNT( DISTINCT fullVisitorId) AS distinct_visitors, SUM(totals_transactions) / COUNT( DISTINCT fullVisitorId) AS avg_products_ordered FROM `data-to-insights.ecommerce.rev_transactions` WHERE avg_products_ordered > 20 GROUP BY geoNetwork_city ORDER BY avg_products_ordered DESC

Solution possible :

#standardSQL SELECT geoNetwork_city, SUM(totals_transactions) AS total_products_ordered, COUNT( DISTINCT fullVisitorId) AS distinct_visitors, SUM(totals_transactions) / COUNT( DISTINCT fullVisitorId) AS avg_products_ordered FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY geoNetwork_city HAVING avg_products_ordered > 20 ORDER BY avg_products_ordered DESC

Cliquez sur Vérifier ma progression pour valider l'objectif. Lister les villes comptant le plus de transactions sur votre site e-commerce

Tâche 4 : Trouver le nombre total de produits dans chaque catégorie de produits

Trouver les produits les plus vendus en filtrant avec des valeurs NULL

1. Quel est le problème avec la requête suivante ? Comment corrigeriez-vous l'erreur ?

#standardSQL SELECT hits_product_v2ProductName, hits_product_v2ProductCategory FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY 1,2

2. Quel est le problème avec la requête suivante ?

#standardSQL SELECT COUNT(hits_product_v2ProductName) as number_of_products, hits_product_v2ProductCategory FROM `data-to-insights.ecommerce.rev_transactions` WHERE hits_product_v2ProductName IS NOT NULL GROUP BY hits_product_v2ProductCategory ORDER BY number_of_products DESC

3. Modifiez la requête précédente pour ne comptabiliser que les produits distincts dans chaque catégorie de produits.

Solution possible :

#standardSQL SELECT COUNT(DISTINCT hits_product_v2ProductName) as number_of_products, hits_product_v2ProductCategory FROM `data-to-insights.ecommerce.rev_transactions` WHERE hits_product_v2ProductName IS NOT NULL GROUP BY hits_product_v2ProductCategory ORDER BY number_of_products DESC LIMIT 5

Remarque :

(not set) pourrait indiquer que le produit n'a pas de catégorie.

${productitem.product.origCatName} correspond à du code frontend chargé de renvoyer la catégorie, ce qui suggère que le script de suivi Google Analytics est déclenché avant le rendu complet de la page

Cliquez sur Vérifier ma progression pour valider l'objectif. Trouver le nombre total de produits dans chaque catégorie de produits

Félicitations !

Vous avez identifié et corrigé des requêtes erronées en langage SQL standard dans BigQuery. N'oubliez pas d'utiliser l'outil de validation de requête pour vérifier la syntaxe, mais aussi pour évaluer les résultats de votre requête, même si celle-ci s'exécute correctement.

Étapes suivantes et informations supplémentaires

• Explorer les ensembles de données publics BigQuery
• Vous possédez un compte Google Analytics et souhaitez interroger vos propres ensembles de données BigQuery ? Suivez ce guide d'exportation.
• Découvrez 15 atouts de BigQuery dont vous n'aviez probablement pas idée.
• Découvrez d'autres ateliers pour en savoir plus sur BigQuery :
  • Explorer votre ensemble de données d'e-commerce avec SQL dans BigQuery
  • Données météorologiques dans BigQuery
  • Créer des tables partitionnées par date dans 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 : 19 janvier 2024

Dernier test de l'atelier : 28 août 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.