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

Puedes especificar datos anidados o anidados y repetidos en la IU o en un archivo de esquema JSON. Para especificar columnas anidadas o repetidas y anidadas, debes usar el tipo de datos RECORD (STRUCT).

Especifica columnas anidadas y repetidas

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.

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.

Para crear una columna con datos anidados y repetidos, establece el tipo de datos de la columna como RECORD. Un RECORD se almacena como STRUCT y se puede acceder a él como STRUCT en SQL estándar. Un STRUCT es un contenedor de campos ordenados, cada uno con un tipo (obligatorio) y un nombre (opcional). Para anidar la columna, agrega campos secundarios al RECORD (superior). Para repetir la columna, cambia el modo a REPEATED.

Limitaciones

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

Cuando cargas datos anidados y repetidos, tu esquema no puede contener más de 15 niveles de STRUCT anidados (tipos RECORD).
BigQuery admite columnas del tipo STRUCT (o RECORD). Un STRUCT es un tipo complejo que puede usarse para representar un objeto que tiene varias columnas secundarias. En una columna STRUCT también puedes definir una o más de las columnas secundarias como tipo STRUCT (denominadas STRUCT anidados o incorporados). Cuando anidas STRUCTS, BigQuery aplica un límite de profundidad de anidado de 15 niveles. El límite de profundidad de anidado es independiente de si los STRUCT son escalares o basados en arreglos.

Ejemplo

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 vería como el siguiente:

[
    {
        "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. Abre la IU web de BigQuery en Cloud Console.
    Ir a Cloud Console

  2. En el panel de navegación, en la sección Recursos, expande tu proyecto y selecciona un conjunto de datos. Haz clic en Crear tabla.

    Crear tabla

  3. En la página Crear tabla, completa estos pasos:

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

        Esquema de direcciones

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

        Botón de anidación

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

          Esquema de estado

        • 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 Edit as text (Editar como texto) y especifica el esquema como un arreglo JSON.

IU clásica

Para especificar la columna anidada y repetida addresses en la IU web clásica de BigQuery, haz lo siguiente:

  1. Ve a la IU web de BigQuery.

    Ir a la IU web de BigQuery

  2. Haz clic en el ícono de flecha hacia abajo ícono de flecha hacia abajo junto al nombre de tu conjunto de datos en el menú de navegación y haz clic en Crear tabla nueva.

  3. En la página Crear tabla, completa estos pasos:

    • En Datos de origen, haz clic en Crear desde la fuente.
    • Para la Tabla de destino, selecciona tu conjunto de datos y, luego, ingresa el nombre de la tabla en el campo Nombre de la tabla de destino.
    • En Schema (Esquema):

      • En el campo Name (Nombre), escribe addresses.
      • En Tipo, selecciona RECORD.
      • En Modo, selecciona REPEATED.
      • A la derecha de RECORD, haz clic en el ícono de signo más í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).

        Campo anidado en la IU

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

Cuando examinas el esquema en la IU web de BigQuery, el campo addresses tiene el siguiente aspecto:

Campo anidado en la pestaña Esquema

CLI

Para especificar la columna anidada y repetida addresses en un archivo de esquema JSON, ingresa lo siguiente mediante un editor de texto:

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

Después de crear tu archivo de esquema JSON, puedes suministrarlo a través de la línea de comandos.

Go

Antes de probar este ejemplo, sigue las instrucciones de configuración de Go incluidas en la guía de inicio rápido de BigQuery con bibliotecas cliente. A fin de obtener más información, consulta la documentación de referencia de la API de BigQuery para 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)
	}

	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
}

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 con bibliotecas cliente. A fin de obtener más información, consulta la documentación de referencia de la API de BigQuery para 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

Antes de probar este ejemplo, sigue las instrucciones de configuración para Python incluidas en la guía de inicio rápido de BigQuery con bibliotecas cliente. Si deseas obtener más información, consulta la documentación de referencia de la API de BigQuery para 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))

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 de forma manual.

¿Te sirvió esta página? Envíanos tu opinión:

Enviar comentarios sobre…

¿Necesitas ayuda? Visita nuestra página de asistencia.