BigQuery — Melhores Práticas: Como melhorar ainda mais a performance das consultas e reduzir custos
O Google BigQuery é um serviço de Data Warehouse na nuvem, realmente muito poderoso e com uma performance incrível para análises de grandes volumes de dados.
Embora o custo do BigQuery seja atrativo, conforme o volume de dados armazenados e processados aumenta podemos ter também um aumento significativo de custos.
Desta forma, como podemos evitar custos desnecessários e ainda ter maior performance com as queries?
No BigQuery sabemos que pagamos pelo que é utilizado, ou seja, além da quantidade de dados armazenados pagamos pela quantidade de bytes processados pelas consultas. Ao mesmo tempo, como na maioria dos bancos de dados, quanto menor a quantidade de bytes que precisamos processar maior será a performance das consultas ao termos planos de execução otimizados. Temos, então, uma relação muito importante entre ganho de performance e redução de custo no BigQuery — uma regra de ganha-ganha que precisamos sempre ter em mente:
Quanto menor a quantidade de bytes processados maior será a performance e, consequentemente, menor o custo.
Existem diversas boas práticas que podemos adotar durante a fase de análise e desenvolvimento e, inclusive, solucionar problemas ou melhorias em ambiente de produção. Algumas delas trataremos no tópico de Melhores Práticas mais adiante neste artigo. Antes disso, teremos uma breve ideia de como o BigQuery funciona por trás das cenas, o que irá nos ajudar a entender melhor não somente como ele oferece alta performance e escalabilidade sob demanda mas também o porquê da utilização das melhores práticas.
Arquitetura do BigQuery
Em sua arquitetura ele utiliza o sistema de gerenciamento de cluster em larga escala do Google, denominado BORG.
Neste sistema o Dremel Query Engine transforma a consulta sql em uma árvore de execução. Os nós folha (Leaf Nodes) são responsáveis pela leitura massiva e processamento dos dados que estão armazenados no FileSystem do Google chamado Colossus (storage que armazena os dados de forma colunar). Após a leitura dos dados pelos nós folha, temos os galhos, também conhecidos como mixers, onde é realizada a agregação dos dados que são retornados para o nível acima e para o usuário.
A alta escalabilidade do BigQuery se deve à separação das camadas de Storage e de Processamento, onde elas podem escalar sob demanda e de forma independente.
Isto permite com que o BigQuery aloque a quantidade necessária de processamento para atender as queries de forma mais performática possível e também disponibilizar a quantidade de storage necessária para a ingestão massiva de dados.
A comunicação entre as camadas de processamento e armazenamento é realizada através da rede Petabit, denominada Júpiter. Esta rede oferece uma comunicação extremamente rápida o que torna possível esta arquitetura:
Por ser “Serverless”, simplesmente utilizamos o serviço, não precisamos nos preocupar com adição de storage, servidores, memória, CPU’s, etc. Estamos falando de um parque gigantesco com alto poder de processamento e armazenamento disponíveis para serem alocados de acordo a nossa demanda.
Melhores Práticas
Agora que temos uma ideia de como o BigQuery funciona, vamos ao que interessa.
A seguir algumas boas práticas que podemos adotar para melhor aproveitamento do BigQuery, ganho de performance e, consequentemente, redução de custos ao reduzirmos a quantidade de bytes a serem processados:
1. Evite utilizar “SELECT * ” nas consultas.
Se não há uma real necessidade de trazer os dados de todas as colunas da tabela, especifique somente as colunas necessárias.
Como o BigQuery é um banco de dados com armazenamento colunar, ele irá acessar somente os dados das colunas especificadas. Diferente de um banco de dados com armazenamento por linha, onde a linha inteira é acessada trazendo os dados de todas as colunas.
Exemplo:
Neste SELECT *, em uma tabela com apenas 16 colunas, serão acessados os dados da tabela inteira, onde o custo será cerca de seis vezes maior em comparação a uma consulta onde especificamos apenas algumas colunas necessárias:
- Quando utilizamos SELECT * trazendo todas as colunas da seguinte tabela, notamos que a quantidade de bytes processada é de 7.5Gb.
- Informando apenas as colunas necessárias, esta consulta irá processar uma quantidade significantemente menor, 1.2Gb!
Lembrem-se: Quanto mais bytes processamos, maior o custo no BigQuery. Evite acessar dados desnecessários.
2. Utilize Particionamento nas tabelas sempre que aplicável.
Com particionamento temos a possibilidade de dividir uma tabela em segmentos menores (partições) baseadas em algum critério.
Quando temos particionamento bem definido evitamos que as consultas precisem acessar todos os dados da tabela para trazer apenas um pequeno subconjunto de dados, ou seja, conseguimos restringir o processamento somente na partição onde os dados que precisamos acessar estão armazenados.
Caso tenhamos uma tabela com 10 anos de dados e você precise acessar apenas os dados de uma data específica, acontece o seguinte:
- Sem particionamento a tabela inteira será acessada, ou seja, 10 anos de dados serão lidos para retornar os dados de apenas um dia.
- Se tivermos particionamento na coluna de data utilizada no filtro da consulta, o BigQuery irá acessar somente a partição onde os dados da data específica estão armazenados. Uma redução de dados a serem processados muito significativa.
Exemplo:
A seguinte tabela citibike_trips, copiada do public dataset new_york_citibike, possui quase 59 milhões de registros, num total de 8Gb.
Uma consulta nesta tabela sem particionamento, onde utilizamos um filtro pela coluna de data para trazer os dados de apenas um dia, constatamos que foi executada em 2,1 segundos, processou 1.2 Gb (estamos acessando somente três colunas) e precisou acessar todos ~59 milhões de registros para retornar apenas ~65 mil registros, conforme o plano de execução abaixo:
Após criarmos a mesma tabela, com os mesmos dados, porém agora com particionamento pela coluna de data, executamos a mesma consulta nesta tabela e constatamos que ela foi executada em 1,2 segundos, processou apenas 1,5 Mb e precisou acessar apenas os ~65 mil registros dos ~59 milhões que existem nesta tabela. Realmente uma diferença de processamento de bytes muito expressiva, de 1.2 Gb para 1.5 Mb.
Como o BigQuery aloca os recursos necessários para que a consulta seja processada o mais rápido possível, notamos que a diferença de performance não foi muito grande, porém a diferença de bytes processados é muito expressiva, logo teremos redução de custo significativa.
No BigQuery podemos particionar as tabelas por colunas do tipo de dado:
- Date/Timestamp: Partições por Ano, Mês ou Dia.
- Integer: Partições por intervalo (Range).
- Ingestion Time: data/horário da ingestão dos dados na tabela. Pseudocolunas: _PARTITIONDATE e _PARTITIONTIME
Sintaxe:
CREATE TABLE <dataset>.<nome da tabela>
(
<coluna1> <tipo de dado>,
<coluna2> <tipo de dado>,
<coluna3> <tipo de dado>,
......
)
PARTITION BY <coluna>3. Utilize Clustering nas tabelas quando aplicável
Quando utilizamos Clustering para as tabelas os dados são agrupados automaticamente em blocos de armazenamento baseado nos valores da(s) coluna(s) definida(s) para o cluster.
Na prática, o que acontece é que uma consulta onde as colunas do filtro fazem parte do cluster, o BigQuery irá acessar somente os blocos de armazenamento onde os dados que precisamos acessar estão armazenados, desta forma evitamos uma leitura da tabela inteira, teremos uma ganho de performance pelo menor processamento de bytes e consequentemente menor custo.
Exemplo:
A tabela citibike_trips, copiada do public dataset new_york_citibike, possui quase 59 milhões de registros, num total de 8Gb.
Uma consulta nesta tabela sem clustering, onde utilizamos um filtro pelas colunas de start_station_id e end_station_id para trazer os dados destas estações, constatamos que foi executada em 0,7 segundos, processou 2 Gb (estamos acessando somente três colunas) e precisou acessar todos ~59 milhões de registros para retornar apenas dois registros, conforme o plano de execução abaixo:
Após criarmos a mesma tabela, com os mesmos dados, porém agora com clustering definido para as colunas start_station_id e end_station_id, executamos a mesma consulta nesta tabela e constatamos que ela foi executada em 0,6 segundos, processou apenas 25,5 Mb e precisou acessar ~4,5 milhões de registros dos ~59 milhões que existem nesta tabela. Novamente temos uma diferença de processamento de bytes muito expressiva e redução de custo.
Com relação ao Clustering no BigQuery:
- Podemos definir até quatro colunas para o clustering de uma tabela.
- A ordem das colunas especificadas determina a ordem da classificação dos dados no cluster.
- Clustering pode ser utilizado em tabelas particionadas ou não-particionadas.
- Ganho de performance especialmente em tabelas com grandes volumes de dados.
Quando utilizar Clustering?
- Consultas que geralmente usam filtros ou agregação em várias colunas específicas.
- Necessidade de maior granularidade do que o particionamento permite.
- A cardinalidade do número de valores em uma coluna ou grupo de colunas é grande.
Sintaxe:
CREATE TABLE <dataset>.<nome da tabela>
(
<coluna1> <tipo de dado>,
<coluna2> <tipo de dado>,
<coluna3> <tipo de dado>,
......
)
CLUSTER BY <coluna1>, <coluna2>, ...4. Evite consultas complexas em Tabelas Externas
No BigQuery podemos criar tabelas externas para acessar dados que não estão armazenados no BigQuery. Como por exemplo arquivos CSV, AVRO, JSON, PARQUET, ORC, Google Sheets e bancos de dados Cloud SQL e BigTable.
Realmente é uma ótima opção para facilitar a integração de dados externos onde podemos executar queries federadas e tratar estas fontes de dados externas como se fossem tabelas dentro do BigQuery.
Porém, ao executarmos queries nestas estruturas externas podemos não ter uma performance muito boa, especialmente em queries mais complexas onde fazemos joins e utilizamos muitos filtros.
Uma alternativa para este caso seria utilizar tabelas externas apenas para trazer os dados para serem inseridos em uma tabela interna (nativa) do BigQuery. Desta forma, podemos acessar os dados desta tabela interna e contar com todas as funcionalidades nativas do BigQuery para termos um alto desempenho.
5. Tire o máximo proveito do Cache
Os resultados das queries no BigQuery são armazenados em cache. Uma grande vantagem desta funcionalidade é que se a mesma query rodar novamente o BigQuery não terá o trabalho de executar novamente todo o processamento da query, ou seja, o resultado da query já está pronto e processado na estrutura do cache, basta retornar os dados. Além deste grande ganho de performance, tudo o que for acessado do cache não tem custo.
Vantagens do Cache:
- Melhor performance por retornar dados já processados.
- Dados lidos do cache não tem custo.
O cache não será utilizado caso:
- A query não seja uma réplica exata da query original.
- Em caso de alteração nos dados da tabela acessada pela query. Neste caso o cache é invalidado e após nova execução da query um novo cache será criado.
- Tabelas utilizando ingestão de dados por streaming.
- Funções não determinísticas: CURRENT_TIMESTAMP(), CURRENT_USER(), etc.
- Tabelas externas.
Exemplo:
A query abaixo processou 405.2 Mb e levou 0.9 segundos:
Quando executamos exatamente a mesma query novamente podemos notar que ela não mostrou a quantidade de bytes processados, ela exibiu que o acesso foi ao cache (cached) e levou 0.0 segundos:
Podemos constatar que tivemos um ganho de performance devido ao resultado da query já estar pronto no cache após a primeira execução e não teremos nenhum custo na segunda execução pois o acesso ao cache não é cobrado.
6. Desnormalização e utilização do tipo de dados RECORD
O BigQuery permite que façamos joins entre tabelas da mesma maneira utilizada em bancos de dados relacionais. Porém, uma técnica que pode aumentar performance é denormalizar as tabelas, ou seja, transformar o modelo relacional em uma única tabela.
O principal objetivo é evitar o processamento extra de joins entre tabelas, principalmente quando estamos trabalhando com um grande volume de dados.
Ao denormalizar um modelo, geralmente teremos uma preocupação com a duplicação dos dados, porém com o BigQuery podemos utilizar o tipo de dado RECORD evitando a duplicação de dados conforme exemplo a seguir.
Exemplo:
Digamos que temos uma tabela de pedidos e uma tabela para os itens dos pedidos como em um modelo relacional:
Com a denormalização teremos uma única tabela. Notem que os dados com a origem na tabela PEDIDO como CODIGO_PEDIDO e CLIENTE ficam duplicados:
Ao utilizarmos o tipo de dado RECORD, conseguimos evitar a duplicação de dados sendo que os registros da tabela de ITENS_PEDIDO ficam aninhadas dentro do registro de PEDIDOS em um ARRAY de registros:
Criando uma tabela com o tipo de dado RECORD:
Schema da tabela com tipo de dado RECORD após criada:
JSON com os dados deste exemplo:
{"codigo_pedido":"1","cliente":"Nome Cliente 1","itens":[{"codigo_item":"1010","item":"Shampoo","valor":"30"},{"codigo_item":"1022","item":"Condicionador","valor":"33"},{"codigo_item":"1013","item":"Sabonete","valor":"15"}]}{"codigo_pedido":"2","cliente":"Nome Cliente 2","itens":[{"codigo_item":"1005","item":"Desodorante","valor":"20"},{"codigo_item":"1023","item":"Hidratante","valor":"40"}]}
Ao executar uma consulta nesta tabela com tipo de dados RECORD, podemos visualizar que os dados com origem na tabela de pedidos não estão duplicados:
Podemos utilizar a função UNNEST para desaninhar os registros da estrutura RECORD e então é realizado um JOIN como se fossem duas tabelas de um modelo relacional, porém, internamente, o BigQuery está acessando uma única tabela denormalizada que contém todos os dados de Pedidos e Itens do Pedido.
Com a utilização desta prática podemos evitar joins entre tabelas distintas. Reduzimos o processamento no BigQuery e ainda temos armazenamento otimizado dos dados ao utilizar o tipo de dado RECORD evitando duplicação de dados. Esta técnica proporciona ganho de performance, redução de bytes armazenados, redução de bytes lidos pelas consultas e redução de custo.
7. Utilização de Materialized Views e Smart Tuning
O BigQuery permite a criação de Materialized Views com um conceito muito parecido com os bancos de dados tradicionais, onde o resultado da consulta da Materialized View fica armazenado em uma estrutura como se fosse uma tabela. Desta forma, podemos reduzir consideravelmente o processamento de queries, sendo que o resultado já está pronto e processado, tendo ganho de performance e redução de custo.
As Materialized Views são ideais para consultas com função de agregação (SUM,AVG,COUNT, MIN,MAX… GROUP BY).
Materialialized Views retornam dados atualizados
Esta é uma grande vantagem das Materialized Views no BigQuery. Os dados estão sempre atualizados, mesmo quando a tabela base é alterada. E isto não requer nenhuma ação por parte do usuário ou job para atualizar os dados, como estamos acostumados nos bancos tradicionais.
Todas as alterações na tabela base acessadas pela consulta da Materialized View, chamadas de alterações delta, são combinadas aos dados da Materialized View para que retorne dados atualizados.
Por padrão, a cada 30 minutos as alterações delta são agregadas de fato às Materialized Views. Este intervalo pode ser alterado pelo parâmetro refresh_interval_minute e também podemos executar manualmente a procedure bq.refresh_materialized_view para unificar as alterações delta a materialized view.
Smart Tuning: Um outro grande diferencial das Materialized Views no BigQuery
O BigQuery verifica se uma consulta em uma tabela pode ser atendida por uma Materialized View, caso positivo, ele redireciona internamente para que seja utilizada a Materialized View para obter melhor performance e redução de processamento, pois os resultados já estão prontos e atualizados.
Exemplo:
Temos uma Materialized View funcionarios_por_departamento acessando a tabela funcionarios:
Ao executarmos uma consulta na tabela funcionarios, o BigQuery verifica que esta consulta pode ser atendida por uma Materialized View. De acordo com o plano de execução a seguir, existe um redirecionamento para que os dados sejam acessados na Materialized View funcionarios_por_departamento, mesmo que na consulta esteja implícito para acessar a tabela funcionarios:
Sintaxe:
CREATE MATERIALIZED VIEW `<nome da materialized view>`
AS SELECT <consulta>;8. Sempre analise o plano de execução das consultas
Podemos extrair informações valiosas dos planos de execução, como identificar algum gargalo de performance e verificar qual etapa da consulta está levando mais tempo e consumindo mais recursos.
Um caso muito comum é verificar que uma tabela está sendo acessada por inteiro — o famoso FULL TABLE SCAN, sendo que na consulta temos um filtro em uma ou mais colunas que deve retornar apenas um subconjunto pequeno de dados.
Neste caso, podemos verificar o schema da tabela e avaliar qual será a melhor estratégia, como a utilização de Particionamento ou Clustering para a coluna utilizada no filtro.
Uma boa técnica para definir qual a melhor modelagem para uma tabela é investigar quais são os filtros mais utilizados nas consultas que acessam esta tabela.
O plano de execução das queries pode ser acessado em Execution Details conforme imagem abaixo:
Como podemos avaliar no plano de execução acima, ele acessou quase 59 milhões de registros, o que corresponde à quantidade total de registros desta tabela:
Analisando o resultado da query podemos verificar que ela retornaria cerca de 229 mil registros de quase 59 milhões de registros da tabela:
Caso grande parte das consultas nesta tabela utilize a coluna “start_station_id” no filtro, poderíamos considerar em particionar esta tabela por esta coluna. Como é do tipo de dado integer, poderíamos utilizar particionamento por RANGE. Outra possível solução seria utilizar esta coluna, ou até mesmo mais colunas como Clustering desta tabela.
Enfim, existem muitos casos que podem gerar um plano de execução não otimizado e até mesmo gerar problemas de performance. Muitos casos podem ser facilmente detectados analisando o plano de execução e corrigidos apenas reescrevendo a consulta de uma maneira diferente ou até mesmo sendo necessário uma nova modelagem da tabela.
Conclusão
Devido à alta escalabilidade do BigQuery, muitas vezes não percebemos um possível processamento de dados não otimizado gerando custos desnecessários. Provavelmente estaremos satisfeitos com o tempo de resposta da grande maioria das consultas, pois inúmeros recursos podem ser alocados para atender as consultas de forma mais performática possível como pudemos constatar nos exemplos deste artigo.
Se formos avaliar as consultas com mais atenção, certamente encontraremos muitas oportunidades de otimização dos planos de execução, ganho de performance e o que mais será perceptível, a redução de custo. Logo, a conscientização dos usuários para a utilização de boas práticas, como algumas citadas neste artigo, é de grande importância nas organizações.
