Laboratório: Dashboard de Vendas do Protheus em Apache Superset via réplica PostgreSQL

Roteiro para estudo em ambiente de laboratório ou de teste. Execute apenas em equipamentos, redes e contas que você tenha autorização para usar, com backup e homologação próprios. Comandos podem causar indisponibilidade ou perda de dados. Software e marcas citados pertencem aos seus titulares. Termos de Uso.

Duração estimada

120 min

Passos

12

Validações

4

Versões da documentação consultada

PostgreSQL 16 · Apache Superset 6.0 · Docker a versão estável atual · psycopg2 a versão estável atual

Topologia

O ambiente terá três componentes principais: (1) um servidor PostgreSQL local (192.168.56.10) com o esquema bi_varejo, (2) uma máquina com Apache Superset (192.168.56.20) conectando-se ao PostgreSQL via rede interna, e (3) um script de extração simulando a carga das tabelas SL1, SL2, SB1 e SA1 do Protheus para o PostgreSQL. O Superset acessa apenas a réplica, nunca o banco original.

+-------------------+        +-------------------+
|  PostgreSQL VM    |<------>| Apache Superset   |
| 192.168.56.10     |        | 192.168.56.20     |
| (bi_varejo)       |        | (Docker)          |
+-------------------+        +-------------------+
         ^
         |
         |
+-------------------+
| Script de carga   |
| (localhost ou VM) |
+-------------------+

Equipamentos e software

Roteiro

1. Preparar a VM do PostgreSQL

   Instale o PostgreSQL 16. Ele será a base de dados de BI, isolada do Protheus. Isso protege o ambiente produtivo e permite consultas analíticas sem impacto.

   sudo apt update && sudo apt install postgresql-16Copiar

   O que você deve ver: Serviço postgresql rodando e porta 5432 aberta.

2. Criar banco e esquema bi_varejo

   Crie um banco chamado bi_varejo e um usuário dedicado. O esquema bi_varejo vai receber os dados extraídos.

   sudo -u postgres psql -c "CREATE DATABASE bi_varejo;"
   sudo -u postgres psql -c "CREATE USER superset_bi WITH PASSWORD 'superset123';"
   sudo -u postgres psql -d bi_varejo -c "GRANT ALL ON DATABASE bi_varejo TO superset_bi;"Copiar

   O que você deve ver: Banco criado, usuário criado, permissões atribuídas.

3. Criar tabelas simuladas no esquema bi_varejo

   Implemente as tabelas fato_venda, fato_venda_item, dim_produto, dim_cliente e dim_loja. Essas tabelas simulam a extração das tabelas SL1, SL2, SB1 e SA1 do Protheus.

   sudo -u postgres psql -d bi_varejo -c "CREATE SCHEMA IF NOT EXISTS bi_varejo;"
   sudo -u postgres psql -d bi_varejo -c "
   CREATE TABLE bi_varejo.dim_produto (
     id_produto SERIAL PRIMARY KEY,
     codigo VARCHAR(20),
     nome VARCHAR(100),
     categoria VARCHAR(50)
   );
   CREATE TABLE bi_varejo.dim_cliente (
     id_cliente SERIAL PRIMARY KEY,
     codigo VARCHAR(20),
     nome VARCHAR(100),
     cidade VARCHAR(50)
   );
   CREATE TABLE bi_varejo.dim_loja (
     id_loja SERIAL PRIMARY KEY,
     codigo VARCHAR(10),
     nome VARCHAR(100)
   );
   CREATE TABLE bi_varejo.fato_venda (
     id_venda SERIAL PRIMARY KEY,
     data_venda DATE,
     id_loja INT REFERENCES bi_varejo.dim_loja(id_loja),
     id_cliente INT REFERENCES bi_varejo.dim_cliente(id_cliente),
     valor_total NUMERIC(12,2)
   );
   CREATE TABLE bi_varejo.fato_venda_item (
     id_item SERIAL PRIMARY KEY,
     id_venda INT REFERENCES bi_varejo.fato_venda(id_venda),
     id_produto INT REFERENCES bi_varejo.dim_produto(id_produto),
     quantidade INT,
     preco_unitario NUMERIC(10,2),
     valor_item NUMERIC(12,2)
   );
   "Copiar

   O que você deve ver: Tabelas criadas no esquema bi_varejo.

4. Popular tabelas com dados simulados

   Insira dados de exemplo para simular vendas reais. Isso permite testar queries e dashboards no Superset.

   sudo -u postgres psql -d bi_varejo -c "INSERT INTO bi_varejo.dim_produto (codigo, nome, categoria) VALUES
   ('P001','Arroz 5kg','Alimentos'),
   ('P002','Feijão 1kg','Alimentos'),
   ('P003','Detergente','Limpeza');"
   sudo -u postgres psql -d bi_varejo -c "INSERT INTO bi_varejo.dim_cliente (codigo, nome, cidade) VALUES
   ('C001','Maria Silva','Manaus'),
   ('C002','João Souza','Manaus');"
   sudo -u postgres psql -d bi_varejo -c "INSERT INTO bi_varejo.dim_loja (codigo, nome) VALUES
   ('L01','Loja Centro'),
   ('L02','Loja Bairro');"
   sudo -u postgres pql -d bi_varejo -c "INSERT INTO bi_varejo.fato_venda (data_venda, id_loja, id_cliente, valor_total) VALUES
   ('2024-06-01',1,1,100.00),
   ('2024-06-01',2,2,80.00);"
   sudo -u postgres psql -d bi_varejo -c "INSERT INTO bi_varejo.fato_venda_item (id_venda, id_produto, quantidade, preco_unitario, valor_item) VALUES
   (1,1,2,20.00,40.00),
   (1,2,3,10.00,30.00),
   (2,3,2,20.00,40.00);"Copiar

   O que você deve ver: Tabelas populadas com registros de teste.

5. Liberar acesso remoto ao PostgreSQL

   Edite o postgresql.conf e o pg_hba.conf para permitir conexões do Superset. Isso é necessário para o Superset acessar o banco.

   sudo nano /etc/postgresql/16/main/postgresql.conf
   # Altere: listen_addresses = '*'
   sudo nano /etc/postgresql/16/main/pg_hba.conf
   # Adicione: host    bi_varejo    superset_bi    192.168.56.20/32    md5
   sudo systemctl restart postgresqlCopiar

   O que você deve ver: PostgreSQL ouvindo em todas as interfaces e aceitando conexões do Superset.

6. Preparar a VM do Superset

   Instale Docker e Docker Compose. O Superset será executado em contêiner, facilitando a instalação e o isolamento.

   sudo apt update && sudo apt install docker.io docker-compose -yCopiar

   O que você deve ver: Docker e Docker Compose instalados.

7. Clonar o repositório oficial do Superset

   Baixe o código-fonte do Superset para garantir que usará a configuração oficial e poderá customizar o requirements-local.txt.

   git clone https://github.com/apache/superset.git && cd supersetCopiar

   O que você deve ver: Repositório clonado localmente.

8. Adicionar driver PostgreSQL ao Superset

   Inclua o pacote psycopg2 no arquivo requirements-local.txt para garantir que o Superset consiga conectar ao PostgreSQL.

   echo 'psycopg2-binary' >> docker/requirements-local.txtCopiar

   O que você deve ver: Arquivo requirements-local.txt atualizado.

9. Subir o Superset via Docker Compose

   Inicie o Superset em modo desenvolvimento. Isso facilita ajustes e testes rápidos.

   docker-compose -f docker-compose-non-dev.yml up -dCopiar

   O que você deve ver: Contêineres do Superset rodando. Acesse http://192.168.56.20:8088.

10. Criar usuário admin no Superset

    Crie um usuário administrador para acessar a interface web.

    docker exec -it superset_app bash
    superset fab create-adminCopiar

    O que você deve ver: Usuário admin criado com sucesso.

11. Conectar o Superset ao PostgreSQL

    No Superset, adicione uma nova conexão de banco de dados usando a URI postgresql://superset_bi:superset123@192.168.56.10:5432/bi_varejo. Isso permite explorar os dados simulados.

    No navegador, acesse http://192.168.56.20:8088, vá em 'Sources' > 'Databases' > 'Add Database'. Preencha o campo SQLAlchemy URI com: postgresql://superset_bi:superset123@192.168.56.10:5432/bi_varejoCopiar

    O que você deve ver: Conexão criada e visível na interface do Superset.

12. Importar tabelas e criar um dashboard simples

    No Superset, importe as tabelas do esquema bi_varejo e crie um dashboard com gráficos de vendas por produto e curva ABC. Use as queries sugeridas no modelo didático.

    No Superset, vá em 'Datasets' > 'Add Dataset', selecione a tabela fato_venda_item. Crie um gráfico de barras com soma de valor_item por produto.Copiar

    O que você deve ver: Dashboard exibindo vendas por produto, com dados simulados.

Como saber que funcionou

Troubleshooting

Segurança e hardening

• Use senhas fortes para os usuários do PostgreSQL
• Restrinja o acesso ao PostgreSQL apenas ao IP do Superset
• Não exponha o Superset para a internet sem autenticação
• Desabilite contas padrão e revise permissões no Superset
• Aplique atualizações de segurança em ambos os sistemas

Checklist final

• PostgreSQL instalado e acessível na rede
• Tabelas bi_varejo criadas e populadas
• Superset rodando em contêiner
• Conexão do Superset ao PostgreSQL testada
• Dashboard criado e exibindo dados