Cómo especificar 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. 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 del 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"
            }
        ]
    }
]

Cómo especificar las columnas anidadas y repetidas en el ejemplo

IU web

Para especificar la columna addresses anidada y repetida en la IU web de BigQuery:

  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. El proceso de carga de datos es el mismo que el proceso para crear una tabla vacía.

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

    • Para los Datos fuente, 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 Schema (Esquema):

      • En el campo Name (Nombre), escribe addresses.
      • En Type (Tipo), selecciona RECORD.
      • En Mode (Modo), selecciona REPEATED.
      • A la derecha de RECORD, haz clic en el signo más signo más para agregar un campo anidado.

        • En el campo Name (Nombre), escribe status. Observa que el campo está previamente propagado con addresses., lo que indica que es un campo anidado.
        • En Type (Tipo), selecciona STRING.
        • En Mode (Modo), 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 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 se ve de la siguiente manera:

Campo anidado en la pestaña Esquema

Línea de comandos

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 esta muestra, sigue las instrucciones de configuración para Go que se encuentran en la Guía de inicio rápido de BigQuery con bibliotecas cliente. Para obtener más información, consulta la documentación de referencia de la API de BigQuery para Go.

// 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 esta muestra, sigue las instrucciones de configuración de Python que se encuentran 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 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))

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 Cómo modificar los esquemas de una tabla.

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 Cómo cambiar de manera manual los esquemas de una tabla.

¿Te ha resultado útil esta página? Enviar comentarios:

Enviar comentarios sobre...

Si necesitas ayuda, visita nuestra página de asistencia.