Especifica columnas anidadas y repetidas en esquemas de tablas

En esta página, se describe cómo definir un esquema de tabla con columnas anidadas y repetidas en BigQuery. Para obtener una descripción general de los esquemas de tablas, consulta Especifica un esquema.

Define columnas anidadas y repetidas

Para crear una columna con datos anidados, establece el tipo de datos de la columna como RECORD en el esquema. Se puede acceder a un RECORD como un tipo STRUCT en SQL estándar. Una STRUCT es un contenedor de campos ordenados.

Para crear una columna con datos repetidos, establece el modo de la columna como REPEATED en el esquema. Se puede acceder a un campo repetido como un tipo ARRAY en SQL estándar.

Una columna RECORD puede tener el modo REPEATED, que se representa como un arreglo de tipos STRUCT. Además, un campo dentro de un registro se puede repetir, que se representa como un STRUCT que contiene un ARRAY. Un arreglo no puede contener otro arreglo directamente. Para obtener más información, consulta Declara un tipo ARRAY.

Limitaciones

Los esquemas anidados y repetidos están sujetos a las siguientes limitaciones:

Un esquema no puede contener más de 15 niveles de tipos RECORD anidados.

Las columnas de tipo RECORD pueden contener tipos RECORD anidados, también llamados registros secundarios. El límite de profundidad de anidado máximo es de 15 niveles. Este límite es independiente de si los RECORDs son escalares o basados en arrays (repetidos).

Esquema de ejemplo

El siguiente ejemplo presenta datos anidados y repetidos de muestra. Esta tabla contiene información sobre las personas. Consta de los siguientes campos:

• id
• first_name
• last_name
• dob (fecha de nacimiento)
• addresses (un campo repetido y anidado)
  • addresses.status (actual o anterior)
  • addresses.address
  • addresses.city
  • addresses.state
  • addresses.zip
  • addresses.numberOfYears (años en la dirección)

El archivo de datos JSON debería ser similar a lo que se muestra a continuación. Observa que la columna de direcciones contiene un arreglo de valores (indicado por [ ]). Las múltiples direcciones en el arreglo son los datos repetidos. Los múltiples campos dentro de cada dirección son los datos anidados.

{"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"}]}

El esquema para esta tabla se ve de la siguiente manera:

[
    {
        "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"
            }
        ]
    }
]

Especifica las columnas anidadas y repetidas en el ejemplo

Console

Para especificar la columna anidada y repetida addresses en Cloud Console, haz lo siguiente:

1. En Cloud Console, abre la página de BigQuery.

   Ir a BigQuery

2. En el panel Explorador, expande tu proyecto y selecciona un conjunto de datos.

3. En el panel de detalles, haz clic en Crear tabla (Create table).

4. En la página Crear tabla:

   • En Fuente, selecciona Tabla vacía.
   • En Destino, elige el conjunto de datos y, luego, ingresa el nombre de la tabla en el campo Nombre de la tabla.

   • En Schema (Esquema), agrega un campo:

     • En el campo Name (Nombre), escribe addresses.
     • En Tipo, selecciona RECORD.

     • En Modo, selecciona REPEATED.

       

     • A la derecha de addresses, haz clic en el ícono de signo más para agregar un campo anidado.

       

       • En el campo Name (Nombre), escribe status. Observa que el campo ya está propagado con addresses., lo que indica que es un campo anidado.
       • En Tipo, selecciona STRING.

       • En Modo, deja el valor configurado como NULLABLE.

         

       • Repite estos pasos para agregar address (STRING NULLABLE), city (STRING NULLABLE), state (STRING NULLABLE), zip (STRING NULLABLE) y numberOfYears (STRING NULLABLE).

   • Como alternativa, haz clic en Editar como texto (Edit as text) y especifica el esquema como un arreglo JSON.

bq

Para especificar la columna anidada y repetida addresses en un archivo de esquema JSON, usa un editor de texto a fin de crear un archivo nuevo. Pega la definición de esquema de ejemplo que se muestra arriba.

Después de crear tu archivo de esquema JSON, puedes proporcionarlo a través de la herramienta de línea de comandos de bq. Para obtener más información, consulta Usa un archivo de esquema JSON.

Go

Antes de probar este ejemplo, sigue las instrucciones de configuración para Go incluidas en la guía de inicio rápido de BigQuery sobre cómo usar bibliotecas cliente. Si deseas obtener más información, consulta la documentación de referencia de la API de Go de BigQuery.

Ver en GitHubComentarios

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

Antes de probar este ejemplo, sigue las instrucciones de configuración para Java incluidas en la guía de inicio rápido de BigQuery sobre cómo usar bibliotecas cliente. Para obtener más información, consulta la documentación de referencia de la API de BigQuery para Java.

Ver en GitHubComentarios

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

Antes de probar este ejemplo, sigue las instrucciones de configuración para Node.js incluidas en la guía de inicio rápido de BigQuery sobre cómo usar bibliotecas cliente. Para obtener más información, consulta la Documentación de referencia de la API de Node.js de BigQuery.

Ver en GitHubComentarios

// 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

Antes de probar esta muestra, sigue las instrucciones de configuración para Python incluidas en la guía de inicio rápido de BigQuery sobre cómo usar bibliotecas cliente. Si deseas obtener más información, consulta la documentación de referencia de la API de Python de BigQuery.

Ver en GitHubComentarios

# from google.cloud import bigquery
# client = bigquery.Client()
# project = client.project
# dataset_ref = bigquery.DatasetReference(project, '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))

Modifica columnas anidadas y repetidas

Después de que agregas una columna anidada o una columna anidada y repetida a la definición del esquema de una tabla, puedes modificarla como lo harías con cualquier otro tipo de columna. BigQuery admite de manera nativa varios cambios de esquema, como agregar un nuevo campo anidado a un registro o relajar el modo de un campo anidado. Para obtener más información, consulta Modifica esquemas de tablas.

Además, puedes modificar de manera manual una definición de esquema que incluya columnas anidadas y repetidas. Para obtener más información, consulta Cambia esquemas de tablas manualmente.

Cuándo usar columnas anidadas y repetidas

BigQuery tiene un mejor rendimiento cuando tus datos están desnormalizados. En vez de conservar un esquema relacional como un esquema estrella o copo de nieve, desnormaliza tus datos y aprovecha las columnas anidadas o repetidas. Estas columnas anidadas o repetidas pueden mantener relaciones sin el impacto en el rendimiento que tiene preservar un esquema relacional (normalizado).

Por ejemplo, es probable que una base de datos relacional usada para rastrear los libros de la biblioteca mantenga toda la información del autor en una tabla separada. Una clave como author_id se usaría para vincular el libro con los autores.

En BigQuery, puedes preservar la relación entre el libro y el autor sin crear una tabla de autor separada. En su lugar, creas una columna de autor y anidas campos dentro de ella, como el nombre, el apellido, la fecha de nacimiento del autor, etcétera. Si un libro tiene varios autores, puedes hacer que la columna de autor anidada se repita.

BigQuery admite la carga de datos anidados y repetidos desde formatos fuente compatibles con esquemas basados en objetos, como archivos JSON y Avro, y archivos de exportación de Firestore y Datastore.

Seguridad de las tablas

Para controlar el acceso a las tablas en BigQuery, consulta Introducción a los controles de acceso a tablas.

Próximos pasos

• Para insertar y actualizar filas con columnas anidadas y repetidas, consulta Sintaxis del lenguaje de manipulación de datos.