Présentation

BigQuery est la base de données d'analyse NoOps, économique et entièrement gérée de Google. Avec BigQuery, vous pouvez interroger plusieurs téraoctets de données sans avoir à gérer d'infrastructure ni faire appel à un administrateur de base de données. Basé sur le langage SQL et le modèle de paiement à l'usage, BigQuery vous permet de vous concentrer sur l'analyse des données pour en dégager des informations pertinentes.

Cet atelier va vous apprendre à concevoir un entrepôt de données de façon à optimiser les performances des requêtes. Vous comparerez un schéma relationnel traditionnel à jointures avec un schéma dénormalisé. Vous utiliserez également le plan d'exécution des requêtes de BigQuery pour évaluer de manière quantifiable les compromis à faire en termes de performances.

Objectifs de l'atelier

Dans cet atelier, vous apprendrez à effectuer les opérations suivantes :

• Charger un fichier CSV (valeurs séparées par des virgules) dans une table BigQuery à l'aide de l'interface utilisateur Web
• Charger un fichier JSON (JavaScript® Object Notation) dans une table BigQuery à l'aide de l'interface de ligne de commande (CLI)
• Transformer les données et effectuer des jointures de tables à l'aide de l'interface utilisateur Web
• Stocker les résultats de requête dans une table de destination
• Interroger une table de destination à l'aide de l'interface utilisateur Web pour confirmer que vos données ont été transformées et chargées correctement

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 :

Résultat :

Exemple de résultat :

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

Résultat :

Exemple de résultat :

Ouvrir la console BigQuery

1. Dans Google Cloud Console, sélectionnez le menu de navigation > BigQuery :

Le message Welcome to BigQuery in the Cloud Console (Bienvenue sur BigQuery dans Cloud Console) s'affiche. Il contient un lien vers le guide de démarrage rapide et répertorie les mises à jour de l'interface utilisateur.

2. Cliquez sur Done (OK).

Tâche 1 : Créer un ensemble de données pour stocker vos tables

Dans votre projet BigQuery, créez un ensemble de données intitulé liquor_sales.

1. Dans la section "Explorateur", cliquez sur l'icône Afficher les actions située à côté de l'ID de votre projet, puis sélectionnez Créer un ensemble de données.

La boîte de dialogue Créer un ensemble de données s'ouvre.

2. Définissez l'ID de l'ensemble de données sur liquor_sales. Laissez la valeur par défaut des autres options et cliquez sur Créer l'ensemble de données.

Dans le volet de gauche, une table liquor_sales apparaît sous votre projet.

Tâche 2 : Charger et interroger des données relationnelles

Dans cette section, vous allez mesurer les performances des requêtes pour les données relationnelles dans BigQuery.

BigQuery accepte les jointures de grande taille, et les performances des jointures sont satisfaisantes. Toutefois, BigQuery est un datastore en colonnes et les performances maximales sont obtenues sur des ensembles de données dénormalisés. Le stockage BigQuery étant peu coûteux et évolutif, il est judicieux de dénormaliser et de préjoindre les ensembles de données dans des tables homogènes. En d'autres termes, vous échangez des ressources de calcul contre des ressources de stockage (ces dernières étant plus performantes et plus rentables).

Dans cette section, vous allez effectuer les opérations suivantes :

• Importer un ensemble de tables à partir d'un schéma relationnel (en 3e forme normale)
• Exécuter des requêtes sur les tables relationnelles
• Observer les performances de ces requêtes et les comparer avec les performances de requêtes identiques effectuées sur une table à partir d'un schéma dénormalisé contenant les mêmes informations

Les tables que vous importez ont un schéma relationnel constitué des tables suivantes :

Voici un diagramme du schéma relationnel :

Créer la table des ventes

1. Dans la section "Explorateur", cliquez sur l'icône Afficher les actions située à côté de l'ensemble de données liquor_sales. Sélectionnez Ouvrir, puis cliquez sur Créer une table.

2. Dans la section Source de la page Créer une table, procédez comme suit :

• Pour le champ Créer une table à partir de, sélectionnez Google Cloud Storage.
• Saisissez le chemin d'accès vers le bucket Google Cloud Storage :

• Pour le champ Format des fichiers, sélectionnez CSV.

3. Dans la section Destination, configurez les éléments suivants :

• Pour Nom de la table, saisissez sales.
• Laissez la valeur par défaut pour les autres champs de la section "Destination".

4. Dans la section Schéma, configurez les éléments suivants :

• Cliquez sur Modifier sous forme de texte.
• Copiez et collez le schéma ci-dessous :

5. Cliquez sur Options avancées pour afficher et configurer ces options :

• Pour Délimiteur de champ, vérifiez que la valeur Virgule est sélectionnée.
• Comme sales.csv contient une seule ligne d'en-tête, définissez l'option Lignes d'en-tête à ignorer sur 1.
• Cochez la case Nouvelles lignes entre guillemets.
• Acceptez les autres valeurs par défaut et cliquez sur Créer la table.

BigQuery crée un job de chargement pour créer la table et y importer les données. Cette opération peut prendre quelques secondes.

6. Cliquez sur Historique personnel pour suivre la progression du job.

Créer les tables restantes

Créez les tables restantes dans le schéma relationnel à l'aide de la ligne de commande Cloud Shell.

1. Créez la table category :

2. Créez la table convenience_store :

3. Créez la table county :

4. Créez la table item :

5. Créez la table store :

6. Créez la table vendor :

7. Retournez sur l'interface utilisateur Web de BigQuery. Vérifiez que les nouvelles tables sont bien chargées dans votre ensemble de données liquor_sales. Actualisez le navigateur, si nécessaire.

Interroger les données relationnelles

Vous allez maintenant utiliser l'éditeur de requête pour interroger vos données.

1. Sous la zone de code de l'éditeur de requête, cliquez sur Plus > Paramètres de requête.

2. Dans la section Gestion des ressources > Préférences pour le cache, décochez la case Utiliser les résultats mis en cache et cliquez sur Enregistrer. Si vous devez exécuter la requête plusieurs fois, vous ne devez pas utiliser les résultats mis en cache.

3. Dans la fenêtre de l'éditeur de requêtes, saisissez la requête suivante sur les tables relationnelles et cliquez sur Exécuter :

4. Dans la section Résultats de la requête située en bas, cliquez sur l'onglet Résultats. Notez la durée d'exécution de la requête. Vous trouverez un exemple ci-dessous (la durée d'exécution peut varier).

Elle servira de point de comparaison avec la durée d'une requête que nous effectuerons sur un ensemble de données aplati dans les sections suivantes.

Tâche 3 : Charger et interroger des données aplaties

Dans cette section, vous allez dénormaliser les schémas et analyser les ventes d'alcool pour l'État de l'Iowa à l'aide des données aplaties. L'exécution de cette requête sur les données aplaties devrait être plus rapide qu'avec les données relationnelles. Vous noterez la durée d'exécution pour faire la comparaison et confirmer cette hypothèse.

Un schéma dénormalisé aplatit toutes les données relationnelles en une seule ligne. Par exemple, dans le schéma dénormalisé, county_number, county, store, name, address, city, zipcode, store_location, county_number et cstore sont des champs qui contiennent tous les champs des tables County, Store et Convenience_store.

Le diagramme suivant illustre le schéma dénormalisé.

Créer la table iowa_sales_denorm

1. Dans le volet de gauche, sélectionnez l'ensemble de données liquor_sales et cliquez sur Créer une table à droite.

La boîte de dialogue Créer une table s'ouvre.

2. Dans la section Source, configurez les éléments suivants :

• Pour le champ Créer une table à partir de, sélectionnez Google Cloud Storage.
• Saisissez le chemin d'accès vers le bucket Google Cloud Storage :

• Pour le champ Format des fichiers, sélectionnez CSV.

3. Dans la section Destination, configurez les éléments comme suit :

• Pour le champ Nom de la table, saisissez iowa_sales_denorm.
• Laissez la valeur par défaut pour les autres champs de la section "Destination".

4. Dans la section Schema, configurez les éléments suivants :

• Cliquez sur Modifier sous forme de texte.
• Copiez et collez le schéma ci-dessous :

5. Dans la section Options avancées, configurez les éléments comme suit :

• Pour Délimiteur de champ, vérifiez que la valeur Virgule est sélectionnée.
• Comme iowa_sales_denorm.csv contient une seule ligne d'en-tête, saisissez 1 pour Lignes d'en-tête à ignorer.
• Cochez la case Nouvelles lignes entre guillemets.
• Acceptez les autres valeurs par défaut et cliquez sur Créer la table.

BigQuery crée un job de chargement pour créer la table et y importer les données. Cette opération peut prendre quelques secondes.

6. Cliquez sur Historique personnel pour suivre la progression du job.

7. Saisissez et exécutez la requête suivante sur la table dont le schéma a été dénormalisé (cette requête produit les mêmes résultats que la requête de la section précédente) :

8. Dans la section Résultats de la requête située en bas, cliquez sur l'onglet Résultats et notez la durée d'exécution de la requête. Elle servira de point de comparaison avec la durée d'une requête que nous effectuerons sur un ensemble de données aplati dans les sections suivantes.

9. Pour calculer la durée d'exécution de la requête, soustrayez l'heure de début à l'heure de fin.

Notez que la requête correspondant à la table dont le schéma a été dénormalisé s'exécute un peu plus rapidement et présente une syntaxe plus simple. Dans la mesure du possible, préjoignez les ensembles de données dans des tables homogènes afin d'optimiser les performances dans BigQuery.

Comparer les performances des requêtes avec les détails de l'exécution

1. Sélectionnez HISTORIQUE DU PROJET.

2. Cliquez sur le premier job de requête exécuté sur le schéma relationnel normalisé, puis sur OUVRIR EN TANT QUE NOUVELLE REQUÊTE.

3. Sélectionnez Détails de l'exécution.

Le plan d'exécution comporte deux sections principales :

• Durée d'utilisation des nœuds de calcul moyenne et maximale par type de tâche et par étape

• Benchmarks de performances de haut niveau

  • Temps écoulé : temps total pour traiter la requête.
  • Durée d'utilisation des emplacements : il s'agit du temps qu'il faudrait pour traiter la requête si elle n'était pas traitée en parallèle sur plusieurs machines.
  • Octets brassés : brassage automatique des données en mémoire pour un traitement parallèle en masse.
  • Octets répandus sur le disque : il s'agit de la quantité de données répandues sur le disque persistant si les données ne peuvent pas être traitées en mémoire (à cause du décalage des données en général).

1. Commencez par comparer les temps des benchmarks entre chacune des requêtes que nous avons exécutées.

2. Ensuite, comparez les types de tâches pour lesquelles les nœuds de calcul ont passé le plus de temps.

Requête 1. Détails de l'exécution du schéma relationnel

Requête 2. Détails de l'exécution du schéma dénormalisé

Observations :

• La requête dénormalisée (n° 2) est plus rapide et demande moins de temps d'utilisation des emplacements pour parvenir au même résultat.
• La requête relationnelle (n° 1) comporte beaucoup plus d'étapes d'entrée, et la durée d'utilisation des nœuds de calcul est majoritairement consacrée à la jointure des ensembles de données.
• La requête dénormalisée (n° 2) est celle qui passe le plus de temps à lire les données en entrée et à générer les résultats. Le temps consacré aux agrégations et aux jointures est minime.
• Aucune de ces deux requêtes n'a entraîné d'octets répandus sur le disque, ce qui sous-entend que nos ensembles de données ne sont probablement pas biaisés (ou qu'ils ne sont pas encore assez volumineux pour dépasser la mémoire d'un nœud de calcul individuel).

Éviter les antimodèles de performances

Maintenant que vous maîtrisez mieux la conception d'un schéma de base de données efficace, il est temps de vous entraîner à optimiser quelques requêtes mal écrites.

La requête ci-dessous met du temps à s'exécuter. Que pouvez-vous faire pour résoudre ce problème ?

1. Copiez et collez la requête ci-dessous dans l'éditeur de requête et exécutez-la pour obtenir un benchmark.

Objectif : compter toutes les organisations américaines à but non lucratif qui ont déclaré leurs impôts au format papier (et non par voie électronique) en 2015

Que pouvez-vous faire pour améliorer les performances ?

2. Comparez avec la solution ci-dessous :

3. Exécutez votre version mise à jour et observez le temps écoulé.

4. Effacez le contenu de l'éditeur de requête.

La nouvelle requête ci-dessous met du temps à s'exécuter. (Exécutez la requête pour obtenir un benchmark. Arrêtez l'opération après un délai de 30 secondes si la requête n'aboutit pas.)

Objectif : en utilisant le numéro d'identification de l'employeur (EIN) comme champ de liaison, joindre la table des déclarations de revenus à la table des noms d'organisations et obtenir les noms de toutes les organisations ayant effectué une déclaration de revenus en 2015

5. Ajoutez cette requête dans l'éditeur de requête, puis cliquez sur Exécuter :

6. Corrigez la requête ci-dessus (indice : rappelez-vous la condition de champ JOIN à utiliser pour notre schéma).

Comparez avec la solution ci-dessous.

7. Ajoutez cette requête dans l'éditeur de requête, puis cliquez sur Exécuter :

8. Exécutez votre version mise à jour et observez le temps écoulé.

Constatez-vous une amélioration ? À quelle vitesse s'exécute la requête ?

Leçons à retenir

Félicitations !

Cet atelier pratique consacré à la conception efficace de schémas BigQuery et aux performances des requêtes est désormais terminé. Vous avez chargé des fichiers CSV et JSON dans des tables BigQuery, transformé les données et joint les tables, stocké les résultats de requête, puis confirmé que les données ont été transformées et chargées correctement.

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.