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 utilisez des colonnes imbriquées et répétées. Les colonnes imbriquées et répétées permettent de maintenir des relations et d'éviter l'altération des performances inhérente à 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 et Avro, et les fichiers d'exportation Firestore et 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 à son ou ses auteurs.

Dans BigQuery, vous pouvez préserver la relation entre livre et auteur sans créer de table d'auteurs 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 Cloud Console, procédez comme suit :

  1. Ouvrez l'UI Web de BigQuery dans Cloud Console.
    Accéder à Cloud Console

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

    Créer une table

  3. Sur la page Créer une table :

    • Pour Source, sélectionnez Table vide.
    • Pour Destination, choisissez votre ensemble de données et saisissez le nom de la table dans le champ 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.

        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, choisissez STRING.

        • Dans le champ 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 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, sélectionnez RECORD.
      • Dans le champ Mode, choisissez REPEATED.

      • À droite de RECORD, cliquez sur l'icône 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, 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).

        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'UI Web de BigQuery, le champ addresses ressemble à celui-ci :

Champ imbriqué dans l'onglet Schéma

bq

Pour spécifier la colonne d'adresses imbriquées et répétées addresses dans un fichier de schéma JSON, saisissez 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 en langage Go. 

import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/bigquery"
)

// createTableComplexSchema demonstrates creating a BigQuery table and specifying a complex schema that includes
// an array of Struct types.
func createTableComplexSchema(w io.Writer, projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydatasetid"
	// tableID := "mytableid"
	ctx := context.Background()

	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	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.Fprintf(w, "created table %s\n", tableRef.FullyQualifiedName())
	return nil
}

Java

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Java 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 Java. 

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.Field.Mode;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.StandardTableDefinition;
import com.google.cloud.bigquery.TableDefinition;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;

public class NestedRepeatedSchema {

  public static void runNestedRepeatedSchema() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    createTableWithNestedRepeatedSchema(datasetName, tableName);
  }

  public static void createTableWithNestedRepeatedSchema(String datasetName, String tableName) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(datasetName, tableName);

      Schema schema =
          Schema.of(
              Field.of("id", StandardSQLTypeName.STRING),
              Field.of("first_name", StandardSQLTypeName.STRING),
              Field.of("last_name", StandardSQLTypeName.STRING),
              Field.of("dob", StandardSQLTypeName.DATE),
              // create the nested and repeated field
              Field.newBuilder(
                      "addresses",
                      StandardSQLTypeName.STRUCT,
                      Field.of("status", StandardSQLTypeName.STRING),
                      Field.of("address", StandardSQLTypeName.STRING),
                      Field.of("city", StandardSQLTypeName.STRING),
                      Field.of("state", StandardSQLTypeName.STRING),
                      Field.of("zip", StandardSQLTypeName.STRING),
                      Field.of("numberOfYears", StandardSQLTypeName.STRING))
                  .setMode(Mode.REPEATED)
                  .build());

      TableDefinition tableDefinition = StandardTableDefinition.of(schema);
      TableInfo tableInfo = TableInfo.newBuilder(tableId, tableDefinition).build();

      bigquery.create(tableInfo);
      System.out.println("Table with nested and repeated schema created successfully");
    } catch (BigQueryException e) {
      System.out.println("Table was not created. \n" + e.toString());
    }
  }
}

Node.js

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Node.js 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 en langage Node.js. 

// Import the Google Cloud client library and create a client
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function nestedRepeatedSchema() {
  // Creates a new table named "my_table" in "my_dataset"
  // with nested and repeated columns in schema.

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = "my_dataset";
  // const tableId = "my_table";
  // const schema = [
  //   {name: 'Name', type: 'STRING', mode: 'REQUIRED'},
  //   {
  //     name: 'Addresses',
  //     type: 'RECORD',
  //     mode: 'REPEATED',
  //     fields: [
  //       {name: 'Address', type: 'STRING'},
  //       {name: 'City', type: 'STRING'},
  //       {name: 'State', type: 'STRING'},
  //       {name: 'Zip', type: 'STRING'},
  //     ],
  //   },
  // ];

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    schema: schema,
    location: 'US',
  };

  // Create a new table in the dataset
  const [table] = await bigquery
    .dataset(datasetId)
    .createTable(tableId, options);

  console.log(`Table ${table.id} created.`);
}

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 de l'API BigQuery en langage 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.