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 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 (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 :

  1. Ouvrez l'UI Web de BigQuery dans la console GCP.
    Accéder à la console GCP

  2. Dans la section Ressources du panneau de navigation, développez votre projet et sélectionnez un ensemble de données. Cliquez sur Create table.

    Créer une table

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

        Schéma d'adresses

      • À droite de addresses, cliquez sur l'icône Plus pour ajouter un champ imbriqué.

        Bouton d'imbrication

        • Dans le champ Name (Nom), saisissez status. Vous remarquerez que le champ est pré-rempli avec addresses., 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.

          Schéma d'état

        • 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 addresses imbriquée et répétée dans l'UI Web classique de BigQuery, procédez comme suit :

  1. Accédez à l'UI Web de BigQuery.

    Accéder à l'UI Web de BigQuery

  2. Dans le volet de navigation, cliquez sur la flèche vers le bas flèche vers le bas à côté du nom de l'ensemble de données, puis sur Créer une table.

  3. 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 plus icône Plus pour ajouter un champ imbriqué.

        • Dans le champ Name (Nom), saisissez status. Vous remarquerez que le champ est pré-rempli avec addresses., 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 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).

        Champ imbriqué dans l'interface utilisateur

    • 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 :

Champ imbriqué dans l'onglet Schéma

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. 

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

# 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 :

Envoyer des commentaires concernant…

Cette page
Commentaires sur la documentation
BigQuery
Commentaires sur le produit
Besoin d'aide ? Consultez notre page d'assistance.