Monitoramento do ambiente do GCP BigQuery
O BigQuery ficou bastante conhecido como um banco colunar de altíssima performance, autogerenciável e com um custo que, dependendo do uso, podendo ser alto. O GCP revisou o modelo de precificação do BigQuery, que será praticado a partir de julho de 2023. Esta nova precificação estará dividida em quatro grandes modelos [1]:
- Sob demanda: 6,25 USD por terabyte consultado;
- Standard: 0,04 USD por slot por hora;
- Enterprise: 0,06 USD por slot por hora;
- Enterprise plus: 0,10 USD por slot por hora.
Os novos planos do BigQuery – Standard, Enterprise e Enterprise Plus – partem da medida de slots para definir o patamar inicial e o final. O patamar inicial é uma contratação mínima, em que você indica o quanto de slots irá consumir (e pagar) no mínimo. Da mesma forma, o patamar final é a quantidade de slots máxima que você irá deixar disponível para os usuários do seu ambiente – isso é uma forma de liberar unidades de processamento para momentos de altíssimo uso, mas ainda com algum controle. Dessa forma, para esses planos será necessário definir o mínimo e o máximo baseado em slots, que são unidades de processamento do BigQuery – e, para melhor entender como realizar essa contratação, é necessário entender como os slots são consumidos pelo BigQuery.
Esses novos modelos de cobrança vão exigir um pouco mais de entendimento de como essa plataforma funciona. Se o plano escolhido for o sob demanda, ações de otimização irão lhe ajudar apenas em gerir melhor os custos e o tempo de resposta; já se o plano não for o sob demanda, os recursos de processamento estarão limitados de alguma forma e consultas mal dimensionadas irão afetar o seu ambiente aumentando significativamente o tempo de resposta. Em um ambiente produtivo, isso significa que um processo importante que costuma executar em até três minutos pode demorar demais ou até não responder em um ambiente com recursos limitados. Os ofensores do ambiente podem ser vários: desde uma consulta mal dimensionada, até uma série de consultas bem dimensionadas sendo executadas no mesmo intervalo e com a mesma prioridade.
Este texto tem como objetivo prover as devidas ferramentas para a análise dos dados de uso do BigQuery com o intuito de identificar os principais ofensores do ambiente, garantindo produtividade e rodando de modo suave e perene. Além disso, parto do pressuposto de que a contratação de slots mínimos e máximos já foi uma ótima contratação.
O que são slots
Slots são unidades de processamento virtuais criadas e usadas pelo BigQuery para executar as consultas SQL. A cada consulta executada, o BigQuery irá calcular automaticamente quantas unidades de processamento serão necessárias para executar a consulta. O cálculo é feito sempre depois do início da execução, e modificado enquanto estiver em execução. [2]
A partir do momento em que fazemos uma contratação de slots, é necessário entender que o limite de slots será o máximo de capacidade de processamento. Isso poderá impactar o ambiente, aumentando o tempo de resposta das consultas concorrentes – as consultas que são executadas ao longo do mesmo período – ou de consultas mal dimensionadas.
Vamos na próxima seção entender como consultar o INFORMATION_SCHEMA.JOBS para clarificarmos tanto quantos slots seriam necessários contratar para o ambiente funcionar plenamente, como também metrificar como essas unidades de processamento estão sendo usadas pelas equipes – identificando aqui oportunidades de otimização.
Identificando consultas que impactam o ambiente
O BigQuery atualmente disponibiliza um acesso mais fácil aos seus logs através das tabelas do INFORMATION_SCHEMA.JOBS [3], em que é possível consultar dados sobre as execuções realizadas e as correntes. O log é retido automaticamente por até 180 dias. Atente que a consulta a esse log através do próprio BigQuery irá consumir do seu processamento ou, caso esteja no sob demanda, será uma consulta cobrada.
Métricas
Usando a tabela de logs citada acima, vamos nos concentrar em métricas que ajudarão a definir a complexidade de uma consulta – ajudando assim a entender como o ambiente é usado pelas equipes e como podemos otimizar. Todas as métricas citadas se utilizam de campos encontrados dentro da tabela INFORMATION_SCHEMA.JOBS.
- Duração da consulta: é a duração da execução a partir da diferença entre o momento do início da execução e o término. Recomendo mensurar em segundos mesmo, usando a função TIMESTAMP_DIFF(end_time,start_time,SECOND).
- Consumo de slots: é a quantidade de slots utilizados por aquela consulta em milissegundos, e está disponível em total_slots_ms.
- Quantidade de passos: passos são etapas de um estágio da execução da consulta, a quantidade de passos total é, portanto, o somatório de quantos passos foram necessários para a execução da consulta. Está disponível no campo job_stages.steps e recomendo o uso da função ARRAY_LENGTH(steps) para calcular a quantidade.
- Quantidade de referências: referências são todas as tabelas usadas ao longo da consulta, seja dentro do FROM, seja dentro de um JOIN. As tabelas referenciadas estão disponíveis dentro do campo referenced_tables, e podemos calcular a quantidade de referências através da função ARRAY_LENGTH. Execuções com muitas referências tornarão a consulta mais complexa por necessitarem mais etapas de leitura de dados e mais etapas de processamento realizando multiplicações matriciais.
As métricas acima ajudam a validar como o ambiente do BigQuery está sendo utilizado. Se muitas consultas são feitas com uso excessivo de referências (joins), talvez faça sentido entender como esses cruzamentos são feitos e criar um datamart com essas visões – facilitando o trabalho do usuário final, como também tornando o cruzamento repetido de informações em um único processamento separado e otimizado.
Todas métricas supracitadas podem ser agrupadas, facilitando a análise, pelo campo query_info.query_hashes.normalized_literals – campo criado pelo BigQuery que exclui da consulta as linhas em branco, comentários da consulta, valores de parâmetros, funções definidas pelo usuário e literais e, depois disso, transforma em um texto hexadecimal chamado de hash. É importante ressaltar que uma mesma hash de consulta pode possuir N execuções e que cada execução é única – e por causa disso, uma mesma hash de consulta pode ter:
- diferentes executores;
- diferentes job_id;
- diferentes tempos de execução;
- diferentes quantidades de passos;
- diferente consumo de slots;
- diferente quantidade de bytes processados e cobrados.
Ao agrupar diversas execuções pela hash de consulta estamos apenas agrupando consultas bastante similares, que em sua maioria só não são iguais por causa de um filtro. Já quando agrupamos as métricas pelas hashes de consulta, é possível encontrar as consultas que são mais repetidamente executadas e identificar o grau de complexidade dessa consulta – tornando-a ou não elegível para uma otimização.
Abaixo temos um exemplo de código de consulta para as métricas citadas acima, adicionando algumas informações como o tipo de execução e de transação e agrupando pelo hash de consulta.
WITH
base_infoschema AS (
SELECT
-- Dados para Identificação da Execução
CAST(todas_execucoes.creation_time AS DATE) AS data_criacao,
todas_execucoes.project_id AS id_do_projeto, -- id do projeto em que a consulta foi executada
todas_execucoes.job_id AS id_da_execucao, -- ID da execução, encontrado no BQ como jobId
todas_execucoes.job_type AS tipo_de_execucao,
todas_execucoes.statement_type AS tipo_de_transacao,
todas_execucoes.user_email AS usuario, -- email do usuário executor da consulta
todas_execucoes.query_info.query_hashes.normalized_literals AS hash_da_consulta, -- identificador de consulta frequente
todas_execucoes.query AS consulta_executada,
-- MÉTRICAS:
-- duração da consulta em segundos:
TIMESTAMP_DIFF(todas_execucoes.end_time,todas_execucoes.start_time,SECOND) AS duracao_consulta_em_segundos,
-- quantidade de passos:
(SELECT SUM(ARRAY_LENGTH(stgs.steps)) FROM UNNEST(job_stages) AS stgs) AS qtd_passos,
-- total de slots em milissegundos usados pela execução ao longo de toda a duração da execução
todas_execucoes.total_slot_ms AS total_de_slots_em_ms,
-- Identificação de todas as tabelas referenciadas na consulta, seja no from, join ou union.
ARRAY_AGG((
SELECT AS STRUCT
CONCAT(r2.project_id, '-', r2.dataset_id, '-', r2.table_id) AS nome_tabela_referenciada
)) AS tabelas_referenciadas
FROM `region-us.INFORMATION_SCHEMA.JOBS` AS todas_execucoes
LEFT JOIN todas_execucoes.referenced_tables AS r2
WHERE 1=1
-- filtrando os dados dos últimos 10 dias e apenas consultas executadas com sucesso.
AND creation_time >= TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL -10 DAY)
AND state = 'DONE'
GROUP BY
data_criacao,
id_do_projeto,
id_da_execucao,
tipo_de_execucao,
tipo_de_transacao,
usuario,
duracao_consulta_em_segundos,
qtd_passos,
total_de_slots_em_ms,
hash_da_consulta,
consulta_executada
)
SELECT
data_criacao,
hash_da_consulta, -- identificador de consulta frequente
-- MÉTRICAS:
COUNT(id_da_execucao) AS frequencia,
AVG(duracao_consulta_em_segundos) AS duracao_media_consulta_em_segundos,
AVG(qtd_passos) AS qtd_media_de_passos,
AVG(total_de_slots_em_ms) AS media_de_slots_ms,
AVG(ARRAY_LENGTH(tabelas_referenciadas)) AS qtd_de_referencias
FROM base_infoschema
GROUP BY
data_criacao,
hash_da_consulta
ORDER BY data_criacao DESCAo executarmos a consulta, teremos como resultado uma tabela parecida com a ilustrada abaixo. Atente que na linha 7 temos uma consulta que, apesar de não ser a mais executada, possui mais de 500 passos e 27 referências. Essa é uma consulta claramente candidata a ser revisada e otimizada.
Ao identificar o hash da consulta candidata a ser revisada, vamos voltar na consulta e procurar identificar as execuções desse hash através do campo id_da_execucao e também visualizarmos o código que gerou a consulta através do campo consulta_executada. Ambos os campos estão dentro do código acima — na CTE base_infoschema. Com o código da consulta em mãos, podemos começar o processo de otimização da consulta, que dependerá das peculiaridades da consulta selecionada.
Conclusão
Como podemos ver, a tabela INFORMATION_SCHEMA.JOBS já contém informações suficientes para o monitoramento do ambiente, de modo que é possível entender e atuar em possíveis ofensores do ambiente para deixá-lo o mais otimizado possível. A exploração dessa tabela é bastante recomendável também por conter diversos metadados sobre tudo o que é executado dentro do BigQuery — em nosso caso interno, conseguimos inclusive identificar a origem das execuções, facilitando a identificação de como cada aplicação influencia no ambiente.
Por fim, a limitação de processamento vista em alguns modelos de contratação do BigQuery não é impeditivo para ser um ambiente otimizado e de baixo custo de manutenção.
Para conhecer mais sobre como é possível monitorar a utilização das diversas camadas do data lake, você pode ler mais em Monitoramento de Utilização das Camadas do Data Lake no contexto de Self-Service BI na Localiza | by Claudio Manoel da Silva e Sousa Neto | #LocalizaLabs | May, 2023 | Medium.
Referências:
- Introducing new BigQuery pricing editions | Google Cloud Blog
- Understand slots | BigQuery | Google Cloud
- JOBS view | BigQuery | Google Cloud
- Monitoramento de Utilização das Camadas do Data Lake no contexto de Self-Service BI na Localiza | by Claudio Manoel da Silva e Sousa Neto | #LocalizaLabs | May, 2023 | Medium
