IMPORTANT
Cet atelier pratique ne peut être réalisé que sur un ordinateur de bureau ou un ordinateur portable.
Vous ne pouvez tenter l'atelier que cinq fois.
Pour rappel, il est normal de ne pas répondre correctement à toutes les questions du premier coup, et même de devoir refaire un exercice. Cela fait partie du processus d'apprentissage.
Une fois l'atelier démarré, le minuteur ne peut pas être mis en pause. Au bout d'une heure et demie, l'atelier se terminera et vous devrez le recommencer.
Pour en savoir plus, consultez le document Conseils techniques pour les ateliers.
Présentation de l'activité
Un pipeline de données est une série de processus consistant à transporter des données de différentes sources vers une destination à des fins de stockage et d'analyse
Il permet d'améliorer les performances et l'efficacité de la transformation des données en automatisant le flux de données et en simplifiant la façon dont elles sont traitées.
L'utilisation d'un pipeline SQL peut vous aider à améliorer les performances en éliminant les étapes inutiles. En effet, il permet de lire et d'écrire des données directement dans la destination sans avoir à créer d'ensembles de données intermédiaires. Vous libérez ainsi du temps et des ressources tout en améliorant la précision des résultats.
De plus, un pipeline SQL peut vous aider à optimiser votre capacité de stockage de données. En effet, vous pouvez utiliser le pipeline pour supprimer ou archiver les données dont vous n'avez plus besoin. Cela peut libérer de l'espace sur vos systèmes de stockage et améliorer les performances.
Il peut être difficile de créer des pipelines SQL efficaces et flexibles, en particulier lorsque les besoins des organisations évoluent rapidement. Toutefois, en planifiant et en concevant soigneusement vos pipelines, vous pouvez en créer qui s'adaptent même aux besoins de données les plus complexes.
Dans cet atelier, vous allez gérer des données et utiliser SQL pour créer un pipeline efficace et flexible qui répond à un besoin métier.
Scénario
Ces dernières années, les bénéfices de TheLook eCommerce ont explosé grâce aux achats en ligne. Cependant, les délais de livraison n'ont pas suivi le rythme, et la satisfaction client a diminué.
En tant qu'analyste de données cloud chez TheLook eCommerce, vous devez collaborer avec Kai, le responsable de l'équipe logistique, pour développer un pipeline de données permettant de collecter, nettoyer, transformer et charger des données sur les livraisons aux clients, y compris la distance parcourue entre le centre de distribution et chaque client.
Ces informations aideront l'équipe logistique à déterminer comment améliorer les délais de livraison et la satisfaction client (par exemple, en ouvrant de nouveaux centres de distribution, en relocalisant des centres existants ou en investissant dans de nouveaux modes de transport).
Vous appliquerez vos compétences BigQuery et SQL pour concevoir un pipeline flexible qui fournit à l'équipe logistique des données fiables pour mieux surveiller les performances de livraison. Vous pourrez facilement le mettre à jour en fonction de l'évolution des besoins de l'équipe logistique en termes de données.
Tout d'abord, vous allez créer un ensemble de données et définir des schémas de table pour les données qui seront ingérées. Ensuite, vous allez effectuer et explorer une série de transformations, puis vous appliquerez les transformations aux données avant de les charger dans les tables nouvellement définies. Enfin, vous formaliserez ces requêtes dans une procédure stockée.
Configuration
Avant de cliquer sur "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 compte temporaire de participant, qui pourraient entraîner des frais supplémentaires facturés sur votre compte personnel.
- Vous disposez d'un temps limité. N'oubliez pas qu'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
-
Cliquez sur le bouton Démarrer l'atelier. Sur la gauche, vous trouverez le panneau Détails concernant l'atelier, qui contient les éléments suivants :
- Le temps restant
- Le bouton Ouvrir la console Google Cloud
- Les identifiants temporaires que vous devez utiliser pour cet atelier
- D'éventuelles informations complémentaires vous permettant d'effectuer l'atelier
Remarque : Si l'atelier est payant, un pop-up s'affiche pour vous permettre de sélectionner un mode de paiement.
-
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. La page Se connecter s'ouvre dans un nouvel onglet du navigateur.
Conseil : Vous pouvez réorganiser les onglets dans des fenêtres distinctes, placées côte à côte, pour passer facilement de l'un à l'autre.
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 Google Cloud ci-dessous et collez-le dans la boîte de dialogue Se connecter. Cliquez sur Suivant.
{{{user_0.username | "Google Cloud username"}}}
Vous trouverez également le nom d'utilisateur Google Cloud dans le panneau Détails concernant l'atelier.
- Copiez le mot de passe Google Cloud ci-dessous et collez-le dans la boîte de dialogue Bienvenue. Cliquez sur Suivant.
{{{user_0.password | "Google Cloud password"}}}
Vous trouverez également le mot de passe Google Cloud dans le panneau Détails concernant l'atelier.
Important : Vous devez utiliser les identifiants qui vous ont été fournis pour l'atelier. N'utilisez 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.
- Parcourez les 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 sans frais.
Après quelques instants, la console 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 : Créer un ensemble de données
Dans cette tâche, vous allez créer un ensemble de données dans le projet existant, puis définir le schéma et les types de données.
- Dans le menu de navigation (
) de la console Cloud, sélectionnez BigQuery.
Remarque : Il est possible que la boîte de dialogue Bienvenue sur BigQuery dans la console Cloud s'affiche. Elle fournit des liens vers le guide de démarrage rapide et les notes de version concernant les mises à jour de l'interface utilisateur. Cliquez sur OK pour continuer.
Le menu de navigation, le volet Explorateur et l'éditeur de requête s'affichent.
- Cliquez sur l'icône Afficher les actions (
) à côté de l'ID de votre projet, puis sur Créer un ensemble de données.

Remarque : Vous devrez peut-être d'abord sélectionner un projet. Pour ce faire, cliquez sur Sélectionner un projet dans la barre de titre de la console Google Cloud, puis sélectionnez le lien du projet dans la boîte de dialogue Sélectionner un projet.
-
Dans le volet Créer un ensemble de données, définissez l'ID de l'ensemble de données sur thelook_ecommerce. Vérifiez que l'emplacement est défini sur l'emplacement multirégional US et conservez les valeurs par défaut des autres champs.
-
Cliquez sur Créer un ensemble de données.
L'ensemble de données thelook_ecommerce s'affiche à présent sous le nom de votre projet.
- Dans l'éditeur de requête, cliquez sur l'icône Requête SQL (+) pour ouvrir un nouvel onglet Requête sans titre dans lequel exécuter la requête.
Remarque : Chaque fois que vous exécutez une nouvelle requête dans l'éditeur de requête, vous pouvez remplacer la précédente requête en copiant et en collant la nouvelle dans le même onglet Requête sans titre, ou vous pouvez cliquer sur l'icône Requête SQL (+) pour ouvrir un nouvel onglet Requête sans titre dans lequel exécuter la requête.
- Copiez et collez la requête suivante dans l'onglet Requête sans titre pour créer la table
product_order_fulfillment :
--Create empty product_orders_fulfillment table
CREATE OR REPLACE TABLE
`thelook_ecommerce.product_orders_fulfillment`
( order_id INT64,
user_id INT64,
status STRING,
product_id INT64,
created_at TIMESTAMP,
returned_at TIMESTAMP,
shipped_at TIMESTAMP,
delivered_at TIMESTAMP,
cost NUMERIC,
sale_price NUMERIC,
retail_price NUMERIC,
category STRING,
name STRING,
brand STRING,
department STRING,
sku STRING,
distribution_center_id INT64);
- Cliquez sur Exécuter.
Remarque : Cette requête crée une table nommée product_orders_fulfillment si elle n'existe pas déjà, ou remplace la table existante par la nouvelle définition de table.
- Cliquez sur Accéder à la table pour explorer le schéma de table et les types de données qui ont été créés.
Bien qu'actuellement vide, cet ensemble de données est destiné à recevoir les données une fois le chargement terminé.
Cliquez sur Vérifier ma progression pour vérifier que vous avez correctement accompli cette tâche.
Créer un ensemble de données
Tâche 2 : Créer une table à partir des résultats d'une requête
Votre analyse s'étend désormais à la proximité des centres de distribution par rapport aux clients qui passent des commandes. Pour ce faire, vous devez calculer des valeurs en fonction des zones géographiques.
Dans cette tâche, vous allez créer des points géométriques à partir des valeurs de latitude et de longitude fournies dans les tables original users et distribution_centers.
- Copiez la requête suivante dans l'éditeur de requête :
--Create empty customers table
CREATE OR REPLACE TABLE
`thelook_ecommerce.customers`
( id INT64,
first_name STRING,
last_name STRING,
email STRING,
age INT64,
gender STRING,
state STRING,
street_address STRING,
postal_code STRING,
city STRING,
country STRING,
traffic_source STRING,
created_at TIMESTAMP,
latitude FLOAT64,
longitude FLOAT64,
point_location GEOGRAPHY);
--Create empty centers table
CREATE OR REPLACE TABLE
`thelook_ecommerce.centers`
( id INT64,
name STRING,
latitude FLOAT64,
longitude FLOAT64,
point_location GEOGRAPHY);
Cette requête crée les définitions des tables "customers" et "centers", qui incluront les colonnes d'origine des tables "users" et "distribution_centers". Elle ajoute également aux deux tables une colonne point_location, dont le type de données est défini sur "Geography".
- Cliquez sur Exécuter.
Cliquez sur Vérifier ma progression pour vérifier que vous avez correctement accompli cette tâche.
Créer des tables pour stocker les données pertinentes
Tâche 3 : Transformer des données BigQuery
Dans cette tâche, vous allez créer des géométries de points à l'aide de la fonction géographique ST_GEOGPOINT, où vous appelez ST_GEOGPOINT(lon, lat).
Tout d'abord, exécutez les requêtes SQL suivantes pour créer et remplir les tables centers et customers. Ces requêtes chargent les données à partir d'une instruction SELECT, étant donné que l'emplacement d'origine (longitude, latitude) des données se trouve dans l'ensemble de données public BigQuery thelook_ecommerce.
Un ensemble de données public Google est un ensemble de données stocké dans BigQuery et mis à la disposition du grand public dans le cadre du Programme d'ensembles de données publics de Google Cloud. Les ensembles de données publics permettent d'utiliser facilement les données publiques disponibles dans BigQuery sans avoir à les charger ni à les gérer.
- Copiez la requête suivante dans l'éditeur de requête :
--load the centers table from public dataset and include geography transformation
CREATE OR REPLACE TABLE
`thelook_ecommerce.centers` AS
SELECT
id,
name,
latitude,
longitude,
ST_GEOGPOINT(dcenters.longitude, dcenters.latitude) AS point_location
FROM
`bigquery-public-data.thelook_ecommerce.distribution_centers` AS dcenters;
Cette requête charge la table "centers", y compris la transformation géographique.
- Cliquez sur Exécuter.
Remarque : Vous pouvez cliquer sur Accéder à la table pour explorer le schéma de table et les types de données qui ont été créés.
- Copiez ensuite la requête suivante dans l'éditeur de requête :
--load the customers table from public dataset and include geography transformation
CREATE OR REPLACE TABLE
`thelook_ecommerce.customers` AS
SELECT
id,
first_name,
last_name,
email,
age,
gender,
state,
street_address,
postal_code,
city,
country,
traffic_source,
created_at,
latitude,
longitude,
ST_GEOGPOINT(users.longitude, users.latitude) AS point_location
FROM
`bigquery-public-data.thelook_ecommerce.users` AS users;
Cette requête charge la table "customers", y compris la transformation géographique.
- Cliquez sur Exécuter.
Ensuite, vous devrez calculer les valeurs de données pour l'analyse.
Étant donné que la relation entre les clients et les centres de distribution est importante, vous utiliserez ces emplacements géographiques pour déterminer le centre de distribution le plus proche de chaque client et la distance entre ces emplacements.
Pour ce faire, utilisez la fonction géographique ST_DISTANCE afin de calculer la distance minimale séparant un client d'un centre.
- Copiez la sous-requête scalaire suivante dans l'éditeur de requête :
SELECT
customers.id as customer_id,
(
SELECT
MIN(ST_DISTANCE(centers.point_location, customers.point_location))/1000,
FROM
`thelook_ecommerce.centers` AS centers) AS distance_to_closest_center
FROM
`thelook_ecommerce.customers` AS customers ;
Cette requête calcule la distance en kilomètres.
- Cliquez sur Exécuter.

Comme le montre la capture d'écran, la requête renvoie la distance entre chaque client et le centre de distribution le plus proche. Prenez le temps d'examiner l'instruction SELECT pour comprendre comment le calcul de la distance est effectué.
- Développez le menu déroulant Enregistrer, puis sélectionnez Enregistrer la requête.

La boîte de dialogue Enregistrer la requête s'ouvre.
- Dans le champ Nom, saisissez
Calculer la distance entre le client et le centre le plus proche.
- Pour Région, sélectionnez .
- Cliquez sur Enregistrer.
La requête est enregistrée dans la liste Requêtes.

Cliquez sur Vérifier ma progression pour vérifier que vous avez correctement accompli cette tâche.
Transformer des données BigQuery
Tâche 4 : Créer une procédure stockée pour faciliter les mises à jour
Une procédure stockée est un ensemble d'instructions SQL stockées dans une base de données et pouvant être exécutées en une seule fois.
Dans cette tâche, vous allez créer une procédure stockée pour inclure toutes les définitions de table, les transformations et les instructions d'ingestion que vous avez générées manuellement et séquentiellement jusqu'à présent dans les tâches 1 à 3.
Pour créer une procédure stockée (dans ce cas, sp_create_load_tables), ajoutez l'instruction CREATE OR REPLACE PROCEDURE au début de votre script, suivie du nom de la procédure stockée et de l'instruction BEGIN. Une fois que vous avez ajouté tous les composants de la procédure, ajoutez l'instruction END.
Complétez la procédure en y insérant vos définitions de tables et vos flux d'ingestion. Libre à vous de centraliser les définitions au début ou de les regrouper par table. Pour cet atelier, nous suivrons une structure séquentielle : chaque définition de table est suivie de l'ingestion des données et des transformations préalables.
Vous pouvez également créer des colonnes dans votre table customers pour stocker les valeurs distance_to_closest_center et closest_center calculées. L'atout majeur de cette méthode est sa modularité. Qu'il s'agisse d'ajuster le schéma, d'insérer des colonnes ou de générer des calculs, la mise à jour de la procédure stockée est un jeu d'enfant. Une fois la procédure stockée mise à jour, vous pouvez la configurer pour qu'elle s'exécute régulièrement.
Créez maintenant une procédure stockée qui exécutera en une seule fois toutes les étapes que vous avez effectuées précédemment dans les tâches 1 à 3. Lorsqu'il est exécuté, le code suivant met à jour les tables et les remplit de manière appropriée.
- Copiez la requête suivante dans un nouvel onglet de requête Sans titre :
CREATE OR REPLACE PROCEDURE
`thelook_ecommerce.sp_create_load_tables`()
BEGIN
--Create empty product_orders_fulfillment table
CREATE OR REPLACE TABLE
`thelook_ecommerce.product_orders_fulfillment` ( order_id INT64,
user_id INT64,
status STRING,
product_id INT64,
created_at TIMESTAMP,
returned_at TIMESTAMP,
shipped_at TIMESTAMP,
delivered_at TIMESTAMP,
cost NUMERIC,
sale_price NUMERIC,
retail_price NUMERIC,
category STRING,
name STRING,
brand STRING,
department STRING,
sku STRING,
distribution_center_id INT64) ;
--load the product_order_fulfillment table from public dataset
CREATE OR REPLACE TABLE thelook_ecommerce.product_orders_fulfillment AS
SELECT
items.*,
products.id AS product_id_products,
products.name AS product_name,
products.category AS product_category,
FROM
bigquery-public-data.thelook_ecommerce.order_items AS items
JOIN
bigquery-public-data.thelook_ecommerce.products AS products
ON
(items.product_id = products.id);
--Create empty centers table
CREATE OR REPLACE TABLE
`thelook_ecommerce.centers` ( id INT64,
name STRING,
latitude FLOAT64,
longitude FLOAT64,
point_location GEOGRAPHY);
--load the centers table from public dataset and include geography transformation
CREATE OR REPLACE TABLE
`thelook_ecommerce.centers` AS
SELECT
id,
name,
latitude,
longitude,
ST_GEOGPOINT(dcenters.longitude, dcenters.latitude) AS point_location
FROM
`bigquery-public-data.thelook_ecommerce.distribution_centers` AS dcenters ;
--Create empty customers table
CREATE OR REPLACE TABLE
`thelook_ecommerce.customers` ( id INT64,
first_name STRING,
last_name STRING,
email STRING,
age INT64,
gender STRING,
state STRING,
street_address STRING,
postal_code STRING,
city STRING,
country STRING,
traffic_source STRING,
created_at TIMESTAMP,
latitude FLOAT64,
longitude FLOAT64,
point_location GEOGRAPHY);
--load the customers table from public dataset and include geography transformation
CREATE OR REPLACE TABLE
`thelook_ecommerce.customers` AS
SELECT
id,
first_name,
last_name,
email,
age,
gender,
state,
street_address,
postal_code,
city,
country,
traffic_source,
created_at,
latitude,
longitude,
ST_GEOGPOINT(users.longitude, users.latitude) AS point_location
FROM
`bigquery-public-data.thelook_ecommerce.users` AS users ;
END
;
- Cliquez sur Exécuter.
Remarque : La procédure stockée crée toutes les tables nécessaires et les remplit avec les données transformées. Toutefois, la procédure stockée ne renvoie pas les résultats de la requête qui indiquent la distance entre les clients et leur centre de distribution le plus proche. Pour ce faire, passez à l'étape 7 ci-dessous.
- Développez le menu déroulant Enregistrer, puis sélectionnez Enregistrer la requête. La boîte de dialogue Enregistrer la requête s'ouvre.
- Dans le champ Nom, saisissez
sp_create_load_tables.
- Pour Région, sélectionnez .
- Cliquez sur Enregistrer. Cette procédure stockée s'affichera dans la section Routines de l'ensemble de données :
- Cliquez sur Exécuter pour exécuter cette procédure stockée.
Comme indiqué à l'étape 5 de la tâche 3 ci-dessus, exécutez l'instruction SELECT SQL pour obtenir la distance entre chaque client et son centre de distribution le plus proche.
- Copiez la sous-requête scalaire suivante dans l'éditeur de requête :
SELECT
customers.id as customer_id,
(
SELECT
MIN(ST_DISTANCE(centers.point_location, customers.point_location))/1000,
FROM
`thelook_ecommerce.centers` AS centers) AS distance_to_closest_center
FROM
`thelook_ecommerce.customers` AS customers ;
Cette requête calcule la distance en kilomètres.
- Cliquez sur Exécuter.

Cliquez sur Vérifier ma progression pour vérifier que vous avez correctement accompli cette tâche.
Créer une procédure stockée pour faciliter les mises à jour
Remarque : Vous n'exécuterez pas de requête planifiée dans cet atelier, mais il est important de comprendre les nombreuses façons de mettre à jour les données et de gérer vos pipelines SQL.
Pour configurer une requête programmée, il vous suffit de cliquer sur Programmer dans l'éditeur de requête. La fenêtre "Nouvelle requête programmée" s'ouvre, avec plusieurs détails de requête à spécifier. Vous pouvez le faire avec n'importe quelle requête qui doit être mise à jour régulièrement.
Conclusion
Bravo !
Vous avez utilisé SQL et BigQuery pour créer un pipeline de données simple et flexible qui répond à un besoin métier.
Vous avez d'abord créé un ensemble de données et défini des schémas de table pour les données à ingérer. Ensuite, vous avez effectué et exploré une série de transformations.
Vous avez ensuite appliqué les transformations aux données avant de les charger dans les tables nouvellement définies.
Enfin, vous avez formalisé ces requêtes dans une procédure stockée pour inclure les valeurs calculées qui vous intéressent, et vous avez appris à configurer des requêtes programmées pour obtenir des mises à jour régulières.
Dans cet atelier, vous avez appris l'importance de créer des ensembles de données et de définir des schémas de table pour les données qu'ils contiennent. En exécutant et en examinant une série de transformations, vous avez acquis les compétences nécessaires pour appliquer des transformations aux données avant de les charger dans des tables nouvellement définies. Vous avez également appris à formaliser ces requêtes dans une procédure stockée pour inclure les valeurs calculées qui vous intéressent, et à configurer des requêtes programmées pour obtenir des mises à jour régulières.
Vous êtes en passe de devenir expert dans la création et la gestion de pipelines SQL avec BigQuery.
Terminer l'atelier
Avant de terminer l'atelier, assurez-vous d'avoir bien accompli toutes les tâches. Cliquez alors sur Terminer l'atelier, puis sur Envoyer.
Une fois l'atelier terminé, vous n'aurez plus accès à l'environnement de l'atelier ni au travail que vous avez effectué.
Copyright 2026 Google LLC Tous droits réservés. Google et le logo Google sont des marques de Google LLC. Tous les autres noms de société et de produit peuvent être des marques des sociétés auxquelles ils sont associés.