Usar campos aninhados e repetidos
O BigQuery pode ser usado com muitos métodos diferentes de modelagem de dados e, geralmente, fornece alto desempenho em muitas metodologias de modelo de dados. Para ajustar ainda mais o desempenho de um modelo de dados, considere a desnormalização de dados, ou seja, adicionar colunas de dados a uma única tabela para reduzir ou remover as junções.
Prática recomendada: use campos aninhados e repetidos para desnormalizar o armazenamento de dados e melhorar o desempenho da consulta.
A desnormalização é uma estratégia comum para aumentar o desempenho de leitura em conjuntos de dados relacionais que foram normalizados anteriormente. A maneira recomendada de desnormalizar dados no BigQuery é usar campos aninhados e repetidos. É melhor usar essa estratégia quando os relacionamentos são hierárquicos e geralmente consultados juntos, como nos relacionamentos pai-filho.
A economia de armazenamento pelo uso de dados normalizados tem menos efeito nos sistemas modernos. Os ganhos de desempenho da desnormalização de dados compensam os aumentos nos custos de armazenamento. As junções exigem coordenação de dados (largura de banda de comunicação). A desnormalização localiza os dados em slots individuais. Dessa forma, a execução pode ser feita em paralelo.
Para manter vínculos enquanto desnormaliza os dados, use campos aninhados e repetidos em vez de nivelar completamente os dados. Quando os dados relacionais estão completamente nivelados, a comunicação de rede (embaralhamento) pode afetar negativamente o desempenho da consulta.
Por exemplo, desnormalizar um esquema de pedidos sem usar campos repetidos e aninhados pode exigir que você agrupe os dados por um campo como order_id
(quando há um vínculo de um para muitos). Devido ao embaralhamento envolvido, o agrupamento dos dados é menos eficiente do que a desnormalização dos dados usando campos aninhados e repetidos.
Em algumas circunstâncias, a desnormalização dos dados e o uso de campos aninhados e repetidos não aumentam o desempenho. Por exemplo, os esquemas em estrela normalmente são otimizados para análise e, como resultado, o desempenho pode não ser significativamente diferente se você tentar desnormalizar ainda mais.
Como usar campos aninhados e repetidos
O BigQuery não exige uma desnormalização totalmente uniforme. Você pode usar campos aninhados e repetidos para manter relacionamentos.
Dados de aninhamento (
STRUCT
)- O aninhamento de dados permite representar entidades estrangeiras in-line.
- Consultar dados aninhados usa uma sintaxe "ponto" para referenciar campos de folha, algo semelhante à sintaxe de uso de uma junção.
- Os dados aninhados são representados como um
tipo
STRUCT
no GoogleSQL.
Dados repetidos (
ARRAY
)- A criação de um campo do tipo
RECORD
com o modo definido comoREPEATED
permite manter um relacionamento de um para muitos in-line, desde que o relacionamento não seja alta cardinalidade. - Com dados repetidos, a reprodução aleatória não é necessária.
- Os dados repetidos são representados como
ARRAY
. É possível usar uma funçãoARRAY
no GoogleSQL ao consultar os dados repetidos.
- A criação de um campo do tipo
Dados aninhados e repetidos (
ARRAY
deSTRUCT
s)- O aninhamento e a repetição se complementam.
- Por exemplo, em uma tabela de registros de transação, seria possível incluir uma matriz de
STRUCT
s de item de linha.
Saiba mais em Especificar colunas aninhadas e repetidas em esquemas de tabelas.
Para mais informações sobre desnormalização de dados, consulte Desnormalização.
Exemplo
Considere uma tabela Orders
com uma linha para cada item de linha vendido:
Order_Id | Item_Name |
---|---|
001 | A1 |
001 | B1 |
002 | A1 |
002 | C1 |
Se quiser analisar os dados dessa tabela, você precisará usar uma cláusula GROUP BY
, semelhante ao seguinte:
SELECT COUNT (Item_Name) FROM Orders GROUP BY Order_Id;
A cláusula GROUP BY
envolve mais sobrecarga de computação, mas isso pode ser
evitado ao aninhar dados repetidos. É possível evitar o uso de uma cláusula GROUP BY
criando uma tabela com uma ordem por linha, em que os itens de linha de pedido ficam em um
campo aninhado:
Order_Id | Item_Name |
---|---|
001 |
A1 B1 |
002 |
A1 C1 |
No BigQuery, você normalmente especifica um esquema aninhado como um ARRAY
de objetos STRUCT
. Use o
operador UNNEST
para nivelar os dados aninhados,
conforme mostrado na consulta a seguir:
SELECT * FROM UNNEST( [ STRUCT('001' AS Order_Id, ['A1', 'B1'] AS Item_Name), STRUCT('002' AS Order_Id, ['A1', 'C1'] AS Item_Name) ] );
Esta consulta gera resultados semelhantes aos seguintes:
Se esses dados não fossem aninhados, seria possível ter várias linhas para cada pedido, uma para cada item vendido nesse pedido, o que resultaria em uma tabela grande e uma operação GROUP BY
dispendiosa.
Exercício
Para ver a diferença de desempenho nas consultas que usam campos aninhados em comparação com as que não usam, siga as etapas desta seção.
Crie uma tabela com base no conjunto de dados público
bigquery-public-data.stackoverflow.comments
:CREATE OR REPLACE TABLE `PROJECT.DATASET.stackoverflow` AS ( SELECT user_id, post_id, creation_date FROM `bigquery-public-data.stackoverflow.comments` );
Usando a tabela
stackoverflow
, execute a seguinte consulta para ver o comentário mais recente de cada usuário:SELECT user_id, ARRAY_AGG(STRUCT(post_id, creation_date AS earliest_comment) ORDER BY creation_date ASC LIMIT 1)[OFFSET(0)].* FROM `PROJECT.DATASET.stackoverflow` GROUP BY user_id ORDER BY user_id ASC;
Essa consulta leva cerca de 25 segundos para ser executada e processa 1,88 GB de dados.
Crie uma segunda tabela com dados idênticos que criem um campo
comments
usando um tipoSTRUCT
para armazenar os dadospost_id
ecreation_date
, em vez de dois campos individuais:CREATE OR REPLACE TABLE `PROJECT.DATASET.stackoverflow_nested` AS ( SELECT user_id, ARRAY_AGG(STRUCT(post_id, creation_date) ORDER BY creation_date ASC) AS comments FROM `bigquery-public-data.stackoverflow.comments` GROUP BY user_id );
Usando a tabela
stackoverflow_nested
, execute a seguinte consulta para ver o comentário mais recente de cada usuário:SELECT user_id, (SELECT AS STRUCT post_id, creation_date as earliest_comment FROM UNNEST(comments) ORDER BY creation_date ASC LIMIT 1).* FROM `PROJECT.DATASET.stackoverflow_nested` ORDER BY user_id ASC;
Essa consulta leva cerca de 10 segundos para ser executada e processa 1,28 GB de dados.
Exclua as tabelas
stackoverflow
estackoverflow_nested
quando não precisar mais delas.