PostgreSQL e Hadoop

Em tempos que o assunto Big Data está cada vez mais presente em nossos dias o PostgreSQL não poderia ficar de fora dessa.
Neste trabalho algumas explanações sobre o assunto e interações entre PostgreSQL e Hadoop.

Transcript

1. PostgreSQL & Hadoop
2. 2 • Apresentar conceitos sobre Big Data, Hadoop e integração / interação com o PostgreSQL; • Fomentar a cultura Big Data para DBAs PostgreSQL; • Apresentar o PostgreSQL para profissionais Big Data; • Disseminar conhecimentos :) Objetivos
3. 3 Lúcio Chiessi Analista de Sistemas, DBA e Programador. Como sócio da VORio Tecnologia da Informação, atua a mais de 20 anos nas áreas de Desenvolvimento de Sistemas, Banco de Dados, Servidores FreeBSD, Linux e Windows. Possui mais de 10 anos de experiência com o PostgreSQL, utilizando-o em seus sistemas e também atuando como DBA para diversas empresas com grande volume de informações e transações e em ambientes de missão crítica. Professor do Curso de Extensão em Banco de Dados PostgreSQL na Universidade do Estado do Rio de Janeiro (UERJ).
4. 4 Juliano Atanazio Graduado pela FATEC Zona Sul, em São Paulo – SP, no curso “Informática para Gestão de Negócios”. Trabalha em uma grande empresa do Governo Federal atuando como DBA e Instrutor PostgreSQL, cujo trabalho é realizar tarefas rotineiras de um Administrador de Banco de Dados tais como Gerenciamento, Migração, Execução de Fluxos de RDM, SS, tratamentos de RIs, atividades de consultoria interna orientando e auxiliando na modelagem de bases de dados e Instrutoria Interna.
5. 5 “Big Data é um termo amplamente utilizado na atualidade para nomear conjuntos de dados muito grandes ou complexos, que os aplicativos de processamento de dados tradicionais ainda não conseguem lidar. Os desafios desta área incluem: análise, captura, curadoria de dados, pesquisa, compartilhamento, armazenamento, transferência, visualização e informações sobre privacidade dos dados.” https://pt.wikipedia.org/wiki/Big_data Big Data
6. 6 É um conceito de Big Data que diz respeito ao tratamento de dados com relação a: Veracidade Variedade Velocidade Volume VALOR 5 Vs de Big Data
7. 7 Como laboratório de testes foram utilizados dados do Portal de Transparência do Governo Federal referentes a pagamentos do programa social Bolsa Família: http://www.portaltransparencia.gov.br/download-de- dados/bolsa-familia-pagamentos Dados de Laboratório
8. 8 • Arquivos CSV no site compactados como zip; • Dados desde janeiro de 2013; • Ano e mês estão no formato YYYYMM; • Shell Script para automatizar os downloads; • Arquivos em formato DOS (CR/LF): converter para formato Unix (dos2unix); Tratamento de Dados
9. 9 • Converter codifcação de ISO-8859-1 para UTF-8 (iconv); • Extrair apenas colunas que interessam (cut); • Converter nomes de campos para fcarem iguais aos da tabela de carga no PostgreSQL tirando letras maiúsculas, espaços e caracteres especiais (sed); • Por questões de falta de espaço, compactar cada arquivo baixado no formato xz com compactação máxima com nomenclatura no formato YYYYMM.csv.xz. Tratamento de Dados
10. 10 Criação do usuário de banco: > CREATE ROLE user_bf ENCRYPTED PASSWORD '123' LOGIN; Criação da base de dados: > CREATE DATABASE db_bf OWNER user_bf; Conectar à base criada (psql): > \c db_bf user_bf Carga no PostgreSQL
11. 11 Criação da tabela de carga: > CREATE UNLOGGED TABLE tb_bf( ano_mes int, uf char(2), municipio text, nis int8, beneficiario text, valor numeric(7,2) ) PARTITION BY LIST (ano_mes); Carga no PostgreSQL
12. 12 Criação de function para criar partições automaticamente: > CREATE OR REPLACE FUNCTION fc_create_part_bf (ano_mes_ini int, ano_mes_fim int) RETURNS VOID AS $BODY$ Continua → Carga no PostgreSQL
13. 13 > DECLARE ano_ini int2 := substring(ano_mes_ini::text, 1, 4); mes_ini int2 := substring(ano_mes_ini::text, 5, 6); dt_ini timestamp := ano_ini||'-'||mes_ini||'-01'; ano_fim int2 := substring(ano_mes_fim::text, 1, 4); mes_fim int2 := substring(ano_mes_fim::text, 5, 6); dt_fim timestamp := ano_fim||'-'||mes_fim||'-01'; r record; Continua → Carga no PostgreSQL
14. 14 > BEGIN FOR r IN SELECT to_char(generate_series(dt_ini, dt_fim, '1 month'), 'YYYYMM')::int AS ano_mes LOOP Continua → Carga no PostgreSQL
15. 15 > EXECUTE 'CREATE UNLOGGED TABLE IF NOT EXISTS tb_bf_'|| r.ano_mes|| ' PARTITION OF tb_bf FOR VALUES IN ('|| r.ano_mes||')'; END LOOP; END; $BODY$ LANGUAGE PLPGSQL; Carga no PostgreSQL
16. 16 Criação das partições desde janeiro de 2013 a maio de 2018: > SELECT fc_create_part_bf(201301, 201805); Carga no PostgreSQL
17. 17 Loop no shell do SO para carregar cada arquivo na base: $ for i in *.xz; do echo ${i}; xzcat ${i} | \ psql -1 -U user_bf -h 192.168.0.2 \ -1qc "COPY tb_bf FROM STDIN CSV \ HEADER DELIMITER ';'" db_bf; done Carga no PostgreSQL
18. 18 • Desenvolvido e mantido pela Apache Foundation como software open-source para computação distribuída de forma escalável e confável. • É um framework que permite o processamento distribuído de grandes volumes de dados através de clusters de computadores. Hadoop
19. 19 • É projetado para escalar desde servidores únicos a milhares de máquinas cada uma oferecendo seu próprio processamento e armazenamento. • É um sistema de arquivos distribuído que o Hadoop com recursos de replicação e tolerância a falhas. http://hadoop.apache.org/ Hadoop
20. 20 É o termo que se refere a vários componentes da biblioteca do Apache Hadoop, bem como outras ferramentas / projetos fornecidos pela Apache Software Foundation e as formas de trabalharem juntos. Ecossistema Hadoop
21. 21 Ecossistema Hadoop
22. 22 • Hadoop Distributed File System, é um sistema de arquivos distribuído que o Hadoop utiliza e tem tolerância a falhas. • Feito com base na especifcação do GFS (Google File System); • Projetado para atender demandas de volume, velocidade e variedade (3 Vs de Big Data); HDFS
23. 23 • Altamente escalável, horizontalmente, adicionando novos nós ao cluster; • Filesystem de computação distribuída, cujos dados são mapeados por um Namenode (que é gerenciado pelo YARN) sabendo onde está cada bloco de arquivo no cluster; • Divide arquivos em blocos de 64MB ou 128 e armazena cada bloco em um datanote sendo que esse bloco é replicado, 3 vezes por padrão, o que é conhecido como fator de replicação. HDFS
24. 24 HDFS
25. 25 • Mecanismo de processamento de dados em paralelo; • É um modelo de programação para processamento de dados; • É um processo / uma tarefa que o Hadoop executa, um map reduce job; • Transforma dados maiores em dados menores conforme o objetivo; Map Reduce
26. 26 Map Reduce
27. 27 • Yet Another Resource Negotiator (Ainda outro negociador de recursos), é o gerenciador de recursos e agendamento de tarefas para cluster Hadoop a partir da versão 2; • É um framework para fornecer recursos computacionais (CPU, memória, I/O de disco e banda de rede) para motores (engines) de execução; • Map Reduce, Spark, Storm, Solr e Tez funcionam rodam sobre o YARN, assim sendo o YARN os intermedia com o HDFS; • Oferece API de recursos do Hadoop. YARN
28. 28 YARN
29. 29 • Desenvolvido e mantido pela Apache Foundation como software open-source para data warehouse, facilita leitura, escrita e gerenciamento de gandes volumes de dados que estão em armazenamento distribuído usando SQL (HiveQL). • Fornece um mecanismo para projetar estrutura para esses dados usando uma linguagem como SQL chamada HiveQL. Hive
30. 30 • Ao mesmo tempo, essa linguagem também permite que os programadores tradicionais de map/reduce conectem seus mapeadores e redutores personalizados quando é inconveniente ou inefciente expressar essa lógica no HiveQL. http://hive.apache.org/ Hive
31. 31 • Desenvolvido e mantido pela Apache Foundation como software open-source é um engine unifcado de análise para grandes volumes de processamento de dados. • É um framework de computação de propósito geral que suporta uma grande variedade de casos de uso. Spark
32. 32 • O Spark não fornece uma camada de armazenamento, ele depende de armazenamento de terceiros como Hadoop (HDFS), HBase, Cassandra, S3, etc. • O Spark integra-se perfeitamente ao Hadoop e pode processar dados existentes. http://spark.apache.org/ Spark
33. 33 • Desenvolvido e mantido pela Apache Foundation como software open-source é uma ferramenta projetada para efcientemente transferir dados em massa (bulk data) entre o Hadoop e bases de dados relacionais estruturadas. Sqoop
34. 34 Sqoop
35. 17 No Hive criar a base de dados para a carga: $ hive -e 'CREATE DATABASE db_bf;' Via Sqoop buscar os dados do servidor PostgreSQL: $ sqoop import \ --connect jdbc:postgresql://192.168.0.2:5432/db_bf \ --username user_bf \ -P \ --target-dir /db/pgsql/db_bf/table/bf \ --table tb_bf \ --split-by ano_mes \ --hive-import \ --hive-table db_bf.tb_bf \ -m 1 Carga no Hadoop
36. 36 É um FDW (Foreign Data Wrapper) desenvolvido e mantido pela EnterpriseDB e seu código-fonte é aberto; Seu objetivo é fornecer uma interface entre o PostgreSQL e o Hive. https://github.com/EnterpriseDB/hdfs_fdw HDFS FDW
37. 37 Instalação no PostgreSQL Instalação de Java OpenJDK e do FDW via yum: # yum install -y hdfs_fdw_10 java-1.8.0-openjdk Precisamos de bibliotecas java (.jar) necessárias para o FDW funcionar, que devem ser extraídas do Hadoop e do Hive. HDFS FDW
38. 38 Instalação no PostgreSQL Download do Hadoop e do Hive: # wget -c \ http://ftp.unicamp.br/pub/apache/hadoop/common/hadoop- 3.1.0/hadoop-3.1.0.tar.gz # wget -c \ http://ftp.unicamp.br/pub/apache/hive/hive-3.1.0/\ apache-hive-3.1.0-bin.tar.gz HDFS FDW
39. 39 Instalação no PostgreSQL Descompactar ambos os arquivos baixados: # ls *.gz | xargs -i tar xf {} Variável de ambiente para o diretório de bibliotecas da instalação do PostgreSQL: # export PGLIB=`/usr/pgsql-10/bin/pg_config --libdir` HDFS FDW
40. 40 Instalação no PostgreSQL Mover as bibliotecas .jar de Java para o diretório de bibliotecas da instalação do PostgreSQL: # mv hadoop-3.1.0/share/hadoop/common/\ hadoop-common-3.1.0.jar ${PGLIB}/ # mv apache-hive-3.1.0-bin/jdbc/\ hive-jdbc-3.1.0-standalone.jar ${PGLIB}/ HDFS FDW
41. 41 Instalação no PostgreSQL Apagar o que foi baixado e / ou descompactado: # rm -fr apache-hive-* hadoop-* Atualizar a base de dados de flesystem do SO: # updatedb Criar um link da biblioteca C da JVM no diretório de bibliotecas do PostgreSQL: # ln -s `locate libjvm.so` ${PGLIB}/ HDFS FDW
42. 42 Instalação no PostgreSQL Variável de ambiente PGDATA: # export PGDATA=`su - postgres -c "psql -Atqc \ 'SHOW data_directory'"` O FDW HDFS tem GUCs próprias e por questões de boas práticas, tais confgurações estarão em um arquivo separado dentro do $PGDATA. No postgresql.conf, será utilizada a directiva include: include = 'hdfs_fdw.conf' HDFS FDW
43. 43 Instalação no PostgreSQL Criação do arquivo de confguração do FDW HDFS: # cat << EOF > ${PGDATA}/hdfs_fdw.conf hdfs_fdw.jvmpath = '${PGLIB}/' hdfs_fdw.classpath = '${PGLIB}/hadoop-common-3.1.0.jar:\ ${PGLIB}/hive-jdbc-3.1.0-standalone.jar:\ ${PGLIB}/HiveJdbcClient-1.0.jar' EOF Dar propriedade para o usuário e grupo postgres do SO: # chown postgres: ${PGDATA}/hdfs_fdw.conf HDFS FDW
44. 44 Uso Habilitar a extensão na base de dados: > CREATE EXTENSION hdfs_fdw; Criação do objeto SERVER do FDW: > CREATE SERVER srv_hdfs FOREIGN DATA WRAPPER hdfs_fdw OPTIONS (host '192.168.0.6', client_type 'spark'); HDFS FDW
45. 45 Uso Criação de mapeamento de usuário: > CREATE USER MAPPING FOR user_bf SERVER srv_hdfs; HDFS FDW
46. 46 Uso Criação da tabela estrangeira que aponta para o Hive no servidor Hadoop: > CREATE FOREIGN TABLE ft_bf( ano_mes int, uf char(2), municipio text, nis int8, beneficiario text, valor numeric(7,2)) SERVER srv_hdfs OPTIONS (dbname 'db_bf', table_name 'tb_bf'); HDFS FDW
47. 47 Uso Somar os valores pagos ao estado de SP em fevereiro de 2014: > SELECT sum(valor) FROM ft_bf WHERE ano_mes = 201402 AND uf = 'SP'; sum ------------- 186483736.0 HDFS FDW
48. 48 Python é uma linguagem de programação de altíssimo nível para uso geral, interpretada, fácil de aprender e de dar manutenção e totalmente orientada a objetos. PL/Python é a implementação de Python como linguagem procedural dentro do PostgreSQL, muito útil para operações fora dele. Acesso ao Hive via PL/Python
49. 49 Instalação de bibliotecas Python via PIP: # pip3.6 install pyhive thrift Habilitar a extensão PL/Python na Base de Dados: > CREATE EXTENSION plpython3u; Acesso ao Hive via PL/Python
50. 50 Criação de function para buscar os resultados no Hive via Python. Recebe como parâmetro ano e mês no formato YYYYMM e a UF. Retorna a soma dos valores pagos: > CREATE OR REPLACE FUNCTION fc_bf_soma_valor_uf(ano_mes INT, uf CHAR(2)) RETURNS NUMERIC(10, 2) AS $$ Continua → Acesso ao Hive via PL/Python
51. 51 > from pyhive import hive conn = hive.Connection(host='192.168.0.6', auth='NOSASL') cursor = conn.cursor() hiveql = ("SELECT sum(valor) FROM db_bf.tb_bf " "WHERE ano_mes = {} AND uf = '{}'") hiveql = hiveql.format(ano_mes, uf) Continua → Acesso ao Hive via PL/Python
52. 52 > cursor.execute(hiveql) result = cursor.fetchone() cursor.close() conn.close() return result[0] $$ LANGUAGE 'plpython3u'; Acesso ao Hive via PL/Python
53. 53 Soma dos valores pagos ao estado de SP em fevereiro de 2014: > SELECT fc_bf_soma_valor_uf(201402, 'SP'); fc_bf_soma_valor_uf --------------------- 186483736.0 Acesso ao Hive via PL/Python
54. 54 twitter.com/lucio_chiessi www.linkedin.com/in/lucio- chiessi twitter.com/julianoatanazio https://www.linkedin.com/in/juliano777