GSP009

Aperçu

Dans cet atelier, vous allez analyser un historique d'observations météorologiques à l'aide de BigQuery et mettre des données météorologiques en relation avec d'autres ensembles de données.

Points abordés

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

Introduction

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 des réclamations déposées par les habitants de la ville de New York.

Vous allez découvrir plusieurs aspects de Google Cloud qui sont particulièrement avantageux 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.

Au final, vous saurez quels types de réclamations municipales sont en lien avec la météo. Par exemple, vous verrez (sans surprise) que les réclamations concernant les chaudières domestiques sont plus courantes par temps froid :

Préparation

Cet atelier s'adresse aux utilisateurs de niveau intermédiaire et suppose une certaine expérience de BigQuery et de SQL. Si vous n'avez jamais utilisé BigQuery ou MySQL, l'atelier d'auto-formation BigQuery : Qwik Start – Console peut vous aider à être rapidement opérationnel avec ces services Google Cloud.

Avant de commencer l'atelier

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 : Explorer les données météorologiques

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.

3. Dans le volet Explorateur, cliquez sur + AJOUTER.

La fenêtre "Ajouter des données" s'affiche.

4. Sous "Sources supplémentaires", cliquez sur Ajouter un projet aux favoris en saisissant son nom.

5. Saisissez bigquery-public-data et cliquez sur Ajouter aux favoris.

Dans la console BigQuery, deux projets sont affichés dans le volet "Explorateur" : l'un a comme intitulé l'ID de projet de votre atelier, et l'autre bigquery-public-data.

6. Dans le volet Explorateur de la console BigQuery, développez bigquery-public-data > noaa_gsod et sélectionnez la table gsod2014.

7. Dans la fenêtre "Table" (gsod2014), cliquez sur l'onglet Aperçu.

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

9. Collez la requête suivante dans l'ÉDITEUR de requête :

SELECT -- Create a timestamp from the date components. stn, TIMESTAMP(CONCAT(year,"-",mo,"-",da)) AS timestamp, -- Replace numerical null values with actual null AVG(IF (temp=9999.9, null, temp)) AS temperature, AVG(IF (wdsp="999.9", null, CAST(wdsp AS Float64))) AS wind_speed, AVG(IF (prcp=99.99, 0, prcp)) AS precipitation FROM `bigquery-public-data.noaa_gsod.gsod20*` WHERE CAST(YEAR AS INT64) > 2010 AND CAST(MO AS INT64) = 6 AND CAST(DA AS INT64) = 12 AND (stn="725030" OR -- La Guardia stn="744860") -- JFK GROUP BY stn, timestamp ORDER BY timestamp DESC, stn ASC

10. Cliquez sur EXÉCUTER. Observez le résultat et essayez de déterminer les actions effectuées par cette requête.

Cliquez sur Vérifier ma progression ci-dessous pour vérifier votre progression dans l'atelier.

Explorer les données météorologiques

Tâche 2 : Explorer les données des réclamations déposées par les New-Yorkais

1. Dans le volet "Explorateur" de la console BigQuery, sélectionnez le projet bigquery-public-data qui vient d'être ajouté, développez l'ensemble de données new_york, puis sélectionnez la table 311_service_requests.

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

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

4. Si l'éditeur a été fermé, cliquez sur l'icône + (Saisir une nouvelle requête).

5. Collez la requête suivante dans l'ÉDITEUR de requête :

SELECT EXTRACT(YEAR FROM created_date) AS year, complaint_type, COUNT(1) AS num_complaints FROM `bigquery-public-data.new_york.311_service_requests` GROUP BY year, complaint_type ORDER BY num_complaints DESC

6. Cliquez sur EXÉCUTER.

7. Observez le résultat et déterminez quelles sont les réclamations les plus courantes. Un peu plus tard dans cet atelier, nous essaierons de voir si ces réclamations ont un rapport avec la météo.

Cliquez sur Vérifier ma progression ci-dessous pour vérifier votre progression dans l'atelier.

Explorer les données des réclamations déposées par les New-Yorkais

Tâche 3 : Enregistrer une nouvelle table de données météorologiques

1. Dans le volet "Explorateur" de la console BigQuery, cliquez sur les trois points à côté de l'ID de votre projet, puis sélectionnez Créer un ensemble de données.

2. Dans la boîte de dialogue "Créer un ensemble de données", définissez le champ ID de l'ensemble de données sur demos et conservez la valeur par défaut de toutes les autres options.

3. Cliquez sur Créer un ensemble de données. Votre projet comprend maintenant un ensemble de données intitulé demos.

4. Cliquez sur l'icône + (Saisir une nouvelle requête), puis exécutez la requête suivante :

SELECT -- Create a timestamp from the date components. timestamp(concat(year,"-",mo,"-",da)) as timestamp, -- Replace numerical null values with actual nulls AVG(IF (temp=9999.9, null, temp)) AS temperature, AVG(IF (visib=999.9, null, visib)) AS visibility, AVG(IF (wdsp="999.9", null, CAST(wdsp AS Float64))) AS wind_speed, AVG(IF (gust=999.9, null, gust)) AS wind_gust, AVG(IF (prcp=99.99, null, prcp)) AS precipitation, AVG(IF (sndp=999.9, null, sndp)) AS snow_depth FROM `bigquery-public-data.noaa_gsod.gsod20*` WHERE CAST(YEAR AS INT64) > 2008 AND (stn="725030" OR -- La Guardia stn="744860") -- JFK GROUP BY timestamp

5. Dans l'ÉDITEUR de requête, cliquez sur Plus > Paramètres de requête.

6. Dans la boîte de dialogue "Paramètres de requête", définissez les champs suivants. Conservez les valeurs par défaut pour les autres champs.

Destination : sélectionnez Définir une table de destination pour les résultats de la requête.

Ensemble de données : saisissez demos et sélectionnez votre ensemble de données.

ID de la table : saisissez nyc_weather.

Taille des résultats : cochez la case Autoriser un nombre élevé de résultats (aucune limite).

7. Cliquez sur ENREGISTRER.

8. Cliquez sur EXÉCUTER.

Les résultats sont désormais enregistrés dans l'ensemble de données que vous venez de créer (demos).

9. Revenez à Plus > Paramètres de requête et, dans le champ Destination, sélectionnez Enregistrer les résultats de la requête dans une table temporaire. Cette opération supprime l'ensemble de données "demos" en tant que destination pour les futures requêtes.

10. Cliquez sur ENREGISTRER pour fermer la requête.

Cliquez sur Vérifier ma progression ci-dessous pour vérifier votre progression dans l'atelier.

Enregistrer une nouvelle table de données météorologiques

Tâche 4 : Établir des corrélations entre la météo et les réclamations

Comparez le nombre de réclamations et la température à l'aide de la fonction CORR.

1. Revenez à l'ÉDITEUR de requête et exécutez la requête suivante :

SELECT descriptor, sum(complaint_count) as total_complaint_count, count(temperature) as data_count, ROUND(corr(temperature, avg_count),3) AS corr_count, ROUND(corr(temperature, avg_pct_count),3) AS corr_pct From ( SELECT avg(pct_count) as avg_pct_count, avg(day_count) as avg_count, sum(day_count) as complaint_count, descriptor, temperature FROM ( SELECT DATE(timestamp) AS date, temperature FROM demos.nyc_weather) a JOIN ( SELECT x.date, descriptor, day_count, day_count / all_calls_count as pct_count FROM (SELECT DATE(created_date) AS date, concat(complaint_type, ": ", descriptor) as descriptor, COUNT(*) AS day_count FROM `bigquery-public-data.new_york.311_service_requests` GROUP BY date, descriptor)x JOIN ( SELECT DATE(timestamp) AS date, COUNT(*) AS all_calls_count FROM `demos.nyc_weather` GROUP BY date )y ON x.date=y.date )b ON a.date = b.date GROUP BY descriptor, temperature ) GROUP BY descriptor HAVING total_complaint_count > 5000 AND ABS(corr_pct) > 0.5 AND data_count > 5 ORDER BY ABS(corr_pct) DESC

Les résultats montrent une corrélation négative entre les réclamations au sujet du chauffage et la température (c'est-à-dire que les appels concernant les problèmes de chauffage sont plus fréquents par temps froid). Inversement, il existe une corrélation positive entre les réclamations au sujet d'arbres morts et la température (ces appels sont plus fréquents par temps chaud).

Ensuite, comparez le nombre de réclamations et la vitesse du vent à l'aide de la fonction CORR.

2. Cliquez sur l'icône + (Saisir une nouvelle requête), puis exécutez la requête suivante :

SELECT descriptor, sum(complaint_count) as total_complaint_count, count(wind_speed) as data_count, ROUND(corr(wind_speed, avg_count),3) AS corr_count, ROUND(corr(wind_speed, avg_pct_count),3) AS corr_pct From ( SELECT avg(pct_count) as avg_pct_count, avg(day_count) as avg_count, sum(day_count) as complaint_count, descriptor, wind_speed FROM ( SELECT DATE(timestamp) AS date, wind_speed FROM demos.nyc_weather) a JOIN ( SELECT x.date, descriptor, day_count, day_count / all_calls_count as pct_count FROM (SELECT DATE(created_date) AS date, concat(complaint_type, ": ", descriptor) as descriptor, COUNT(*) AS day_count FROM `bigquery-public-data.new_york.311_service_requests` GROUP BY date, descriptor)x JOIN ( SELECT DATE(timestamp) AS date, COUNT(*) AS all_calls_count FROM `demos.nyc_weather` GROUP BY date )y ON x.date=y.date )b ON a.date = b.date GROUP BY descriptor, wind_speed ) GROUP BY descriptor HAVING total_complaint_count > 5000 AND ABS(corr_pct) > 0.5 AND data_count > 5 ORDER BY ABS(corr_pct) DESC

3. Vous pouvez remarquer que les colonnes "Corr" portant sur les réclamations liées au bruit sont toutes deux négatives. Il ne vous reste qu'à élaborer une hypothèse expliquant pourquoi les réclamations qui touchent aux nuisances sonores sont moins fréquentes lorsque le vent souffle. Les coefficients sont-ils statistiquement suffisants ?

Comme vous pouvez le voir, BigQuery permet de présenter un grand nombre de problèmes sous différents angles.

Cliquez sur Vérifier ma progression ci-dessous pour vérifier votre progression dans l'atelier.

Établir des corrélations entre la météo et les réclamations

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 votre quête

Cet atelier d'auto-formation fait partie de la quête Scientific Data Processing. Une quête est une série d'ateliers associés qui constituent un parcours de formation. Si vous terminez cette quête, vous obtiendrez le badge ci-dessus attestant de votre réussite. Vous pouvez rendre publics les badges que vous recevez et ajouter leur lien dans votre CV en ligne ou sur vos comptes de réseaux sociaux. Inscrivez-vous à cette quête pour obtenir immédiatement les crédits associés à cet atelier si vous l'avez suivi. Découvrez les autres quêtes disponibles.

Atelier suivant

Continuez sur votre lancée en suivant l'atelier Traitement des images distribué dans Cloud Dataproc, ou consultez nos suggestions de ressources :

• Prédire le poids d'un bébé à l'aide de TensorFlow sur Vertex AI
• Analyser les données relatives à la natalité à l'aide de Vertex AI et BigQuery

Étapes suivantes et informations supplémentaires

• Si vous souhaitez découvrir davantage d'analyses étonnantes sur les données de la ville de New York et leur rapport avec la météo, consultez l'article de blog publié par Reto Meier.

• Découvrez les ensembles de données publics 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 : 27 juin 2023

Dernier test de l'atelier : 27 juin 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.