Substring

Existem algumas formas de obtermos pedaços de um texto ou string no Kettle, no entanto gostaria de demonstrar rapidamente como usar a maneira mais simples e adequada, através do step Strings Cut, que pode ser encontrado na aba Transform.

 

Primeiramente, é importante deixar claro que, caso o usuário tente pegar o substring de um campo numérico, data, ou qualquer outro que não seja um string/texto, o Kettle interromperá a execução e exibirá um erro.    Se em alguma parte do processo isso for necessário, será importante realizar a conversão prévia do data type.

 

Observe abaixo que, em nosso exemplo, vamos utilizar o step Generate Rows para criar um campo e simular um valor para recuperarmos o substring.

 

 

 





 

 

Vamos para as propriedades do Strings Cut:

1. A contagem começa do zero, ou seja, a primeira posição é o 0;
2. O step corta os pedaços de acordo com a posição inicial e a posição final.    É muito comum confundirmos o uso deste step com a lógica da função substring do SQL ou Javascript, que normalmente recebe a posição inicial e o número de casas/caracteres a partir dela.    Neste caso, coloque no campo Cut from a posição inicial e no campo Cut to a posição final;
3. O campo In stream field recebe o campo texto onde será aplicado o corte;
4. O campo Out stream field recebe o nome do campo criado com o corte;
5. Caso você coloque o Out stream field com o nome de um campo que já existe, o Kettle criará um novo campo seguido de _1.    Ex: se voce colocar o nome do Out stream field como CampoSubstring e este campo já existir, o Kettle mantém o campo original com o mesmo nome e valor, além de criar o campo CampoSubstring_1, que receberá o campo criado neste step.

 

 

Por fim, fazendo um simples preview, teremos o seguinte resultado:

 

 

 

Além do step Strings Cut, poderíamos utilizar o step Formula ou até o Modified Java Script Value para obter o mesmo resultado, porém é aconselhado evitar o uso de scripts quando desejamos melhorar a performance de execução do ETL.

 

Group by e Funções de Agregação

Até hoje não explicamos como aplicar a cláusula Group by no Kettle.   Para quem já tem uma ideia de como aplicá-la em um Select, não haverá problemas para identificar a melhor forma de preencher os atributos do step que já leva o mesmo nome da cláusula.

Para encontrar o step Group by, basta acessar a guia "Statistics".    

A única premissa para a utilização deste step, é a de que os campos agrupados precisam estar ordenados antes de chegar ao step.    Adotando uma prática simples, o ideal é que sempre utilizemos um step Sort Rows imediatamente antes do Group by, para garantir que os dados chegarão devidamente ordenados ao step de agrupamento.

Observe pela figura acima que estamos selecionando todos os alunos matriculados durante o ano letivo de 2012 em uma determinada instituição de ensino, buscando seus respectivos cursos, matérias cursadas e notas recebidas em cada matéria.

Com um cenário deste, podemos imaginar uma série de exemplos de agrupamento.    Podemos pegar o aluno de maior ou menor nota, a média das notas de cada aluno durante todo o ano, a média de notas em determinado curso, em determinada matéria, entre outros.

Mostraremos agora um pequeno exemplo de utilização do step Group by, com o intuito de obter apenas a média de notas de cada aluno em todas as matérias cursadas por ele durante todo o ano.

Ex:

Luciano Peixoto - Cálculo A - 6,5

Luciano Peixoto - Lógica Computacional - 8,0

Luciano Peixoto - Gerencia de Projetos - 7,0

Luciano Peixoto - Engenharia de Software - 6,0

O resultado deste agrupamento seria:

Luciano Peixoto - 6,875

Para isso, teríamos que primeiramente ordenar os registros de nosso fluxo pelo nome dos alunos, utilizando por exemplo, o Sort Rows.

Depois disso, definimos apenas as características de nosso agrupamento no step Group by, conforme demonstra a figura abaixo:

Apenas de termos escolhido realizar o cálculo da média de notas de cada aluno, poderíamos de feito a soma destes valores, contado o número total de valores ou o número de valores distintos, escolhido o maior ou menor valor de nota para cada aluno, concatenado todos os valores divididos por vírgulas, entre outras opções.

É importante lembrarmos que apenas os campos selecionados como agrupadores e os campos criados neste step continuarão no fluxo.   Todos os outros campos não passarão adiante, visto que, caso continuassem, o agrupamento não faria o menor sentido ou causaria uma grande confusão na visualização dos dados.

Além disso, todos os valores numéricos sairão deste step com formatação 0.0.   Isso significa que, primeiramente, o resultado de nosso exemplo ao invés de aparecer como 6.875, seria arredondado para 6.9.    Utilizando o step Select Values logo em seguida e convertendo o formato deste campo (mediaNotas) para Number com formato 0.00 ou 0.000, ele converteria o número para o seu resultado correto.

Executando Scripts SQL e Stored Procedures

Quando tratamos sobre o step Table Input, vimos como inserir dados no fluxo provenientes do banco de dados.    Também abordamos outras possíveis formas de utilização deste step durante o fluxo, ao invés de utilizá-lo apenas para dar início ao processo.

Independente da posição em que este step encontra-se no fluxo de dados, sabemos que seu comportamento é o de inserir dados no fluxo, ou seja, independente do script que é executado nele, seu final deve conter uma cláusula SELECT, a fim de que os dados sejam extraídos do banco de dados direto para o fluxo que se inicia (ou continua).

Por exemplo, caso utilizemos este step apenas para executar um comando DELETE ou INSERT, o Kettle gerará um erro e interromperá a execução.   Isto acontece porque uma inserção de dados no fluxo sempre será esperada. O que é possível fazer como alternativa, em casos específicos, é executarmos um comando DELETE ou INSERT sucedido por um SELECT.

Considerando qualquer uma destas hipóteses, ainda podemos considerar que há uma forma mais interessante e correta de executarmos apenas comandos SQL que não necessariamente venham selecionar dados e inseri-los ao fluxo.   Para isso, existe o "Execute SQL Script", disponível na guia "Scripting".

Com este step, será possível executar um ou mais comandos SQL, assim como capturar a quantidade de registros afetados por cada comando.   

De acordo com a imagem acima, podemos ver diversas opções semelhantes àquelas disponíveis em outros steps.

O print destaca três propriedades do step em questão.    A primeira, indica que podemos definir se o script será executado uma única vez, ou será executado em cada linha que passar por este step.

A segunda define possíveis campos que podem ser utilizados como argumentos no meio do código, representados pelo ponto de interrogação (?). A terceira mostra como definir e dar nomes aos campos que retornarão a quantidade de registros afetados por cada comando (SELECT, INSERT, UPDATE e DELETE) no script.

A figura abaixo mostra um exemplo comum de utilização do "Execute SQL Script".   Este step, por não produzir uma saída de dados (apenas executa um script), não deve ser colocado no início do fluxo.    O fato de colocarmos um SELECT no script não significa que os campos do select serão inseridos no fluxo.   Reforçamos que este step não possui este comportamento.

Quanto ao uso de stored procedures, podemos utilizá-la em qualquer step que permita a construção de uma query, mas da mesma maneira que possuímos steps específicos e mais recomendados para extração de dados do BD para o fluxo (Table Input) e steps para simples execução de scripts (Execute SQL Scripts), também temos um step específico para isso.

Este step é o "Call DB Procedure".

Este step também pode ser inserido em qualquer ponto do fluxo, porém não recomendo que seja no início.    Observe que é muito simples configurar o step para a execução da procedure, principalmente para quem já desenvolveu ou executou uma anteriormente.

O usuário deverá definir pelo menos a conexão, o nome da procedure, além dos parâmetros da mesma, caso seja necessário.

Além disso, caso esta procedure retorne algum resultado, semelhante a uma FUNCTION no SQL, você poderá definir o nome do campo que conterá este resultado, assim como seu tipo.

Da mesma forma, caso queira executar uma stored procedure que retorne diversas linhas e colunas como resultado, ou seja, quando o código da procedure termina com um SELECT, e você entenda que é importante inserir estas informações no fluxo, você deverá utilizar o Table Input.     Se a procedure terminar com qualquer comando que não seja um SELECT, e você utilizar o Table Input para executá-la, o Kettle gerará um erro, visto o comportamento deste step, o qual já apresentamos no início deste post.

Para quem já executou procedures utilizando script, não há qualquer diferença ao fazê-lo no Table Input.    Além disso, caso queira utilizar variáveis ${VAR} ou parâmetros (?), basta seguir o mesmo conceito apresentado durante o post sobre o Table Input.

Como alterar data types

O Kettle permite que alteremos o tipo de cada dado com extrema facilidade.     

Primeiramente, é importante que tenhamos o controle do formato e do tipo de cada dado que entra no fluxo, já que, quando extraímos dados de diferentes bancos de dados, os formatos nem sempre são exatamente iguais para uma data, número ou até mesmo um texto.    Desta forma, ao invés de tentarmos realizar uma possível conversão e padronização para cada input, podemos simplesmente utilizar o step "Select Values" para converter todos os campos desejados.

Uma forma de visualizar os dados e seus tipos em qualquer parte do fluxo é clicando com o botão direito em qualquer step e visualizando os dados de entrada e/ou de saída, através das opções "Mostra campos de entrada" e "Mostra campos de saída", conforme vemos na figura abaixo:

Ao clicar na opção para exibir os campos de saída do step, veremos os campos (e não os valores) e seus tipos, da forma como entrarão no próximo step, ou seja, incluindo qualquer modificação que tenha sido realizada dentro do step.    Além disso, esta opção também é interessante porque mostra qual é o step de origem de cada dado, permitindo o acesso direto ao step de origem.

Como estamos ilustrando nosso tema com o step Select Values, utilizaremos o mesmo para alterar agora os data types que nos for conveniente.

Sendo assim, podemos acessar a aba Meta-Data e incluir os campos que desejamos alterar.   Observe que, mesmo havendo oito campos no fluxo, só queremos editar o data type de três deles, por isso inserimos apenas os três campos desejados.     Observe também que, conforme a figura acima, os dois campos numéricos já eram numéricos anteriormente, assim como o campo "data_venda" já era do tipo Date.    O que estamos fazendo aqui é apenas dando uma formatação específica para estes campos.    Embora tenhamos utilizado esta maneira de exemplificar a conversão, é importante saber que qualquer conversão possível em um banco de dados, por exemplo, também será possível realizar no Kettle.    Conversões que geralmente são proibidas, provavelmente também serão proibidas, gerarão alguma mensagem de erro ou um resultado inesperado no Kettle.   Resumindo, não há surpresas nem milagres.

Coloco aqui apenas um cuidado, que é o fato de utilizarmos o Select Values também para a exclusão de campos do fluxo ou para renomeá-los quando necessário.    Quando quisermos alterar o data type, aplicar uma formatação e também renomear o campo, podemos fazer tudo na aba "Meta-Data", ou seja, não precisamos usar a aba "Select e Alter" para renomear e a "Meta-Data" para converter.

Por fim, para garantirmos que alcançamos o resultado desejado, é bom visualizarmos novamente os dados de saída deste step ou os dados de entrada do próximo para percebermos as diferenças.   Aconselho também a realização de um preview nos dados, para evitarmos erros ou surpresas desagradáveis em partes posteriores do fluxo.

Exibimos neste post uma das possibilidades na conversão dos dados, sem a necessidade de scripts (javascript), além de salientarmos mais uma vez a importância de observamos criteriosamente os dados que trafegam durante a transformation, à medida que desenvolvemos a mesma.

Como buscar informações do Banco de Dados

Em nosso último post, explicamos alguns conceitos importantes sobre o início do desenvolvimento de um processo de ETL.

Após extrairmos as informações disponíveis de uma determinada fonte, pode ser necessário procurarmos informações complementares em uma ou mais tabelas no banco de dados.    Isso pode acontecer, por exemplo, quando temos ao nosso dispor o código de um produto e precisamos buscar outras informações em tabelas do BD, tais como o nome do produto, a quantidade de produtos no estoque, a quantidade de produtos vendidos, o preço do produto, etc.

Caso tenhamos no Kettle um componente onde podemos comparar um campo do fluxo com um campo de uma tabela (chave) e com isso recuperar outras informações deste registro, supriremos nossa necessidade com certa facilidade.

É com este objetivo que explicamos em posts anteriores o funcionamento do step "Database Lookup".   Através deste e outros steps, podemos recuperar informações do banco de dados a partir das informações que temos à nossa disposição.

Observe pela figura acima que modelamos um processo de ETL extraindo informações sobre a venda de produtos de uma tabela do Excel, com a finalidade de gravar estas e outras informações no banco de dados.    Na metade de nosso processo, temos um Database Lookup com a seguinte configuração:

Observe novamente que iremos extrair informações da tabela de produtos.   Como a única informação que temos na planilha excel é o ID do produto, cujo nome do campo é "id_produto", iremos comparar este campo com o campo "COD_PRODUTO" da tabela de produtos, para recuperarmos, conforme mostra a parte inferior do print, as colunas "nome_produto", "preco" e "quantidade" que estão gravados nesta tabela.

A parte final deste processo mostra os dados sendo inseridos em uma tabela de venda de produtos.

O objetivo deste post não é mostrar os detalhes deste step, mas sim o seu comportamento principal, visto que já existe um post exclusivo sobre o Database Lookup.   
Através destas instruções, você poderá recuperar a partir de agora, informações importantes gravadas em tabelas do banco de dados.

Os steps "Stream Lookup" e "Database Join" também podem nos ajudar neste objetivo, porém com uma abordagem diferente.

Por último, aconselho uma "releitura" do post sobre o Database Lookup, para que possam relembrar os detalhes importantes sobre este step.

Como começar uma transformation

Para aqueles que estão iniciando o desenvolvimento de uma transformation, pode existir a dúvida de como iniciar o processo, ou seja, de quais steps utilizar para dar início ao novo processo.

Resumindo de forma bem simples, destacamos que uma transformation, salvo raras exceções que serão mostradas adiante, sempre devem começar com um step de Input.     São os steps de Input que são responsáveis por extrair as informações que serão inseridas e trabalhadas no fluxo e automaticamente criar o stream de dados que passarão por todos os steps de nossa transformation.

Desta forma, podemos entender que, caso tentemos dar o start em uma transformation sem que haja um step para inserção dos dados no fluxo, o kettle reproduzirá um erro ou simplesmente não fará nada.

Seguindo esta linha de pensamento, concluímos também que, independentemente da posição dos steps de Input no fluxo, um novo fluxo de dados será iniciado em cada um dos steps de Input, ou seja, se tivermos 3 steps de Input, teremos nossa transformation iniciando em 3 pontos diferentes que, posteriormente, podem ou não convergir em um único fluxo através da utilização de um step de Join, por exemplo.

Observe pela figura acima, que nossa transformation de exemplo possui um Excel Input, um Table Input e um Text File Input.   Com este cenário, teremos nossa transformation sendo iniciada em três pontos diferentes, porém o step  Join Rows é responsável por unir dois destes fluxo, enquanto o step Append Stream (Union) converge o terceiro.

Se por algum motivo você utilizar um step de Input no meio de um fluxo, como por exemplo executar um script SQL utilizando um Table Input ou adicionar um Text File Input no meio do caminho, conforme demonstra a figura abaixo, teremos a transformations sendo iniciada em todos os inputs, o que poderá nos levar a um resultado indesejado, caso este comportamento não esteja previsto ou controlado.

O desenho acima não faz muito sentido, visto que teríamos dados saindo do step de Texto e chegando ao final antes mesmo de muitos dados terem saído da planilha Excel.    Sinceramente, não conheço nenhuma aplicação prática para isso, o que constitui uma ação inconsistente.

O que poderíamos ter, conforme já mostramos em nosso post sobre o Table Input, é o recebimento de parâmetros de entrada que serão utilizados nestes steps de input, como um dado que será utilizado dentro do script, uma variável ou até mesmo o nome do arquivo de onde serão extraídas as informações que também pode ser recebido como parâmetro de um step anterior.

Exemplo com o Excel Input recebendo o nome do arquivo como parâmetro do step anterior

Table Input recebendo um valor que será utilizado dentro do script como parâmetro do step anterior.

Estes dois exemplos acima (Excel e Table Input) mostram como utilizar um step de input no meio do fluxo sem que necessariamente o fluxo inicie duas vezes em dois pontos diferentes, pois para que o step de input insira os dados extraídos via SQL, ele precisará receber uma informação do step imediatamente anterior.

Aprendemos, portanto, o comportamento correto da transformation no caso dos steps de Input, deixando claro que o início de uma transformation e a ordem do fluxo depende da posição e de quantos steps de Input existem em nosso processo.

A única forma de iniciar uma transformation sem utilizar os steps de input é utilizando os steps da guia "Job", através dos steps  "Get rows from result", "Get Variables", etc, cujos comportamentos já foram explicados em posts anteriores.

Alinhando os Steps

Este post tem o objetivo apenas de dar uma dica simples sobre como melhorar a aparência de uma transformation ou de um job.

É comum, em diversas situações, após a criação e posicionamento dos steps no workspace gráfico do Spoon, visualizarmos nosso trabalho da seguinte forma:

Para alinharmos os steps, a fim de deixar a visualização um pouco mais agradável e até mesmo intuitiva, siga os seguintes passos.


1- Com um clique-e-arraste do mouse, selecione todos os steps que estão na horizontal, conforme a figura abaixo.

Quando os steps estiverem selecionados, eles estarão com a borda destacada em preto, como mostrado nesta primeira figura.


2- Para alinhar os steps na mesma linha horizontal, você deve pressionar CTRL+baixo para alinhar os steps no nível do step mais abaixo desta linha horizontal, ou CTRL+cima para alinhar os steps no nível do step mais acima desta linha.


Observe na figura abaixo o resultado de um CTRL+baixo

Agora que os steps já estão alinhados horizontalmente, ajustaremos por fim os steps que estão (ou deveriam estar) na mesma linha na vertical.


3- Para isso, selecione com um clique-e-arraste os três steps que estão na vertical e pressione CTRL+direita (conforme figura abaixo) ou CTRL+esquerda.

Aprendemos, desta forma, a organizar e melhorar o alinhamento dos steps, permitindo uma visualização mais agradável, além de não precisarmos mais "apanhar" do mouse para alinhar dois ou mais steps.
Apesar de utilizarmos uma transformation neste exemplo, o mesmo conceito aplica-se na organização de componentes dos jobs (job entry).