Rafael Lopes
331a021d9a
Some checks failed
Deploy WiFi-ETL Prod / deploy (push) Failing after 0s
- Adicionado suporte para extração de dados do Ruijie e WiFeed, incluindo autenticação e tratamento de erros. - Adicionado suporte para watermarking em ambas as fontes para extração incremental. - Criado script de transformação para mesclagem de MAC addresses. - Implementado Backfill para WiFeed, permitindo extração histórica com controle de taxa. - Adicionado script de depuração para testes de transformação do WiFeed. - Desenvolvido scripts de implantação e configurações do Docker para setup de produção. - Criado script de inicialização do schema do PostgreSQL em infra/init.sql. - Adicionado teste automatizado para lógica de transformação e carregamento em test_transform_load.py. - Atualizado documentation para implantação e setup de produção.
116 lines
3.5 KiB
PL/PgSQL
116 lines
3.5 KiB
PL/PgSQL
-- WiFi ETL — Schema Mínimo + Watermarks
|
|
-- 3 tabelas: watermarks, users, sessions
|
|
|
|
-- -----------------------------------------------------
|
|
-- Tabela: watermarks (controle de última extração)
|
|
-- -----------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS watermarks (
|
|
source VARCHAR(50) PRIMARY KEY, -- 'ruijie', 'wifeed'
|
|
last_value TEXT, -- valor: epoch ms (Ruijie) ou 'YYYY-MM-DD' (WiFeed)
|
|
last_run_at TIMESTAMP WITH TIME ZONE -- quando a extração rodou
|
|
);
|
|
|
|
-- -----------------------------------------------------
|
|
-- Tabela: users (WiFeed)
|
|
-- -----------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
mac_address VARCHAR(17) UNIQUE NOT NULL,
|
|
name VARCHAR(255),
|
|
cpf VARCHAR(14),
|
|
gender VARCHAR(20),
|
|
email VARCHAR(255),
|
|
birthdate DATE,
|
|
phone VARCHAR(20),
|
|
client_id INTEGER,
|
|
host_type VARCHAR(50),
|
|
local_name VARCHAR(255),
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|
updated_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_users_mac ON users(mac_address);
|
|
|
|
-- -----------------------------------------------------
|
|
-- Tabela: sessions (Ruijie)
|
|
-- -----------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS sessions (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
mac_address VARCHAR(17) NOT NULL,
|
|
access_point_name VARCHAR(255) NOT NULL,
|
|
building_name VARCHAR(255),
|
|
band VARCHAR(10),
|
|
channel VARCHAR(10),
|
|
rssi INTEGER,
|
|
user_ip INET,
|
|
bytes_up BIGINT,
|
|
bytes_down BIGINT,
|
|
bytes_total BIGINT,
|
|
serial_number VARCHAR(100),
|
|
online_time TIMESTAMP NOT NULL,
|
|
offline_time TIMESTAMP NOT NULL,
|
|
active_time_ms INTEGER,
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|
|
|
CONSTRAINT uq_session UNIQUE (mac_address, online_time)
|
|
);
|
|
|
|
CREATE INDEX idx_sessions_mac ON sessions(mac_address);
|
|
CREATE INDEX idx_sessions_online ON sessions(online_time DESC);
|
|
|
|
-- -----------------------------------------------------
|
|
-- Função simples: update_updated_at_colummn
|
|
-- -----------------------------------------------------
|
|
CREATE OR REPLACE FUNCTION update_updated_at_column()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Triggers
|
|
CREATE TRIGGER update_users_updated_at
|
|
BEFORE UPDATE ON users
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
-- Views
|
|
|
|
CREATE OR REPLACE VIEW vw_sessions_enriched AS
|
|
SELECT
|
|
-- Identificação
|
|
u.mac_address,
|
|
u.name,
|
|
u.cpf,
|
|
u.gender,
|
|
u.email,
|
|
u.birthdate,
|
|
u.phone,
|
|
u.host_type,
|
|
u.local_name,
|
|
|
|
-- Sessão
|
|
s.id AS session_id,
|
|
s.access_point_name,
|
|
s.building_name,
|
|
s.band,
|
|
s.channel,
|
|
s.rssi,
|
|
s.user_ip,
|
|
s.online_time,
|
|
s.offline_time,
|
|
s.active_time_ms,
|
|
ROUND(s.active_time_ms / 1000.0) AS active_time_sec,
|
|
ROUND(s.active_time_ms / 60000.0, 1) AS active_time_min,
|
|
|
|
-- Tráfego em KB
|
|
ROUND(s.bytes_up / 1024.0, 2) AS kb_up,
|
|
ROUND(s.bytes_down / 1024.0, 2) AS kb_down,
|
|
ROUND(s.bytes_total / 1024.0, 2) AS kb_total,
|
|
|
|
s.serial_number,
|
|
s.created_at AS session_created_at
|
|
|
|
FROM sessions s
|
|
LEFT JOIN users u ON u.mac_address = s.mac_address; |