Pentaho Data Integration - ETL em Software Livre

Este artigo é um relato de experiência para solucionar um problema relacionado à transferência de grandes volumes de dados entre sistemas utilizando Pentaho data Integration como solução, com isso reduzindo o tempo de processamento, o esforço de desenvolvimento e aumentando o valor agregado para os usuários finais do sistema.

A suite Pentaho é formada por um conjunto de softwares voltados para construção de soluções de BI de ponta-a-ponta, que inclui programas para extrair os dados de sistemas de origem em uma empresa, gravá-los em um data warehouse (ou base de dados), limpá-los, prepará-los e entregá-los a outros sistemas de destino ou mesmo a outros componentes da suíte para estudar ou dar acesso aos dados ao usuário final.​

O Pentaho Data Integration, é parte das soluções disponibilizadas pela suite Pentaho, possui versões Community e Enterprise mas a diferença existente entre as versões não representa nenhum impeditivo para o uso da versão Community. A versão Community possui todos os recursos necessários a qualquer implementação que se deseje realizar e possui vasta disponibilidade de plugins para serem utilizados, inclusive plugins que geram a documentação de projetos, como o Kettle Cookbook. Todo o processo de extração e transformação e carga descrito neste artigo foi realizado com o Pentaho Data Integration Community, versão 7.1.

O que é possível fazer com o Pentaho Data Integration?

O Pentaho Data Integration é o componente da suíte Pentaho usado para criar processos de extração, transformação e carga (do inglês Extraction, Transformation and Loading, ETL) que alimentam o banco de dados.​ Trata-se da ferramenta mais popular e madura da suíte inteira, com seus mais de 15 anos de existência.

Com o Pentaho Data Integration é possível fazer inúmeras operações de Integração de Dados. Como por exemplo:

• Migração de dados;
• Movimentação de grandes volumes de dados;
• Transformação de dados;
• Limpeza de dados;
• Conformidade de dados,

Conceitos Básicos - Spoon, Kitchen e Pan

O Spoon

O Pentaho Data Integration é formado por duas categorias de artefatos, Jobs e Transformaç&os, e estes artefatos são construídos por meio de sua interface gráfica, o Spoon. O Spoon é a interface gráfica do Pentaho Data Integration que facilita na concepção de rotinas e lógica ETL. A seguir, apresentamos a interface do Spoon.

Transformações

Uma transformação registra o passo-a-passo de como a extração ou leitura de uma fonte de informação é realizada. É a transformação que opera sobre os dados. Ela pode conter:

• Leitura de dados de uma tabela, de um banco de dados;
• Seleção de campos específicos de uma tabela;
• Concatenação de valores de dois campos distintos de uma tabela;
• Divisão de valores contidos em um único campo gerando dois ou mais novos campos ou linhas;
• Merge de dados de tabelas contidas em bancos de dados diferentes;
• Merge de dados originados em tabelas, arquivos XML, TXT ou CSV, entre outras fontes de dados;
• Aplicação de expressões regulares em texto para limpeza.

O aspecto mais importante em uma transformação é que ela opera todas as etapas simultaneamentes - uma transformação não tem início ou fim, ela apenas processa linhas que chegam.

Jobs

Um Job sequencia operações. Ao contrário de uma transformação, que opera sobre as linhas de dados em paralelo, um job realiza operações completas, uma por uma. Ele permite, por exemplo, combinar transformações em uma sequência específica e, com isto, automatizar uma dada tarefa. Por sua natureza, ele não fornece muitos recursos técnicos para manusear os dados em si, deixando isto à cargo das transformações.

Eis as principais características de JOBs e Transformações.

A lista completa de funcionalidades dos Jobs e Transformações está disponível na Wiki do projeto.

Pan

O Spoon, porém, é só a interface gráfica para criar os processos de integração de dados. Ele não serve para executá-los em produção, ou seja, no ambiente sem supervisão humana. Para isso usamos outros programas, que operam em linha de comando, sem interface gráfica.

O Pan é o programa que executa transformações. Vale a pena mencionar que tanto jobs quanto transformações podem ser arquivos em um sistema de arquivos, normal, ou em um repositório em banco de dados. O pan pode executar uma transformação a partir de qualquer uma destas origens.

Em geral, as transformações executadas pelo Pan são agendadas em modo batch, para que possam ser executadas automaticamente em intervalos regulares por alguma ferramenta de gerenciamento de tarefas como o crontab por exemplo.

Kitchen

Enquanto o Pan executa transformações, o Kitchen executa jobs. Tal qual ocorre com o Pan, o Kitchen pode executar jobs a partir de um sistema de arquivos ou de um repositório em banco de dados.

Novamente, tal qual o Pan, jobs são executados em modo batch através do agendamento no modo batch para serem executados automaticamente em intervalos regulares por alguma ferramenta de gerenciamento de tarefas como o crontab por exemplo.

A tríade Spoon, Pan e Kitchen são os responsáveis pela criação e execução de artefatos criados para solucionar um problema de extração, transformação e carga de dados em um projeto de ETL com o Pentaho Data Integration.

Caso de uso

Segundo Nitin Anand, em seu artigo para o International Journal of Scientific and Research Publications:

Um componente importante em um projeto de BI é o processo de Extrair, Transformar e Carregar (ETL). Ele descreve a coleta de dados de várias fontes (extrair), sua modificação para combinar o estado desejado (transformação) e sua importação em um banco de dados ou data warehouse (carga). Os processos de ETL são responsáveis por até 80% do esforço em projetos de BI. Um alto desempenho é, portanto, vital para poder processar grandes quantidades de dados e ter um banco de dados atualizado.

Recentemente, enfrentamos um cenário em que precisávamos mover dados de um banco de dados Oracle para serem consumidos por um outro sistema que também utilizava Oracle como sistema de armazenamento. A necessidade de se copiar estes dados ocorria devido a natureza da aplicação destino que necessitava de intensa sumarização de dados e processamento o que não poderia ser realizado no banco de dados de origem para não comprometer o seu uso, uma vez que o mesmo possui uma natureza transacional, ou seja, é utilizado diariamente por todos os usuários da empresa em suas atividades. Sumarizar dados neste ambiente poderia implicar em prejuízo para as operações normais do dia a dia.

Neste cenário, a tarefa de transferir dados do banco de dados de origem para o destino era realizada por meio de um processo de ETL, desenvolvido em JAVA quando este sistema legado foi entregue pela primeira vez 6 anos atrás. Esta solução se comunicava com os sistemas origem e destino por meio de uma API que consultava dados de um lado e escrevia do outro lado. APIs são soluções tecnológicas concebidas para transferir informação entre sistemas de forma cadenciada e em pequenas porções. Utilizar esta tecnologia para mover grande quantidade de dados pode implicar, entre outros problemas, no seguinte:

• Criação de gargalos nos sistemas de origem e destino devido a alta carga que será movimentada;
• Caso seja utilizado algum mecanismo de ESB (Enterprise Service Bus) este ambiente pode ficar sobrecarregado devido a alta volumetria de mensagens, talvez arquivos JSON, que trafegarão por este meio (middleware);
• Gestão orientada a codificação. Quando a tarefa de transformar dados ou mesmo mover entre sistemas é endereçada via aplicação desenvolvida em Java ou outra linguagem de programação, momentaneamente esta solução pode resolver o problema mas com o passar do tempo, e à medida que novas necessidades surgem nas fontes de informação de origem, modificações necessitarão ser realizadas no código e, consequentemente, com o passar do tempo o conhecimento e esforço despendido nesta tarefa vai aumentando. A tendência de se perder em meio a essa complexidade aumenta, deixando a manutenção do legado cada vez mais custosa. Devemos sempre pensar no futuro e na manutenibilidade, que quando mal planejada pode encarecer os custos de suporte e manutenção de um software.

O cenário descrito neste artigo trata de um sistema legado, desenvolvido sem as boas práticas de engenharia de software e que possuía uma natureza crítica. Além disso, não poderia deixar de funcionar nem por 1 minuto. Por esta natureza de alta criticidade, à medida que o ETL em Java falhava, cada vez mais a fragilidade do sistema destino para o usuário aumentava gerando insegurança.

Com o passar dos anos, essa solução não se mostrou eficiente o bastante e constantemente apresentava problemas, incluindo a interrupção de seu funcionamento. Partimos, então, para outras alternativas.

Para solucionar este problema encaramos o desafio de utilizar o Pentaho data Integration. O resultado dessa abordagem será descrito nos próximos parágrafos.

Estratégia

Nossa tarefa inicial consistiu em depurar o legado em Java e entender cada uma das consultas e extrações que estavam sendo realizadas e mapear de tal forma que apenas a parte referente às consultas pudessem ser reaproveitadas.

Uma das medidas mais importantes que devemos adotar quando estamos optando por melhorar um sistema legado é o mapeamento adequado das funcionalidades e o reaproveitamento de funcionalidades que apesar de estarem mal implementadas ainda possuem valor. Com isto, todas as consultas SQL utilizadas para a extração da informação no banco de dados de origem foram reaproveitadas ou sofreram os ajustes necessários para melhorar seu desempenho.

Partimos então para o desenho da solução, onde basicamente identificamos as tabelas e views no banco de dados origem que teriam dados a ser coletados, a lógica das transformações, os jobs necessários e por fim o armazenamento no banco de dados de destino, aproveitando a estrutura de tabelas e funções PL/SQL existentes no sistema legado de forma que não houvesse impacto para o usuário final.

Feito o mapeamento e o primeiro esboço da solução, decidimos iniciar os testes e, em ambiente de testes, fazer a transferência dos dados.

No mapeamento identificamos duas tabelas principais necessárias para trazer os dados utilizados pela aplicação. Essas duas tabelas teriam seus dados capturados no banco de dados origem. Usando essa informação criamos as transformações necessárias para realizar a tarefa antes executada pelo ETL em Java, de transferir essas informações do banco de dados origem para o destino.

Nossa implementação da solução utilizando o Pentaho Data Integration levou menos de 2 dias de trabalho (12 horas comerciais). Com ela conseguimos transferir todos os dados em apenas 30 minutos em ambiente de testes.

A Lei de Murphy 1

Murphy existe e logo descobrimos outras duas tabelas, não identificadas antes, que também precisavam ser copiadas no banco de dados de destino. Além disso precisamos criar mais transformações para corrigir pequenos problemas que estavam sendo capturados no banco de dados de origem e arrumar estes dados para ser gravados, de tal forma que o sistema destino reconhecesse as informações.

Mais um dia útil de desenvolvimento de transformações e conseguimos criar toda a estrutura necessária para que os dados de origem fossem adequadamente copiados para o banco de dados de destino.

Todo o processo de carga, após o mapeamento correto da fonte de informação, foi executado em cerca de 1 hora e 40 minutos e com isto tivemos um ganho considerável com a tarefa de mover dados de uma fonte origem para o destino.

A lei de Murphy 2

Após a cópia dos dados ser concluída e consolidada, descobrimos que a camada de visualização e os dashboards estavam exibindo as informações de forma errada, não representando o estado correto dos dados persistidos no banco de dados destino. Após mais 5 dias de trabalho árduo, descobrimos que, devido às falhas e indisponibilidades como a não conclusão das extrações realizadas pela antiga solução de ETL em Java, que as interfaces e APIs que consultavam os dados estavam com implementações que realizam ajustes nos dados para que ao menos representassem as informações esperadas. Por isso precisamos ajustar toda essa parte do sistema, que não tinha nenhuma relação com a cópia dos dados, para que as informações pudessem ser exibidas e representadas tal como estavam armazenadas no banco de dados de destino.

Esta tarefa exigiu depuração de código, conferência de informações no banco de dados origem e destino, correção de métodos e das APIs de recuperação de informação para os dashboards entre outras correções.

Note que estas tarefas não possuem relação alguma com o trabalho executado pelo Pentaho Data Integration mas precisaram ser realizadas pois a tarefa de construir e ajustar software é parte intrínseca de qualquer time de desenvolvimento ágil e que se preocupa com a qualidade de sua entrega final.

Por fim, ao todo, atuamos por cerca de 10 dias úteis até concluir todos os ajustes necessários para garantir a qualidade do produto a ser entregue. Recebemos feedbacks valiosos de nosso cliente e uma parte importante de manutenção e ajuste desse legado foi alcançada, dando muito mais flexibilidade a tarefa de manter este processo, sem contar com a estabilidade alcançada pelo sistema, que aumentou em muito a confiabilidade dos clientes nele.

Características e particularidades do Pentaho Data Integration

O Pentaho Data Integration possui características muito particulares quando nos referimos a captura de dados em fontes de informações, sejam elas bancos de dados, arquivos TXT ou CSV, arquivos XML ou JSON ou até mesmo arquivos DBF. É possível fazer ajustes finos, inclusive com relação ao número de threads que podem ser executadas por um passo na transformação.

Outro aspecto importante e que também pode ser executado com o Pentaho Data Integration é escolher, a partir de uma fonte de dados, quais informações desejamos que seja transferida para o passo seguinte, ou seja, em uma tabela de uma banco de dados origem, é possível selecionar os campos exatos que se deseja capturar, em uma planilha eletrônica também podemos selecionar exatamente as colunas que são necessárias.

A ferramenta é muito flexível e possibilita inúmeros arranjos para que ao final, após a cópia de dados e transformações destes, tenhamos apenas o desejado.

As integrações que podem ser realizadas com o Pentaho Data Integration incluem, entre outros recursos:

• Exportar dados para um arquivo em formato texto em uma conta do Amazon Simple Storage Service (S3);
• Conectar a um serviço JIRA e executar a extração de dados JSON sobre os resultados;
• Capturar dados da conta do Google Analytics;
• Ler e enviar mensagens binárias para uma fila de mensagens do Apache Kafka;
• Enviar mensagens para canais ou grupos no Slack;
• Ler conteúdo de textos de vários tipos de arquivos (PDF, DOC, etc...) usando o Apache Tika.

Conclusão

Com o tempo e à medida que usamos cada vez mais o Pentaho data Integration, as funcionalidades necessárias para a construção de transformações que geram valor ficam cada vez mais inteligíveis. Esta curva de aprendizado é crescente mas de inclinação suave, pois essa ferramenta é muito intuitiva.

Adotar o Pentaho Data Integration gera valor a um custo comparativamente menor que o desenvolvimento de ETLs com código.

Há algum tempo presenciamos um cenário onde uma solução para ler um arquivo XML e transformar o conteúdo deste arquivo em formato CSV para que pudesse ser lido por outro sistema levou cerca de 3 meses para ser concluído. Devido à falta de conhecimento em uma ferramenta robusta de integração de dados via ETL, o time que desenvolveu esta solução precisou passar por todas as fases de um modelo tradicional de desenvolvimento de software, onde só a fase de mapeamento das informações de entrada levou 30 dias para ser concluída. A solução, que nada mais era que um ETL, foi desenvolvida totalmente em Java por pura falta de conhecimento de ferramentas de ETL, como o Pentaho Data Integration. O valor investido nestes três meses de desenvolvimento certamente poderia ter sido investido em outras iniciativas.

Quando nos referimos à gerar valor estamos nos referindo não apenas em satisfazer as necessidades de nosso cliente ou unidade de negócio, estamos também nos referindo a salvar recursos financeiros evitando o desperdício de implementações manuais de código para realizar tarefas de um ETL. Com investimento em um código personalizado há uma falsa impressão de redução de custos, já que o custo inicial é baixo mas os custos com suporte e melhorias crescem à medida que as necessidades dos negócios mudam.

Mover grande quantidades de dados por meio de código, utilizando APIs pode, entre outras situações, sobrecarregar um barramento de serviços e cedo ou tarde o histórico de desenvolvimento desta solução irá se perder ou, como ocorreu com o sistema legado mencionado neste artigo, deixar de entregar resultados e se tornar um problema para o sistema e os times de desenvolvimento e, acima de tudo, a empresa.

O uso de um ETL consolidado e largamente utilizado como o Pentaho Data Integration traz maior flexibilidade, menor tempo de desenvolvimento e melhor estruturação para tarefas como as discutidas neste artigo. Entre as principais características do Pentaho Data Integration, podemos destacar:

• Abordagem orientada a modelos com o uso de metadados:

• Intuitivo com possibilidade de responder facilmente à perguntas tais como o que fazer e como fazer;
• Realizar transformações complexas com zero codificação;
• Representar graficamente fluxos de transformações de dados (transformações) e orquestração de tarefas (jobs);

• 100% em Java, com suporte multiplataforma (Windows, Linux e Mac OSX);
• Arquitetura extensível por meio de plugins, sem contar o fato de que é Software Livre e pode ser modificado à vontade

A seguir apresentamos os benefícios alcançados com esta solução por meio do Pentaho Data Integration.

Note que em um processo de ETL, a volumetria dos dados nem é tão significativa diante da natureza multithread que uma solução como esta pode prover. O fator mais relevante é a quantidade de linhas que serão lidas, processadas, transformadas e gravadas no destino e em quanto tempo. A quantidade de linhas (e sua complexidade) que será processado pela solução é um fator importante e que necessita ser avaliado com todo o cuidado. Há inúmeros relatos publicados na Internet de casos de processamento de milhões de registros por segundo utilizando o Pentaho Data Integration. Obter esse alto poder de processamento depende da forma como o processo é concebido.

Eis o tempo total de processamento para o cenário do sistema legado descrito neste artigo, antes e depois da implementação de um ETL com a ferramenta.

E a seguir apresentamos uma tabela comparativa com relação ao tempo de desenvolvimento, comparando uma solução codificada com uma solução em ferramenta.

A infraestrutura utilizada para este cenário em produção fez uso de um servidor com as seguintes características:

• XEON CPU (24 Core)
• 64 GB de memória
• HD SSD com 500GB
• SO Windows Server

O consumo do hardware pela solução mostrou-se insignificante, visto que sequer onerou recursos durante a execução conforme apresenta a imagem a seguir.

No servidor de origem, onde as informações são capturadas, também não houve nenhum impacto e sequer notamos qualquer diferença com relação à extração dos dados no banco de dados Oracle quando comparado com a solução codificada em Java. O SGDB se comportou sem nenhum incidente.

No total, foram construídas 28 transformações e 17 Jobs para atender a demanda, tarefa esta executada em cerca de 3 dias úteis ou 24 horas de trabalho por um time formado por 3 integrantes que receberam treinamento adequado e profissional e que já conheciam a suíte Pentaho de experiências anteriores.

Não é fácil realizar experimentos com processos de desenvolvimento, mas ocasionalmente nos deparamos com o caso das duas Alemanhas, que é o mais perto que podemos chegar de um experimento de laboratório com pessoas: duas equipes completamente separadas, desenvolvendo a mesma coisa a partir do mesmo ponto inicial e condições semelhantes. As escolhas ao longo do caminho são, então, a única explicação para quaisquer diferenças de resultados. No nosso caso, a diferença é a opção de tecnologia para resolver um problema de integração de dados e os fatos claramente favorecem a ferramenta de um ETL sobre o código puro. Se ainda havia alguma dúvida acerca da vantagem em se usar uma ferramenta de ETL em relação a desenvolver a mesma operação em código, os fatos aqui descritos resolveram-na completamente: ferramentas de ETL dão resultados vastamente superiores à criação de código a um menor custo e em um menor prazo.

Sobre os Autores