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 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 types STRUCT (appelés 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

Interface utilisateur Web

Pour spécifier la colonne d'adresses imbriquées et répétées addresses dans l'interface utilisateur Web de BigQuery :

  1. Accédez à l'interface utilisateur Web de BigQuery.

    Accéder à l'UI Web de BigQuery

  2. Cliquez sur la flèche vers le bas flèche vers le bas qui est située à côté du nom de votre ensemble de données dans la barre de navigation, puis sur Créer une table. Le processus de chargement des données est identique à celui permettant de créer une table vide.

  3. Sur la page Create table (Créer une table) :

    • Pour les Source Data (Données sources), cliquez sur Create from source (Créer à partir de la source).
    • Pour la Destination Table (Table de destination), choisissez votre ensemble de données et entrez le nom de la table dans le champ Destination table name (Nom de la table de destination).
    • Pour le schéma :

      • Dans le champ Name (Nom), saisissez addresses.
      • Pour le paramètre Type, sélectionnez RECORD.
      • Pour le paramètre 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 des adresses addresses. indiquant qu'il s'agit d'un champ imbriqué.
      • Pour le paramètre Type, choisissez STRING.
      • Pour le paramètre 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 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 cet exemple, suivez les instructions de configuration de Go décrites 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 cet exemple, suivez les instructions de configuration de Python décrites 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 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 prend en charge 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 plus d'informations, consultez 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 plus d'informations, consultez Modifier manuellement des schémas de table .

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

Envoyer des commentaires concernant…

Besoin d'aide ? Consultez notre page d'assistance.