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 las columnas anidadas y repetidas, se debe utilizar el tipo de datos RECORD (STRUCT).

Cómo especificar 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 Cloud Firestore y Cloud Datastore.

Por ejemplo, una base de datos relacional usada para rastrear los libros de la biblioteca probablemente mantendría toda la información del autor en una tabla separada. Se debería usar una clave como author_id 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, configura el tipo de datos de la columna en RECORD. Se almacena un RECORD como STRUCT, al cual se puede acceder como STRUCT en SQL estándar. STRUCT es un contenedor de campos ordenados, cada uno con un tipo (requerido) 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 del tipo STRUCT anidados (tipos RECORD).
BigQuery admite columnas de tipo STRUCT (o RECORD). STRUCT es un tipo complejo que puede utilizarse 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 de tipo anidadas o incrustadas). 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 las STRUCT son escalares o basadas 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 anidado y repetido)
    • 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 se vería como el siguiente. 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 de addresses anidadas y repetidas en GCP Console, haz lo siguiente:

  1. Abre la IU web de BigQuery en GCP Console.
    Ir a GCP 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 (Create table).

    Crear tabla

  3. En la página Crear tabla, haz lo siguiente:

    • 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 Esquema (Schema), agrega un campo:

      • En el campo Nombre (Name), escribe addresses.
      • En Tipo (Type), selecciona RECORD.
      • En Modo (Mode), 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 Nest

        • En el campo Nombre (Name), escribe status. Observa que el campo está previamente propagado con addresses., lo que indica que es un campo anidado.
        • En Tipo (Type), selecciona STRING.
        • En Modo (Mode), deja el valor configurado en NULLABLE.

          Esquema de estado

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

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

IU clásica

Para especificar la columna addresses anidada y repetida en la IU web 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, haz lo siguiente:

    • 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.
    • Para el Esquema, haz lo siguiente:

      • En el campo 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 signo más para agregar un campo anidado.

        • En el campo Nombre (Name), escribe status. Observa que el campo está previamente propagado con addresses., lo que indica que es un campo anidado.
        • En Tipo (Type), selecciona STRING.
        • En Modo (Mode), deja el valor configurado en NULLABLE.
        • Repite estos pasos para agregar address (NULLABLE STRING), city (NULLABLE STRING), state (NULLABLE STRING), zip (NULLABLE STRING) y numberOfYears (NULLABLE STRING).

        Campo anidado en la IU

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

Cuando examinas el esquema en la IU web de BigQuery, el campo addresses se ve de la siguiente manera:

Campo anidado en la pestaña Esquema

CLI

Para especificar la columna addresses anidada y repetida 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 Go de BigQuery.

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

Antes de probar este ejemplo, sigue las instrucciones de configuración de Python 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 Python de BigQuery.

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

Cómo modificar 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 modificar la columna 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.

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

Enviar comentarios sobre…

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