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 de formatos de origem compatíveis com esquemas baseados em objeto. Isso inclui arquivos JSON e Avro, bem como arquivos de exportação do Cloud Firestore e do Cloud 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. É usada uma chave como author_id 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 STRUCT e pode ser acessado como um STRUCT em 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 aninhados (tipos RECORD).
O BigQuery é compatível com colunas do tipo STRUCT (ou RECORD). O STRUCT é um tipo complexo que pode ser usado para representar um objeto com 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 exemplo a seguir. 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 GCP:

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

  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.

      Criar origem da tabela

    • Em Destino, escolha um conjunto de dados e preencha o campo Nome da tabela.

    • Em Esquema, adicione um campo:

      • No campo Nome, insira 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, insira status. O campo já vem preenchido com addresses. para indicar que ele é 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 clássica da Web do BigQuery, siga as etapas abaixo:

  1. Acesse a IU da Web do BigQuery.

    Acessar 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 selecione 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, insira addresses.
      • Em Tipo, selecione RECORD.
      • Em Modo, selecione REPEATED.

      • À direita de RECORD, clique no ícone de adição Ícone "Mais" para incluir um campo aninhado.

        • No campo Nome, insira status. O campo já vem preenchido com addresses. para indicar que ele é 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 é igual a este:

Campo aninhado na guia Esquema

CLI

Para especificar a coluna addresses aninhada e repetida em um arquivo de esquema JSON, use um editor de texto para inserir 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"
            }
        ]
    }
]

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. 

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

Esta página foi útil? Conte sua opinião sobre:

Enviar comentários sobre…

Esta página
Comentários sobre a documentação
BigQuery
Comentários sobre o produto
Precisa de ajuda? Acesse nossa página de suporte.