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
STRUCT
s aninhadas (tiposRECORD
). - O BigQuery é compatível com colunas do tipo
STRUCT
(ouRECORD
). UmSTRUCT
é um tipo complexo que pode ser usado para representar um objeto que tem várias colunas filho. Em uma colunaSTRUCT
, também é possível definir uma ou mais colunas filho como tiposSTRUCT
(mencionados comoSTRUCT
s aninhados ou incorporados). Quando você aninhaSTRUCTS
, o BigQuery impõe um limite de profundidade de 15 níveis. Esse limite não depende do fato de osSTRUCT
s 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:
Abra a página do BigQuery no Console do Cloud.
No painel Explorer, expanda o projeto e selecione um conjunto de dados.
Na visualização de detalhes, clique em Criar tabela.
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 comaddresses.
, 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
) enumberOfYears
(NULLABLE
STRING
).
- No campo Nome, digite
- No campo Nome, digite
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.
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.
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.
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.
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.