O BigQuery é um data warehouse versátil que ajuda você a transformar big data em insights valiosos – mas os custos podem disparar rapidamente. No primeiro de uma série de guias detalhados, mostramos como usá-lo de forma eficiente.

Apresentamos um novo guia para otimização de custos e desempenho no BigQuery

Picos repentinos de custo não são incomuns quando você opera vários datasets no BigQuery com diversos times de analistas executando consultas. Mas eles não são inevitáveis.

No nosso ebook mais recente, The BigQuery Optimization Handbook: Preparing to Save, Sayle Matthews, Senior Cloud Architect e especialista em dados na DoiT, traz a primeira parte de uma série de análises técnicas sobre como gastar menos e fazer mais com o BigQuery, deixando suas faturas do Google Cloud mais previsíveis.

O ebook aborda:

1. Como acertar nos fundamentos
2. Como evitar erros comuns em queries
3. Como identificar suas queries mais caras

Acertando nos fundamentos

O data warehouse BigQuery é totalmente gerenciado e oferece recursos nativos como machine learning, análise geoespacial e business intelligence para ajudar você a gerenciar e analisar big data. Mas é fácil acumular custos inesperados se você não fizer um trabalho inicial de preparação. Antes de mexer nos custos do BigQuery, é preciso entender alguns fundamentos. Vamos começar pelos slots e modelos de preços:

Slots

A computação do BigQuery se baseia em uma estrutura chamada slot, que é uma vCPU com uma quantidade de memória associada. Em teoria, quanto mais slots alocados e disponíveis, mais rápidas são as consultas. Os slots padrão são sempre adquiridos em grupos de 100, dentro de um commitment de um mês ou de um ano.

Os slots executam a tarefa não documentada de embaralhar dados (shuffling). Em termos simples, shuffling é redistribuir dados processados para um novo local, para que a etapa atual ou a próxima do plano de consulta seja executada mais rapidamente. A qualquer momento, até 60% dos slots alocados ao seu projeto podem atuar como shuffle slots. O shuffling melhora a eficiência das consultas, mas consome slots valiosos em operações que não fazem a execução da query avançar diretamente.

Modelos de preços

Os modelos de preços são on-demand ou flat-rate. O modelo padrão é o on-demand, que cobra US$ 5 por TB de dados escaneados pelas consultas. Para a maioria das empresas, esse é o principal motor de custos no BigQuery.

O preço flat-rate, ou por slots, define um valor fixo para o escaneamento no BigQuery, com possível impacto no desempenho. Ele funciona limitando o número de slots disponíveis ao que você comprou antecipadamente e eliminando a cobrança de US$ 5 por TB escaneado.

Com o flat-rate, você pode ajustar o desenho da sua arquitetura adicionando um pool de slots chamado Flex slots para ampliar ou substituir os slots existentes. Os Flex slots oferecem commitments mais curtos e flexíveis, com tempo mínimo de 60 segundos e a opção de cancelar e eliminar os slots a qualquer momento.

Para igualar o desempenho do on-demand, é preciso comprar 20 blocos de 100 slots, ao custo de US$ 40.000 por mês ou US$ 34.000 por um ano. Não vale a pena adquirir 2.000 slots a menos que você esteja gastando pelo menos esse valor em scans no BigQuery.

Como apurar os custos

Antes de otimizar seus custos, é preciso analisar o uso de dados dos seus projetos. Para isso, você precisa ter acesso aos dados de queries dos seus projetos/datasets.

Há duas formas de fazer isso: pelos audit log sinks ou pelas tabelas INFORMATION_SCHEMA. O audit log sink é o método preferido, porque os dados são bem mais ricos . Os clientes DoiT que usam o recurso BigQuery Lens no DoiT Cloud Intelligence™ já têm o audit log sink configurado em seus ambientes. Saiba mais aqui.

Evitando erros comuns em queries

Antes de partir para as queries que vão te entregar os dados que você precisa, vamos olhar alguns exemplos de erros comuns que as pessoas cometem ao escrever queries no BigQuery. Eles podem fazer com que as consultas demorem mais e custem mais do que o necessário.

1. SELECT *

Essa é provavelmente a maior fonte de custos adicionais desnecessários em queries no BigQuery. Em geral, não há necessidade de selecionar todas as colunas de uma tabela ou view. Lembre-se: as faturas do BigQuery se baseiam no volume de dados escaneado pelas queries, então reduza sua seleção ao mínimo para diminuir esse custo.

2. Joins desnecessários ou grandes demais

Em data warehouses focados em estratégia OLAP (como o BigQuery), a melhor prática é desnormalizar os esquemas do banco de dados. Na prática, isso achata as estruturas de dados e reduz o número de joins necessários em comparação com um banco relacional tradicional.

3. Cross joins

Os cross joins são necessários em vários casos de uso no BigQuery, mas o problema aparece quando as pessoas os colocam como operação mais interna da consulta, o que faz com que ela puxe muito mais dados do que será de fato passado para a saída.

4. Uso incorreto de Common Table Expressions (CTEs)

As Common Table Expressions (CTEs) são recursos incríveis que simplificam imensamente o código SQL. No entanto, ao usar uma CTE em uma query e referenciá-la várias vezes, ela acaba sendo executada várias vezes — ou seja, você será cobrado pela leitura dos mesmos dados repetidas vezes.

5. Não usar partições em cláusulas WHERE

As partições são um dos recursos mais importantes do BigQuery para reduzir custos e otimizar o desempenho de leitura. Ainda assim, são frequentemente omitidas, gerando custos desnecessários nas consultas.

6. Views complexas demais

Criar views complexas pode degradar o desempenho. Se a lógica de uma view ficar complicada demais, talvez faça mais sentido pré-calculá-la em outra tabela ou movê-la para uma materialized view, ganhando desempenho.

7. Inserts pequenos

Se você precisa inserir poucos registros em uma tabela, insira os dados em lote em vez de fazer várias inserções pequenas.

8. Abuso de DML Statements

É comum abusar de DML statements quando se trata o BigQuery como um RDBMS tradicional, recriando dados à vontade. Uma alternativa melhor é adotar um "modelo aditivo", que insere novas linhas com timestamp para indicar a versão mais recente e remove periodicamente as linhas antigas, caso não seja necessário manter histórico.

Identificando suas queries mais caras

O ebook traz o link para um repositório no GitHub com os arquivos SQL que você vai precisar para encontrar suas consultas mais caras. Estas são as três principais queries que você vai usar:

• Queries mais caras no geral
• Queries individuais mais caras
• Usuários mais caros

Outras queries no repositório

O repositório no GitHub também inclui queries adicionais para finalidades mais específicas, como identificar consultas originadas no Looker, contar quantas vezes uma query é executada, calcular o custo de queries com labels específicas, entre outras.

Encontrando queries com problemas de desempenho

Depois de identificar as consultas mais caras, é hora de descobrir aquelas que consomem mais recursos do que deveriam e não entregam o desempenho esperado. Essas queries costumam coincidir com as mais caras, então é natural que haja alguma sobreposição.

O ajuste de desempenho será abordado com mais profundidade em uma parte futura desta série, com foco em algumas das armadilhas menos conhecidas do desempenho do BigQuery e em formas de contorná-las.

O último tópico desta parte da série é um conjunto de queries no repositório do GitHub que mostram informações mais gerais ou metadados:

• Queries por tipo de job
• Queries concorrentes
• Contagem de queries

Próximos passos

Baixe o The BigQuery Optimization Handbook: Preparing to Save. Como esta e as próximas partes da série trazem um volume considerável de conteúdo detalhado, recomendamos configurar um audit log sink para seus projetos do BigQuery para coletar dados ao longo do tempo e deixar essas queries e o restante da série ainda mais úteis. Lembre-se: os clientes DoiT que já usam o recurso BigQuery Lens no DoiT Cloud Intelligence já têm o audit log sink configurado em seus ambientes.