Especificar colunas aninhadas e repetidas em esquemas de tabelas

Nesta página, você verá como definir um esquema de tabela com colunas aninhadas e repetidas no BigQuery. Para ter uma visão geral dos esquemas de tabelas, consulte Como especificar um esquema.

Definir colunas aninhadas e repetidas

Para criar uma coluna com dados aninhados, defina o tipo de dados da coluna como RECORD no esquema. Um RECORD pode ser acessado como um tipo STRUCT no GoogleSQL. Um STRUCT é um contêiner de campos ordenados.

Para criar uma coluna com dados repetidos, defina o modo da coluna como REPEATED no esquema. Um campo repetido pode ser acessado como um tipo ARRAY no GoogleSQL.

Uma coluna RECORD pode ter o modo REPEATED, que é representado como uma matriz de tipos STRUCT. Além disso, um campo dentro de um registro pode ser repetido, que é representado como um STRUCT que contém um ARRAY. Uma matriz não pode conter outra matriz diretamente. Para mais informações, consulte Como declarar um tipo ARRAY.

Limitações

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

Um esquema não pode conter mais de 15 níveis de tipos de RECORD aninhados.
Colunas do tipo RECORD podem conter tipos RECORD aninhados, também chamados de registros filhos. O limite máximo de profundidade aninhado é de 15 níveis. Esse limite não depende do fato que os RECORDs sejam escalares ou baseados em matrizes (repetidos).

O tipo de RECORD é incompatível com UNION, INTERSECT, EXCEPT DISTINCT e SELECT DISTINCT.

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 o seguinte:

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

Para criar uma nova tabela com as colunas aninhadas e repetidas anteriores, selecione uma das seguintes opções:

Console

Especifique a coluna addresses aninhada e repetida:

  1. No Console do Google Cloud, abra a página do BigQuery.

    Ir para o BigQuery

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

  3. No painel de detalhes, clique em Criar tabela.

  4. Na página Criar tabela, especifique os seguintes detalhes:

    • Em Origem, no campo Criar tabela de, selecione Tabela vazia.

    • Na seção Destino, especifique os seguintes campos:

      • Em Conjunto de dados, selecione o conjunto de dados em que você quer criar a tabela.
      • Em Tabela, insira o nome da tabela que você quer criar.

    • Em Esquema, clique em Adicionar campo e insira o seguinte esquema de tabela:

      • Em Nome do campo, insira addresses.
      • Em Tipo, selecione RECORD.
      • Em Modo, escolha REPEATED. Esquema de endereços

      • Especifique os seguintes campos para um campo aninhado:

        • No campo Nome do campo, insira status.
        • Em Tipo, escolha STRING.
        • Em Modo, configure o valor como NULLABLE.

        • Clique em Adicionar campo para adicionar os seguintes campos:

          Nome do campo Tipo Modo
          address STRING NULLABLE
          city STRING NULLABLE
          state STRING NULLABLE
          zip STRING NULLABLE
          numberOfYears STRING NULLABLE

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

SQL

Use a instrução CREATE TABLE. Especifique o esquema usando a opção coluna.

  1. No Console do Google Cloud, acesse a página BigQuery.

    Ir para o BigQuery

  2. No editor de consultas, digite a seguinte instrução:

    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. Clique em Executar.

Para mais informações sobre como executar consultas, acesse Executar uma consulta interativa.

bq

Para especificar a coluna addresses aninhada e repetida em um arquivo de esquema JSON, use um editor de texto para criar um novo arquivo. Cole a definição de esquema de exemplo mostrada acima.

Depois de criar o arquivo de esquema JSON, forneça-o pela ferramenta de linha de comando bq. Para mais informações, consulte Como usar um arquivo de esquema JSON.

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 em Go.

Para autenticar no BigQuery, configure o Application Default Credentials. Para mais informações, acesse Configurar a autenticação para bibliotecas de 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 testar esta amostra, siga as instruções de configuração do 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.

Para autenticar no BigQuery, configure o Application Default Credentials. Para mais informações, acesse Configurar a autenticação para bibliotecas de 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 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 em Node.js.

Para autenticar no BigQuery, configure o Application Default Credentials. Para mais informações, acesse Configurar a autenticação para bibliotecas de 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 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 em Python.

Para autenticar no BigQuery, configure o Application Default Credentials. Para mais informações, acesse Configurar a autenticação para bibliotecas de 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}.")

Inserir dados em colunas aninhadas no exemplo

Use as consultas a seguir para inserir registros de dados aninhados em tabelas que tenham colunas com tipo de dados RECORD.

Exemplo 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")])

Exemplo 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")])

Consultar colunas aninhadas e repetidas

Para selecionar o valor de uma ARRAY em uma posição específica, use um operador de subscrito da matriz. Para acessar elementos em um STRUCT, use o operador de ponto. O exemplo a seguir seleciona o nome, o sobrenome e o primeiro endereço listados no campo addresses:

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

O resultado é o seguinte:

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

Para extrair todos os elementos de um ARRAY, use o operador UNNEST com um CROSS JOIN. O exemplo a seguir seleciona o nome, o sobrenome, o endereço e o estado de todos os endereços não localizados em Nova York:

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

O resultado é o seguinte:

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

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.

Quando usar 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.

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.

Suponha que você tenha a tabela mydataset.books a seguir:

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

Você também tem a tabela a seguir, mydataset.authors, com informações completas de 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    |
+-----------+-------------+---------------+

Quando as tabelas são grandes, elas podem consumir muitos recursos regularmente. Dependendo da situação, pode ser vantajoso criar uma única tabela com todas as informações:

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

A tabela terá esta aparência:

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

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.

Eliminar a duplicação de registros duplicados em uma tabela

A consulta a seguir usa a função row_number() para identificar registros duplicados que têm os mesmos valores para last_name e first_name nos exemplos usados e os classifica 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
)

Segurança de tabelas

Para controlar o acesso a tabelas no BigQuery, consulte Introdução aos controles de acesso a tabelas.

A seguir