Como especificar colunas aninhadas e repetidas

O BigQuery funciona melhor quando os dados são desnormalizados. Em vez de preservar um esquema relacional, como em estrela ou em floco de neve, desnormalize os dados e aproveite as colunas aninhadas e repetidas. Elas se relacionam sem o impacto sobre o desempenho da preservação de um esquema relacional ou normalizado.

É possível especificar dados aninhados e repetidos ou apenas aninhados na IU ou em um arquivo de esquema JSON. Para especificar colunas desses tipos, use o tipo de dados RECORD (STRUCT).

Como especificar colunas aninhadas e repetidas

O BigQuery permite o carregamento de dados aninhados e repetidos a partir de formatos de origem que permitem esquemas baseados em objeto, como arquivos JSON, arquivos Avro e arquivos de exportação do Firestore e do Datastore.

Por exemplo, é provável que um banco de dados relacional usado para rastrear os livros de uma biblioteca mantenha todas as informações do autor em uma tabela separada. Uma chave como author_id seria usada para vincular o livro aos autores.

No BigQuery, é possível preservar a relação entre o livro e o autor sem criar uma tabela de autor separada. Na verdade, você cria uma coluna de autor e aninha os campos dentro dela como nome, sobrenome, data de nascimento e assim por diante. Se um livro tiver vários autores, será possível repetir essa coluna.

Para criar uma coluna com dados aninhados e repetidos, defina o tipo de dados da coluna como RECORD. Um RECORD é armazenado como um STRUCT e pode ser acessado como um STRUCT no SQL padrão. Um STRUCT é um contêiner de campos ordenados, cada um com um tipo (obrigatório) e um nome (opcional). Para aninhar a coluna, adicione campos filho ao RECORD (pai). Para repeti-la, altere o modo para REPEATED.

Limitações

Os esquemas aninhados e repetidos estão sujeitos às seguintes limitações:

Quando você carrega dados aninhados e repetidos, o esquema não pode conter mais de 15 níveis de STRUCTs aninhadas (tipos RECORD).
O BigQuery é compatível com colunas do tipo STRUCT (ou RECORD). Um STRUCT é um tipo complexo que pode ser usado para representar um objeto que tem várias colunas filho. Em uma coluna STRUCT, também é possível definir uma ou mais colunas filho como tipos STRUCT (mencionados como STRUCTs aninhados ou incorporados). Quando você aninha STRUCTS, o BigQuery impõe um limite de profundidade de 15 níveis. Esse limite não depende do fato de os STRUCTs serem escalares ou baseados em matrizes.

Exemplo

Esquema de exemplo

O exemplo a seguir exibe dados aninhados e repetidos de amostra. A tabela contém informações sobre pessoas. Ela consiste nos campos abaixo:

  • id
  • first_name
  • last_name
  • dob (data de nascimento)
  • addresses (um campo aninhado e repetido)
    • addresses.status (atual ou anterior)
    • addresses.address
    • addresses.city
    • addresses.state
    • addresses.zip
    • addresses.numberOfYears (anos no endereço)

O arquivo de dados JSON se parece com o seguinte exemplo. Observe que a coluna de endereços contém uma matriz de valores (indicada por [ ]). Os vários endereços na matriz são os dados repetidos. Os vários campos dentro de cada endereço são os dados aninhados.

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

O esquema dessa tabela se parece com este:

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

Como especificar colunas aninhadas e repetidas no exemplo

Console

Para especificar a coluna addresses aninhada e repetida no Console do Cloud:

  1. Abra a IU da Web do BigQuery no Console do Cloud.
    Acessar o Console do Cloud

  2. Na seção Recursos do painel de navegação, expanda o projeto e selecione um conjunto de dados. Clique em Criar tabela.

    Criar tabela

  3. Na página Criar tabela:

    • Em Origem, selecione Tabela vazia.
    • Em Destino, escolha um conjunto de dados e preencha o campo Nome da tabela.
    • Em Esquema, adicione um campo:

      • No campo Nome, digite addresses.
      • Em Tipo, selecione RECORD.
      • Em Modo, selecione REPEATED.

        Esquema de endereços

      • À direita de addresses, clique no ícone de adição para incluir um campo aninhado.

        Botão Nest

        • No campo Nome, digite status. Observe que o campo está pré-preenchido com addresses., indicando que é um campo aninhado.
        • Em Tipo, selecione STRING.
        • Em Modo, deixe o valor como NULLABLE.

          Esquema de status

        • Repita essas etapas para adicionar address (NULLABLE STRING), city (NULLABLE STRING), state (NULLABLE STRING), zip (NULLABLE STRING) e numberOfYears (NULLABLE STRING).

    • Se preferir, clique em Editar como texto e especifique o esquema como uma matriz JSON.

IU clássica

Para especificar a coluna addresses aninhada e repetida na IU da Web clássica do BigQuery:

  1. Acesse a IU da Web do BigQuery.

    Acesse a IU da Web do BigQuery

  2. Clique no ícone de seta para baixo ícone de seta para baixo ao lado do nome do conjunto de dados na navegação e clique em Criar nova tabela.

  3. Na página Criar tabela:

    • Em Dados de origem, clique em Criar a partir da origem.
    • Em Tabela de destino, escolha o conjunto de dados e insira o nome da tabela no campo Nome da tabela de destino.
    • Em Esquema:

      • No campo Nome, digite addresses.
      • Em Tipo, selecione RECORD.
      • Em Modo, selecione REPEATED.
      • À direita de RECORD, clique no ícone de adição Ícone para incluir um campo aninhado.

        • No campo Nome, digite status. Observe que o campo está pré-preenchido com addresses., indicando que é um campo aninhado.
        • Em Tipo, selecione STRING.
        • Em Modo, deixe o valor como NULLABLE.
        • Repita essas etapas para adicionar address (NULLABLE STRING), city (NULLABLE STRING), state (NULLABLE STRING), zip (NULLABLE STRING) e numberOfYears (NULLABLE STRING).

        Campo aninhado na IU

    • Se preferir, clique em Editar como texto e especifique o esquema como uma matriz JSON.

Quando você analisa o esquema na IU da Web do BigQuery, o campo addresses é semelhante ao seguinte:

Campo aninhado na guia Esquema

CLI

Para especificar a coluna addresses aninhada e repetida em um arquivo de esquema JSON, insira o seguinte usando um 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"
            }
        ]
    }
]

Depois de criar o arquivo de esquema JSON, é possível fornecê-lo por meio da linha de comando.

Go

Antes de testar esta amostra, siga as instruções de configuração do Go no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery 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 testar esta amostra, siga as instruções de configuração do Node.js no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery 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 testar esta amostra, siga as instruções de configuração do Python no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery 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))

Como modificar colunas aninhadas e repetidas

Depois de adicionar uma coluna aninhada ou uma aninhada e repetida à definição de esquema de uma tabela, é possível modificá-la como qualquer outro tipo de coluna. O BigQuery é compatível nativamente com várias alterações de esquema, como adicionar um novo campo aninhado a um registro ou relaxar o modo de um campo aninhado. Para saber mais, consulte Como modificar os esquemas das tabelas.

Além disso, é possível modificar manualmente uma definição de esquema que inclui colunas aninhadas e repetidas. Para mais informações, consulte Como alterar manualmente esquemas de tabelas.