BigQuery fonctionne mieux lorsque vos données sont dénormalisées. Plutôt que de conserver un schéma relationnel en étoile ou en flocon, dénormalisez vos données et tirez parti des colonnes imbriquées et répétées. Les colonnes imbriquées et répétées permettent d'entretenir des relations sans compromettre les performances pour conserver un schéma relationnel (normalisé).
Vous pouvez spécifier des données imbriquées ou imbriquées et répétées dans l'interface utilisateur ou dans un fichier de schéma JSON. Pour spécifier des colonnes imbriquées ou imbriquées et répétées, utilisez le type de données RECORD
(STRUCT
).
Spécifier des colonnes imbriquées et répétées
BigQuery accepte le chargement de données imbriquées et répétées à partir de formats sources compatibles avec les schémas basés sur des objets, comme les fichiers JSON, Avro et les fichiers d'exportation Cloud Firestore et Cloud Datastore.
Par exemple, une base de données relationnelle servant à gérer le fonds documentaire d'une bibliothèque, conserverait probablement toutes les informations sur l'auteur dans une table distincte. Une clé de type author_id
serait utilisée pour lier le livre aux auteurs.
Dans BigQuery, vous pouvez préserver la relation entre livre et auteur sans créer de table d'auteur distincte. Vous créez plutôt une colonne auteur et y imbriquez des champs comme le prénom, le nom, la date de naissance de l'auteur, etc. Si un livre a plusieurs auteurs, vous pouvez répéter la colonne auteur imbriquée.
Pour créer une colonne avec des données imbriquées et répétées, définissez le type de données de la colonne sur RECORD
. Un type RECORD
est stocké sous la forme d'un élément STRUCT
. Il est accessible en tant que STRUCT
en langage SQL standard. Un élément STRUCT
est un conteneur de champs numérotés qui disposent tous d'un type (obligatoire) et d'un nom (facultatif). Pour que la colonne soit imbriquée, ajoutez des champs enfants à l'élément RECORD
(l'enregistrement parent). Pour que la colonne soit répétée, changez le mode en REPEATED
.
Limites
Les schémas imbriqués et répétés sont sujets aux limites suivantes :
- Lorsque vous chargez des données imbriquées et répétées, votre schéma ne peut pas contenir plus de 15 niveaux de conteneurs
STRUCT
imbriqués (typesRECORD
). - BigQuery accepte les colonnes de type
STRUCT
(ouRECORD
). UnSTRUCT
est un type de données complexe qui peut servir à représenter un objet comportant plusieurs colonnes enfants. Dans une colonneSTRUCT
, vous pouvez également définir une ou plusieurs colonnes enfants de typeSTRUCT
(appeléesSTRUCT
imbriqués ou répétés). Lorsque vous imbriquez desSTRUCTS
, BigQuery limite la profondeur des données imbriquées à 15 niveaux. La limite de profondeur des données imbriquées est indépendante des propriétés scalaires ou basées sur des tableaux deSTRUCT
.
Exemple
Exemple de schéma
L'exemple suivant présente un échantillon de données imbriquées et répétées. La table contient des informations sur des personnes. Elle comporte les champs suivants :
id
first_name
last_name
dob
(date de naissance)addresses
(champ imbriqué et répété)addresses.status
(actuel ou précédent)addresses.address
addresses.city
addresses.state
addresses.zip
addresses.numberOfYears
(années à l'adresse)
Le fichier de données JSON ressemblerait à ce qui suit. Vous remarquerez que la colonne d'adresses contient un tableau de valeurs (indiqué par [ ]
). Les multiples adresses du tableau sont les données répétées. Les champs multiples contenus dans chaque adresse sont les données imbriquées.
{"id":"1","first_name":"John","last_name":"Doe","dob":"1968-01-22","addresses":[{"status":"current","address":"123 First Avenue","city":"Seattle","state":"WA","zip":"11111","numberOfYears":"1"},{"status":"previous","address":"456 Main Street","city":"Portland","state":"OR","zip":"22222","numberOfYears":"5"}]} {"id":"2","first_name":"Jane","last_name":"Doe","dob":"1980-10-16","addresses":[{"status":"current","address":"789 Any Avenue","city":"New York","state":"NY","zip":"33333","numberOfYears":"2"},{"status":"previous","address":"321 Main Street","city":"Hoboken","state":"NJ","zip":"44444","numberOfYears":"3"}]}
Le schéma de la table devrait ressembler à ceci :
[ { "name": "id", "type": "STRING", "mode": "NULLABLE" }, { "name": "first_name", "type": "STRING", "mode": "NULLABLE" }, { "name": "last_name", "type": "STRING", "mode": "NULLABLE" }, { "name": "dob", "type": "DATE", "mode": "NULLABLE" }, { "name": "addresses", "type": "RECORD", "mode": "REPEATED", "fields": [ { "name": "status", "type": "STRING", "mode": "NULLABLE" }, { "name": "address", "type": "STRING", "mode": "NULLABLE" }, { "name": "city", "type": "STRING", "mode": "NULLABLE" }, { "name": "state", "type": "STRING", "mode": "NULLABLE" }, { "name": "zip", "type": "STRING", "mode": "NULLABLE" }, { "name": "numberOfYears", "type": "STRING", "mode": "NULLABLE" } ] } ]
Spécifier les colonnes imbriquées et répétées de l'exemple
Console
Pour spécifier la colonne addresses
imbriquée et répétée dans la console GCP, procédez comme suit :
Ouvrez l'UI Web de BigQuery dans la console GCP.
Accéder à la console GCPDans la section Ressources du panneau de navigation, développez votre projet et sélectionnez un ensemble de données. Cliquez sur Create table.
Sur la page Créer une table :
- Pour Source, sélectionnez Empty table (Table vide).
- Pour Destination, choisissez votre ensemble de données et saisissez le nom de la table dans le champ Table name (Nom de la table).
Pour Schema (Schéma), ajoutez un champ :
- Dans le champ Name (Nom), saisissez
addresses
. - Dans le champ Type (Type), sélectionnez
RECORD
. Dans le champ Mode (Mode), choisissez
REPEATED
.À droite de
addresses
, cliquez sur l'icône Plus pour ajouter un champ imbriqué.- Dans le champ Name (Nom), saisissez
status
. Vous remarquerez que le champ est pré-rempli avecaddresses.
, indiquant qu'il s'agit d'un champ imbriqué. - Dans le champ Type (Type), choisissez
STRING
. Dans le champ Mode (Mode), conservez la valeur
NULLABLE
.Répétez ces étapes pour ajouter l'adresse
address
(NULLABLE
STRING
), la villecity
(NULLABLE
STRING
), le départementstate
(NULLABLE
STRING
), le code postalzip
(NULLABLE
STRING
) et le nombre d'annéesnumberOfYears
(NULLABLE
STRING
).
- Dans le champ Name (Nom), saisissez
- Dans le champ Name (Nom), saisissez
Vous pouvez également cliquer sur Edit as Text (Modifier sous forme de texte) et spécifier le schéma dans un tableau JSON.
UI classique
Pour spécifier la colonne addresses
imbriquée et répétée dans l'UI Web classique de BigQuery, procédez comme suit :
Accédez à l'UI Web de BigQuery.
Dans le volet de navigation, cliquez sur la flèche vers le bas
à côté du nom de l'ensemble de données, puis sur Créer une table.
Sur la page Créer une table :
- Dans la section Données sources, cliquez sur Créer à partir de la source.
- Dans la section Table de destination, choisissez votre ensemble de données et entrez le nom de la table dans le champ Nom de la table de destination.
Pour le schéma :
- Dans le champ Name (Nom), saisissez
addresses
. - Dans le champ Type (Type), sélectionnez
RECORD
. - Dans le champ Mode (Mode), choisissez
REPEATED
. À droite de
RECORD
, cliquez sur l'icône du signe pluspour ajouter un champ imbriqué.
- Dans le champ Name (Nom), saisissez
status
. Vous remarquerez que le champ est pré-rempli avecaddresses.
, indiquant qu'il s'agit d'un champ imbriqué. - Dans le champ Type (Type), choisissez
STRING
. - Dans le champ Mode (Mode), conservez la valeur
NULLABLE
. - Répétez ces étapes pour ajouter l'adresse
address
(NULLABLE
STRING
), la villecity
(NULLABLE
STRING
), le départementstate
(NULLABLE
STRING
), le code postalzip
(NULLABLE
STRING
) et le nombre d'annéesnumberOfYears
(NULLABLE
STRING
).
- Dans le champ Name (Nom), saisissez
- Dans le champ Name (Nom), saisissez
Vous pouvez également cliquer sur Edit as Text (Modifier sous forme de texte) et spécifier le schéma dans un tableau JSON.
Lorsque vous examinez le schéma dans l'interface utilisateur Web de BigQuery, le champ addresses
ressemble à celui-ci :
CLI
Pour spécifier la colonne d'adresses imbriquées et répétées addresses
dans un fichier de schéma JSON, entrez les éléments suivants dans un éditeur de texte :
[ { "name": "id", "type": "STRING", "mode": "NULLABLE" }, { "name": "first_name", "type": "STRING", "mode": "NULLABLE" }, { "name": "last_name", "type": "STRING", "mode": "NULLABLE" }, { "name": "dob", "type": "DATE", "mode": "NULLABLE" }, { "name": "addresses", "type": "RECORD", "mode": "REPEATED", "fields": [ { "name": "status", "type": "STRING", "mode": "NULLABLE" }, { "name": "address", "type": "STRING", "mode": "NULLABLE" }, { "name": "city", "type": "STRING", "mode": "NULLABLE" }, { "name": "state", "type": "STRING", "mode": "NULLABLE" }, { "name": "zip", "type": "STRING", "mode": "NULLABLE" }, { "name": "numberOfYears", "type": "STRING", "mode": "NULLABLE" } ] } ]
Une fois le fichier de schéma JSON créé, vous pouvez le transmettre via la ligne de commande.
Go
Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Go décrite dans le guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery Go.
Python
Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Python décrite dans le guide de démarrage rapide de BigQuery relatif à l'utilisation des bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence sur l'API BigQuery Python.
Modifier les colonnes imbriquées et répétées
Lorsque vous ajoutez une colonne imbriquée ou une colonne imbriquée et répétée à la définition de schéma d'une table, vous pouvez ensuite la modifier comme vous le feriez pour n'importe quel type de colonne. BigQuery accepte de manière native plusieurs modifications de schéma, comme l'ajout d'un nouveau champ imbriqué à un enregistrement ou l'assouplissement du mode d'un champ imbriqué. Pour en savoir plus, consultez la page Modifier des schémas de table.
Vous pouvez également modifier manuellement une définition de schéma qui inclut des colonnes imbriquées et répétées. Pour en savoir plus, consultez la page Modifier manuellement des schémas de table.