Aperçu

Dans cet atelier, vous allez analyser deux ensembles de données publics différents. Vous les interrogerez séparément, puis simultanément, pour en tirer des informations intéressantes.

Points abordés

Au cours de cet atelier, vous allez :

• lancer des requêtes interactives dans la console BigQuery ;
• combiner et exécuter des analyses sur plusieurs ensembles de données.

Prérequis

Cet atelier s'adresse aux utilisateurs de niveau intermédiaire et suppose une certaine expérience de BigQuery et de SQL.

Présentation

Cet atelier s'articule autour de deux ensembles de données publics dans BigQuery : les données météorologiques de l'Agence américaine d'observation océanique et atmosphérique, et les données sur les locations de vélos dans la ville de New York.

Vous allez découvrir plusieurs aspects de Google Cloud Platform qui sont particulièrement utiles pour les scientifiques :

1. Sans serveur : vous n'avez pas besoin de télécharger les données sur votre ordinateur pour travailler avec. L'ensemble de données reste stocké dans le cloud.
2. Simplicité d'utilisation : vous pouvez exécuter des requêtes SQL ad hoc sur votre ensemble de données sans avoir à préparer les données au préalable (par exemple, la création d'index est inutile). C'est un avantage précieux pour l'exploration de données.
3. Évolutivité : vous pouvez explorer de très grands ensembles de données de façon interactive. Vous n'avez pas besoin d'échantillonner les données pour travailler avec en temps opportun.
4. Partage : vous pouvez interroger les données issues de plusieurs ensembles de données sans aucun problème. BigQuery est un outil pratique pour partager des ensembles de données. Bien entendu, vous pouvez garder vos données confidentielles ou les partager uniquement avec certaines personnes. Il n'est pas question de rendre toutes les données publiques.

En définitive, vous pourrez déterminer si le nombre de locations de vélos diminue les jours de pluie.

Préparation

Mettre en place l'atelier

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. 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 sans frais.

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, ou saisissez le nom du service ou du produit dans le champ Recherche.

Tâche 1 : Explorer les données concernant les locations de vélos

Dans cette tâche, vous allez explorer l'ensemble de données public bigquery-public-data, plus précisément l'ensemble de données new_york_citibike, en examinant le schéma de la table citibike_trips et en exécutant des requêtes pour analyser la durée des trajets et calculer la distance totale parcourue avec chaque vélo.

1. Dans la console Google Cloud, accédez au menu de navigation (), puis cliquez sur BigQuery.

Le message "Bienvenue sur BigQuery dans la console Cloud" s'affiche. Il contient un lien vers le guide de démarrage rapide et liste les mises à jour de l'interface utilisateur.

2. Cliquez sur OK.

3. Dans le volet de gauche, cliquez sur + Ajouter, puis sur Ajouter un projet aux favoris en saisissant son nom. Ensuite, saisissez bigquery-public-data dans la fenêtre pop-up et cliquez sur Ajouter aux favoris.

4. Dans la console BigQuery, deux projets sont affichés dans le volet de gauche : l'un a comme intitulé l'ID du projet Qwiklabs, et l'autre bigquery-public-data.

5. Dans le volet gauche de la console BigQuery, sélectionnez bigquery-public-data > new_york_citibike > table citibike_trips.

6. Dans la fenêtre de la table (citibike_trips), cliquez sur l'onglet Schéma.

7. Examinez les noms des colonnes et les types de données.

8. Cliquez sur le bouton + bleu pour rédiger une nouvelle requête.

Saisissez la requête suivante :

SELECT MIN(start_station_name) AS start_station_name, MIN(end_station_name) AS end_station_name, APPROX_QUANTILES(tripduration, 10)[OFFSET (5)] AS typical_duration, COUNT(tripduration) AS num_trips FROM `bigquery-public-data.new_york_citibike.citibike_trips` WHERE start_station_id != end_station_id GROUP BY start_station_id, end_station_id ORDER BY num_trips DESC LIMIT 10

9. Cliquez sur Exécuter. Analysez le résultat et essayez de déterminer les actions effectuées par cette requête.

   Indice : Durée moyenne des 10 locations en aller simple les plus courantes

10. Ensuite, exécutez la requête ci-dessous pour trouver une autre information intéressante : la distance totale parcourue avec chaque vélo de l'ensemble de données. Notez que la requête limite les résultats aux cinq valeurs les plus élevées.

WITH trip_distance AS ( SELECT bikeid, ST_Distance(ST_GeogPoint(s.longitude, s.latitude), ST_GeogPoint(e.longitude, e.latitude)) AS distance FROM `bigquery-public-data.new_york_citibike.citibike_trips`, `bigquery-public-data.new_york_citibike.citibike_stations` as s, `bigquery-public-data.new_york_citibike.citibike_stations` as e WHERE start_station_name = s.name AND end_station_name = e.name) SELECT bikeid, SUM(distance)/1000 AS total_distance FROM trip_distance GROUP BY bikeid ORDER BY total_distance DESC LIMIT 5 Remarque : Pour cette requête, nous avons également récupéré des données sur les stations de vélos de l'autre table de l'ensemble de données (citibike_stations).

Tâche 2 : Explorer l'ensemble de données météorologiques

Dans cette tâche, vous allez explorer l'ensemble de données ghcn_d en prévisualisant la table ghcnd_2015 et en exécutant une requête pour récupérer les données de précipitations d'une station météorologique de New York.

1. Dans le volet de gauche de la console BigQuery, sélectionnez le projet bigquery-public-data récemment ajouté, puis sélectionnez ghcn_d > ghcnd_2015.

2. Cliquez ensuite sur l'onglet Aperçu. La console doit se présenter comme suit :

Passez les colonnes en revue, ainsi que certaines valeurs des données.

3. Cliquez sur le bouton + bleu pour rédiger une nouvelle requête et saisissez ce qui suit :

SELECT wx.date, wx.value/10.0 AS prcp FROM `bigquery-public-data.ghcn_d.ghcnd_2015` AS wx WHERE id = 'USW00094728' AND qflag IS NULL AND element = 'PRCP' ORDER BY wx.date

4. Cliquez sur Exécuter.

Cette requête va renvoyer la quantité de précipitations (en mm) de chaque jour de l'année 2015. Ces données proviennent de la station météorologique de New York dont l'identifiant est fourni dans la requête (station correspondant à NEW YORK CNTRL PK TWR).

Tâche 3 : Établir des corrélations entre les précipitations et les locations de vélos

Dans cette tâche, vous allez analyser la corrélation entre les précipitations et les locations de vélos en joignant l'ensemble de données new_york_citibike.citibike_trips à l'ensemble de données météorologiques ghcn_d.ghcnd_2015. Vous déterminerez ainsi le nombre moyen de trajets à vélo les jours de pluie et les jours sans pluie.

Essayons de comparer les données sur les locations de vélos aux données météorologiques pour savoir s'il y a moins de locations de vélos les jours de pluie.

1. Cliquez sur le bouton + bleu pour rédiger une nouvelle requête et saisissez ce qui suit :

WITH bicycle_rentals AS ( SELECT COUNT(starttime) as num_trips, EXTRACT(DATE from starttime) as trip_date FROM `bigquery-public-data.new_york_citibike.citibike_trips` GROUP BY trip_date ), rainy_days AS ( SELECT date, (MAX(prcp) > 5) AS rainy FROM ( SELECT wx.date AS date, IF (wx.element = 'PRCP', wx.value/10, NULL) AS prcp FROM `bigquery-public-data.ghcn_d.ghcnd_2015` AS wx WHERE wx.id = 'USW00094728' ) GROUP BY date ) SELECT ROUND(AVG(bk.num_trips)) AS num_trips, wx.rainy FROM bicycle_rentals AS bk JOIN rainy_days AS wx ON wx.date = bk.trip_date GROUP BY wx.rainy

2. Cliquez sur Exécuter.

Vous obtenez les résultats de la fusion d'un ensemble de données sur la location de vélos et d'un ensemble de données météorologiques provenant d'une source complètement différente :

Cette requête permet d'arriver à la conclusion qu'il y a 47 % de locations de vélo en moins à New York par temps de pluie.

Résumé

Dans cet atelier, vous avez lancé des requêtes ad hoc sur deux ensembles de données. Vous avez réussi à interroger les données sans passer par la configuration de clusters, la création d'index, etc. Vous avez également réussi à mettre en corrélation deux ensembles de données afin de dégager des tendances intéressantes. Le tout, sans quitter votre navigateur !

Félicitations !

Vous avez appris à exécuter des requêtes très intéressantes sur BigQuery.

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.