ETL – De uma API Rest para o MySQL utilizando Pentaho – Parte 3

Olá, chegamos a terceira parte do tutorial, para você que ainda não viu a Parte 1 foi uma introdução do Pentaho Data Integration (PDI), já na Parte 2 começamos a colocar a mão-na-massa fazendo a extração de dados de uma API Rest para uma planilha. Agora o nosso objetivo é salvar estes dados em um SGBD, vou utilizar o MySQL neste exemplo, mas qualquer outro pode ser utilizado como o MariaDB, PostgreSQL, SQL Server, Oracle e etc. Além do Pentaho vamos utilizar uma ferramenta que vai facilitar (e muito!) nosso trabalho, o Docker! Vamos lá!

O que é o Docker?

Docker é uma plataforma Open Source escrito em Go, que é uma linguagem de programação de alto desempenho desenvolvida dentro do Google, que facilita a criação e administração de ambientes isolados.
by Mundo Docker

Quem já conhece sabe o quanto ajuda trabalhar com containers para montar um ambiente, basicamente, ele empacota tudo em um container, que nada mais é do que uma imagem, com tudo configurado. Existem diversos containers prontos, você só precisa instalar o Docker e executá-los. Um simples comando no terminal, de uma linha, você pode configurar um ambiente completo com servidor de aplicação e banco de dados, prontos para usar e sem nenhuma configuração, isso mesmo, nenhuma configuração!

Instalando e configurando o Docker

Bem, agora vamos precisar do Docker instalado e também do Docker Compose (um facilitador de configurações), como o nosso objetivo aqui é o Pentaho e existem ótimos tutorias na internet vou terceirizar a instalação e configuração do Docker:

Linux Ubuntu
– Como Instalar e Usar o Docker no Ubuntu 16.04
– How To Install Docker Compose on Ubuntu 16.04

Windows
– Usando Docker no Windows 10
– Install Docker Compose

Iniciando o container do MySQL

Vamos precisar de uma instância do MySQL, então criei o arquivo docker-compose.yml com as configurações do nosso banco:

  version: '2'

  services: 
  mysql:
    image: mysql:5.7
    container_name:cidades_brasil
  volumes:
    - /var/lib/cidades_brasil
  environment:
    - MYSQL_USER=pentaho
    - MYSQL_PASSWORD=pentaho123
    - MYSQL_DATABASE=cidades_brasil
    - MYSQL_ROOT_PASSWORD=pentaho123
 restart: always
 networks:
   - network_artigo1

networks:
  network_artigo1:
    driver: bridge
    ipam:
      config:
        - subnet: 172.10.0.0/16
          gateway: 172.10.0.1
          aux_addresses:
            mysql: 172.10.0.2

Este arquivo deve ficar na pasta do nosso projeto e seu nome é convencionado para que ao executarmos o Compose no diretório ele já encontre o arquivo com as definições do container. Vamos executar o Docker Compose, para iniciar o nosso container, execute comando abaixo, pelo terminal no mesmo diretório do arquivo docker-compose.yml:

docker-compose up

Ele irar baixar uma imagem de um container do MySQL versão 5.7 já configurado com os dados abaixo:

<strong>endereço:</strong> 172.10.0.2
<strong>porta:</strong> 3306
<strong>banco:</strong> cidades_brasil
<strong>usuário:</strong> pentaho
<strong>senha:</strong> pentaho123

Claro que você pode utilizar qualquer outro banco ou até mesmo uma instalação nativa no MySQL, neste caso nos passos a seguir troque as informações de conexão pelas suas.

Criando e compartilhando uma conexão

Na Parte 2 do nosso artigo nós criamos uma transformação que ao final exportava os dados para uma planilha, agora vamos salvar esses dados no banco de dados que criamos anteriormente. Vamos criar uma conexão do PDI com o MySQL para utilizarmos na Transformação, clique no menu File e escolha a opção New e então Database Connection…

Na janela que se abriu vamos dar um nome para a nossa conexão, vou colocar CidadesBrasil, na lista Connection Type vamos escolher o MySQL, em Access podemos deixar o a opção Native (JDBC). Agora vamos informar os dados da conexão nos campos do Settings, a janela deve ficar como na imagem abaixo, você pode testar a conexão através do botão Test.

Conexão com MySQL
Conexão com MySQL

No PDI a conexão fica vinculada ao arquivo onde foi criada. Então eu preciso criar uma nova conexão para uma nova transformação ou job? Não! Podemos compartilhar esta conexão, assim ela estará disponível para ser utilizada a qualquer momento. Para fazer isso, no menu lateral do PDI, clique na aba View, nela é possível ver todos os objetos utilizados na Transformação, vamos escolher a pasta Conexões, nela está a conexão que criamos: CidadesBrasil. Clique com o botão direito na conexão e escolha a opção Share, como na imagem abaixo:

Compartilhamento de Conexão
Compartilhamento de Conexão

Repare que o nome da conexão ficou em negrito, agora a conexão está compartilhada e poderá ser utilizada em qualquer novo objeto que seja criado. Caso queira remover o compartilhamento clique novamente com o botão direito na conexão e escolha a opção Stop sharing.

Salvando as informações no MySQL

Dentro do PDI, na aba lateral, escolha a opção Design, e na pasta Output, vamos utilizar o Step Insert / Update, poderíamos utilizar o Step Table Output, porém o primeiro tem a opção de verificar se um registro já existe e assim atualizá-lo. Vamos adicionar este Step a transformação, utilizaremos duas saídas, a planilha e o banco de dados, vamos fazer um hop do penúltimo step, Remoção urlAPI para o novo que acabamos de criar.

Ao fazer isso, vai abrir um menu, escolha a opção Main output of step, uma nova janela de aviso é aberta com as opções: DistribuirCopiar. Na primeira opção as linhas serão distribuídas para os steps, no nosso caso, deixando alguns estados na planilha e outros no banco. Já a opção copiar envia todas as linhas para os dois steps, assim tanto a planilha quando o banco receberão todos os estados, vamos escolher esta opção.

Distribuir ou copiar linhas
Distribuir ou copiar linhas

Vamos abrir os detalhes do step Insert / Update, vou trocar o nome desse step para Persistir MySQL, abaixo no campo Connection vamos escolher a conexão que criamos: CidadesBrasil. No campo Target table vamos colocar o nome da tabela que será utilizada, coloque estado. Mas ainda não temos esta tabela, certo? Correto, temos que criá-la, para isso temos, nesta janela, o botão SQL! Ao clicar nele obtemos o código necessário para criar a nossa tabela, o PDI cria de acordo com os campos e os tipos associados a eles. Então o código SQL para criar a nossa tabela de estado ficou assim:

CREATE TABLE estado (
    id INT, 
    sigla TINYTEXT, 
    nome TINYTEXT, 
    regiao_id TINYTEXT
);

CREATE INDEX idx_estado_lookup ON estado(id);

Nesta mesma janela temos o botão Execute, clique nele para que a tabela e seu índice, sejam criados em nossa base de dados. Pronto, já temos a tabela! Voltando a janela Insert / Update, temos duas áreas para associarmos os campos da nossa resposta com os campos da tabela que acabamos de criar:  The key(s) to look up the value(s) Update fields. No primeiro colocamos qual campo da nossa resposta corresponde ao campo da chave primária da tabela, já no segundo colocamos todos os outros campos que serão atualizados, caso seja uma atualização. Repare que na parte de baixo o campo ID fica com o valor “N” na coluna Update, pois ele só será inserido e não atualizado. A janela deverá ficar como na imagem abaixo:

Detalhes de Insert / Update
Detalhes de Insert / Update

Clique em OK e execute a transformação, agora, além da planilha, os dados dos estados obtidos na API também foram persistidos no banco de dados.

Conclusão

Nesta Parte 3 conseguimos montar um fluxo mais parecido com um cenário “real”, pois o resultado de um processo ETL, geralmente, é armazenado em uma base de dados para ser consultado posteriormente por uma aplicação que utilizará estes valores. Abaixo temos o resultado da transformação com algumas estatísticas de execução, analisar esses pontos é sempre importante para verificarmos onde pode existir gargalos em nosso processo, e assim mitigá-los.  Na próxima, e última, parte deste artigo veremos como compartilhar informações entre transformações através de jobs. Caso alguma parte tenha ficado meio nebulosa ou não tenha conseguido executar entre em contato e mande a sua dúvida nos comentários!

Resultado da Transformação
Resultado da Transformação

Até o próximo!

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *