PostgreSQL e Hadoop - Speaker Deck

Slide 1

Slide 1 text

PostgreSQL & Hadoop

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

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).

Slide 4

Slide 4 text

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.

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

16 Criação das partições desde janeiro de 2013 a maio de 2018: > SELECT fc_create_part_bf(201301, 201805); Carga no PostgreSQL

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

21 Ecossistema Hadoop

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

24 HDFS

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

26 Map Reduce

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

28 YARN

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

34 Sqoop

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

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

Slide 37

Slide 37 text

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

Slide 38

Slide 38 text

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

Slide 39

Slide 39 text

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

Slide 40

Slide 40 text

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

Slide 41

Slide 41 text

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

Slide 42

Slide 42 text

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

Slide 43

Slide 43 text

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

Slide 44

Slide 44 text

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

Slide 45

Slide 45 text

45 Uso Criação de mapeamento de usuário: > CREATE USER MAPPING FOR user_bf SERVER srv_hdfs; HDFS FDW

Slide 46

Slide 46 text

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

Slide 47

Slide 47 text

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

Slide 48

Slide 48 text

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

Slide 49

Slide 49 text

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

Slide 50

Slide 50 text

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

Slide 51

Slide 51 text

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

Slide 52

Slide 52 text

52 > cursor.execute(hiveql) result = cursor.fetchone() cursor.close() conn.close() return result[0] $$ LANGUAGE 'plpython3u'; Acesso ao Hive via PL/Python

Slide 53

Slide 53 text

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

Slide 54

Slide 54 text

54 twitter.com/lucio_chiessi www.linkedin.com/in/lucio- chiessi twitter.com/julianoatanazio https://www.linkedin.com/in/juliano777