GSP009

Présentation

Dans cet atelier, vous allez analyser des observations météorologiques historiques de la NOAA à l'aide de BigQuery, puis les mettre en corrélation avec les réclamations des habitants de la ville de New York. Cette activité est semblable à l'article de blog de Reto Meier, qui montre comment les variables de données peuvent être corrélées les unes aux autres.

Vous allez découvrir plusieurs aspects de Google Cloud 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).
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 des habitants sont liés à 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.

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 ;
• utiliser la fonction CORR pour déterminer si la corrélation entre les ensembles de données est positive, négative ou nulle.

Prérequis

Cet atelier s'adresse aux débutants et nécessite une certaine expérience de BigQuery et de SQL. Si vous n'avez jamais utilisé BigQuery ou MySQL, l'atelier BigQuery : Qwik Start – Console peut vous aider à vous familiariser avec ces services Google Cloud.

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 les activités dans un véritable environnement cloud, et non dans un environnement de simulation ou de démonstration. Des identifiants temporaires vous sont fournis pour vous permettre de 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 (recommandé) ou de 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 : Utilisez uniquement le compte de participant pour cet atelier. Si vous utilisez un autre compte Google Cloud, des frais peuvent être facturés à ce compte.

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, une boîte de dialogue 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 accéder aux produits et services Google Cloud, cliquez sur le menu de navigation ou saisissez le nom du service ou du produit dans le champ Recherche.

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 nom l'ID du projet de l'atelier, et l'autre bigquery-public-data.

6. Dans le volet Explorateur de la console BigQuery, développez l'ensemble de données bigquery-public-data. Dans le champ Saisissez un terme à rechercher, recherchez noaa_gsod et sélectionnez la table gsod2014.

7. Dans la fenêtre de la table gsod2014, cliquez sur l'onglet Aperçu.

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

9. Cliquez sur Requête > Dans un nouvel onglet, puis collez la requête suivante :

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 valider 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 récemment ajouté. Dans le champ Saisissez un terme à rechercher, recherchez l'ensemble de données new_york_311, 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 + (Créer une requête SQL).

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 valider 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 nommé demos.

4. Cliquez sur l'icône + (Créer une requête SQL), 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, pour l'option "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 les paramètres de la requête.

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

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

Tâche 4 : Trouver une corrélation entre des ensembles de données

Une forte corrélation, mesurée par la fonction CORR, indique une relation étroite et régulière entre deux variables. Lorsque la valeur d'une variable augmente, la valeur de l'autre variable a tendance à augmenter (corrélation positive) ou à diminuer (corrélation négative) de manière prévisible. Une forte corrélation est généralement considérée comme une valeur supérieure ou égale à 0,7 (en termes absolus). Cela signifie que l'évolution d'une variable peut expliquer au moins 49 % de l'évolution de l'autre variable.

Vous allez maintenant comparer le nombre de réclamations reçues et la température quotidienne à l'aide de la fonction CORR.

1. Créez une requête SQL en cliquant sur l'icône +, puis 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 + (Créer une requête SQL), 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 ?

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

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

Félicitations !

Dans cet atelier, 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.

Étapes suivantes et informations supplémentaires

• En savoir plus sur la fonction CORR() dans BigQuery

• En savoir plus sur 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 : 20 mars 2025

Dernier test de l'atelier : 20 mars 2025

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