GCP - BigQuery SQL

O BigQuery usa o GoogleSQL (anteriormente chamado de Standard SQL), um dialeto que é majoritariamente compatível com ANSI SQL mas com extensões importantes para lidar com dados semiestruturados, escala e produtividade.

Diferenças sintáticas do ANSI SQL

Referência a tabelas

Tabelas são identificadas por três partes separadas por ponto. Quando o nome contém hífens ou caracteres especiais, usar crases:

-- Forma padrão
SELECT * FROM projeto.dataset.tabela
 
-- Com crases (obrigatório se o project_id tiver hífens)
SELECT * FROM `meu-projeto.dataset.tabela`
 
-- Wildcard para múltiplas tabelas com sufixo (ex: tabelas por ano)
SELECT * FROM `projeto.dataset.eventos_*`
WHERE _TABLE_SUFFIX BETWEEN '2025' AND '2026'

EXCEPT e REPLACE em SELECT *

-- Selecionar todas as colunas exceto algumas
SELECT * EXCEPT (coluna_sensivelidade, coluna_interna)
FROM tabela
 
-- Substituir o valor de uma coluna mantendo as demais
SELECT * REPLACE (UPPER(nome) AS nome, preco * 1.1 AS preco)
FROM tabela

QUALIFY: filtrar sobre window functions

Equivalente ao HAVING mas para funções de janela. Evita a necessidade de uma subquery:

-- Sem QUALIFY (subquery necessária)
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY usuario_id ORDER BY ts DESC) AS rn
  FROM eventos
) WHERE rn = 1
 
-- Com QUALIFY (mais limpo)
SELECT *
FROM eventos
QUALIFY ROW_NUMBER() OVER (PARTITION BY usuario_id ORDER BY ts DESC) = 1

TABLESAMPLE

Amostra aleatória de uma tabela sem ler todos os dados, útil para exploração barata:

SELECT * FROM tabela_grande TABLESAMPLE SYSTEM (1 PERCENT)

FOR SYSTEM_TIME AS OF (time travel)

Consulta o estado histórico de uma tabela em um momento passado (até 7 dias):

-- Como a tabela estava ontem
SELECT * FROM meu_dataset.pedidos
FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
 
-- Recuperar linhas deletadas acidentalmente
INSERT INTO meu_dataset.pedidos
SELECT * FROM meu_dataset.pedidos
FOR SYSTEM_TIME AS OF '2026-05-04 10:00:00 UTC'
WHERE id NOT IN (SELECT id FROM meu_dataset.pedidos)

Tipos ARRAY e STRUCT

São os tipos mais distintos do BigQuery em relação a bancos relacionais tradicionais.

ARRAY

Coluna que contém uma lista de valores do mesmo tipo:

-- Criar array literal
SELECT [1, 2, 3] AS numeros
 
-- Array de structs (comum em dados de eventos)
SELECT
  usuario_id,
  ARRAY_AGG(STRUCT(produto_id, quantidade, preco) ORDER BY preco DESC) AS itens
FROM pedidos
GROUP BY usuario_id
 
-- Funções de array
SELECT
  ARRAY_LENGTH(itens)                        AS qtd_itens,
  (SELECT SUM(i.preco) FROM UNNEST(itens) i) AS total
FROM pedidos_com_itens

STRUCT

Registro aninhado (equivale a um objeto JSON ou uma linha dentro de uma coluna):

-- Criar struct literal
SELECT STRUCT('Ana' AS nome, 30 AS idade) AS pessoa
 
-- Acessar campo do struct
SELECT pessoa.nome, endereco.cidade
FROM clientes
 
-- Struct com array (padrão comum em dados desnormalizados)
SELECT
  pedido_id,
  cliente.nome,
  cliente.endereco.cidade,
  item.produto_id
FROM pedidos,
UNNEST(itens) AS item   -- expande o array de itens

UNNEST

Transforma um ARRAY em linhas. Essencial para trabalhar com dados aninhados:

-- Cada elemento do array vira uma linha
SELECT
  p.pedido_id,
  item.produto_id,
  item.quantidade
FROM pedidos AS p,
UNNEST(p.itens) AS item
 
-- Com índice (OFFSET)
SELECT
  p.pedido_id,
  item,
  posicao
FROM pedidos AS p,
UNNEST(p.tags) AS item WITH OFFSET AS posicao

Funções exclusivas ou notáveis

Agregação condicional

SELECT
  COUNTIF(status = 'ativo')                     AS ativos,
  COUNTIF(status = 'inativo')                   AS inativos,
  AVG(IF(status = 'ativo', valor, NULL))         AS ticket_medio_ativos,
  STRING_AGG(nome, ', ' ORDER BY nome LIMIT 5)  AS primeiros_5
FROM clientes

Datas e timestamps

SELECT
  DATE_TRUNC(data_criacao, MONTH)                        AS mes,
  DATE_DIFF(data_entrega, data_criacao, DAY)              AS dias_entrega,
  EXTRACT(DAYOFWEEK FROM data_criacao)                    AS dia_semana,
  FORMAT_DATE('%Y-%m', data_criacao)                      AS ano_mes,
  TIMESTAMP_MICROS(ts_unix_micros)                        AS ts,
  DATETIME(ts, 'America/Sao_Paulo')                       AS ts_local
FROM pedidos

JSON

-- Extrair campo de coluna JSON (string)
SELECT
  JSON_VALUE(payload, '$.usuario.id')         AS usuario_id,
  JSON_VALUE(payload, '$.evento.tipo')        AS tipo_evento,
  CAST(JSON_VALUE(payload, '$.valor') AS FLOAT64) AS valor
FROM eventos_raw
 
-- Parsear JSON para tipo nativo
SELECT JSON_QUERY(payload, '$.itens') AS itens_json
FROM pedidos_raw

Strings

SELECT
  REGEXP_EXTRACT(email, r'@(.+)$')     AS dominio,
  REGEXP_REPLACE(texto, r'\s+', ' ')   AS texto_normalizado,
  SPLIT(categorias, ',')               AS array_categorias,
  TO_BASE64(dados_binarios)            AS base64
FROM tabela

SQL Procedural (scripting)

O BigQuery suporta scripts multi-statement com variáveis, condicionais e loops:

DECLARE total INT64;
DECLARE data_inicio DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY);
 
-- Contar registros
SET total = (
  SELECT COUNT(*) FROM meu_dataset.pedidos
  WHERE DATE(criado_em) >= data_inicio
);
 
-- Condicional
IF total > 10000 THEN
  SELECT FORMAT("Alto volume: %d pedidos", total);
ELSE
  SELECT FORMAT("Volume normal: %d pedidos", total);
END IF;
 
-- Loop com WHILE
DECLARE i INT64 DEFAULT 1;
WHILE i <= 5 DO
  INSERT INTO meu_dataset.log VALUES (i, CURRENT_TIMESTAMP());
  SET i = i + 1;
END WHILE;

Stored Procedures

CREATE OR REPLACE PROCEDURE meu_dataset.processar_pedidos(
  IN data_ref DATE,
  OUT total_processados INT64
)
BEGIN
  DELETE FROM meu_dataset.staging WHERE DATE(ts) = data_ref;
 
  INSERT INTO meu_dataset.pedidos_processados
  SELECT * FROM meu_dataset.staging_raw WHERE DATE(ts) = data_ref;
 
  SET total_processados = @@ROW_COUNT;
END;
 
-- Chamar a procedure
DECLARE resultado INT64;
CALL meu_dataset.processar_pedidos(CURRENT_DATE(), resultado);
SELECT resultado;

PIVOT e UNPIVOT

-- PIVOT: linhas para colunas
SELECT *
FROM (
  SELECT mes, categoria, receita FROM vendas
)
PIVOT (
  SUM(receita)
  FOR categoria IN ('eletronicos', 'roupas', 'alimentos')
)
 
-- UNPIVOT: colunas para linhas
SELECT mes, categoria, receita
FROM vendas_wide
UNPIVOT (
  receita FOR categoria IN (eletronicos, roupas, alimentos)
)

WITH RECURSIVE

-- Expandir hierarquia de categorias
WITH RECURSIVE hierarquia AS (
  SELECT id, nome, parent_id, 0 AS nivel
  FROM categorias
  WHERE parent_id IS NULL
 
  UNION ALL
 
  SELECT c.id, c.nome, c.parent_id, h.nivel + 1
  FROM categorias c
  JOIN hierarquia h ON c.parent_id = h.id
)
SELECT * FROM hierarquia ORDER BY nivel, nome

Table-Valued Functions (TVFs)

Funções que retornam uma tabela, reutilizáveis em queries:

CREATE OR REPLACE TABLE FUNCTION meu_dataset.pedidos_por_periodo(
  inicio DATE,
  fim    DATE
)
AS (
  SELECT * FROM meu_dataset.pedidos
  WHERE DATE(criado_em) BETWEEN inicio AND fim
);
 
-- Usar a TVF
SELECT * FROM meu_dataset.pedidos_por_periodo('2026-01-01', '2026-03-31')

Ver também: gcp-bigquery | gcp-bigquery-otimizacao | gcp-bigquery-cobranca | gcp-bigquery-dataform