Soluções de Software Fazendo BI com o PostgreSQL

Fazendo BI com o PostgreSQL
Soluções de Software
Sistemas e aplicações sob medida para as
necessidades do seu negócio.
Matheus Espanhol
Outubro/2012
Agenda
Cenário
ETL
Recursos PostgreSQL
Particionamento horizontal
Escalabilidade vertical
Escalabilidade horizontal
Arquitetura shared nothing
Soluções opensource
Soluções proprietárias
Soluções de Software
Projeto e Sustentação de
software complexos, com
alta criticidade para
os negócios
Resolução de problemas tecnologicamente
desafiadores e implementação de
melhorias de forma prática
Transferência de conhecimento
e aprimoramento de competências
Clientes
Dextra e PostgreSQL
A Dextra oferece serviços no banco de dados PostgreSQL desde
1999
Gerência de Serviços PostgreSQL, focada na garantia de qualidade
dos serviços oferecidos
Projetos de grande porte com empresas e governo
Equipe especializada de DBAs PostgreSQL
Consultoria
Resolução efetiva de problemas desafiadores e complexos
Os serviços oferecidos englobam:
Instalação e configuração de servidores PostgreSQL para aplicações
críticas
Migração de sistemas de outros bancos de dados (Oracle, SQL
Server, Informix, MySQL entre outros) para PostgreSQL
Modelagem de banco de dados
Administração preventiva
Soluções de monitoramento
Ajustes de performance
Replicação de bancos de dados
Soluções de alta disponibilidade e desempenho
Desenvolvimento de aplicações com PostgreSQL
Suporte Técnico
Segurança na implantação e administração de ambientes críticos
Gestão voltada à garantia da qualidade dos serviços
Acordos de Nível de Serviço (SLA)
Aumento do nível de satisfação dos usuários
Gerenciamento de ambientes PostgreSQL
Monitoramento eficiente do banco de dados
Administração preventiva
Transferência de conhecimento
Modelo flexível: 24 x 7 ou 8 x 5
Casos de Sucesso – Consultoria e Suporte
Capacitação
Transferência de conhecimento e aprimoramento de competências
Treinamentos com profissionais que vivenciam o dia-a-dia do
desenvolvimento de software e das rotinas do banco de dados PostgreSQL
Turmas abertas ou In-Company
Customização de conteúdos
Mais de 15 mil alunos treinados
PostgreSQL Essencial
Linguagem Procedural PL/pgSQL
Administração (DBA)
Performance Tuning
PostgreSQL Alta Disponibilidade
PostGIS
PostgreSQL para BI e Datawarehouse
Casos de Sucesso – Treinamento
Business Intelligence
Conjunto de ferramentas e técnicas para aprimorar a tomada
de decisão
Data warehouse
Coletar
Integrar
Limpar
Consolidar
Armazenar
Analítico
Analisar
Explorar
Business Intelligence
Fonte: Ponniah, Paulraj. Data warehousing fundamentals for IT professionals 2nd ed.
Data Warehouse
Transformar dados em informação
Extract
Transform
Load
Características
Orientado a assunto
Integração de dados
Dados históricos
Dados não-voláteis
Sumarização
Data Warehouse
Banco de dados muito maior que a memória
Consultas complexas
Alto processamento
Grande carga de dados
Evolução
BI em tempo real
Dados não-estruturados
Grande volume de dados
ETL: Foreign Data Wrapper (FDW)
Funcionalidade que possibilita o desenvolvimento de plugins
para acessar qualquer fonte de dados a partir do PostgreSQL
Importação de dados facilitada para datawarehouses
CREATE EXTENSION oracle_fdw;
CREATE SERVER oracle_svr FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (address '192.168.1.10', port '1521');
CREATE USER MAPPING FOR PUBLIC SERVER oracle_svr
OPTIONS (username 'scott', password 'tiger');
CREATE FOREIGN TABLE customer_oracle (
customer_id SMALLINT,
store_id BIGINT,
first_name VARCHAR(45),
last_name VARCHAR(45),
SERVER oracle_svr OPTIONS (table 'customer');
SELECT * FROM customer JOIN customer_oracle USING (customer_id);
T
E L: Linguagens procedurais
Variedade de linguagens procedurais:
PL/pgSQL
PL/Perl
PL/Python
PL/Tcl
Projetos externos
PL/Java
PL/V8 → 9.2
PL/PHP
PL/Python
PL/Ruby
PL/sh
T
E L: Linguagens procedurais
Capacidade de executar funcões sem a necessidade de criá-las
Todas as linguagens procedurais podem ser utilizadas em linha
de comando
Sintaxe:
DO [ LANGUAGE nome_linguagem ] código
Facilita tarefas de transformação
Não há necessidade de CREATE/DROP FUNCTION
A estrutura das funções são mantidas:
[ DECLARE ]
BEGIN
END
L
ET : Unlogged Tables
Funcionalidade que possibilita a alteração de tabelas sem a
gravação nos logs de transação
Maior performance para operações de escrita na tabela
Não são recuperadas após um crash e não são replicadas
CREATE TABLE foo(id int);
CREATE UNLOGGED TABLE bar(id int);
100.000 registros com INSERT
100.000 registros com COPY
time psql -f inserts_foo.sql time psql -f inserts_foo.sql
1m40.155s
0m2.204s
time psql -f inserts_bar.sql time psql -f inserts_bar.sql
0m16.631s
0m0.738s
L
ET : Carga de dados
INSERT com múltiplas linhas
INSERT … SELECT * FROM …
COPY
Retirar chaves primárias e índices
Paralelizar a recriação
Melhor que COPY:
pg_loader
http://pgfoundry.org/projects/pgloader/
pg_bulkload
http://pgbulkload.projects.postgresql.org/
L
ET : Carga de dados
Carga de dados sem TRUNCATE (append):
Particionamento horizontal
Utiliza as funcionalidades de constraint_exclusion e herança
entre tabelas
Particionamento horizontal
CREATE TABLE fato_venda(sk_tempo int, sk_vendedor
int, data_venda date, valor_venda numeric);
CREATE TABLE fato_venda_08_2012 (CHECK (data_venda
BETWEEN '01-08-2012' AND '31-08-2012'))
INHERITS (fato_venda);
CREATE TABLE fato_venda_09_2012 (CHECK (data_venda
BETWEEN '01-09-2012' AND '30-09-2012'))
INHERITS (fato_venda);
CREATE TABLE fato_venda_10_2012 (CHECK (data_venda
BETWEEN '01-10-2012' AND '31-10-2012'))
INHERITS (fato_venda);
. . .
Novas partições podem ser criadas pela própria trigger de
inserção ou pela ferramenta de ETL
Particionamento horizontal
EXPLAIN SELECT * FROM fato_venda WHERE data_venda =
'03-10-2012';
QUERY PLAN
---------------------------------------------------------------------------------------------------Result (cost=0.00..23.75 rows=7 width=44)
-> Append (cost=0.00..23.75 rows=7 width=44)
-> Seq Scan on fato_venda (cost=0.00..0.00 rows=1 width=44)
Filter: (data_venda = '2012-10-03'::date)
-> Seq Scan on fato_venda_10_2012 fato_venda (cost=0.00..23.75 ...
Filter: (data_venda = '2012-10-03'::date)
Tablespaces
CREATE TABLESPACE tbs_indexes LOCATION '/disc2';
ALTER INDEX tempo_data_idx SET TABLESPACE tbs_indexes;
ALTER DATABASE dw SET temp_tablespaces TO tbs_temp;
Index-only scan
Consulta varre apenas o índice
Evita a busca de dados na tabela
Planos de execuções melhores para consultas que
retornam as colunas disponíveis em algum índice
2-20% mais rápido para data warehouses
CREATE INDEX foobar_idx ON foobar(id,nome);
EXPLAIN SELECT id, nome FROM foobar WHERE id = 10;
QUERY PLAN
-------------------------------------------------------Index
Only
Scan
using
foobar_idx
on
foobar
(cost=0.00..8.28 rows=1 width=11)
Index Cond: (id = 10)
Window function
Facilita o desenvolvimento de consultas complexas
SELECT regiao, loja, SUM(valor_venda) OVER
(PARTITION BY regiao) FROM vendas;
Alternativa para views materializadas
Criar uma tabela a partir do resultado de uma consulta pesada
Atualizar a tabela após alterações nas tabelas de referência
Recomendações:
Manter a view com o menor número de registros
possível
Evitar atualizações via trigger
Utilizar processos em background ou agendados
Agendamento com Pgagent
Agendador de tarefas para o PostgreSQL
Gerenciamento e monitoramento através do Pgadmin
PGQ: Fila de eventos em background
Implementação de fila (queue) no PostgreSQL
Producers e Consumers: Aplicações que criam e leem eventos
na fila respectivamente
Útil para mover dados constantemente de bases OLTP
Escalabilidade vertical
PostgreSQL 9.2
Escalabilidade com 64 cores
Redução do consumo de CPU
Melhorias na manipulação de locks
Melhorias na eficiência de escrita
4x mais rápido para operações de leitura
5x mais rápido para operações de escrita
Escalabilidade vertical
Escalabilidade horizontal
Servidores slaves replicados a partir de outro slave
Redução do impacto de vários processos wal sender no
servidor master
Arquitetura
Shared memory
CPUs compartilham memória e disco
Controle de transações simplificado
Escalabilidade limitada
Shared disc
Apenas o disco é compartilhado
Controle de transações complexo
Escalabilidade um pouco menos limitada
Fibra ótica
Cache de disco compartilhado
Arquitetura
Shared nothing
Sharding
Cada nó com seu próprio conjunto de dados
Bloqueios ocorrem no próprio nó
Maior escalabilidade
PL/Proxy
Linguagem procedural para execução de funções remotas
Pode ser utilizada para implementar sharding no PostgreSQL
Pgbouncer pode ser usado para diminuir o impacto da
abertura de conexões em todos os nós
http://www.pgxn.org/dist/plproxy/
CREATE SERVER foobar FOREIGN DATA WRAPPER plproxy
OPTIONS (connection_lifetime '600',
p0 'dbname=foobar0 host=127.0.0.1 port=15432',
p1 'dbname=foobar1 host=127.0.0.1 port=15433');
…
CREATE FUNCTION get_itens_vendidos(data_venda date)
RETURNS SETOF text AS $$
CLUSTER 'foobar';
RUN ON ALL;
$$ LANGUAGE plproxy;
PL/Proxy
StadoBI (GridSQL)
Stado BI
Continuação do GridSQL
Shared nothing
Sharding
Limitações
Window functions
Funções
Full Text Search
Postgres-XC
Fork do PostgreSQL
Merge com versões do PostgreSQL (atualmente 9.1.5)
Mesma licença
Cluster shared nothing
Escalabilidade de escrita e leitura
Sharding
Replicação
Extensão do core do PostgreSQL
Controle de transação global (GTM)
Criação de tabelas distribuídas e replicadas via SQL
Postgres-XC
Postgres-XC - Replicação
Replicação baseada em comandos
Operações de escrita em todos os nós (2PC)
Replicação sincrona = overhead
Operações de leitura balanceadas entre os nós
Escalabilidade horizontal de leitura
CREATE TABLE fato_venda (
sk_vendedor int,
sk_tempo int,
sk_produto int,
sk_regiao int,
qtd_venda int,
valor_venda numeric)
DISTRIBUTE BY REPLICATION ;
Postgres-XC - Sharding
Dados distribuídos entre os nós
Operações de escrita em paralelo
Escalabilidade horizontal de escrita
A mesma consulta ocorre em todos os nós
Consultas paralelas com resultado combinado
CREATE TABLE fato_venda (
sk_vendedor int,
sk_tempo int,
sk_produto int,
sk_regiao int,
qtd_venda int,
valor_venda numeric)
DISTRIBUTE BY HASH (sk_produto);
Postgres-XC - Sharding
ROUND ROBIN
Não necessita de chave de particionamento
A consulta sempre será executada em todos os nós
HASH (column) | MODULO (column)
Calcula o hash/módulo a partir de valores da coluna
A consulta que utilizar o campo chave do
particionamento será executada em apenas um nó
Postgres-XC
Quando replicar?
Tabela com poucas alterações
Referenciada por tabelas distribuídas (JOINs)
Necessita de índice único que não pode ser usado como
chave de particionamento
Alta disponibilidade
Quando distribuir/particionar?
Necessita de escalabilidade de escrita
Necessita de processamento paralelo
Postgres-XC
Recomendações
Replicar os nós com Streaming Replication síncrono para
o sharding
Limitações
ALTER TABLE … DISTRIBUTED BY
Triggers
WHERE CURRENT OF
SAVEPOINT
Outras características
Data warehouse Appliance
banco de dados, servidores e storages integrados
Bancos de dados orientado a coluna ou híbridos
Colunas da tabela em diferentes arquivos
Melhor aproveitamento de memória
Melhor performance com sharding e compressão
Compressão de dados
Maior capacidade de armazenamento em memória
Redução do I/O
Descompressão de dados somente na exibição
Greenplum
Banco de dados proprietário derivado do PostgreSQL
Shared nothing
Paralelizado
Híbrido
Compressão de dados
Funções analíticas
Priorização de consultas
Empresa: EMC
Vertica
Banco de dados proprietário derivado do PostgreSQL
Shared nothing
Paralelizado
Orientado a coluna
Compressão de dados
Otimização de operações de ordenação
Empresa: HP
Netezza
Plataforma analítica com hardware
Banco de dados proprietário derivado do PostgreSQL
Shared nothing
Compressão de dados
Empresa: IBM
Aster
Banco de dados proprietário derivado do PostgreSQL
Shared nothing
Paralelizado
Híbrido
Empresa: Teradata
ParAccel
Plataforma analítica
Banco de dados proprietário derivado do PostgreSQL
Shared nothing
Paralelizado
Orientado a coluna
Compressão de dados
Empresa: ParAccel
Conclusão
PostgreSQL é uma excelente escolha...
Especialmente para data warehouse
Funcionalidades avançadas
Extensões poderosas
Escalabilidade é necessária
Postgres-XC está pronto para produção
Boa parte das ferramentas proprietárias famosas específicas
para data warehouse são derivadas do PostgreSQL
Have fun with PostgreSQL !!!
Fale conosco
Matheus Ricardo Espanhol
[email protected]
www.dextra.com.br
Campinas 19 3256.6722
Treinamento www.facebook.com.br/dextratreinamentos
Treinamento @dextracursos
Sistemas www.facebook.com.br/dextrasis
Sistemas @dextrasistemas