GSP416

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.

Dans cet atelier, vous allez travailler avec des données semi-structurées (ingestion JSON, types de données tableau) au sein de BigQuery. Dénormaliser votre schéma en une table unique avec des champs imbriqués et répétés permet d'améliorer les performances, mais l'utilisation de la syntaxe SQL avec les données de tableau (array) peut être délicate. Vous allez vous entraîner à charger, interroger, corriger et désimbriquer divers ensembles de données semi-structurées.

Objectifs de l'atelier

Dans cet atelier, vous allez apprendre à effectuer les tâches suivantes :

• Charger et interroger des données semi-structurées, y compris les désimbriquer
• Corriger des requêtes sur des données semi-structurées

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

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.

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.

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

1. Dans 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. Nommez le nouvel ensemble de données fruit_store. Conservez les valeurs par défaut des autres options, à savoir Emplacement des données et Expiration du tableau par défaut.

3. Cliquez sur Créer un ensemble de données.

Tâche 2 : S'entraîner à utiliser des tableaux en SQL

Normalement, en langage SQL, chaque ligne est associée à une seule valeur, comme dans la liste de fruits ci-dessous :

Ligne	Fruit
1	raspberry
2	blackberry
3	strawberry
4	cherry

Comment procéder si vous voulez une liste de fruits pour chaque personne dans le magasin ? La liste pourrait alors se présenter comme suit :

Ligne	Fruit	Personne
1	raspberry	sally
2	blackberry	sally
3	strawberry	sally
4	cherry	sally
5	orange	frederick
6	apple	frederick

Dans une base de données relationnelle SQL traditionnelle, vous constateriez que les noms sont répétés et penseriez immédiatement à diviser la table ci-dessus en deux tables distinctes : Fruits et Personnes. Ce processus s'appelle la normalisation (passer d'une table à plusieurs). Il s'agit d'une approche courante pour les bases de données transactionnelles telles que mySQL.

Pour l'entreposage de données, les analystes optent souvent pour la méthode inverse (dénormalisation), qui consiste à regrouper plusieurs tables distinctes dans une grande table de rapport.

Vous allez aujourd'hui découvrir une approche différente consistant à stocker les données avec différents niveaux de précision dans une seule et même table, à l'aide de champs répétés :

Ligne	Fruit (objet array)	Personne
1	raspberry	sally
	blackberry
	strawberry
	cherry
2	orange	frederick
	apple

Qu'est-ce qui vous paraît bizarre dans la table ci-dessus ?

• Elle ne comporte que deux lignes.
• Une même ligne contient plusieurs valeurs du champ Fruit.
• Les personnes sont associées à toutes les valeurs du champ.

Quel est le principal élément à noter ? Le type de données array !

Il existe une méthode plus simple pour interpréter l'objet array Fruit :

Ligne	Fruit (objet array)	Personne
1	[raspberry, blackberry, strawberry, cherry]	sally
2	[orange, apple]	frederick

Ces deux tables sont identiques. Deux points importants sont à retenir ici :

• Un objet array est simplement une liste d'éléments entre crochets [ ].
• BigQuery présente ces objets sous une forme aplatie. BigQuery liste simplement la valeur verticalement dans le tableau (notez que toutes les valeurs appartiennent toujours à une ligne unique)

À votre tour !

1. Saisissez la commande suivante dans l'éditeur de requête BigQuery :

#standardSQL SELECT ['raspberry', 'blackberry', 'strawberry', 'cherry'] AS fruit_array

2. Cliquez sur Exécuter.

3. Essayez maintenant d'exécuter cette requête :

#standardSQL SELECT ['raspberry', 'blackberry', 'strawberry', 'cherry', 1234567] AS fruit_array

Vous allez normalement recevoir un message d'erreur ressemblant à celui-ci :

Error: Array elements of types {INT64, STRING} do not have a common supertype at [3:1]

Les objets array ne peuvent contenir qu'un seul type de données (seulement des chaînes ou seulement des nombres).

4. Voici la requête finale, qui indique la table à interroger :

#standardSQL SELECT person, fruit_array, total_cost FROM `data-to-insights.advanced.fruit_store`;

5. Cliquez sur Exécuter.

6. Après avoir observé les résultats, cliquez sur l'onglet JSON pour afficher la structure imbriquée des résultats.

Charger des données JSON semi-structurées dans BigQuery

Comment procéderiez-vous si vous deviez ingérer un fichier JSON dans BigQuery ?

Créez une nouvelle table fruit_details dans l'ensemble de données.

1. Cliquez sur l'ensemble de données fruit_store.

L'option Créer une table s'affiche.

Remarque : Vous devrez peut-être agrandir votre fenêtre de navigateur pour voir l'option "Créer une table".

2. Ajoutez les informations suivantes pour la table :

• Source : choisissez Google Cloud Storage dans le menu déroulant Créer une table à partir de.
• Sélectionnez un fichier du bucket Cloud Storage : cloud-training/data-insights-course/labs/optimizing-for-performance/shopping_cart.json.
• Format de fichier : JSONL (fichier JSON délimité par un retour à la ligne)

3. Nommez la nouvelle table fruit_details.

4. Cochez la case Détection automatique sous "Schéma".

5. Cliquez sur Créer une table.

Dans le schéma, notez que fruit_array est marqué comme REPEATED (RÉPÉTÉ), ce qui signifie qu'il s'agit d'un tableau.

Résumé

• BigQuery est nativement compatible avec les tableaux.
• Les valeurs contenues dans un tableau doivent être du même type.
• Les tableaux sont appelés des champs REPEATED (RÉPÉTÉS) dans BigQuery.

Cliquez sur Vérifier ma progression pour valider l'objectif. Créer un ensemble de données et une table pour stocker les données

Tâche 3 : Créer vos propres tableaux avec ARRAY_AGG()

Si vous n'avez pas encore de tableaux dans vos tables, vous pouvez en créer.

1. Copiez et collez la requête ci-dessous pour explorer cet ensemble de données public :

SELECT fullVisitorId, date, v2ProductName, pageTitle FROM `data-to-insights.ecommerce.all_sessions` WHERE visitId = 1501570398 ORDER BY date

2. Cliquez sur Exécuter, puis observez les résultats.

Vous allez maintenant agréger les valeurs de chaîne dans un tableau avec la fonction ARRAY_AGG().

3. Copiez et collez la requête ci-dessous pour explorer cet ensemble de données public :

SELECT fullVisitorId, date, ARRAY_AGG(v2ProductName) AS products_viewed, ARRAY_AGG(pageTitle) AS pages_viewed FROM `data-to-insights.ecommerce.all_sessions` WHERE visitId = 1501570398 GROUP BY fullVisitorId, date ORDER BY date

4. Cliquez sur Exécuter, puis observez les résultats.

5. Ensuite, utilisez la fonction ARRAY_LENGTH() pour compter le nombre de pages et de produits qui ont été visualisés :

SELECT fullVisitorId, date, ARRAY_AGG(v2ProductName) AS products_viewed, ARRAY_LENGTH(ARRAY_AGG(v2ProductName)) AS num_products_viewed, ARRAY_AGG(pageTitle) AS pages_viewed, ARRAY_LENGTH(ARRAY_AGG(pageTitle)) AS num_pages_viewed FROM `data-to-insights.ecommerce.all_sessions` WHERE visitId = 1501570398 GROUP BY fullVisitorId, date ORDER BY date

6. Pour poursuivre, dédupliquez les pages et les produits afin de déterminer combien de produits uniques ont été visualisés, en ajoutant le paramètre DISTINCT à la fonction ARRAY_AGG() :

SELECT fullVisitorId, date, ARRAY_AGG(DISTINCT v2ProductName) AS products_viewed, ARRAY_LENGTH(ARRAY_AGG(DISTINCT v2ProductName)) AS distinct_products_viewed, ARRAY_AGG(DISTINCT pageTitle) AS pages_viewed, ARRAY_LENGTH(ARRAY_AGG(DISTINCT pageTitle)) AS distinct_pages_viewed FROM `data-to-insights.ecommerce.all_sessions` WHERE visitId = 1501570398 GROUP BY fullVisitorId, date ORDER BY date

Cliquez sur Vérifier ma progression pour valider l'objectif. Exécuter la requête pour savoir combien de produits uniques ont été visualisés

Résumé

Les tableaux vous permettent de faire des choses très utiles comme :

• trouver le nombre d'éléments dans le tableau avec ARRAY_LENGTH(<array>) ;
• dédupliquer ces éléments avec ARRAY_AGG(DISTINCT <field>) ;
• classer ces éléments avec ARRAY_AGG(<field> ORDER BY <field>) ;
• définir une limite avec ARRAY_AGG(<field> LIMIT 5).

Tâche 4 : Interroger des tables contenant des tableaux

L'ensemble de données public de BigQuery pour Google Analytics (bigquery-public-data.google_analytics_sample) comprend beaucoup plus de champs et de lignes que celui de notre atelier (data-to-insights.ecommerce.all_sessions). Mais surtout, il stocke déjà nativement des valeurs de champs (produits, pages et transactions, par exemple) sous la forme d'objets ARRAY.

1. Copiez et collez la requête ci-dessous pour explorer les données disponibles et voir si vous pouvez trouver des champs contenant des valeurs répétées (tableaux) :

SELECT * FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801` WHERE visitId = 1501570398

2. Exécutez la requête.

3. Parcourez les résultats jusqu'à ce que vous trouviez le champ hits.product.v2ProductName (nous aborderons très bientôt les alias de champs multiples).

Le nombre de champs disponibles dans le schéma Google Analytics peut être trop important pour une analyse.

4. Essayez de formuler une requête uniquement sur les champs des visites et des noms de pages, comme précédemment :

SELECT visitId, hits.page.pageTitle FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801` WHERE visitId = 1501570398

Vous recevez le message d'erreur suivant : Error:Cannot access field page on a value with type ARRAY<STRUCT<hitNumber INT64, time INT64, hour INT64, ...>> at [3:8]

Pour pouvoir interroger des champs REPEATED (tableaux) normalement, vous devez tout d'abord fractionner ces tableaux en lignes.

Par exemple, le tableau associé à hits.page.pageTitle est actuellement stocké sous la forme d'une ligne unique semblable à ceci :

['homepage','product page','checkout']

et il doit se présenter ainsi :

['homepage', 'product page', 'checkout']

Comment faire cela avec SQL ?

Réponse : En appliquant la fonction UNNEST() à votre champ de tableau :

SELECT DISTINCT visitId, h.page.pageTitle FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`, UNNEST(hits) AS h WHERE visitId = 1501570398 LIMIT 10

Nous parlerons de UNNEST() plus tard. Pour le moment, notez simplement les points suivants :

• Vous devez appliquer la fonction UNNEST() aux tableaux pour replacer chacun de leurs éléments sur une ligne distincte.
• UNNEST() suit toujours le nom de table figurant dans votre clause FROM (conceptuellement, vous pouvez considérer cela comme une table préjointe).

Cliquez sur Vérifier ma progression pour valider l'objectif. Exécuter la requête pour utiliser la fonction UNNEST() sur le champ de tableau

Tâche 5 : Présentation des objets STRUCT

Vous vous êtes peut-être demandé pourquoi l'alias de champ hit.page.pageTitle ressemble à trois champs séparés par un point. Tout comme les valeurs ARRAY vous donnent la possibilité d'accroître la précision de vos champs, un autre type de données vous permet d'élargir la portée des requêtes dans votre schéma en regroupant des champs associés. Il s'agit du type de données SQL STRUCT.

Pour comprendre facilement ce qu'est un objet STRUCT, il suffit de se le représenter comme une table distincte qui est préjointe à votre table principale.

Un objet STRUCT peut inclure :

• un ou plusieurs champs ;
• des types de données identiques ou différents pour chaque champ ;
• son propre alias.

Cela ressemble à une table, n'est-ce pas ?

Explorer un ensemble de données avec des objets STRUCT

1. Pour ouvrir l'ensemble de données bigquery-public-data, cliquez sur + AJOUTER, sélectionnez Ajouter un projet aux favoris en saisissant son nom, puis saisissez le nom bigquery-public-data.

2. Cliquez sur Ajouter aux favoris.

Le projet bigquery-public-data apparaît désormais dans la section "Explorateur".

3. Ouvrez bigquery-public-data.

4. Recherchez et ouvrez l'ensemble de données google_analytics_sample.

5. Cliquez sur la table ga_sessions(366)_.

6. Commencez à faire défiler le schéma, puis répondez à la question suivante en utilisant la fonction de recherche de votre navigateur.

Comme vous pouvez l'imaginer, un site Web d'e-commerce moderne peut stocker un volume impressionnant de données de sessions.

Le principal intérêt d'une table comportant 32 objets STRUCT est que cela permet d'exécuter des requêtes comme celle-ci sans jointures :

SELECT visitId, totals.*, device.* FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801` WHERE visitId = 1501570398 LIMIT 10 Remarque : La syntaxe .* indique à BigQuery de retourner tous les champs pour cet objet STRUCT (comme il le ferait si totals.* était une table distincte avec laquelle une jointure existait).

Lorsque vous stockez vos grandes tables de rapport sous la forme d'objets STRUCT ("tables" préjointes) et ARRAY (précision accrue), vous pouvez :

• bénéficier de bien meilleures performances en évitant d'avoir à effectuer 32 jointures de tables avec JOIN ;
• obtenir des données précises des objets ARRAY, si nécessaire, mais sans être pénalisé si vous n'en avez pas besoin (BigQuery stocke chaque colonne individuellement sur le disque) ;
• disposer de tout le contexte commercial dans une seule et même table, sans avoir à vous soucier des clés JOIN ou de savoir quelles tables contiennent les données voulues.

Tâche 6 : S'entraîner à utiliser des objets STRUCT et ARRAY

L'ensemble de données suivant contient les temps mis par des coureurs pour réaliser un tour de piste. Chaque tour sera nommé "split" (tour de piste).

1. Avec cette requête, essayez la syntaxe STRUCT et notez les différents types de champ présents dans le conteneur struct :

#standardSQL SELECT STRUCT("Rudisha" as name, 23.4 as split) as runner

Ligne	runner.name	runner.split
1	Rudisha	23.4

Que remarquez-vous concernant les alias de champ ? En raison de la présence de champs imbriqués dans l'objet struct ("name" et "split" sont un sous-ensemble de "runner"), vous obtenez une notation par points.

Comment associer le coureur à plusieurs temps pour chaque tour d'une même course ?

Avec un tableau bien sûr !

2. Exécutez la requête ci-dessous pour vérifier :

#standardSQL SELECT STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits) AS runner

Ligne	runner.name	runner.splits
1	Rudisha	23.4
		26.3
		26.4
		26.1

En résumé :

• Les objets struct sont des conteneurs qui peuvent présenter plusieurs noms de champ et types de données imbriqués.
• Un objet struct peut contenir un champ de type array (comme on peut le voir ci-dessus avec le champ "splits").

S'entraîner à ingérer des données JSON

1. Créez un ensemble de données nommé racing.

2. Cliquez sur l'ensemble de données racing, puis sur "Créer une table".

Remarque : Vous devrez peut-être agrandir votre fenêtre de navigateur pour voir l'option Créer une table.

• Source : sélectionnez Google Cloud Storage dans la liste déroulante Créer une table à partir de.
• Sélectionnez un fichier du bucket Cloud Storage : cloud-training/data-insights-course/labs/optimizing-for-performance/race_results.json.
• Format de fichier : JSONL (fichier JSON délimité par un retour à la ligne)
• Dans la section Schéma, cliquez sur Modifier sous forme de texte et ajoutez les lignes suivantes :

[ { "name": "race", "type": "STRING", "mode": "NULLABLE" }, { "name": "participants", "type": "RECORD", "mode": "REPEATED", "fields": [ { "name": "name", "type": "STRING", "mode": "NULLABLE" }, { "name": "splits", "type": "FLOAT", "mode": "REPEATED" } ] } ]

3. Nommez la nouvelle table race_results.

4. Cliquez sur Créer une table.

5. Une fois le chargement effectué, affichez l'aperçu du schéma associé à la table que vous venez de créer :

Quel champ correspond à l'objet STRUCT ? Comment le savez-vous ?

Le champ participants correspond à l'objet STRUCT, car il est du type RECORD.

Quel champ correspond à l'objet ARRAY ?

Le champ participants.splits est un tableau de valeurs flottantes à l'intérieur de l'objet struct participants parent. Il possède un mode REPEATED qui indique un tableau. On dit que les valeurs de ce tableau sont imbriquées, car plusieurs valeurs sont présentes dans un champ unique.

Cliquez sur Vérifier ma progression pour valider l'objectif. Créer un ensemble de données et un tableau pour ingérer les données JSON

S'entraîner à interroger des champs imbriqués et répétés

1. Examinons l'ensemble des coureurs pour le 800 mètres :

#standardSQL SELECT * FROM racing.race_results

Combien de lignes ont été renvoyées ?

Réponse : 1

Comment procéder si vous voulez lister le nom de chaque coureur et le type de course ?

2. Exécutez la requête ci-dessous et observez le résultat :

#standardSQL SELECT race, participants.name FROM racing.race_results

Error: Cannot access field name on a value with type ARRAY<STRUCT<name STRING, splits ARRAY<FLOAT64>>>> at [2:27]

Le résultat est très semblable à celui qui vous serait renvoyé si vous omettiez l'instruction GROUP BY avec les fonctions d'agrégation. Il existe ici deux niveaux de précision différents : une ligne pour la course et trois lignes pour les noms des participants. Comment transformer ceci...

Ligne	race	participants.name
1	800M	Rudisha
2	???	Makhloufi
3	???	Murphy

...en ceci :

Ligne	race	participants.name
1	800M	Rudisha
2	800M	Makhloufi
3	800M	Murphy

Avec une base de données relationnelle SQL traditionnelle comportant une table des courses et une table des participants, comment procéderiez-vous pour obtenir des informations à partir de ces deux tables ? Vous utiliseriez la commande JOIN pour les joindre. Ici, l'objet STRUCT des participants (qui est très semblable du point de vue conceptuel à une table) fait déjà partie de votre table des courses, mais il n'est pas encore correctement corrélé à votre champ non-STRUCT "race".

Quelle commande SQL en deux mots pourriez-vous utiliser pour corréler la course de 800 mètres avec chacun des coureurs de la première table ?

Réponse : CROSS JOIN

Parfait.

3. Essayez maintenant d'exécuter cette requête :

#standardSQL SELECT race, participants.name FROM racing.race_results CROSS JOIN participants # this is the STRUCT (it is like a table within a table)

Ensemble de données manquant pour la table "participants", alors qu'aucun ensemble de données par défaut n'est défini dans la requête.

Bien que l'objet STRUCT des participants soit semblable à une table, techniquement il s'agit toujours d'un champ de la table racing.race_results.

4. Ajoutez le nom de l'ensemble de données à la requête :

#standardSQL SELECT race, participants.name FROM racing.race_results CROSS JOIN race_results.participants # full STRUCT name

5. Ensuite, cliquez sur Exécuter.

Félicitations ! Vous avez réussi à afficher la liste de tous les coureurs de chaque course.

Ligne	race	name
1	800M	Rudisha
2	800M	Makhloufi
3	800M	Murphy
4	800M	Bosse
5	800M	Rotich
6	800M	Lewandowski
7	800M	Kipketer
8	800M	Berian

6. Pour simplifier la dernière requête, vous pouvez :

• ajouter un alias pour la table initiale ;
• remplacer les mots "CROSS JOIN" par une virgule (la virgule représente implicitement une jointure croisée).

Le résultat de la requête sera identique :

#standardSQL SELECT race, participants.name FROM racing.race_results AS r, r.participants

S'il y a plusieurs types de courses (800M, 100M, 200M), une commande CROSS JOIN n'aurait-elle pas seulement pour effet d'associer chaque nom de coureur à chaque course possible de la même manière qu'un produit cartésien ?

Réponse : Non. Il s'agit ici d'une jointure croisée corrélée qui ne désimbrique que les éléments associés à une ligne unique. Pour plus de détails, consultez la page consacrée à l'utilisation des objets ARRAY et STRUCT.

Résumé concernant les objets STRUCT :

• Un objet SQL STRUCT est simplement un conteneur d'autres champs de données qui peuvent être de types différents. Le mot "struct" signifie "structure de données". Souvenez-vous de l'exemple précédent : STRUCT(``"Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits``)`` AS runner
• Les objets STRUCT reçoivent un alias (comme "runner" dans l'exemple ci-dessus) et peuvent être conceptuellement assimilés à une table contenue dans votre table principale.
• Vous devez désimbriquer les objets STRUCT (et ARRAY) afin de pouvoir exécuter des opérations sur leurs éléments. Appliquez la fonction UNNEST() autour du nom de l'objet STRUCT lui-même ou du champ STRUCT se présentant sous forme de tableau, pour le désimbriquer et l'aplatir.

Tâche 7 : Questions de l'atelier : STRUCT()

Répondez aux questions ci-dessous en vous servant de la table racing.race_results que vous avez créée précédemment.

Tâche : écrire une requête pour compter (COUNT) le nombre total de coureurs.

• Pour commencer, utilisez la requête partiellement rédigée ci-dessous :

#standardSQL SELECT COUNT(participants.name) AS racer_count FROM racing.race_results Remarque : N'oubliez pas que vous devrez utiliser une jointure croisée dans votre nom d'objet struct en tant que source de données supplémentaire après l'instruction FROM.

Solution possible :

#standardSQL SELECT COUNT(p.name) AS racer_count FROM racing.race_results AS r, UNNEST(r.participants) AS p

Ligne	racer_count
1	8

Réponse : 8 coureurs ont participé à cette course.

Cliquez sur Vérifier ma progression pour valider l'objectif. Exécuter la requête pour COMPTER le nombre de coureurs au total

Tâche 8 : Question de l'atelier : Désimbriquer les éléments d'un objet ARRAY avec UNNEST( )

Rédigez une requête qui listera le temps de course total pour les coureurs dont le nom commence par R. Classez les résultats par ordre décroissant du meilleur temps total. Utilisez l'opérateur UNNEST() avec la requête partiellement rédigée ci-dessous.

• Complétez la requête :

#standardSQL SELECT p.name, SUM(split_times) as total_race_time FROM racing.race_results AS r , r.participants AS p , p.splits AS split_times WHERE GROUP BY ORDER BY ; Remarque :

• Vous devrez désimbriquer à la fois l'objet STRUCT et l'objet ARRAY contenu dans l'objet STRUCT sous la forme de sources de données après votre clause FROM.
• Veillez à utiliser des alias si nécessaire.

Solution possible :

#standardSQL SELECT p.name, SUM(split_times) as total_race_time FROM racing.race_results AS r , UNNEST(r.participants) AS p , UNNEST(p.splits) AS split_times WHERE p.name LIKE 'R%' GROUP BY p.name ORDER BY total_race_time ASC;

Ligne	name	total_race_time
1	Rudisha	102.19999999999999
2	Rotich	103.6

Cliquez sur Vérifier ma progression pour valider l'objectif. Exécuter la requête qui listera la durée de course totale pour les coureurs dont les noms commencent par R

Tâche 9 : Filtrer les valeurs d'un tableau

Vous avez pu constater que le tour de piste le plus rapide enregistré pour la course de 800 mètres a été effectué en 23,2 secondes, mais vous n'avez pas identifié le coureur qui a réalisé ce temps. Créez une requête qui renvoie ce résultat.

• Complétez la requête partiellement rédigée :

#standardSQL SELECT p.name, split_time FROM racing.race_results AS r , r.participants AS p , p.splits AS split_time WHERE split_time = ;

Solution possible :

#standardSQL SELECT p.name, split_time FROM racing.race_results AS r , UNNEST(r.participants) AS p , UNNEST(p.splits) AS split_time WHERE split_time = 23.2;

Ligne	name	split_time
1	Kipketer	23.2

Cliquez sur Vérifier ma progression pour valider l'objectif. Exécuter la requête pour afficher le coureur ayant le meilleur temps par tour

Félicitations !

Vous avez ingéré des ensembles de données JSON, créé des objets ARRAY et STRUCT, et désimbriqué des données semi-structurées pour en dégager des insights.

Étapes suivantes et informations supplémentaires

• Pour en savoir plus, consultez Utiliser des tableaux.
• Découvrez les ateliers suivants :
  • Créer des tables partitionnées par date dans BigQuery
  • Prédire le prix d'une course en taxi à l'aide d'un modèle de ML BigQuery
  • Ingérer de nouveaux ensembles de données dans 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 : 3 février 2024

Dernier test de l'atelier : 25 août 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.