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

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

El tipo RECORD no es compatible con UNION, INTERSECT, EXCEPT DISTINCT y SELECT DISTINCT.

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

Para crear una tabla nueva con las columnas anidadas y repetidas anteriores, selecciona una de las siguientes opciones:

Console

Especifica la columna anidada y repetida addresses:

1. En la consola de Google Cloud, 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.

4. En la página Crear tabla, especifica los siguientes detalles:

   • En Fuente, en el campo Crear tabla desde, selecciona Tabla vacía.

   • En la sección Destino, especifica los siguientes campos:

     • En Conjunto de datos, selecciona el conjunto de datos en el que deseas crear la tabla.
     • En Tabla, ingresa el nombre de la tabla que deseas crear.

   • En Esquema, haz clic en Agregar campo y, luego, ingresa el siguiente esquema de la tabla:

     • En Nombre del campo (Field name), ingresa addresses.
     • En Tipo (Type), selecciona REGISTRO (RECORD).
     • En Modo (Mode), selecciona REPEATED.

     • Especifica los siguientes campos para un campo anidado:

       • En el campo Nombre del campo (Field name), ingresa status.
       • En Tipo (Type), selecciona STRING.
       • En Modo (Mode), deja el valor configurado como NULLABLE.

       • Haz clic en Agregar campo para agregar los siguientes campos:

         Nombre del campo	Tipo	Modo
         address	STRING	NULLABLE
         city	STRING	NULLABLE
         state	STRING	NULLABLE
         zip	STRING	NULLABLE
         numberOfYears	STRING	NULLABLE

       Como alternativa, haz clic en Editar como texto y especifica el esquema como un arreglo JSON.

SQL

Usa la sentencia CREATE TABLE. Especifica el esquema con la opción de columna:

1. En la consola de Google Cloud, ve a la página de BigQuery.

   Ir a BigQuery

2. En el editor de consultas, escribe la siguiente sentencia:

   CREATE TABLE IF NOT EXISTS mydataset.mytable (
     id STRING,
     first_name STRING,
     last_name STRING,
     dob DATE,
     addresses
       ARRAY<
         STRUCT<
           status STRING,
           address STRING,
           city STRING,
           state STRING,
           zip STRING,
           numberOfYears STRING>>
   ) OPTIONS (
       description = 'Example name and addresses table');

3. Haz clic en Ejecutar.

Si deseas obtener información sobre cómo ejecutar consultas, visita Ejecuta una consulta interactiva.

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. Para obtener más información, consulta la documentación de referencia de la API de BigQuery para Go.

Para autenticarte en BigQuery, configura las credenciales predeterminadas de la aplicación. Si deseas obtener más información, consulta Configura la autenticación para bibliotecas cliente.

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.

Para autenticarte en BigQuery, configura las credenciales predeterminadas de la aplicación. Si deseas obtener más información, consulta Configura la autenticación para bibliotecas cliente.

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 BigQuery para Node.js.

Para autenticarte en BigQuery, configura las credenciales predeterminadas de la aplicación. Si deseas obtener más información, consulta Configura la autenticación para bibliotecas cliente.

// 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 sobre cómo usar bibliotecas cliente. Para obtener más información, consulta la documentación de referencia de la API de BigQuery para Python.

Para autenticarte en BigQuery, configura las credenciales predeterminadas de la aplicación. Si deseas obtener más información, consulta Configura la autenticación para bibliotecas cliente.

from google.cloud import bigquery

client = bigquery.Client()

# TODO(dev): Change table_id to the full name of the table you want to create.
table_id = "your-project.your_dataset.your_table_name"

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 = bigquery.Table(table_id, schema=schema)
table = client.create_table(table)  # API request

print(f"Created table {table.project}.{table.dataset_id}.{table.table_id}.")

Inserta datos en columnas anidadas en el ejemplo

Usa las siguientes consultas para insertar registros de datos anidados en tablas que tengan columnas de tipo de datos RECORD.

Ejemplo 1

INSERT INTO mydataset.mytable (id,
first_name,
last_name,
dob,
addresses) values ("1","Johnny","Dawn","1969-01-22",
    ARRAY<
      STRUCT<
        status STRING,
        address STRING,
        city STRING,
        state STRING,
        zip STRING,
        numberOfYears STRING>>
      [("current","123 First Avenue","Seattle","WA","11111","1")])

Ejemplo 2

INSERT INTO mydataset.mytable (id,
first_name,
last_name,
dob,
addresses) values ("1","Johnny","Dawn","1969-01-22",[("current","123 First Avenue","Seattle","WA","11111","1")])

Consulta columnas anidadas y repetidas

Para seleccionar el valor de un ARRAY en una posición específica, usa un operador de subíndice del arreglo. Para acceder a los elementos en una STRUCT, usa el operador de puntos. En el siguiente ejemplo, se selecciona el nombre, el apellido y la primera dirección que aparecen en el campo addresses:

SELECT
  first_name,
  last_name,
  addresses[offset(0)].address
FROM
  mydataset.mytable;

El resultado es el siguiente:

+------------+-----------+------------------+
| first_name | last_name | address          |
+------------+-----------+------------------+
| John       | Doe       | 123 First Avenue |
| Jane       | Doe       | 789 Any Avenue   |
+------------+-----------+------------------+

Para extraer todos los elementos de un ARRAY, usa el operador UNNEST con una CROSS JOIN. En el siguiente ejemplo, se selecciona el nombre, el apellido, la dirección y el estado de todas las direcciones que no se encuentran en Nueva York:

SELECT
  first_name,
  last_name,
  a.address,
  a.state
FROM
  mydataset.mytable CROSS JOIN UNNEST(addresses) AS a
WHERE
  a.state != 'NY';

El resultado es el siguiente:

+------------+-----------+------------------+-------+
| first_name | last_name | address          | state |
+------------+-----------+------------------+-------+
| John       | Doe       | 123 First Avenue | WA    |
| John       | Doe       | 456 Main Street  | OR    |
| Jane       | Doe       | 321 Main Street  | NJ    |
+------------+-----------+------------------+-------+

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.

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.

Supongamos que tienes la siguiente tabla mydataset.books:

+------------------+------------+-----------+
| title            | author_ids | num_pages |
+------------------+------------+-----------+
| Example Book One | [123, 789] | 487       |
| Example Book Two | [456]      | 89        |
+------------------+------------+-----------+

También tienes la siguiente tabla, mydataset.authors, con información completa para cada ID de autor:

+-----------+-------------+---------------+
| author_id | author_name | date_of_birth |
+-----------+-------------+---------------+
| 123       | Alex        | 01-01-1960    |
| 456       | Rosario     | 01-01-1970    |
| 789       | Kim         | 01-01-1980    |
+-----------+-------------+---------------+

Si las tablas son grandes, puede ser intensivo con los recursos unirse a ellas con regularidad. Según tu situación, podría ser beneficioso crear una sola tabla que contenga toda la información:

CREATE TABLE mydataset.denormalized_books(
  title STRING,
  authors ARRAY<STRUCT<id INT64, name STRING, date_of_birth STRING>>,
  num_pages INT64)
AS (
  SELECT
    title,
    ARRAY_AGG(STRUCT(author_id, author_name, date_of_birth)) AS authors,
    ANY_VALUE(num_pages)
  FROM
    mydataset.books,
    UNNEST(author_ids) id
  JOIN
    mydataset.authors
    ON
      id = author_id
  GROUP BY
    title
);

La tabla resultante se ve de la siguiente manera:

+------------------+-------------------------------+-----------+
| title            | authors                       | num_pages |
+------------------+-------------------------------+-----------+
| Example Book One | [{123, Alex, 01-01-1960},     | 487       |
|                  |  {789, Kim, 01-01-1980}]      |           |
| Example Book Two | [{456, Rosario, 01-01-1970}]  | 89        |
+------------------+-------------------------------+-----------+

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.

Anula el duplicado de registros duplicados en una tabla

En la siguiente consulta, se usa la función row_number() para identificar los registros duplicados que tienen los mismos valores para last_name y first_name en los ejemplos que se usaron, y se los ordena por dob:

CREATE OR REPLACE TABLE mydataset.mytable AS (
  SELECT * except(row_num) FROM (
    SELECT *,
    row_number() over (partition by last_name, first_name order by dob) row_num
    FROM
    mydataset.mytable) temp_table
  WHERE row_num=1
)

Seguridad de las tablas

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

¿Qué sigue?

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