Présentation

Dans cet atelier, vous allez apprendre à importer des données dans Cloud SQL à partir de fichiers texte CSV, puis à effectuer certaines analyses de données de base à l'aide de requêtes simples.

L'ensemble de données utilisé dans cet atelier provient de la NYC Taxi and Limousine Commission (Commission des taxis et limousines de la ville de New York). Ces données contiennent les enregistrements de toutes les courses des taxis jaunes et verts de la ville de New York depuis 2009, et de tous les trajets des véhicules de transport avec chauffeur depuis 2015. Ces enregistrements comprennent des champs permettant de saisir les dates/heures et lieux de prise en charge et de dépose des clients, les distances des trajets, les tarifs détaillés, les types de tarifs, les types de paiements et le nombre de passagers déclarés par le chauffeur.

Plusieurs ateliers du programme Data Engineering utilisent cet ensemble de données, qui permet d'illustrer un large éventail de concepts et de techniques de la science des données.

Objectifs

• Créer une instance Cloud SQL
• Créer une base de données Cloud SQL
• Importer des données textuelles dans Cloud SQL
• Vérifier l'intégrité des données

Préparation

Pour chaque atelier, nous vous attribuons un nouveau projet Google Cloud et un nouvel ensemble de ressources pour une durée déterminée, sans frais.

1. Connectez-vous à Qwiklabs dans une fenêtre de navigation privée.

2. Vérifiez le temps imparti pour l'atelier (par exemple : 01:15:00) : vous devez pouvoir le terminer dans ce délai.
   Une fois l'atelier lancé, vous ne pouvez pas le mettre en pause. Si nécessaire, vous pourrez le redémarrer, mais vous devrez tout reprendre depuis le début.

3. Lorsque vous êtes prêt, cliquez sur Démarrer l'atelier.

4. Notez vos identifiants pour l'atelier (Nom d'utilisateur et Mot de passe). Ils vous serviront à vous connecter à Google Cloud Console.

5. Cliquez sur Ouvrir la console Google.

6. Cliquez sur Utiliser un autre compte, puis copiez-collez les identifiants de cet atelier lorsque vous y êtes invité.
   Si vous utilisez d'autres identifiants, des messages d'erreur s'afficheront ou des frais seront appliqués.

7. Acceptez les conditions d'utilisation et ignorez la page concernant les ressources de récupération des données.

Activer Google Cloud Shell

Google Cloud Shell est une machine virtuelle qui contient de nombreux outils pour les développeurs. Elle comprend un répertoire d'accueil persistant de 5 Go et s'exécute sur Google Cloud.

Google Cloud Shell vous permet d'accéder à vos ressources Google Cloud grâce à une ligne de commande.

1. Dans la barre d'outils située en haut à droite dans la console Cloud, cliquez sur le bouton "Ouvrir Cloud Shell".

   

2. Cliquez sur Continuer.

Le provisionnement et la connexion à l'environnement prennent quelques instants. Une fois connecté, vous êtes en principe authentifié et le projet est défini sur votre ID_PROJET. Par exemple :

gcloud est l'outil de ligne de commande pour Google Cloud. Il est préinstallé sur Cloud Shell et permet la complétion par tabulation.

• Vous pouvez lister les noms des comptes actifs à l'aide de cette commande :

gcloud auth list

Résultat :

Credentialed accounts: - @.com (active)

Exemple de résultat :

Credentialed accounts: - google1623327_student@qwiklabs.net

• Vous pouvez lister les ID de projet à l'aide de cette commande :

gcloud config list project

Résultat :

[core] project =

Exemple de résultat :

[core] project = qwiklabs-gcp-44776a13dea667a6 Remarque : Pour consulter la documentation complète sur gcloud, accédez au guide de présentation de la gcloud CLI.

Tâche 1 : Préparer l'environnement

• Pour la suite de l'atelier, créez les variables d'environnement correspondant à l'ID du projet et au bucket de stockage qui contiendra vos données :

export PROJECT_ID=$(gcloud info --format='value(config.project)') export BUCKET=${PROJECT_ID}-ml

Tâche 2 : Créer une instance Cloud SQL

1. Saisissez les commandes suivantes pour créer une instance Cloud SQL :

gcloud sql instances create taxi \ --tier=db-n1-standard-1 --activation-policy=ALWAYS

Cette opération prend quelques minutes.

Tester la tâche terminée

Cliquez sur Vérifier ma progression pour valider la tâche exécutée. Si votre tâche a bien été exécutée, vous recevez une note d'évaluation.

Créer une instance Cloud SQL

2. Définissez un mot de passe racine pour l'instance Cloud SQL :

gcloud sql users set-password root --host % --instance taxi \ --password Passw0rd

3. Lorsque vous êtes invité à saisir le mot de passe, indiquez Passw0rd et appuyez sur "Entrée" pour mettre à jour le mot de passe racine.

4. Ensuite, créez une variable d'environnement avec l'adresse IP de Cloud Shell :

export ADDRESS=$(wget -qO - http://ipecho.net/plain)/32

5. Ajoutez l'instance Cloud Shell à la liste blanche pour l'autoriser à gérer votre instance SQL :

gcloud sql instances patch taxi --authorized-networks $ADDRESS

6. Lorsque vous y êtes invité, appuyez sur Y pour accepter la modification.

Tester la tâche terminée

Cliquez sur Vérifier ma progression pour valider la tâche exécutée. Si votre tâche a bien été exécutée, vous recevez une note d'évaluation.

Ajouter l'instance Cloud Shell à la liste blanche pour l'autoriser à accéder à votre instance SQL

7. Pour obtenir l'adresse IP de votre instance Cloud SQL, exécutez la commande suivante :

MYSQLIP=$(gcloud sql instances describe \ taxi --format="value(ipAddresses.ipAddress)")

8. Vérifiez la variable MYSQLIP :

echo $MYSQLIP

Vous devriez obtenir une adresse IP en tant que sortie.

9. Connectez-vous à l'interface de ligne de commande mysql pour créer la table des courses en taxi :

mysql --host=$MYSQLIP --user=root \ --password --verbose

10. Lorsque vous êtes invité à entrer un mot de passe, saisissez Passw0rd.

11. Collez le contenu suivant dans la ligne de commande pour créer le schéma de la table trips :

create database if not exists bts; use bts; drop table if exists trips; create table trips ( vendor_id VARCHAR(16), pickup_datetime DATETIME, dropoff_datetime DATETIME, passenger_count INT, trip_distance FLOAT, rate_code VARCHAR(16), store_and_fwd_flag VARCHAR(16), payment_type VARCHAR(16), fare_amount FLOAT, extra FLOAT, mta_tax FLOAT, tip_amount FLOAT, tolls_amount FLOAT, imp_surcharge FLOAT, total_amount FLOAT, pickup_location_id VARCHAR(16), dropoff_location_id VARCHAR(16) );

Tester la tâche terminée

Cliquez sur Vérifier ma progression pour valider la tâche exécutée. Si votre tâche a bien été exécutée, vous recevez une note d'évaluation.

Créer une base de données "bts" et une table "trips"

12. Dans l'interface de ligne de commande mysql, vérifiez l'importation en saisissant les commandes suivantes :

describe trips;

13. Interrogez la table trips :

select distinct(pickup_location_id) from trips;

La requête renvoie un ensemble vide puisque la base de données ne contient aucune donnée pour l'instant.

14. Quittez la console interactive mysql :

exit

Tâche 3 : Ajouter des données à l'instance Cloud SQL

Copiez maintenant sur votre machine locale les fichiers CSV des courses des taxis de la ville de New York stockés dans Cloud Storage. Pour limiter l'utilisation des ressources, vous travaillerez uniquement avec un sous-ensemble de données (environ 20 000 lignes).

1. Exécutez la commande suivante dans la ligne de commande :

gcloud storage cp gs://cloud-training/OCBL013/nyc_tlc_yellow_trips_2018_subset_1.csv trips.csv-1 gcloud storage cp gs://cloud-training/OCBL013/nyc_tlc_yellow_trips_2018_subset_2.csv trips.csv-2

2. Connectez-vous à la console interactive mysql pour charger les données des fichiers INFILE locaux :

mysql --host=$MYSQLIP --user=root --password --local-infile

3. Lorsque vous êtes invité à entrer un mot de passe, saisissez Passw0rd.

4. Dans la console interactive mysql, sélectionnez la base de données :

use bts;

5. Chargez les données du fichier CSV local en utilisant local-infile :

LOAD DATA LOCAL INFILE 'trips.csv-1' INTO TABLE trips FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES (vendor_id,pickup_datetime,dropoff_datetime,passenger_count,trip_distance,rate_code,store_and_fwd_flag,payment_type,fare_amount,extra,mta_tax,tip_amount,tolls_amount,imp_surcharge,total_amount,pickup_location_id,dropoff_location_id); LOAD DATA LOCAL INFILE 'trips.csv-2' INTO TABLE trips FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES (vendor_id,pickup_datetime,dropoff_datetime,passenger_count,trip_distance,rate_code,store_and_fwd_flag,payment_type,fare_amount,extra,mta_tax,tip_amount,tolls_amount,imp_surcharge,total_amount,pickup_location_id,dropoff_location_id);

Tâche 4 : Vérifier l'intégrité des données

Il est toujours important de vérifier l'intégrité des données chaque fois que vous en importez à partir d'une source. Il s'agit grosso modo de s'assurer que les données répondent à vos attentes.

1. Interrogez la table trips pour connaître les zones où se trouvent les lieux de prise en charge :

select distinct(pickup_location_id) from trips;

Cette commande doit renvoyer 159 identifiants uniques.

2. Commençons par examiner la colonne trip_distance. Saisissez la requête suivante dans la console :

select max(trip_distance), min(trip_distance) from trips;

On peut raisonnablement supposer que la distance d'un trajet est comprise entre 0 et 1 000 miles. Une distance maximale de trajet de 85 miles semble raisonnable, tandis qu'une distance minimale de trajet de 0 mile semble absurde.

3. Combien de trajets d'une distance de 0 mile y a-t-il dans l'ensemble de données ?

select count(*) from trips where trip_distance = 0;

Il y en a 155. Ces trajets nécessitent qu'on les examine de plus près. Vous constaterez que ces courses sont associées à des paiements de montants non nuls. S'agit-il de transactions frauduleuses ?

4. Recherchons d'autres données qui ne nous semblent pas logiques. La colonne fare_amount indiquant le tarif devrait logiquement être positive. Saisissez la requête suivante et vérifiez si c'est le cas pour cette base de données :

select count(*) from trips where fare_amount < 0;

Le résultat doit afficher 14 courses. Ces courses nécessitent également qu'on les examine de plus près. Il peut y avoir une explication logique au fait que le tarif de ces courses soit négatif. L'ingénieur de données doit s'assurer que ce résultat n'est pas dû à un bug dans le pipeline de données.

5. Enfin, analysons la colonne payment_type.

select payment_type, count(*) from trips group by payment_type;

Les résultats de cette requête indiquent quatre types de paiements :

• Type de paiement = 1 dans 13 863 lignes
• Type de paiement = 2 dans 6 016 lignes
• Type de paiement = 3 dans 113 lignes
• Type de paiement = 4 dans 32 lignes

Des recherches dans la documentation révèlent que le type de paiement 1 correspond à la carte de crédit, le type de paiement 2 correspond aux espèces et le type de paiement 4 correspond à une contestation. Les chiffres sont cohérents.

6. Quittez la console interactive "mysql" :

exit

Terminer l'atelier

Une fois l'atelier terminé, cliquez sur Terminer l'atelier. Google Cloud Skills Boost supprime les ressources que vous avez utilisées, puis efface le compte.

Si vous le souhaitez, vous pouvez noter l'atelier. Sélectionnez un nombre d'étoiles, saisissez un commentaire, puis cliquez sur Envoyer.

Le nombre d'étoiles correspond à votre degré de satisfaction :

• 1 étoile = très insatisfait(e)
• 2 étoiles = insatisfait(e)
• 3 étoiles = ni insatisfait(e), ni satisfait(e)
• 4 étoiles = satisfait(e)
• 5 étoiles = très satisfait(e)

Si vous ne souhaitez pas donner votre avis, vous pouvez fermer la boîte de dialogue.

Pour soumettre des commentaires, suggestions ou corrections, veuillez accéder à l'onglet Assistance.

Copyright 2020 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.