Matrizes no BigQuery - Como melhorar o desempenho da consulta e otimizar o armazenamento

Nesta postagem, quero ilustrar as vantagens do uso de matrizes Bigquery para aproveitar o poder do BigQuery para casos de uso analítico em relação aos modelos de dados tradicionais.

Opções do DataModel para Armazéns

Normalmente, os data warehouses são uma extensão dos bancos de dados criados com recursos OLAP para atender às necessidades analíticas e de relatórios. Da perspectiva do modelo de dados, eles ainda imitam o RDBMS nas estruturas de tabela subjacentes. Para desempenho, as tabelas são desnormalizadas no armazém para reduzir a sobrecarga das junções da tabela. Essa é uma prática recomendada comprovada para reduzir o custo de junções com o armazenamento de dados redundantes.

Motivação

Com o BigQuery Arrays, podemos aproveitar essa vantagem para o próximo nível, ajustando o modelo de dados para reduzir o armazenamento redundante. Com data warehouses baseados em nuvem, onde cada byte armazenado e processado contribui para o custo, a eficiência das consultas é um fator essencial para economizar custos.

Para ilustrar esse conceito, vamos avaliar a eficiência de diferentes modelos de dados explorando os três modelos de dados a seguir de um sistema de pedidos de uma loja fictícia de esportes de futebol.

  1. Um Modelo de Dados Normalizado no qual as linhas de ordem e ordem são armazenadas como duas tabelas diferentes
  2. Um Modelo de Dados Des Normalizado em que as linhas de pedidos e pedidos são armazenadas na mesma tabela em que os dados do pedido são repetidos no nível da linha
  3. Um Modelo de Dados Des Normalizado que utiliza matrizes do BigQuery em que as linhas de pedidos e pedidos são armazenadas na mesma tabela, mas os dados do pedido não são repetidos

Matrizes no BigQuery

Matrizes são colunas com vários valores no BigQuery, em que um campo da matriz contém mais de um valor (do mesmo tipo de dados)

Cada linha pode ter várias colunas com vários valores

Essa estrutura de tabela com matrizes mantém uma relação entre o valor em species_group (Mammals) com cada valor na matriz species_list sem repetir seu valor. Assim, o armazenamento de dados usando matrizes minimiza o armazenamento (os mamíferos são armazenados apenas uma vez em vez de três vezes). Mas essa estrutura não suporta a análise típica do estilo SQL. Para converter essa estrutura em um conjunto de resultados no estilo SQL, use UNNEST, que descompacta a matriz e replica o valor de species_group para cada species_list, como uma junção cruzada.

Conjunto de resultados no estilo SQL, adequado para aplicar qualquer tipo de análise SQL
Nota: Armazenando Mamíferos (16 bytes) e Répteis (18 bytes) uma vez, salvamos (2 * 16 + 3 * 18 = 86) bytes de armazenamento (72%) em apenas uma coluna. Projete isso para uma tabela de escala de terabytes com várias colunas, que se traduz imediatamente em economia de custos de armazenamento.

Consulte os preços do BigQuery Storage para calcular o tamanho do armazenamento para tipos de dados e muito mais sobre matrizes aqui

Dados de amostra

Usando a mágica de matrizes e números aleatórios, essa consulta simula um sistema de pedidos criando um pedido a cada 5 segundos, fornecendo aproximadamente 518 mil pedidos por mês. Estendendo-se para um ano, a consulta gera 6,3 milhões de pedidos aleatórios com um total de 22 milhões de linhas de pedidos, criando volume de pedidos suficiente para testar o desempenho dos três modelos de dados.

Configuração de dados

Faça o download deste repositório do github e execute setup.sh Os scripts preenchem dados para 3 modelos de dados.

Modelo de dados

Modelo de Dados Normalizado

Relação típica de detalhes mestres entre ordem e linhas

Modelo de Dados Des Normalizado

Modelo não normalizado com pedido, IDs do cliente e data do pedido repetidos para cada linha

Desnormalizada com matrizes BigQuery

Ordem e linhas salvas na mesma linha, mas não repetidas para cada linha

Teste de performance

Vamos executar algumas consultas de relatório para testar esses modelos de dados, localizando

  1. Total do pedido pelo cliente
  2. Total do pedido por mês
  3. Total do pedido por item

Aqui está uma comparação das principais métricas de consulta capturadas para cada consulta

Os modelos não normalizados se saíram melhor em todas as métricas com o modelo de matrizes BQ consumindo menos bytes processados ​​e tempo gasto

Comparando o Tamanho das Tabelas por Modelo de Dados

Métricas de consulta

  • Bytes processados ​​- quantidade de dados lidos em tabelas, contribui diretamente para o custo do processamento de consultas
  • Bytes Aleatórios - Quantidade de dados ou resultados intermediários movidos entre nós de processamento paralelo. É uma medida da quantidade de poder de processamento dedicada à movimentação de dados, o que aumenta o tempo de espera e limita o paralelismo
  • Tempo decorrido - tempo decorrido para executar a consulta
  • Usos de Slot (Segundos) - Quantidade acumulada de tempo gasto por nós paralelos para processar a consulta
O design do modelo de dados e as consultas devem ter como objetivo minimizar essas métricas

Observações

  • O modelo normalizado consumiu a maior quantidade de bytes e levou mais tempo para fornecer um resultado. As consultas tiveram a maior quantidade de bytes embaralhados (ordens de magnitude maiores que o modelo normalizado), resultado da junção entre tabelas. Prova proibitivamente caro.
  • Ambos os modelos não normalizados consumiram bytes mínimos embaralhados, pois os dados estão na mesma linha, sem necessidade de junções
  • O modelo com matrizes BigQuery teve as melhores métricas em bytes processados ​​e tempo gasto

Recomendações

Ao definir modelos de dados, considere o seguinte para um desempenho ideal

  1. Use Matrizes para armazenar tabelas com relações pai / filho. Quanto maior a cardinalidade, maiores os benefícios
  2. Use o formulário Des Normalizado se o uso de Matrizes complicar consultas para desenvolvedores ou envolver uma curva de aprendizado acentuada para usuários / analistas finais que usam o BigQuery
  3. Otimizar tipos de dados: determine os tipos de dados apropriados ao definir tabelas. Considere alternativas, quando aplicável.

Essas pequenas decisões de tempo de design podem aumentar rapidamente a economia, pois cada byte adicional aumenta o custo de armazenamento e de consulta toda vez que é recuperado e processado.

Leitura / Prática Adicional

  • Visualizando planos de consulta do BigQuery - represente visualmente o desempenho dessas consultas, pretendo ilustrar os planos de execução de consultas usando esta ótima ferramenta de Stephan Meyn
  • Como usar a função UNNEST no BigQuery para analisar parâmetros de eventos no Analytics - Aqui está um artigo com excelente uso do UNNEST, incluindo uma bela ilustração de Todd Kerpelman