PostgreSQL e Hadoop

Ab16e23b7ca3e889e6f6231b2fb32340?s=47 Juliano Atanazio
August 13, 2018
310

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.

Ab16e23b7ca3e889e6f6231b2fb32340?s=128

Juliano Atanazio

August 13, 2018
Tweet

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