Google BigQuery: como reduzir custos de armazenamento e processamento

Otimizar modelos de dados no Google BigQuery é uma solução para que os custos de armazenamento e processamento não sofram escalações abruptas. 

Além de aprimorar seus modelos e evitar surpresas indesejadas de cobrança por esse data warehouse, conhecer práticas para otimização de modelos é uma via para entregas mais performáticas e seguras. 

Saiba como os custos no BQ funcionam e os principais recursos disponíveis para otimização de modelos e consultas. Aprofunde-se em temas como:

• recursos de samples; 
• particionamento; 
• clusterização;  
• estratégias de configurações incrementais; 
• consultas em cache; 
• e melhores práticas com SQL.

Boa leitura!

BigQuery: o data warehouse moderno do Google

O BigQuery é um serviço de data warehouse para análise de dados, que é autogerenciável e autoescalável. 

No BQ, uma tabela não é um arquivo único no filesystem. Na verdade, cada coluna da tabela é um arquivo no filesystem, e cada um desses arquivos que representam as colunas são distribuídos dentro da região geográfica em que o projeto está configurado. 

Isso permite o paralelismo, subparticionamento e leitura massiva dos dados, que é a atribuição principal para o grande desempenho do BigQuery.

BigQuery: custo por byte armazenado 

Otimizar modelos de dados no Google BigQuery é uma solução para que os custos de armazenamento e processamento não sofram escalações abruptas. 

Primeiramente, é necessário compreender como os custos no BigQuery funcionam para definir as melhores maneiras de otimização desses custos. 

O custo default de armazenamento atualmente no BQ é de 2 centavos de dólar por GB podendo cair para 1.

Isso acontece quando uma tabela fica mais de 90 dias apenas como leitura. 

Nesse caso, o BQ faz um movimento retirando a tabela de um storage de uso comum para um storage de armazenamento de longo prazo. 

Nessa reclassificação, o custo cai para 1 centavo de dólar devido à economia de storages de longo prazo. 

Quando uma tabela é processada em sua integridade, full scan, ela pode nunca atingir esses 90 dias necessários para ser movida para storage de longo prazo devido sua ingestão ou leitura dos lados. 

Contudo, caso uma tabela esteja particionada, suas partições seguem a regra acima, ou seja, ao completar 90 dias ou mais que determinada partição não foi processada, ela é reclassificada e seu custo é otimizado. 

Outra informação importante quanto ao custo do BQ é que ele pode variar dependendo da região, mas a redução dos custos entre os storages, de curto e longo prazos, segue sempre a proporção de 50%.

BigQuery: custo por byte processado 

Quando uma query é escrita no BQ, ele mostra o plano de execução e a quantidade de bytes que vão ser escaneados. 

De maneira sucinta, o BQ entende quanto da query ele consegue paralelizar e alocar em slots, que são conjuntos de Central Process Unit (CPU) e memória. 

O BQ executa a query através dos slots e, por fim, envia para os diversos storages. Processando, assim, uma quantidade de bytes por query. 

O custo default por cada terabyte processado no BigQuery é em torno de 5 dólares, podendo variar entre regiões. 

Opção alternativa

Existe outro tipo de custo por processamento que é flexível, mas não é o default do serviço, que basicamente funciona como  uma reserva de slots. 

Esse custo em específico pode ser negociado nos contratos entre o Google e a empresa contratante, e servem propósitos específicos.

A partir disso, você pode identificar que existem diversas formas de otimização no BQ e a redução de custos de armazenamento e processamento é uma meta a ser alcançada nas empresas que utilizam esse recurso. 

BigQuery: otimizações que não podem faltar em seus modelos

Quanto mais otimizada sua tabela e consultas, menor o gasto de processamento e armazenamento no BigQuery. 

Veja algumas estratégias para otimizar os custos do BQ tanto em processamento quanto em armazenamento dos dados na tabela a seguir.

BigQuery: amostragem de dados

Quando estamos testando lógicas de negócio e rodando modelos no ambiente de desenvolvimento, na maioria das vezes,  o objetivo não é processar todos os dados de uma tabela. 

Nesse sentido, aplicar lógicas de amostragem de dados é uma estratégia muito eficiente e garante economia de armazenamento e processamento no BigQuery. 

Esse data warehouse possui algumas funções que garantem uma amostragem de dados, dependendo do que você necessita. 

Temos dois exemplos distintos de como realizar a amostragem de dados. Veja!

Função TABLESAMPLE

Para trabalhar com uma amostra aleatória de dados, a função TABLESAMPLE SYSTEM é a escolha ideal, pois ela considera apenas uma porcentagem dos dados de uma tabela, que é determinada pela pessoa. 

No (primeiro) exemplo abaixo, estamos selecionando 10% aleatórios dos dados de uma tabela.

select *

from `projeto.dataset.tabela`

tablesample system (10);

Title tag: Uso da função TABLESAMPLE SYSTEM no BigQuery.

Alt tag: Representação de um prompt do BigQuery na linguagem JavaScript fazendo o uso da função TABLESAMPLE SYSTEM.

Abordagem MOD e FARMFINGERPRINT()

São mais apropriadas para quem deseja ter um controle personalizado sobre o processo de amostragem para garantir uma seleção de linhas em intervalos específicos ou quer implementar lógicas específicas. 

Contudo, vale ressaltar que a implementação da abordagem ou da função mencionadas é mais complexa. Veja o segundo exemplo:

select *

from `projeto.dataset.tabela`

where mod(farm_fingerprint(concat(cast(data as string))), 10) = 0;

Title tag: Uso da abordagem MOD e FARMFINGERPRINT() no BigQuery.

Alt tag: Representação de um prompt do BigQuery na linguagem JavaScript fazendo o uso da abordagem MOD e FARMFINGERPRINT().

A partir da amostra de dados, você vai conseguir executar seus modelos, criar uma tabela amostral e fazer a validação sem processar toda a tabela.

BigQuey: particionamento de dados

O particionamento de dados em uma tabela acontece quando ela é dividida em segmentos, chamados de partições, que possuem como base determinado critério em uma coluna.

Temos um caso prático para você entender como funciona o BigQuery.

Imagine uma tabela com mais de 1 bilhão de linhas, mas você costuma consultar somente os dados do último mês dessa tabela. 

Sem um particionamento, o BQ acessa toda a sua tabela. Ou seja, 1 bilhão de linhas são lidas para retornar os dados do último mês. 

Essa consulta processa uma grande quantidade de bytes, gerando um custo mais alto do que uma consulta particionada. Certo!?

Com um particionamento de mês, por exemplo, o BQ acessa somente a partição que sua data no filtro estiver armazenada, diminuindo o custo de processamento da query.

Ou seja, quanto menor a quantidade de bytes processados, maior é a performance e menor o custo.

O que não fazer  no particionamento

1. Policy tags

Campos mascarados, ou seja, com policy tags (PII), não devem ser utilizados para o particionamento. Isso porque o particionamento é realizado tendo como referência colunas específicas que possuem como base critérios de distribuição dos dados e frequência das consultas. 

O BQ não é capaz de relacionar uma coluna PII com o resultado para a área de negócio. Além disso, essas colunas podem mudar sua natureza ao longo do tempo, tendo uma taxa de mutação variável, o que implica não utilização do storage de armazenamento de longo prazo. 

No BigQuery, geralmente é recomendado escolher colunas que sejam estáveis e tenham uma distribuição relativamente uniforme dos dados. 

2. Limite de partições

O BigQuery limita até 4000 partições numa tabela, então é importante pensar em estratégias em casos de partições que ultrapassem esse limite.

Se uma tabela é particionada por dia, e é uma tabela que tenha mais de 4000 dias, a tabela vai ultrapassar o limite. Nesse caso, é interessante mudar a partição por mês.

BigQuery: clusterização

No Google BigQuery, é possível estruturar fisicamente os dados em uma tabela com base em uma ou mais colunas específicas. 

Isso resulta na organização dos registros de maneira que dados semelhantes sejam armazenados próximos uns dos outros em disco, proporcionando otimização no acesso aos dados durante as consultas.

Atreladas ao particionamento, essas são poderosas configurações que potencializam as consultas, diminuindo custos e aumentando a performance.

A clusterização é similar a um group by, porém com essa consulta é gravada em seu armazenamento. Os blocos de armazenamento são dimensionados de modo adaptável com base no tamanho da tabela.

Assim como no particionamento, as consultas que filtram ou agregam as colunas em cluster verificam diretamente os blocos relacionados aquela consulta, e não toda a tabela. 

Essa característica faz com que, automaticamente, haja uma redução dos bytes processados e diminui o custo de processamento. 

Ou seja, é como se o BQ soubesse o caminho direto daqueles dados e não precisasse escanear todos os dados da tabela.

BigQuery: colunas na clusterização

Ao contrário do particionamento, é possível criar a clusterização com até quatro colunas, sendo que a ordem delas  indica a precedência de agrupamento e classificação no BQ. 

Ou seja, a ordem das colunas é importante para a clusterização. Sendo assim, considere:

• utilizar a primeira coluna na clusterização com base no filtro mais utilizado da tabela. Isso pode ser analisado pelos filtros mais aplicados nos dashboards que consomem essa tabela, por exemplo;
• fazer uso de outras colunas que façam sentido como filtro nas tabelas para clusterização;
• para otimizar os resultados em clustering, é preciso filtrar utilizando as colunas em cluster seguindo as recomendações:some text
  • quando realizar uma consulta em uma tabela clusterizada utilize sempre que possível a primeira coluna da ordem da clusterização como filtro e idealmente as outras colunas em cluster;
  • e, caso uma consulta não leve em consideração a primeira coluna do cluster, sua consulta não vai ser otimizada.

Performance e redução de custos: particionamento e clusterização

Pensando em termos de performance e redução de custos no processamento, o particionamento e clusterização são as configurações mais poderosas que o BigQuery oferece. 

Quando há a junção de ambas em sua tabela, você vai ter os maiores aliados na otimização de processamento.  

Veja como ficam as tabelas sem particionamento e clusterização e, à direita da tabela, quando há ambas as combinações.

E veja o exemplo de uma query sem particionamento e clusterização e de outra com ambos.

Essa imagem não utiliza configuração de particionamento ou clusterização, processando no total 1,47 MB quando executada, mesmo havendo um filtro de data e outras condições. 

Isso se dá pelo fato de que, independentemente do filtro, a query vai processar todas as linhas da tabela para trazer os dados solicitados.

Já nessa imagem acima, a tabela é particionada pela coluna orderdate, com partições por dia e clusterizada pelas colunas salesorderid e customerid, processando no total 1,08 KB. 

Quando executada, a query vai escanear os dados somente da partição que foi aplicada no filtro where. 

Além disso, a clusterização auxilia a encontrar os dados que estão clusterizados naquela partição em específico, diminuindo assim o processamento.

BigQuery: tabela incremental

Tabelas incrementais processam menos dados devido à sua característica de inserir somente novos dados ou alterações desde a última execução processada. Sendo assim, a tabela não é reprocessada em sua totalidade. 

Como um banco colunar, e conforme a primeira parte deste artigo, caso haja atualização de um registro somente em algumas colunas específicas, o BQ consegue capturar essa modificação e alterar as colunas sem que haja um reprocessamento do registro completo.

No BigQuery, existem duas possibilidades de modelos incrementais: Merge, que é o padrão, e Insert overwrite.

1. Merge

A estratégia de merge utiliza uma chave única para atualizar os dados. Se já existe uma chave única na tabela de destino, ele vai atualizar os dados dessa linha com a função update. 

Se a chave única não existe na tabela, o dado é simplesmente inserido, tendo um comportamento parecido com append. 

Contudo, vale ressaltar que a estratégia append não está disponível para o BQ.

Com a estratégia de merge, você garante que os dados estejam deduplicados graças à utilização da chave primária. 

Para checar se a chave única existe na tabela, essa estratégia precisa escanear toda a tabela, o que a faz ainda ser  bastante custosa em termos de processamento.

2. Insert overwrite

Já a estratégia de substituição de inserção (insert overwrite) é mais complexa e mais otimizada de incremental. 

Essa opção resolve o problema da varredura completa. 

A solução utilizada pelo insert overwrite é trabalhar com partições. Ela exclui as partições selecionadas da tabela de destino atual e insere nela as que estiverem transformadas selecionadas a partir da lógica implementada no modelo.

Exceto pelo uso de partições, esse processo se parece com a estratégia delete+insert. No entanto, essa usa duas instruções separadas, “delete” e “insert”. 

Insert overwrite usa a instrução merge apenas no que é menos custoso para o BQ.

É uma estratégia de alta complexidade e se não é definida corretamente, pode gerar dados duplicados.

Merge e Insert Overwrite: comparação das estratégias

Na tabela a seguir, há um comparativo entre a estratégia Merge e o Insert Overwrite.

Cache de consultas e BI Engine

O BigQuery possui uma função nativa que armazena temporariamente em cache os resultados de consultas por um determinado período de tempo. 

Essa funcionalidade  economiza recursos computacionais, porém o armazenamento é descartado após o tempo de expiração, que é geralmente curto, mesmo podendo ser ajustado. 

A consulta em cache não possui custos adicionais e é de grande ajuda em cenários em que você não possui tantos usuários e consultas distintas em um curto intervalo de tempo, ou então quando consultas repetidas são executadas com frequência.

Contudo, muitas vezes é necessário potencializar as consultas em cache. Para isso, o BigQuery apresenta o BI Engine, uma solução que oferece aprimoramento no desempenho por meio de aceleração em memória para consultas interativas. 

Esse recurso é adicional aos serviços da Google que possui atualização contínua mantendo os dados em cache atualizado automaticamente.

É autoescalável e possui integração com ferramentas de Business Intelligence (BI) para melhor experiência com as pessoas usuários.

SQL: boas práticas

Para finalizar o tema sobre otimizações, veja algumas dicas para realizar suas consultas com maior performance.

• Evite select *. Selecione somente as colunas necessárias para sua consulta.
• Use filtros como where, principalmente com as colunas particionadas e clusterizadas da tabela.
• Evite usar a função limit sem nenhum filtro associado. Ela continua escaneando a tabela por completo se utilizado sozinho, apenas limitando a quantidade de linhas mostradas pela interface do BQ para o usuário.
• Utilize joins com colunas particionadas e clusterizadas.
• Não use subselects (subqueries), ao invés disso tente utilizar CTEs. Dessa forma, o BQ consegue paralelizar melhor a consulta.
• Aproveite as funções nativas do BQ ao invés de recriar várias funções SQL.

BigQuery: monitoramento

O monitoramento de consultas e modelos que rodam em produção é uma das formas mais eficazes de repensar estratégias que já foram aplicadas ou que necessitam de aplicação. 

O monitoramento pode ser realizado a partir de BIs com metadados produzidos pelo BQ ou pela ferramenta que está sendo utilizada para compilar o código, como o dbt.

O plano de execução do BQ auxilia a encontrar possíveis gargalos e produz insights quando bem analisado. 

Além disso, o monitoramento constante pode identificar modelos inutilizados que estão consumindo processamento e armazenamento do data warehouse.

Redução de custos no Google BigQuery: o que podemos concluir?

Ao explorar diferentes métodos para redução de custos no Google BigQuery (BQ), fica evidente que a eficiência na gestão de dados é essencial para evitar surpresas financeiras indesejadas. 

A implementação cuidadosa de estratégias como: 

• amostragem de dados; 
• particionamento; 
• clusterização; 
• configurações incrementais;
• e consultas em cache.

Esses recursos associados com as  melhores práticas em SQL, não apenas aprimoram o desempenho, mas também contribuem diretamente para a redução de custos.  

Cabe ao time de dados compreender e aplicar esses recursos de maneira estratégica no BQ, garantindo uma utilização eficiente dos recursos e, consequentemente, uma gestão financeira mais eficaz em projetos. 

A Indicium pode ajudar você a obter as melhores soluções para os seus dados

Indicium é referência em criar soluções em ciência de dados, analytics e inteligência artificial para empresas nacionais e internacionais.

E queremos ajudar você a alcançar a máxima usabilidade dos seus dados com as ferramentas mais modernas, promovendo a inovação com base em dados com velocidade, segurança e governança.

Quer incluir o Google BigQuery ou outro data warehouse no seu time de dados? 

Fale com nosso time de especialistas por aqui e descubra soluções de dados personalizadas para o seu caso.

‍