Otimizações

É crucial garantir o desempenho eficiente das consultas de pipelines em SQL, para auxiliar nesta tarefa que pode ser extensa, estaremos utilizando o plano de execução do BigQuery.

→ Como o Bigquery armazena os dados

→ Como o Bigquery processa os dados

→ Compreendendo o plano de execução

→ Analisando planos de execução

→ Otimizações em SQL

→ Estudos de Caso

---

Como o Bigquery armazena os dados

Columnar storage

Bancos de dados relacionais tradicionais, como Postgres e MySQL, armazenam dados linha por linha em armazenamento orientado a registros. Isso os torna ótimos para atualizações transacionais e casos de uso OLTP (Online Transaction Processing), porque só é necessário recuperar uma única linha para ler ou gravar dados. No entanto, se você quiser realizar uma agregação como a soma de uma coluna inteira, precisará ler a tabela inteira na memória.

O BigQuery usa armazenamento em colunas, onde cada coluna é armazenada em um bloco de arquivo separado. Isso torna o BigQuery uma solução ideal para casos de uso OLAP (Online Analytical Processing). Quando você deseja realizar agregações, você só precisa ler a coluna que está agregando.

Partitioning

Uma tabela particionada é uma tabela fisicamente dividida em segmentos, chamados partições. O BigQuery utiliza o particionamento para diminuir a quantidade de dados que os slots lêem do disco. As consultas que contêm filtros na coluna de particionamento podem reduzir o total de dados armazenados que são lidos, o que pode gerar melhor desempenho e redução do custo de processamento. Quando novos dados são gravados em uma tabela particionada, eles são automaticamente separados na partição apropriada.

O BigQuery oferece suporte às seguintes formas de criar tabelas particionadas:

• Tabelas particionadas por tempo de ingestão: partições diárias que refletem a hora em que os dados foram ingeridos no BigQuery.
• Tabelas particionadas por coluna de unidade de tempo: o BigQuery atribui dados para a partição apropriada com base no valor da data na coluna de particionamento.
• Tabelas particionadas de intervalo INTEGER: Você pode agrupar os valores inteiros para criar partições do tamanho desejado, como ter todos os clientes com IDs de 0 a 100 na mesma partição.

Clustering

Quando uma tabela é agrupada em cluster no BigQuery, os dados são ordenados automaticamente com base no conteúdo de uma ou mais colunas (até quatro, conforme definição da tabela). Normalmente, colunas de alta cardinalidade e não temporais são preferidas para agrupamento, em oposição ao particionamento, que é melhor para campos com cardinalidade mais baixa.

A ordem das colunas agrupadas determina a ordem de classificação dos dados. Quando novos dados são adicionados a uma tabela, o BigQuery realiza o novo clustering automático e sem custos em segundo plano.

O clustering pode melhorar o desempenho das consultas:

• Cláusulas WHERE com coluna clusterizada: o BigQuery usa os blocos clusterizados para eliminar leitura de dados desnecessários. A ordem dos filtros na cláusula `WHERE`` é importante, devem ser usados filtros que aproveitem o cluster primeiro.
• Agregações com base em valores de coluna clusterizada: o desempenho é melhorado porque os blocos clusterizados ordenam as linhas com valores idênticos.
• Cláusulas JOIN com coluna clusterizada: menos dados são lidos durante a junção de duas tabelas.

Partitioning + Clustering

Não há limitação na escolha da utilização de Clustering ou Partitioning, é possível a definição de ambos em uma única tabela.

Como usar na Carol?

→ Configurações Avançadas do Data Model

Como o Bigquery processa os dados

Arquitetura

Como é possível ver no diagrama abaixo, a arquitetura de processamento do BigQuery é composta por um conjunto de workers. Cada um desses workers executa parte de uma tarefa de forma independente e paralela. O BigQuery usa uma área de armazenamento de memória distribuída (memory shuffle) para armazenar dados intermediários produzidos por workers em vários estágios de execução. Isso torna possível executar operações distribuídas em um pipeline.

Ciclo de Vida

Utilizando a arquitetura de processamento descrita anteriormente, o BigQuery realiza a execução de queries, que passam por diferentes estágios de validação e processamento.

• API request management: algumas das coisas que devem ser feitas são autenticar e autorizar a solicitação.
• Lexing and parsing: análise léxica e sintática da linguagem SQL.
• Catalog resolution: validação dos identificadores de projeto, dataset, tabelas, views e colunas presentes na query.
• Query planning: definição do plano de execução mais rápido e eficiente para a quey, como um conjunto de tasks de execução distribuída.
• Query execution: processo de avançar através dos estágios de execução definidos pelo plano até a conclusão.
• Scheduling and dynamic planning: além dos workers envolvidos na execução do próprio plano, workers adicionais monitoram e direcionam o progresso geral da execução, definindo questões como o enfileiramento e provisionamento de novos workers.
• Finalize results: a conclusão do processamento produz artefatos de saída na forma de resultados, que neste estágio são enviados à camada de armazenamento.

Como usar na Carol?

→ VS Code Extension
→ SQL Query API
→ Processing Data API
→ Carol UI Explorer
→ pyCarol
→ Ferramentas de BI/Analytics

Compreendendo o plano de execução

Input e Output

Referem-se aos dados lidos (input) e escritos (output) durante cada estágio do processamento de consultas.

• Input: quantidade de registros que são lidos a partir do armazenamento distribuído ou memory shuffle, e necessários para processamento daquele estágio.

• Output: após o processamento daquele estágio, os registros resultantes resultados são escritos para o armazenamento em disco ou memory shuffle.

MENOS DADOS!

A quantidade de dados lidos interfere na quantidade de slots necessária para consumo destes dados naquele estágio do processamento, deste modo, a utilização de Partitioning e Clustering auxilia na diminuição da quantidade de dados lidos.

Fases e duração

É possível ver o tempo médio e máximo que cada worker (slot) gastou esperando, lendo, processando e escrevendo em cada etapa do processamento:

• Waiting: aguardando que slots fiquem disponíveis, ou que um estágio anterior comece a escrever resultados que possam ser consumidos do memory shuffle. Muito tempo gasto na fase de espera, pode indicar uma retenção de slots devido à concorrência com outros processamentos.

• Read: o slot está lendo dados do armazenamento distribuído ou do shuffle. Muito tempo gasto nesta fase, indica que pode haver uma oportunidade de limitar a quantidade de dados consumidos pela consulta.

• Compute: onde ocorre o processamento, tal como executando funções ou expressões SQL. Uma consulta bem ajustada, normalmente passa a maior parte do tempo na fase de computação. Muito tempo gasto nesta fase, pode indicar a necessidade de reavaliação de manipulações mais custosas nos dados.

• Write: quando os dados são gravados, seja no próximo estágio, shuffle ou resultado final retornado ao usuário. Muito tempo gasto aqui, indica que pode haver uma oportunidade de limitar os resultados deste estágio, através de consolidações ou filtros por exemplo.

Bytes Shuffled

Representa a quantidade de dados movimentada para o trabalhos dos workers durante os estágios do processamento.

Como dados presentes no shuffle são distribuídos entre workers de um estágio, operações como JOIN e GROUP BY realizam processamentos parciais antes da agregação final.

Apesar de ser uma área em memória, em algumas circunstâncias o worker pode ser forçado a utilizar o disco, por exemplo quando não houver simetria na distribuição dos dados entre os workers.

Consumo de Slots

• Um slot representa uma unidade computacional de processamento.

• Quantidade de slots necessária é calculada automaticamente para cada estágio do plano de execução.

• A Carol utiliza o modelo de reserva compartilhada por todos os projetos e seus processamentos.

• A disponibilidade de slots livres afeta o tempo de execução das queries.

• A quantidade de dados lidos em cada estágio do plano de execução define a quantidade de slots necessária para esta operação de leitura.

Analisando planos de execução

Em construção

Otimizações em SQL

Em tabelas particionadas, o atributo da partição deve ser a primeira condicional informada na query. Além disso, a condição no atributo da partição deve conter apenas constantes. Por exemplo, o cenário abaixo NÃO efetua o filtro de partições não referênciadas:

SELECT * FROM A WHERE A_date >= (SELECT B_date FROM B LIMIT 1)

Enquanto isso, a query acima trata corretamente a partição e irá ler menos dados pelo fato de utilizar partições corretamente:

DECLARE dateB TIMESTAMP;
SET dateB =  (SELECT B_date FROM B LIMIT 1);
SELECT * FROM A WHERE A_date >= dateB;

Estudos de Caso

Em construção