AVISO - NÃO RESPONDO PELOS FORKS GERADOS A PARTIR DESTE PROJETO ORIGINAL, SÓ VALIDEI ESTA VERSÃO E SEI QUE ESTÁ FUNCIONAL, JÁ OS FORKS CASO ESTEJAM USANDO ENTREM EM CONTATO COM O COLABORADOR RESPENSÁVEL PELO FORK EM QUESTÃO

EXTRACT TRANSFORM LOAD - ETL (Extrair, Transformar e Carregar)

DADOS PÚBLICOS DA RECEITA FEDERAL DO BRASIL - RFB, INSTITUTO BRASILEIRO DE GEOGRAFIA E ESTATÍSTICA - IBGE E AGÊNCIA NACIONAL DO PETRÓLEO, GÁS NATURAL E BIOCOMBUSTÍVEIS - ANP

`RECEITA FEDERAL DO BRASIL - RFB - Dados Públicos CNPJ`

Fonte de informações oficial da Receita Federal do Brasil, aqui.
Fonte oficial que é usada para baixar os arquivos da Receita Federal do Brasil, aqui.
Layout dos arquivos, aqui.

A Receita Federal do Brasil disponibiliza bases com os dados públicos do cadastro nacional de pessoas jurídicas (CNPJ).

De forma geral, nelas constam as mesmas informações que conseguimos ver no cartão do CNPJ, quando fazemos uma consulta individual, acrescidas de outros dados de Simples Nacional, sócios e etc.

`INSTITUTO BRASILEIRO DE GEOGRAFIA E ESTATÍSTICA - IBGE - Dados Públicos (Municípios, População, PIB, Território e CNAEs detalhado)`

Fonte de informações oficial do IBGE, aqui.
Fonte oficial que é usada para baixar os arquivos do IBGE (Municípios), aqui.
Fonte oficial que é usada para baixar os arquivos do IBGE (População), aqui.
Fonte oficial que é usada para baixar os arquivos do IBGE (PIB), aqui.
Fonte oficial que é usada para baixar os arquivos do IBGE (Território Urbano), aqui.
Fonte oficial que é usada para baixar os arquivos do IBGE (Território Total), aqui.
Fonte oficial que é usada para baixar os arquivos do IBGE (CNAEs detalhado), aqui.

O IBGE disponibiliza bases com os dados públicos quantitativos e qualitativos do Censo demográfico do Brasil de 2022 (Municípios, População, PIB, Território e CNAEs detalhado).

`AGÊNCIA NACIONAL DO PETRÓLEO, GÁS NATURAL E BIOCOMBUSTÍVEIS - ANP - Dados Públicos (Cadastrais dos Revendedores Varejistas de Combustíveis Automotivos)`

Fonte de informações oficial da ANP, aqui.
Fonte oficial que é usada para baixar os arquivos da ANP (Cadastrais dos Revendedores Varejistas de Combustíveis Automotivos), aqui.

A ANP disponibiliza bases com os dados públicos quantitativos e qualitativos do Censo demográfico do Brasil de 2022 (Municípios, População, PIB, Território e CNAEs detalhado).

Com os dados acimas são possíveis Análises muito ricas, desde econômicas, mercadológicas, sociais e até investigações.

`DADOS EXTRAS PARA CRIAÇÃO DE VARIÁVEL ESTRUTURANTE (ANEEL, DENIT, IBGE, MJSP, CORREIOS e ICMBio)`

Dados ICMBio.
- Atributos das Unidades de Conservação Federais, aqui.
- Limites oficiais das Unidades de Conservação Federais, aqui.
Dados IBGE - Municípios da Faixa de Fronteira e Cidades Gêmeas, aqui.
Dados ANEEL - Capacidade Instalada por Unidade da Federação - ENERG, aqui.
Dados DNIT - Plano Nacional de Viação e Sistema Nacional de Viação - TRANSP, aqui.
Dados Telecomunicações.
- Cobertura Fibra Ótica - ANATEL - Plano Estrutural de Redes de Telecomunicações - PERT - , aqui.
- Cobertura Correios - Agência nos municípios Brasileiros, aqui.
Dados IBGE - PNSB - Pesquisa Nacional de Saneamento Básico, aqui.
- Cobertura Água - Pesquisa Nacional de Saneamento Básico, aqui.
- Cobertura Esgoto - PNSB - Pesquisa Nacional de Saneamento Básico, aqui.
Dados Ministério da Justiça e Segurança Pública - MJSP - Ocorrências Criminais - Sinesp, aqui.

Definição - Análise de Componentes Principais (PCA) técnica utilizada para criação da variável INFRA

Em suma, o PCA é uma de redução de dimensionalidade técnica que transforma um conjunto de recursos em um conjunto de dados em um número menor de recursos chamados componentes principais , ao mesmo tempo em que tenta reter o máximo possível de informações no conjunto de dados original.

Using Principal Component Analysis (PCA) for Machine Learning, aqui.

Nesse projeto consta um processo de ETL para:

i) baixar os arquivos;
ii) descompactar;
iii) ler, tratar, converter e dividir;
iv) inserir num banco de dados relacional PostgreSQL;
v) criar relacionamento através de chaves primárias e estrangeiras para todas as tabelas.

Métodos utilizados neste projeto

Foi escolhido a modularização dos códigos através de funções genéricas e específicas, para que com as funções genéricas pudessem ser reaproveitadas em vários Scripts
- Artigo "Lição 8: Modularização de código com Funções!" - Abr/2018 aqui
- Artigo "Reutilização de código e modularidade em Python" - Jul/2012 aqui
- Artigo "Código Limpo: dicas práticas para turbinar a escrita, leitura e escalabilidade de um código" - Mai/2020 aqui
- Artigo "Código Limpo – 7 dicas na criação de funções" - Set/2020 aqui
- Artigo "Clean code: o que é, porque usar e principais regras!" - Dez/2022 aqui

Foi escolhido a injeção direta dos arquivos csv por query sql com o comando copy devido o alto desempenho obtido em cerca de 10x mais rápido que pelo comando to_sql do pandas:
- COMMAND COPY documentação aqui
- Artigo "How To Load Your Pandas DataFrame To Your Database 10x Faster" - Dez/2020 aqui

Foi escolhido o uso de chaves primárias (para valores únicos - Dimensão) e estrangeiras (para valores múltiplos - Fato) para criação do relacionamento entre as tabelas:
- Artigo "Fato e Dimensão no Power BI (Tabelas)" - Ago/2020 aqui.

Foi colocado nas principais funções registradores de tempo para medição/registro de desempenho.

Foi colocado em todas as funções registro de log ERROR e alguns logs de INFO com os valores dos registradores de tempo para medição/registro de desempenho.

Foi incluído para acompanhar o andamento de uma forma visual barras de progresso em 3 níveis representado por cores, sendo essas:
- Barra verde para internas(loops de download/descompactação/conversão de arquivos);
- Barra azul para intermediárias(entre funções internas);
- Barra vermelha para externas(entre funções externas);

Rede/Hardware utilizado neste projeto:

Link internet

10Mbs/s a 10Gb/s - compartilhado entre instituições - RNP aqui

Máquina de trabalho utilizada para teste

Windows 10 Pro - 64Bits;
Processador Core i7 - Oitava Geração;
16GB Memoria DDR4;
HD SATA 500GB; Recomendado uso de SSD, melhora até 3x a velocidade de leitura e escrita

Infraestrutura necessária/opcional:

Link download - Windows 10 ou superior - 64Bits
Link download - Python 3.11.4 - Programar em Python no VS Code
NÃO UTILIZAR O PYTHON 12, DARÁ ERRO COM A INSTALAÇÃO DOS PACOTES (DEVIDO REQUERIMENTO DO USO DE ARQUIVO "pyproject.toml")
Link download - Visual Studio Code - Python + VS Code no Windows em 2022
Link download - PostgreSQL 16 - Resumido para utilização do PostgreSQL
Packages Python mais atuais conforme "requirements.txt"
Link download Java Release 8 - Atualização 333 - 32Bits e 64Bits - Requerido pelo pacote Tabula-Py para leitura de pdf"
Link download opcional - Insomnia - API Client
Link download opcional - DBeaver Community
Link download opcional - Notepad++
Link download opcional - paint.net

Fonte de Conhecimento para resolução de problemas

Erros comuns

Como usar: (ATENÇÃO AO FAZER ALGUNS QUESTIONAMENTO SOBRE ALGUM PROBLEMA, COLE O CONTEÚDO DO ARQUIVO "log.log" QUE ESTÁ NA RAIZ DO DIRETÓRIO DO PROJETO PARA QUE POSSA SER IDENTIFICADO O PROBLEMA MAIS FACILMENTE)

1. Com o Python, PostgreSQL e o Visual Studio Code instalado.

2. Baixe e extraia o conteúdo do projeto em uma pasta de sua escolha (preferencialmente na raiz do drive D:\*), abra a pasta dentro do Visual Studio Code já pré configurado para uso da linguagem Pyhon.

3. Execute no Powershell do windows como administrador o comando "Set-ExecutionPolicy AllSigned" e coloque a opção A [Para todos] para liberação de execução de scripts, já no terminal do VSCODE execute o arquivo .\01_Instalacao_venv.bat como administrador, tal passo foi criado para automatizar o processo de criação do ambiente de variáveis, atualização do PIP e instalação dos pacotes necessários através do arquivo requirements.txt.

CÓDIGO DO ARQUIVO BAT

REM Verificar versao instalado do Python (este projeto foi feito em Python 3.11.4)
python --version

REM Crie um ambiente virtual em Python 3.11.4
python -m venv venv

REM Ativar o ambiente virtual
call venv\Scripts\activate.bat

REM Atualizar PIP para última versão
python.exe -m pip install --upgrade pip

REM Instalar pacotes necessários usando o arquivo requerimentos.txt
python -m pip install -r requirements.txt

REM Listar pacotes instalados
pip list

4. Para facilitação foi criado uma interface gráfica pelo pacote "tkinter", execute o arquivo "main.py" da pasta ./src para iniciar o mesmo, más também existe um menu via console caso queiram utilizar execute o arquivo "A_Main.py".

5. Através do menu 2 "Variáveis ambiente" submenu "Criar arquivo de configuração de ambiente em txt" será criado um arquivo .env com configurações padrão para posterior edição neste mesmo menu, o arquivo será criado no diretório raiz ./, conforme as variáveis de ambiente do seu ambiente de trabalho altere para customizar só os dados abaixo do arquivo .env.

Padrão que será criado pelo código

"DB_HOST=localhost #Caso queira altere para endereço de servidor que você vai usar, a instalação padrão é esta "localhost" / "127.0.0.1"

DB_PORT=5432 #Caso queira altere para a porta que você vai usar, a instalação padrão é esta "5432"

DB_USER=postgres #Tem que ser informado o usuário root (Tem que ter privilégios completos)

DB_PASSWORD=XXXX #Altere para senha que foi cadastrada na instalação do seu PostgreSQL

DB_NAME=dados_etl" #Caso queira altere o nome do banco de dados que será criado

6. Através do menu 3 "Banco Dados" submenu "Criar banco de dados" será criado um banco de dados com o nome advindo do arquivo .env conforme as variáveis de ambiente já pre customizadas, também é possível neste menu exibir banco de dados e tabelas existentes e excluir o banco de dados que foi criado:

7. Através do menu 4 "Diretórios" submenu "Criar diretórios" será criado diretórios com os nomes advindo do arquivo .env conforme as variáveis de ambiente já pré customizadas, para execução necessária dos próximos Scripts, também é possível neste menu exibir diretórios existentes e excluir o diretórios que foram criados.

8. Através do menu 5 "Script RFB" é possível executar partes ou completo do script de baixar, descompactar, converter/separar/1ºtratamento, inseri no banco de dados, 2ºtratamento(repetidos e faltantes) e criação de relacionamento por chaves primárias e estrangeiras:

Tabelas da RFB geradas :

Para maiores informações, consulte o layout.
- tb_rfb_empresas: dados cadastrais da empresa em nível de matriz
- tb_rfb_estabelecimentos: dados analíticos da empresa por unidade / estabelecimento (telefones, endereço, filial, etc)
- tb_rfb_socios: dados cadastrais dos sócios das empresas
- tb_rfb_simples: dados de MEI e Simples Nacional
- tb_rfb_cnaes: código e descrição dos CNAEs
- tb_rfb_qualsocio: tabela de qualificação das pessoas físicas - sócios, responsável e representante legal.
- tb_rfb_natju: tabela de naturezas jurídicas - código e descrição.
- tb_rfb_motivos: tabela de motivos da situação cadastral - código e descrição.
- tb_rfb_pais: tabela de países - código e descrição.
- tb_rfb_municipios: tabela de municípios - código e descrição.

A tabela/coluna tb_rfb_empresas/id_cod_cnpj_basico possui uma chave primária.
Já as tabelas tb_rfb_estabelecimentos, tb_rfb_socios e tb_rfb_simples possuem uma chave estrangeira para a tabela/coluna tb_rfb_empresas/id_cod_cnpj_basico, que é a principal chave de ligação entre elas.
A tabela/coluna tb_rfb_estabelecimentos/id_cod_cnpj_completo_num possui uma chave primária.
A tabela/coluna tb_rfb_municipios/ id_cod_municipio_tom_rfb possui uma chave primária.
Já a tabel/coluna tb_rfb_estabelecimentos/id_cod_municipio_tom possui uma chave estrangeira para a tabela/coluna tb_rfb_municipios/id_cod_municipio_tom_rfb, que é a principal chave de ligação entre elas.
A tabela/coluna tb_rfb_natju/id_cod_natiju possui uma chave primária.
já a tabela/coluna tb_rfb_empresas/cod_natureza_juridica possui uma chave estrangeira para a tabela/coluna tb_rfb_natju/id_cod_natiju, que é a principal chave de ligação entre elas.
A tabela/coluna tb_rfb_qualsocio/id_cod_qual_socio possui uma chave primária.
Já a tabela/coluna tb_rfb_socios/qualificacao_socio possui uma chave estrangeira para a tabela/coluna tb_rfb_qualsocio/id_cod_qual_socio, que é a principal chave de ligação entre elas.
A tabela/coluna tb_rfb_motivos/id_cod_motivo possui uma chave primária
Já a tabela/coluna tb_rfb_estabelecimentos/cod_motivo_situacao_cadastral possui uma chave estrangeira para a tabela/coluna tb_rfb_motivos/id_cod_motivo, que é a principal chave de ligação entre elas.
A tabela/coluna tb_rfb_pais/id_cod_pais possui uma chave primária.
Já a tabela tb_rfb_estabelecimentos/cod_pais possui uma chave estrangeira para a tabela/coluna tb_rfb_pais/id_cod_pais, que é a principal chave de ligação entre elas.
A tabela/coluna tb_rfb_cnaes/id_cod_cnaes_ibge possui uma chave primária.
Já a tabela/coluna tb_rfb_estabelecimentos/cod_cnaes_fiscal_principal possui uma chave estrangeira para a coluna , que é a principal chave de ligação entre elas.

9. Através do menu 6 "Script IBGE" é possível executar partes ou completo do script de baixar, converter/separar/1ºtratamento, inseri no banco de dados, 2ºtratamento(repetidos e faltantes) e criação de relacionamento por chaves primárias e estrangeiras:

Tabelas da IBGE geradas :

Para maiores informações, consulte o aqui.
- tb_ibge_municipios: dados para correlação cód. município Siafi/TOM que o RFB usa para o código IBGE.
- tb_ibge_pop_2022: dados de população do Censo 2022 por municípios
- tb_ibge_PIB_2020: dados do PIB 2020 por municípios
- tb_ibge_areas_urbanizadas_2019: dados de Território Urbanizados 2019 por municípios
- tb_ibge_areas_territoriais_2022: dados de Território Total do Censo 2022 por municípios
- tb_ibge_cnaes_detalhado: dados do CNAES detalhado por atividade econômica.

A tabela/coluna tb_ibge_municipios/id_cod_municipio_ibge possui uma chave primária, e uma chave estrangeira para a tabela/coluna tb_rfb_municipios/id_cod_municipio_tom_rfb.
Já as tabelas tb_ibge_pop_2022, tb_ibge_PIB_2020, tb_ibge_areas_urbanizadas_2019 e tb_ibge_areas_territoriais_2022possuem uma chave estrangeira para a tabela/coluna tb_ibge_municipios/id_cod_municipio_ibge, que é a principal chave de ligação entre elas.
A tabela/coluna tb_ibge_cnaes_detalhado/id_cod_cnaes_subclasse_ibge possui uma chave primária, e uma chave estrangeira para a tabela/coluna tb_rfb_cnaes/id_cod_cnaes_ibge.

10. Através do menu 6 "Script ANP" é possível executar partes ou completo do script de baixar, converter/separar/1ºtratamento, inseri no banco de dados, 2ºtratamento(repetidos e faltantes) e criação de relacionamento por chaves primárias e estrangeiras:

Tabelas da ANP geradas :

Para maiores informações, consulte o aqui.
- tb_anp_postos_combustiveis: dados cadastrais dos revendedores varejistas de combustíveis automotivos (Postos de Combustíveis)

A tabela/coluna tb_anp_postos_combustiveis/id_cnpj_completo_anp possui uma chave primária, e uma chave estrangeira para a tabela/coluna tb_rfb_estabelecimentos/id_cod_cnpj_completo_num.

Modelo de Entidade Relacionamento - ERD:

FINALIZAÇÃO DO PROCESSO

Dados obtidos sobre desempenho do código:

Data atualização dos arquivos, tempos obtidos processo e tamanhos dos arquivos da RFB:
- 2024-01-15;

Tempos parciais coletados no processo da RFB:

Item	Descrição Processo	Tempo decorrido no processo PC Intel i7 8Gen-Ram 16GB-HD-10Mbps	Tempo decorrido no processo PC Intel i7 4Gen-Ram 8GB-SSD-200Mbps	Tempo decorrido no processo PC Ryzen 5 PRO 4650GE -Ram 16GB-SSD-10Mbps
1	Baixar	`5:37:43`	`1:08:00`	`3:38:50`
2	Descompactar	`0:07:42`	`0:06:00`	`0:04:12`
3	Converter/separar/1º Tratamento (dataStyle '%y%m%d', substituição 0 e nulos na coluna data situação, correção data, criação da coluna cnpj completo)	`0:25:13`	`1:01:00`	`0:21:13`
4	Inserir no Banco de dados	`0:38:13`	`0:26:00`	`0:23:17`
5	2º Tratamento - Repetidos	`0:08:18`	`0:09:00`	`0:02:04`
6	2º Tratamento - Faltantes	`0:08:09`	`0:04:00`	`0:02:08`
7	Criar chaves primárias/estrangeiras nas tabelas para relacionamentos	`0:25:44`	`0:16:00`	`0:11:19`
	Total	`7:31:02`	`3:10:00`	`4:43:03`

Tamanhos dos arquivos da RFB:

Item	Tipo	Tamanho
1	Compactados - originais	`5,75 GB`
2	Descompactados - originais	`21,1 GB`
3	Convertidos - padronizados	`17,9 GB`
4	Banco de dados - Inicial	`21,69 GB`
4	Banco de dados - 2º Tratamentos	`21,79 GB`
4	Banco de dados - Chaves	`23,33 GB`

Considerações finais:

Os arquivos são grandes e dependendo da infraestrutura(hardware/software) isso pode levar muitas horas para conclusão.
O código foi pensado para adaptação para outras fontes/origens abertas, sendo públicas ou privadas.
O código pode ser melhorado com o uso de 'CLASSES'.
O código pode ser adaptado para uso em notebooks (google colab) para minimizar o tempo de download usando o acesso direto a internet e também poderia injetar os dados diretos dos arquivos csv em uma banco de dados PostgreSQL em uma instância em nuvem.
Todos os agradecimentos a Aphonso Henrique do Amaral Rafael Fonte github aqui, desenvolvedor do código original utilizado como inspiração/adaptação/inclusão para este meu projeto para atendimento das minhas necessidades, acompanhem o github dele e agradeçam a iniciativa de disponibilização inicial, pois acredito que o código original ou o meu alterado pode ajudar outras pessoas na obtenção mais facilitada dos dados na internet abertos (Públicos e privados).
Mediante a ajuda do colaborador Henrique Santos que está testando o projeto, foi possível atualizar os dados de tempo decorrido e teste de uso inicial.
Também foi identificado que uma internet mais potente e o uso de SSD podem melhorar significativamente a velocidade total do Script da RFB.

Name		Name	Last commit message	Last commit date
Latest commit History 24 Commits
.VSCODE		.VSCODE
Images		Images
extras		extras
src		src
.gitignore		.gitignore
01_Instalacao_venv.bat		01_Instalacao_venv.bat
LICENSE		LICENSE
README.md		README.md
logs.log		logs.log
requirements.txt		requirements.txt

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Repository files navigation