Upload
pgday-campinas
View
80
Download
1
Embed Size (px)
Citation preview
PGDay Campinas 2013
PL/pg...ETL – Transformação de dados para DW e BI usando linguagem procedural
2
Flavio Gurgel
● Líder de projetos especiais da 4Linux
● Consultor e instrutor PostgreSQL
● EnterpriseDB Associate Certificate
● Engenheiro Eletricista pela UFPR
Resuminho ETL
● Extrair - do(s) sistema(s) de origem
● Transformar - filtrar, traduzir, (de)codificar, (pré)calcular,
juntar, concatenar, transpor, separar
● Carregar - em um Data Warehouse
● Integrar!
● BI - Business Inteligence
Processo ETL - Tradicional
Processo ETL - PostgreSQL
Processo ETL – Tudo
Como é possível - FDW
● Foreign Data Wrappers
● Tabelas estrangeiras
● Acessíveis como se fossem locais
– Outros SGBD
– Arquivos
– Web Services
– Etc.
Como é possível – PL/pgSQL
● Linguagem procedural padrão do PostgreSQL
● Estende a linguagem SQL com estruturas de controle
● Pode fazer computação complexa
● Herda tipos, operadores e funções
● "Acreditada" - usuários fazem apenas o que lhes é
permitido
● É fácil de usar
Por que fazer ETL via PL/pgSQL?
● Desempenho
● Reduzir número de viagens pela rede
● Eliminar viagens de dados desnecessários
● Aproveitar recursos do PostgreSQL
● Funções avançadas internas
● Rede de extensões PGXN
● Outras PL
● Aproveitar conhecimento dos DBAs
Quando evitar
● Desconhecimento de SQL
● DBA não conhece estratégias DW
● O destino dos dados não é PostgreSQL
● FDW que escreve já disponível
● Normativos
● que podem ser revisados
FDW - Instalar
● Instalar o driver desejado
● Gerenciador de pacotes
● Contrib
● PGXN - http://pgxn.org/tag/fdw/
FDW – Exemplos de extensões
● Conectado ao banco desejado:
CREATE EXTENSION oracle_fdw;
CREATE EXTENSION postgres_fdw;
CREATE EXTENSION file_fdw;
FDW – Conectar aos servidores
CREATE SERVER ora123 FOREIGN DATA WRAPPER oracle_fdw
OPTIONS ('//192.168.0.1/meuoracle');
CREATE SERVER postgres_prod FOREIGN DATA WRAPPER
postgres_fdw OPTIONS (host '192.168.0.2' dbname
'prod');
CREATE SERVER csv_do_ze FOREIGN DATA WRAPPER file_fdw;
FDW – Mapear usuários
CREATE USER MAPPING FOR flavio SERVER postgres_prod
OPTIONS (user 'flavio', password '123456');
FDW – Conectar à tabela estrangeira
CREATE FOREIGN TABLE est.pedidos (
id bigint NOT NULL,
data date NOT NULL,
cliente bigint NOT NULL,
valor float
) SERVER postgres_prod;
FDW – Conectar a um arquivo CSV
CREATE FOREIGN TABLE est.tabelao_do_ze (
setor BIGINT;
vendas FLOAT;
) SERVER csv_do_ze OPTIONS ( filename
'/home/ze/tabelao.csv', format 'csv' );
Exemplo de esquemas
● est - onde as tabelas estrangeiras estão
● dw - onde serão armazenados os dados
de BI
Exemplo: Tabela -> Tabela
CREATE OR REPLACE FUNCTION dw.carrega() RETURNS boolean AS
$BODY$
DECLARE
BEGIN
INSERT INTO dw.tabela SELECT colA, colB, colC FROM est.tabela;
RETURN true;
END;
$BODY$
LANGUAGE plpgsql;
Exemplo: 2 Tabelas -> Tabela com laço
CREATE OR REPLACE FUNCTION dw.carrega() RETURNS boolean AS
$BODY$
DECLARE
resultado RECORD;
soma FLOAT;
BEGIN
FOR resultado IN SELECT id, nome FROM est.tabelaA LOOP
SELECT sum(valor) INTO soma FROM est.tabelaB WHERE id = resultado.id;
INSERT INTO dw.tabela VALUES (resultado.id, resultado.nome, soma);
END LOOP;
RETURN true;
END;
$BODY$
LANGUAGE plpgsql;
Exemplo: juntar sempre que possível
CREATE OR REPLACE FUNCTION dw.carrega() RETURNS boolean AS
$BODY$
DECLARE
BEGIN
INSERT INTO dw.tabela
SELECT a.id, a.nome, sum(b.valor) as total
FROM est.tabelaA a
INNER JOIN est.tabelaB b ON b.id=a.id GROUP BY 1, 2;
RETURN true;
END;
$BODY$
LANGUAGE plpgsql;
Exemplo: Funcionalidades PostgreSQL
CREATE OR REPLACE FUNCTION dw.carrega() RETURNS boolean AS
$BODY$
DECLARE
BEGIN
INSERT INTO dw.tabela SELECT
a.id,
a.nome,
left(a.nome, 1) as inicial,
CASE WHEN b.regiao = 1 THEN 'Sudeste', WHEN b.regiao = 2 THEN 'Sul' END as regiao,
EXTRACT(QUARTER from b.data) as trimestre,
sum(b.valor) as total,
avg(b.valor) as media
FROM est.tabelaA a INNER JOIN est.tabelaB b ON b.id=a.id GROUP BY 1, 2;
RETURN true;
END;
$BODY$
LANGUAGE plpgsql;
Exemplo: Transposição de tabelas
● Original: tabela est.tabelaA
nome valor
João 100
Maria 200
José 150
Destino: tabela dw.salarios
João Maria José
100 200 150
Exemplo: TransposiçãoCREATE OR REPLACE FUNCTION dw.carrega() RETURNS boolean AS
$BODY$
DECLARE
resultado RECORD;
montado1 TEXT;
montado2 TEXT;
BEGIN
montado1 := 'CREATE TABLE dw.salarios (';
montado2 := 'INSERT INTO dw.salarios VALUES (';
FOR resultado IN SELECT nome, valor FROM est.tabelaA LOOP
montado1 := montado1 || ''' || resultado.nome || ''' || ', ';
montado2 := montado2 || resultado.valor::text || ', ';
END LOOP;
montado1 := left (montado1, length(montado1) - 2) || ');';
montado2 := left (montado2, length(montado2) - 2) || ');';
PERFORM montado1;
PERFORM montado2;
RETURN true;
END;
$BODY$
LANGUAGE plpgsql;
Exemplo: Avisos
CREATE OR REPLACE FUNCTION dw.carrega() RETURNS boolean AS
$BODY$
DECLARE
BEGIN
RAISE NOTICE 'Iniciando carga da tabela';
INSERT INTO dw.tabela SELECT colA, colB, colC FROM est.tabela;
RAISE NOTICE 'Fim da carga da tabela';
RETURN true;
END;
$BODY$
LANGUAGE plpgsql;
Como disparar as transformações
● Manualmente:
SELECT dw.carrega();
● Automárico:
● cron
● PgAgent
Cardinalidade
● Entender 1:1, 1:n, n:n
● Os volumes serão grandes
● Selecionar sempre o menor subconjunto
possível
Cardinalidade – Tabela vendedores
id nome
1 João
2 Maria
3 Huguinho
4 Zezinho
Cardinalidade – Tabela salarios
id departamento salario
1 Vendas 1000
2 Vendas 1500
3 Vendas 1200
4 Vendas 1300
5 Produção 1150
6 Produção 1400
7 Produção 1300
8 Produção 1000
Cardinalidade - Consultas
● Funciona:
SELECT sum(b.salario) as folha
FROM vendedores a
INNER JOIN salarios b ON b.id=a.id;
● Melhor
SELECT sum(b.salario) as folha
FROM vendedores a
INNER JOIN salarios b ON b.id=a.id
WHERE departamento = 'Vendas';
Conclusão
● A estratégia é poderosa
● Já implementada com sucesso
● Centralização no DBA
● Com integração de times
● Desempenho
● Com as milhares de vantagens PostgreSQL
31
Obrigado!
● O nome mais forte em integração Open Source no Brasil● O maior case Open Source em missão crítica: Caixa Econômica Federal● Mais de 50.000 alunos treinados● Mais de 10 anos apenas com Open Source● Equipe multidisciplinar: analistas de negócios, programadores, arquitetos,
sysadmins e gerentes de projetos.● Quando pega um desafio, sai do outro lado● Parceira Red Hat, Zend, Puppetlabs, EnterpriseDB● Orientada a padrões abertos e melhores práticas
● 4Linux