O Governo brasileiro mantém uma publicação mensal dos dados do CNPJ - cadastro nacional de pessoa jurídica. Para novembro de 2024, são 35 arquivos compactados em format ".zip" (link para download no final do texto)

Um dos nossos clientes, nos trouxe a demanda para melhorar sua aplicaçao que mantém um banco de dados local atualziado com os dados do CNPJ.

O banco do nosso cliente atende outras aplicações internas e deveria ser mantido em um servidor Microsoft SQL Server. Fizemos a manutenção do código com algumas melhorias e houve algum ganho no tempo de atualização dos dados - mas ainda levava algumas horas. Não exatamente um problema para um dado atualizado a cada 4 ou 5 semanas.

A dificuldade da importação tanto pelo tamanho dos arquivos quanto peloo tempo de importação, foi pauta de algumas discussões dentro do time da Necto. Então decidimos fazer outros testes mesmo já tendo entergue uma solução para o nosso cliente.

A necto há anos usa o PostgreSQL como banco de dados, é nossa primeira escolha sempre que possível. É um banco de dados robusto, de código aberto, e que não perde em nada para outros SGDBs do mercado. E resolvemos fazer alguns testes com o PostgreSQL para importar os dados do CNPJ.

O comando COPY do PostgreSQL

Os dados do CNPJ são disponibilizados em arquivos CSV compactados em formato ZIP. Hoje (2024) são 35 arquivos ZIP, que cotem algo em torno de 170.000.000 de registros - CNPJs únicos em torno de ~60.000.000.

Com os dados em mãos, usamos o comando `COPY` do PostgreSQL

COPY moves data between PostgreSQL tables and standard file-system files. COPY TO copies the contents of a table to a file, while COPY FROM copies data from a file to a table (appending the data to whatever is in the table already). COPY TO can also copy the results of a SELECT query.

_{^{source: https://www.postgresql.org/docs/16/sql-copy.html}}

COPY table_name [ ( column_name [, ...] ) ]
FROM { 'filename' | PROGRAM 'command' | STDIN }
[ [ WITH ] ( option [, ...] ) ]
[ WHERE condition ]

COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
TO { 'filename' | PROGRAM 'command' | STDOUT }
[ [ WITH ] ( option [, ...] ) ]

Algumas configurações foram feitas na tentativa de melhorar a performance da importação.

São muitas possibilidade de configuração do Postgresql, algumas devem ser feitas na inicialização do PostgreSQL outras podem ser feitas em tempo de execução.

Veja aqui uma lista completa dos parâmetros que são possíveis alterar para execução do PostgreSQL (https://www.postgresql.org/docs/16/runtime-config.html).

No caso de importações massivas de atenção a configuração do "WAL" (Write ahed log - https://www.postgresql.org/docs/16/wal-configuration.html)

PostgreSQL Config Settings

autovacuum.......................off
checkpoint_completion_target............0.9
checkpoint_timeout...............900 s
constraint_exclusion.......partition
default_statistics_target........100
effective_cache_size..........524288 kB
effective_io_concurrency...........1
fsync............................off
full_page_writes.................off
huge_pages.......................try
maintenance_work_mem...........65536 kB
max_wal_size....................5120 MB
min_wal_size....................1024 MB
random_page_cost...................4
shared_buffers.................98304 kB
synchronous_commit................on
wal_buffers.....................2048 kB
work_mem........................4096 kB

Estes parâmetros podem ser definidos no arquivo de configuração do PostgreSQL. Como estamos usando uma imagem docker, passamos como parâmetro no `command` do docker-compose .

# docker compose file
# PostgreSQL config settings before start.

postgis16:
    image: postgis/postgis:16-3.5-alpine
    mem_limit: 2.5GB  # limit the memory used by PostgreSQL container
    mem_reservation: 1.5GB
    shm_size: '1gb'   # its needed to build indexes and Primary Keys.
    ... # other
    volumes:
      - data-files:/usr/src/data_files/
    container_name: postgis16

    command:
      - "postgres"
      - "-c"
      - "autovacuum=OFF"
      - "-c"
      - "min_wal_size=1GB"
      - "-c"
      - "max_wal_size=5GB"
      - "-c"
      - "shared_buffers=768MB"
      - "-c"
      - "fsync=OFF"
      - "-c"
      - "full_page_writes=off"
      - "-c"
      - "checkpoint_timeout=15min" # min must be lowercase

Outros parâmetros, são alterados em tempo de execução ...

# python code, before start import
# PostgreSQL config settings runtime

cur.execute("ALTER DATABASE cnpj SET constraint_exclusion='OFF';")
cur.execute("ALTER DATABASE cnpj SET maintenance_work_mem='512MB';")
cur.execute("ALTER DATABASE cnpj SET synchronous_commit='OFF';")
cur.execute("ALTER DATABASE cnpj SET work_mem='4GB';")
cur.execute("SELECT pg_reload_conf();")

e ainda, alguns outros logo antes da execução dos comandos SQL como o `INSERT INTO`.

ALTER TABLE cnae DISABLE TRIGGER ALL;

Results

Abaixo os resultados e o tempo de importação em 2 maquinas distintas.

As especificações estão abaixo.

Python Code and Data Corrections

Tivemos problemas durante a criação dos índices, e tivemos que altera a "shared memory" do docker - `shm_size: '1gb'` - veja no arquivo "docker-compose" no repositório.

O Python foi usado principalmente como um orquestrador, mas também faz algumas pequenas correções no dado e nome dos arquivos. O primeiro é devido ao um registro de CNPJ duplicado, o que impedia a criação da PK. O o outro é que alguns nomes de arquivos originais tem um caracter `$` no meio do nome.

Ainda há um problema de codificação em um dos arquivos CSV, que perdura entre as verões publicadas, então o Python executa um script Perl - `perl -i -pe 's/\\x00//g' {fle}` para corrigir o problema em tempo de execução.

perl -i -pe ‘s/\\x00//g’ {fle}

Escrever em Python nos deu maior maleabilidade para controlar o processo todo. Mas sim, seria possível fazer tudo somente usando PSQL.

Repository: https://github.com/znc-sistemas/cnpj_import

For more details, check out: https://www.postgresql.org/docs/16/runtime-config-resource.html.