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 no Console do Cloud 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 página do BigQuery no Console do Cloud.

   Acesse a página do BigQuery

2. No painel Explorer, expanda o projeto e selecione um conjunto de dados.

   Observação: a experiência padrão é a versão de visualização do Console do Cloud. Se você clicou em Ocultar recursos da versão de visualização para acessar a versão de disponibilidade geral do Console do Cloud, faça o seguinte: na seção Recursos do painel de navegação, expanda o projeto e selecione um conjunto de dados.

3. Na visualização de detalhes, clique em Criar tabela.

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

       

     • À direita de addresses, clique no ícone de adição 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, configure 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).

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

bq

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 essa amostra, siga as instruções de configuração para 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 em Go.

Ver no GitHub Feedback

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 testar essa amostra, siga as instruções de configuração para Java 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 em Java.

Ver no GitHub Feedback

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 testar essa amostra, siga as instruções de configuração para 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.

Ver no GitHub Feedback

// 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 essa amostra, siga as instruções de configuração para 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 em Python.

Ver no GitHub Feedback

# from google.cloud import bigquery
# client = bigquery.Client()
# project = client.project
# dataset_ref = bigquery.DatasetReference(project, '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.