Second Brain

GCP - BigQuery Otimização de Queries

Created Updated 4 min read

Otimizar queries no BigQuery significa principalmente reduzir bytes lidos, que é ao mesmo tempo a métrica de custo (on-demand) e a principal determinante de velocidade. A maioria das otimizações age no storage (como os dados são organizados) antes de chegar na query em si.

Particionamento

Divide fisicamente a tabela em segmentos. Queries com filtro na coluna de partição leem apenas as partições relevantes (partition pruning).

Tipos de partição

-- Por coluna DATE ou TIMESTAMP
CREATE TABLE dataset.eventos
PARTITION BY DATE(event_time);
 
-- Por coluna INTEGER (range)
CREATE TABLE dataset.usuarios
PARTITION BY RANGE_BUCKET(id, GENERATE_ARRAY(0, 10000000, 100000));
 
-- Por ingest time (sem coluna explícita)
CREATE TABLE dataset.logs
PARTITION BY _PARTITIONDATE;

Regras para partition pruning funcionar

O filtro na coluna de partição precisa ser uma expressão constante ou baseada em funções determinísticas. Aplicar funções não determinísticas (como CURRENT_DATE() em certas situações) pode inibir o pruning:

-- RUIM: função na coluna de partição inibe pruning
WHERE DATE(event_time) = DATE_SUB(DATE(CURRENT_TIMESTAMP()), INTERVAL 1 DAY)
 
-- BOM: filtrar diretamente
WHERE event_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND event_time < CURRENT_TIMESTAMP()

Verificar se pruning está acontecendo no Query Plan (aba “Execution details” no console): procurar por partitions_pruned.

Clustering

Ordena os dados dentro de cada partição pelas colunas especificadas. Reduz bytes lidos em filtros e JOIN nas colunas de cluster.

CREATE TABLE dataset.vendas
PARTITION BY DATE(data_venda)
CLUSTER BY regiao, categoria, produto_id;
  • Até 4 colunas de cluster, em ordem de seletividade decrescente (coluna mais filtrada primeiro)
  • O BigQuery reorganiza automaticamente os blocos de dados à medida que a tabela cresce (auto-reclustering), sem custo adicional
  • Mais eficiente que índices tradicionais para queries analíticas

Materialized Views

Resultado de uma query persistido e atualizado de forma incremental pelo BigQuery. Queries que “se encaixam” na view são reescritas automaticamente para usá-la:

CREATE MATERIALIZED VIEW dataset.vendas_diarias
PARTITION BY data
CLUSTER BY regiao
AS
SELECT
  DATE(data_venda)    AS data,
  regiao,
  SUM(valor)          AS receita,
  COUNT(*)            AS qtd_pedidos
FROM dataset.vendas
GROUP BY 1, 2;
  • Atualização incremental automática quando a tabela base muda
  • Smart rewrite: o BigQuery redireciona automaticamente queries elegíveis para a view materializada, mesmo sem referenciá-la explicitamente
  • Limitação: não suporta todas as funções SQL (ex: funções de janela, subconsultas correlacionadas)

Plano de execução

O console BigQuery mostra o plano de execução de cada job na aba “Execution details”:

graph TD
    S1["Input - 12/365 partições lidas"] --> S2["Filter - 2.3 GB processados"]
    S2 --> S3["Aggregate - 150k linhas"]
    S3 --> S4["Output - destino: resultado"]

O que observar:

  • Partitions read vs total: confirma que partition pruning está funcionando
  • Bytes shuffled: alto shuffle indica JOINs ou GROUP BYs pesados, candidatos a otimização
  • Slot utilization: slots ociosos indicam straggler workers ou dados muito skewed

Via API/bq:

bq show --format=prettyjson --job meu-projeto:US.job_id | jq '.statistics.query.queryPlan'

Evitar problemas comuns

Skew de dados (data skew)

Quando a distribuição de dados em uma chave de JOIN ou GROUP BY é muito desigual, alguns workers ficam sobrecarregados enquanto outros ficam ociosos:

-- Sintoma: slot utilization irregular no plano de execução
-- Solução: usar approximate aggregation quando exatidão não é crítica
SELECT
  APPROX_COUNT_DISTINCT(usuario_id) AS usuarios_unicos,
  APPROX_QUANTILES(valor, 100)[OFFSET(50)] AS mediana_valor
FROM eventos

Cartesian product acidental

JOIN sem condição ou com condição inadequada pode explodir o número de linhas:

-- PERIGOSO: cross join acidental
SELECT a.*, b.*
FROM tabela_a a, tabela_b b   -- vírgula = CROSS JOIN
 
-- CORRETO
SELECT a.*, b.*
FROM tabela_a a
JOIN tabela_b b ON a.id = b.id

Funções em colunas de partição no WHERE

-- RUIM: impede partition pruning
WHERE EXTRACT(YEAR FROM data_evento) = 2026
 
-- BOM
WHERE data_evento BETWEEN '2026-01-01' AND '2026-12-31'

Time Travel e clones

O BigQuery mantém histórico de até 7 dias para qualquer tabela:

-- Consultar estado passado
SELECT * FROM dataset.pedidos
FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 HOUR)
 
-- Restaurar tabela para ponto anterior
CREATE OR REPLACE TABLE dataset.pedidos
AS SELECT * FROM dataset.pedidos
FOR SYSTEM_TIME AS OF '2026-05-04 18:00:00 UTC'

Table clones: cópia zero-cost de uma tabela. Armazena apenas o delta em relação à tabela original:

CREATE TABLE dataset.pedidos_clone
CLONE dataset.pedidos
FOR SYSTEM_TIME AS OF CURRENT_TIMESTAMP();

Table snapshots: ponto de restauração imutável, sem delta incremental:

CREATE SNAPSHOT TABLE dataset.pedidos_snapshot_20260505
CLONE dataset.pedidos;

Análise de desempenho com INFORMATION_SCHEMA

-- Queries com mais bytes shuffled (candidatas a otimização de JOIN)
SELECT
  job_id,
  ROUND(total_bytes_processed / POW(1024, 3), 1) AS gb_lidos,
  ROUND(total_slot_ms / 1000, 0)                  AS slot_segundos,
  SUBSTR(query, 0, 120)                           AS query
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  DATE(creation_time) = CURRENT_DATE()
  AND statement_type = 'SELECT'
ORDER BY total_slot_ms DESC
LIMIT 20;
 
-- Tabelas mais acessadas
SELECT
  referenced_table.table_id,
  COUNT(*)                                         AS acessos,
  SUM(total_bytes_processed) / POW(1024, 3)       AS gb_total
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT,
UNNEST(referenced_tables) AS referenced_table
WHERE DATE(creation_time) >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
GROUP BY 1
ORDER BY 3 DESC
LIMIT 20;

Ver também: gcp-bigquery | gcp-bigquery-cobranca | gcp-bigquery-sql | gcp-boas-praticas

Graph View

Backlinks