ClickHouse para busca de empresas: como modelar dezenas de milhões de CNPJs

Uma busca de empresas parece simples: o usuário escolhe setor, estado, porte e recebe uma lista. Por trás, cada clique dispara uma combinação de filtros sobre dezenas de milhões de estabelecimentos, uma contagem total e, muitas vezes, contagens por faceta ("quantos resultados em cada UF?"). Num banco transacional tradicional, isso vira uma coleção de índices compostos que nunca cobre todas as combinações.

O ClickHouse resolve esse padrão de acesso muito bem: é colunar, lê só as colunas da consulta, comprime bastante e varre blocos em paralelo. Mas o desempenho depende quase inteiro de uma decisão de modelagem: a chave de ordenação. Este artigo mostra como modelar a tabela de estabelecimentos, com exemplos que você pode adaptar.

O padrão de acesso define o modelo

Antes de escrever o CREATE TABLE, liste as consultas reais. Numa busca de empresas, elas costumam ser:

1. Filtro + contagem: situação ativa, UF, CNAE, porte → total de resultados.
2. Filtro + página de resultados: os mesmos filtros, 50 linhas por vez.
3. Facetas: contagens agrupadas por UF, porte ou CNAE para os filtros atuais.
4. Busca por nome: trecho da razão social ou do nome fantasia.
5. Detalhe por CNPJ: uma linha, por chave exata.

Quase todas filtram situação cadastral e localização, e muitas filtram setor. Essa observação vai guiar a chave.

A tabela

CREATE TABLE estabelecimentos
(
    cnpj               FixedString(14),
    cnpj_basico        FixedString(8),
    matriz             Bool,
    situacao           LowCardinality(String),
    uf                 LowCardinality(String),
    municipio          LowCardinality(String),
    cnae_principal     LowCardinality(String),
    cnae_divisao       LowCardinality(String) MATERIALIZED substring(cnae_principal, 1, 2),
    cnaes_secundarios  Array(LowCardinality(String)),
    porte              LowCardinality(String),
    natureza_juridica  LowCardinality(String),
    razao_social       String,
    nome_fantasia      String,
    capital_social     Decimal(18, 2),
    data_inicio        Date32,
    referencia         Date,

    INDEX idx_secundarios cnaes_secundarios TYPE bloom_filter(0.01) GRANULARITY 4,
    INDEX idx_razao lower(razao_social) TYPE ngrambf_v1(3, 65536, 3, 0) GRANULARITY 4,
    INDEX idx_fantasia lower(nome_fantasia) TYPE ngrambf_v1(3, 65536, 3, 0) GRANULARITY 4,
    INDEX idx_inicio data_inicio TYPE minmax GRANULARITY 1
)
ENGINE = MergeTree
ORDER BY (situacao, uf, cnae_divisao, cnae_principal, municipio, cnpj);

Vamos às decisões, uma a uma.

Chave de ordenação: do filtro mais comum ao mais específico

No MergeTree, os dados ficam fisicamente ordenados pela chave, e o índice primário guarda uma marca a cada bloco de linhas (8.192 por padrão). Quando a consulta filtra pelas primeiras colunas da chave, o ClickHouse pula blocos inteiros sem lê-los.

A regra prática é ordenar as colunas da chave:

• das que mais aparecem nos filtros para as que aparecem menos;
• de menor cardinalidade para maior, entre as que aparecem com frequência parecida.

situacao vem primeiro porque quase toda busca filtra empresas ativas. Depois uf, presente na maioria das buscas regionais. Em seguida a divisão e a subclasse do CNAE, o município e, por fim, o cnpj, que torna a ordenação única e permite paginação estável.

Uma consulta que filtra só por CNAE, sem UF, ainda funciona, mas aproveita menos o índice. Se esse padrão for frequente, há saídas, como veremos nas projeções.

Tipos que economizam espaço e tempo

• LowCardinality(String) para colunas com poucos valores distintos (UF, porte, situação, CNAE, município). O ClickHouse guarda um dicionário e compara inteiros internamente.
• FixedString(14) para o CNPJ. Funciona para o formato numérico e para o alfanumérico, que tem letras na raiz e na ordem. Nunca use tipo numérico para CNPJ.
• Date32 para a data de início de atividade. O tipo Date só representa datas a partir de 1970, e o cadastro tem empresas abertas muito antes disso. Com Date, elas seriam corrompidas.
• Decimal(18, 2) para valores monetários.
• MATERIALIZED para colunas derivadas, como a divisão do CNAE: calculada na inserção, sem custo na consulta.

Índices de salto para o que não está na chave

Os índices de salto (data skipping indexes) guardam um resumo por grupo de blocos e permitem descartar os que certamente não contêm o valor procurado:

• bloom_filter no array de CNAEs secundários acelera has e hasAny;
• ngrambf_v1 sobre o nome em minúsculas acelera buscas com LIKE '%trecho%';
• minmax na data de início ajuda filtros por faixa de idade.

Eles não são índices tradicionais: se o valor está espalhado por todos os blocos, não há o que pular. Meça sempre (veja a seção sobre EXPLAIN).

As consultas

Contagem e lista com filtros combinados

SELECT cnpj, razao_social, municipio
FROM estabelecimentos
WHERE situacao = '02'
  AND uf = 'SP'
  AND (cnae_principal IN ('6201501', '6202300')
       OR hasAny(cnaes_secundarios, ['6201501', '6202300']))
  AND porte IN ('01', '03')
  AND data_inicio <= today() - INTERVAL 3 YEAR
ORDER BY cnpj
LIMIT 50;

Tamanho de mercado: estabelecimentos e empresas

SELECT uf,
       count()                  AS estabelecimentos,
       uniqExact(cnpj_basico)   AS empresas
FROM estabelecimentos
WHERE situacao = '02' AND cnae_divisao = '62'
GROUP BY uf
ORDER BY empresas DESC;

uniqExact dá o número exato de raízes distintas. Para painéis em que uma aproximação basta, uniq é mais leve.

Facetas numa única leitura

Em vez de uma consulta por faceta, use agregações condicionais quando as categorias são conhecidas:

SELECT
    countIf(porte = '01') AS me,
    countIf(porte = '03') AS epp,
    countIf(porte = '05') AS demais,
    count()               AS total
FROM estabelecimentos
WHERE situacao = '02' AND cnae_divisao = '62' AND uf = 'SP';

Para facetas com muitos valores (municípios, CNAEs), um GROUP BY por faceta continua sendo o caminho, de preferência em paralelo na aplicação.

Paginação por cursor, não por OFFSET

LIMIT 50 OFFSET 50000 obriga o banco a ler e descartar 50 mil linhas. Com a ordenação da tabela, a paginação por cursor (keyset) é muito mais eficiente: a aplicação guarda a última chave da página e pede as próximas.

SELECT cnpj, razao_social
FROM estabelecimentos
WHERE situacao = '02' AND uf = 'SP' AND cnae_divisao = '62'
  AND (cnae_principal, municipio, cnpj) > ('6201501', '6291', '12345678000195')
ORDER BY cnae_principal, municipio, cnpj
LIMIT 50;

A tupla do cursor segue a ordem da chave, a partir das colunas que não estão fixadas por igualdade no filtro.

Meça com EXPLAIN

O ClickHouse mostra quantas partes e blocos cada índice descartou:

EXPLAIN indexes = 1
SELECT count()
FROM estabelecimentos
WHERE situacao = '02' AND uf = 'SP' AND cnae_divisao = '62';

Na saída, compare os blocos selecionados (granules) com o total. Se um filtro importante não reduz quase nada, a chave ou o índice não estão ajudando. A tabela system.query_log complementa a análise com linhas lidas, bytes lidos e tempo de cada consulta real.

Projeções para padrões de acesso alternativos

Se uma parte relevante das buscas filtra por município sem UF, ou por CNAE sem localização, uma projeção guarda uma cópia dos dados com outra ordenação, mantida automaticamente pelo ClickHouse:

ALTER TABLE estabelecimentos
    ADD PROJECTION por_municipio (SELECT * ORDER BY municipio, cnae_principal, cnpj);

ALTER TABLE estabelecimentos MATERIALIZE PROJECTION por_municipio;

O otimizador escolhe a projeção quando ela lê menos dados. O custo é espaço em disco e tempo de inserção, então crie projeções só para padrões medidos, não por precaução.

Carga mensal sem downtime

O cadastro da Receita é uma fotografia mensal. Em vez de aplicar milhões de UPDATE, é mais simples e seguro carregar o mês inteiro numa tabela nova e trocar as duas de uma vez:

CREATE TABLE estabelecimentos_novo AS estabelecimentos;

-- carregue o mês em estabelecimentos_novo e valide contagens

EXCHANGE TABLES estabelecimentos_novo AND estabelecimentos;

O EXCHANGE TABLES é atômico em bancos com o motor Atomic (o padrão nas versões atuais): as consultas em andamento terminam na tabela antiga e as novas já veem a nova. Se a validação falhar, basta não trocar. Guarde a tabela anterior por um tempo: ela é a base para calcular o que mudou de um mês para o outro.

Lições práticas

• Comece pelas consultas, não pelo esquema. A chave de ordenação é difícil de mudar depois.
• Use LowCardinality sem medo em colunas categóricas; é um dos ganhos mais baratos.
• Date32 para datas antigas. Esse erro só aparece quando alguém pergunta por que uma empresa de 1950 "abriu em 1970".
• Evite FINAL e OFFSET grandes no caminho crítico da busca.
• Meça cada índice com EXPLAIN e com o query_log, sempre com dados reais.
• Troque tabelas inteiras em vez de atualizar linha a linha nas cargas mensais.

Conclusão

Com uma chave de ordenação que segue os filtros mais comuns, tipos adequados, alguns índices de salto bem escolhidos e paginação por cursor, o ClickHouse entrega buscas e contagens interativas sobre todo o cadastro de empresas do país, sem exigir um cluster grande. O restante é disciplina: medir, ajustar e evitar otimizações que ninguém pediu.

Para ver uma busca de empresas com dezenas de filtros funcionando, experimente a CNPJpedia.