Spécifier des colonnes imbriquées et répétées

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 insérez 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 (types RECORD).
- BigQuery accepte les colonnes de type STRUCT (ou RECORD). Un STRUCT est un type de données complexe qui peut servir à représenter un objet comportant plusieurs colonnes enfants. Dans une colonne STRUCT, vous pouvez également définir une ou plusieurs colonnes enfants de type STRUCT (appelées STRUCT imbriqués ou répétés). Lorsque vous imbriquez des STRUCTS, 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 de STRUCT.

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 à l'UI Web de BigQuery
Dans la section Ressources du panneau de navigation, développez votre projet et sélectionnez un ensemble de données. Cliquez sur Créer une table. Le processus de chargement des données est identique au processus de création d'une table vide.
Sur la page Create table (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, sélectionnez RECORD.
  - Dans le champ 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 avec des adresses addresses. indiquant qu'il s'agit d'un champ imbriqué.
    - Dans le champ Type, choisissez STRING.
    - Dans le champ Mode, conservez la valeur NULLABLE.
    - Répétez ces étapes pour ajouter l'adresse address (NULLABLE STRING), la ville city (NULLABLE STRING), le département state (NULLABLE STRING), le code postal zip (NULLABLE STRING) et le nombre d'années numberOfYears (NULLABLE STRING).
- 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 d'adresses imbriquées et répétées addresses dans l'UI Web classique de BigQuery, procédez comme suit :

Accédez à l'UI Web de BigQuery.

Accéder à l'UI Web de BigQuery
Dans le volet de navigation, cliquez sur la flèche vers le bas située à côté du nom de l'ensemble de données, puis sur Create new table (Créer une table). Le processus de chargement des données est identique à celui permettant de créer une table vide.
Sur la page Create table (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, sélectionnez RECORD.
  - Dans le champ Mode, choisissez REPEATED.
  - À droite de RECORD, cliquez sur l'icône du signe plus pour ajouter un champ imbriqué.
    - Dans le champ Name (Nom), saisissez status. Vous remarquerez que le champ est pré-rempli avec des adresses addresses. indiquant qu'il s'agit d'un champ imbriqué.
    - Dans le champ Type, choisissez STRING.
    - Dans le champ Mode, conservez la valeur NULLABLE.
    - Répétez ces étapes pour ajouter l'adresse address (NULLABLE STRING), la ville city (NULLABLE STRING), le département state (NULLABLE STRING), le code postal zip (NULLABLE STRING) et le nombre d'années numberOfYears (NULLABLE STRING).
- 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 d'adresse addresses ressemble à celui-ci :

Champ imbriqué dans l'onglet Schéma

Ligne de commande

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 sur l'API BigQuery Go.

Afficher sur GitHub Commentaires

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
sampleSchema := bigquery.Schema{
	{Name: "id", Type: bigquery.StringFieldType},
	{Name: "first_name", Type: bigquery.StringFieldType},
	{Name: "last_name", Type: bigquery.StringFieldType},
	{Name: "dob", Type: bigquery.DateFieldType},
	{Name: "addresses",
		Type:     bigquery.RecordFieldType,
		Repeated: true,
		Schema: bigquery.Schema{
			{Name: "status", Type: bigquery.StringFieldType},
			{Name: "address", Type: bigquery.StringFieldType},
			{Name: "city", Type: bigquery.StringFieldType},
			{Name: "state", Type: bigquery.StringFieldType},
			{Name: "zip", Type: bigquery.StringFieldType},
			{Name: "numberOfYears", Type: bigquery.StringFieldType},
		}},
}

metaData := &bigquery.TableMetadata{
	Schema: sampleSchema,
}
tableRef := client.Dataset(datasetID).Table(tableID)
if err := tableRef.Create(ctx, metaData); err != nil {
	return err
}
fmt.Printf("created table %s\n", tableRef.FullyQualifiedName())

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 : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence sur l'API BigQuery Python.

Afficher sur GitHub Commentaires

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_ref = client.dataset('my_dataset')

schema = [
    bigquery.SchemaField("id", "STRING", mode="NULLABLE"),
    bigquery.SchemaField("first_name", "STRING", mode="NULLABLE"),
    bigquery.SchemaField("last_name", "STRING", mode="NULLABLE"),
    bigquery.SchemaField("dob", "DATE", mode="NULLABLE"),
    bigquery.SchemaField(
        "addresses",
        "RECORD",
        mode="REPEATED",
        fields=[
            bigquery.SchemaField("status", "STRING", mode="NULLABLE"),
            bigquery.SchemaField("address", "STRING", mode="NULLABLE"),
            bigquery.SchemaField("city", "STRING", mode="NULLABLE"),
            bigquery.SchemaField("state", "STRING", mode="NULLABLE"),
            bigquery.SchemaField("zip", "STRING", mode="NULLABLE"),
            bigquery.SchemaField("numberOfYears", "STRING", mode="NULLABLE"),
        ],
    ),
]
table_ref = dataset_ref.table("my_table")
table = bigquery.Table(table_ref, schema=schema)
table = client.create_table(table)  # API request

print("Created table {}".format(table.full_table_id))

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.

Cette page vous a-t-elle été utile ? Évaluez-la :

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see our Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Dernière mise à jour : août 15, 2019

Spécifier des colonnes imbriquées et répétées

Spécifier des colonnes imbriquées et répétées

Limites

Exemple

Exemple de schéma

Spécifier les colonnes imbriquées et répétées de l'exemple

Console

UI classique

Ligne de commande

Go

Python

Modifier les colonnes imbriquées et répétées

Envoyer des commentaires concernant…