---
title: "ClickHouse para busca de empresas: como modelar dezenas de milhões de CNPJs"
description: "Como modelar o cadastro de CNPJ no ClickHouse: chave de ordenação, tipos, índices de salto, paginação por cursor e carga mensal sem downtime."
url: "https://cnpjpedia.com.br/blog/clickhouse-busca-empresas-modelagem"
date: "2026-10-02"
updated: "2026-10-02"
tags: ["Engenharia de dados", "ClickHouse", "Busca"]
reading_time_minutes: 7
author: "CNPJpedia"
language: "pt-BR"
---

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

> Como modelar o cadastro de CNPJ no ClickHouse: chave de ordenação, tipos, índices de salto, paginação por cursor e carga mensal sem downtime.

Publicado em 2 de outubro de 2026 · 7 min de leitura · Tags: Engenharia de dados, ClickHouse, Busca

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

```sql
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

```sql
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

```sql
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:

```sql
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.

```sql
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:

```sql
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:

```sql
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:

```sql
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](https://cnpjpedia.com.br/cadastro).

## Leia também

- [Diffs mensais do CNPJ: como transformar snapshots da Receita em sinais de prospecção](https://cnpjpedia.com.br/blog/diffs-mensais-cnpj-sinais-prospeccao.md): Sinais extraídos da comparação mensal do cadastro do CNPJ (filiais, CNAE, endereço, sócios) e como calculá-los em SQL sem falsos positivos.
- [Processando o dump do CNPJ com Polars: do ZIP ao Parquet](https://cnpjpedia.com.br/blog/dump-cnpj-polars-zip-parquet.md): Tutorial para carregar o dump do CNPJ com Polars: conversão de latin-1, leitura lazy, tipagem, Parquet e consultas em streaming sem estourar a memória.
- [CNPJ alfanumérico: o que muda e como adaptar validação, banco de dados e integrações](https://cnpjpedia.com.br/blog/cnpj-alfanumerico-como-adaptar-sistemas.md): O CNPJ com letras já está valendo. Veja o novo cálculo do dígito verificador, como migrar colunas e APIs e uma bateria de testes para não quebrar seus sistemas.