Usar campos aninhados e repetidos

O BigQuery pode ser usado com muitos métodos diferentes de modelagem de dados e geralmente oferece alto desempenho em muitas metodologias de modelo de dados. Para ajustar ainda mais um modelo de dados de desempenho, um método que você pode considerar é a desnormalização de dados, que significa adicionar colunas de dados a uma única tabela para reduzir ou remover mesclagens de tabelas.

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 como REPEATED 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ção ARRAY no GoogleSQL ao consultar os dados repetidos.

  • Dados aninhados e repetidos (ARRAY de STRUCTs)

    • 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 STRUCTs 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:

Saída de consulta com dados não aninhados

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ícios

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.

  1. 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`
);

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

  3. Crie uma segunda tabela com dados idênticos que criem um campo comments usando um tipo STRUCT para armazenar os dados post_id e creation_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
);

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

  5. Exclua as tabelas stackoverflow e stackoverflow_nested quando não precisar mais delas.