Ordenando os registros com o Sort Rows

Hoje iremos explicar sobre o componente de ordenação do Kettle.

O Sort Rows, além de sua utilidade natural, também é indispensável quando desejarmos utilizar componentes de junção (JOIN), agrupamento (GROUP BY) ou de eliminação de duplicidade (UNIQUE).

Isso acontece pela maneira que os componentes "entendem" os registros que passam pelo fluxo.

Se utilizarmos como exemplo o componente de group by, podemos dizer que primeiro precisamos organizar as linhas e juntar todas as que devem ser agrupadas, e aí sim utilizar a função de agregação (count, max, min, avg, etc).

Voltando a tratar especificamente sobre o step "Sort Rows", vamos ao preenchimento dos campos:



Como o Kettle precisa ordenar as linhas utilizando arquivos temporários quando o número de linhas excede ao tamanho especificado, cujo padrão é de 5000, existem alguns campos que podem ser preenchidos quando a situação o exigir.

Sort directory: diretório onde serão gravados os arquivos temporários caso necessário.   O padrão é o diretório do sistema;

TMP-file prefix:  prefixo que será utilizado para reconhecer os arquivos temporários quando estes forem exibidos no diretório de arquivos temporários;

Sort Size: número de linhas que serão gravadas na memória.   Quanto mais, melhor;

Free memory threshold (in %):  Se o algoritmo de ordenação achar que existe menos memória livre disponível que o indicado, começará a paginar os dados no disco;

Compress TMP Files:   compacta os arquivos temporários quando estes forem necessários para completar a ordenação.

Apesar de úteis, os itens acima são pouco utilizados e normalmente não os alteramos.   Os itens que precisamos nos concentrar são os seguintes:

Only pass unique rows:   linhas duplicadas serão retiradas do fluxo após a ordenação;

Fieldname:  campo(s) que será(ão) ordenados;

Ascending:   ordenação de forma ascendente ou descendente;

Case sensitive compare:   se será case sensitive (maiúsculas e minúsculas);

Get Fields:  coloca na lista de fieldnames todos os campos que estão passando pelo fluxo.

Lembrando que o Sort Rows, semelhante ao comando order by do SQL, poderá ordenar campos do tipo integer, string, date, entre outros.   Quando desejar utilizar os componente de junção, agregação ou de eliminação de duplicidade, certifique-se sempre de que os dados que estão passando pelo fluxo estão corretamente ordenados, seja pelo step Sort Rows ou pela própria query que originou os dados.

Movimentação de Dados

Quando estamos trabalhando com os dados em nosso fluxo, é comum termos dois ou mais destinos para um determinado step.    

Um exemplo mais óbvio disso é quando queremos que os dados sejam gravados em uma tabela e também em uma planilha do Excel.   Ou quando queremos garantir que metade das linhas sejam gravadas em um destino, enquanto a outra metade seguirá por outro caminho.  A mesma divisão pode ser feita em três, quatro ou diversos steps.

No primeiro exemplo, precisaremos copiar os dados para os dois destinos.    No segundo exemplo, precisaremos distribuir (dividir) os dados.

E como faremos isto?

Esta é mais uma dica simples, para facilitar a vida daqueles que estão começando a trabalhar com a ferramenta.

Para definir como será o destino dos dados de um determinado step, podemos clicar neste step com o botão direito e selecionar a opção "Movimento de dados", conforme mostra a figura abaixo:

Quando escolhermos a opção de "Distribuir os dados para os próximos steps", caso tenhamos 100 linhas em nosso fluxo e dois steps de destino, teremos 50 linhas seguindo para um destino e 50 para outro.   Se tivermos 3 destinos, teremos 33, 33 e 34, e assim por diante.

Importante destacar que a divisão NÃO é feita de forma ordenada, ou seja, o primeiro destino NÃO receberá as primeiras 50 linhas (registros) se dividirmos o fluxo em dois destinos.    Caso desejemos dividir desta forma, precisaremos utilizar outros componentes para este controle, como o Filter Rows ou Switch/Case, por exemplo.

Se escolhermos a opção "Copia dados para os próximos steps", utilizando o exemplo das 100 linhas (registros), todos os 100 registros serão copiados para TODOS os destinos, independente de quantos sejam.

A figura abaixo mostra como podemos identificar o movimento de dados de um step através do hop.   No modo de distribuição, o hop mostrará a penas uma seta.   No modo de cópia, um iconezinho de cópia ficará desenhado sobre o hop.

Quando estivermos modelando nosso fluxo e apontarmos dois hops saindo do mesmo step para dois destinos diferentes, o próprio Kettle já solicitará esta informação, ou seja, perguntará qual será o tipo de movimentação de dados, indicando que o movimento padrão é o de cópia.

Com estas informações, poderemos trabalhar melhor com fluxos mais complexos, principalmente aqueles que possuem condições de tráfego, vários destinos ou um tratamento mais sofisticado.    Lembre-se que o componente Filter Rows (objeto de nosso último post), que divide os dados de acordo com uma condição definida (IF-ELSE), obviamente deve distribuir os dados.

Filter Rows

Veremos hoje como utilizar o componente Filter Rows.  Para deixar claro a sua utilidade, poderíamos compará-lo ao comando IF-THEN-ELSE, já que através de uma comparação simples, o step direcionará o registro entre duas opções de destino.

Para começar, arraste o step Filter Rows da pasta Flow (no menu à esquerda) para a área de trabalho do Kettle, na parte do fluxo que desejar.

Observe pelo exemplo abaixo que estamos utilizando este step para filtrar os registros e, dependendo do resultado, o fluxo descartará as linhas indesejadas.

Neste cenário, ainda não definimos a comparação que será feita.

Vamos definir então as condições de continuidade do fluxo.    Usaremos como exemplo a coluna qtd, que significa a quantidade de vendas de um determinado funcionário.   Se a quantidade de vendas de um produto for maior do que 5, os registros continuarão no fluxo, do contrário, irão para o step Descarta.

Perceba que agora os hops (setas de indicação de fluxo) mudaram da cor preta para as cores verde e vermelha.   O hop de cor verde indica a condição true, enquanto o hop de cor vermelha indica a condição false.

Se quisermos incrementar nossa comparação, comparando outros campos (literalmente um if-then-else), basta clicar no botão Add Condition  

Com este botão, você terá acesso aos operadores AND, OR, OR NOT, AND NOT e XOR.

Assim, podemos mudar nossa condição do fluxo, para linhas que tiverem a qtd > 5 AND id_funcionario <= 300.    Esta condição indica que manteremos os registros entre os funcionários com id <= 300 e os produtos que tiveram mais de 5 vendas.    Apenas se as duas condições forem satisfeitas é que o registro continuará pelo fluxo no caminho indicado pela condição true.

Perceba que este componente pode ser considerado também como a cláusula WHERE do SQL.   Esta percepção é importante, pois notaremos que há varios componentes que representarão as cláusulas SELECT, DISTINCT, TOP, GROUP BY, ORDER BY, etc, permitindo assim que manipulemos os dados sem precisar de componentes de script, que piorariam a performance de nosso ETL.

Além deste componente, existe o Switch/Case, que será útil quando precisarmos de mais saídas além do true ou false.   A utilização deste step, que também é simples, será vista mais adiante.

Página de dicas rápidas

Inauguramos hoje a página Dicas Rápidas, que contará com algumas dicas sobre as funcionalidades, utilização da ferramenta e organização dos steps, de forma a ajudá-lo(a) a criar seus processos e ETLs de forma mais simples, dinâmica e organizada.

Logo irei organizar os posts sobre o Table Input, Table Output e Database Lookup em uma página apenas sobre componentes/steps, de forma a facilitar a busca por assuntos dentro do blog, já que continuaremos a explicar sobre os diversos steps disponíveis para transformations e jobs.

Table Output

Como já aprendemos anteriormente a utilizar o componente Table Input, veremos agora como utilizar o Table Output.

Primeiramente deixaremos claro alguns conceitos básicos.

• Podemos ter vários steps Table Input na mesma transformation, ou seja, podemos extrair dados de várias fontes distintas;
• Eles não precisam necessariamente apontar para o mesmo banco ou para o mesmo SGBD.  Isso ficará a critério do número e do tipo de conexões criadas;
• Também podemos ter vários componentes Table Output na mesma transformation, ou seja, podemos ter vários "destinos" para os dados que passaram pelo fluxo;
• O Table Output terá o comportamento de um comando Insert.   Caso os dados já existam na tabela, ainda assim as linhas serão inseridas.  Caso haja problema com a integridade, por exemplo, a transformation apontará um erro e provavelmente será interrompida, a não ser que haja um tratamento para este erro.

E se eu quisesse verificar a existência de cada linha, atualizando os dados quando o registro existir e inserindo apenas quando este não for encontrado na tabela?

Esta pode ser uma dúvida muito interessante e uma situação comum em muitos casos.   Para isso, temos o componente Insert/Update.   Este componente responde exatamente à pergunta acima, porém será explicado posteriormente.

Observe na figura abaixo as opções do componente:

- Nome do Step:   este nome deve ser único em toda a transformation;

- Connection:  conexão com o banco que será usado para este step. O botão Edit abrirá uma janela de edição da conexão escolhida.   O botão New abrirá uma janela de criação para uma nova conexão;
- Target table: nome da tabela na qual os dados serão gravados;

- Commit size: bloco de linhas que serão inseridas por vez.  Ex: se o número for 1000, apenas quando as primeiras mil linhas chegarem ao step é que será feito o Insert na tabela;

- Truncate table:  quando esta opção estiver habilitada, um comando truncate será enviado para a tabela antes que a primeira linha do fluxo chegue ao step;

- Ignore insert errors:  o wiki do pentaho indica que com esta opção os erros de insert serão ignorados, como por exemplo, violações de chave primária.   No entanto, esta opção não estará disponíveis para insert em batch (lote);

- Use batch update for inserts: habilita a opção de inserts em lote.   Esta opção vem habilitada por padrão e torna a inserção mais veloz;

- Partition data over tables: permite que os dados sejam gravados em múltiplas tabelas.  A divisão dos dados provenientes do fluxo é definida através dos campos "Partitioning field", "Partition data per month", "Partition data per day";

- Is the name of the table defined in a field: define se o nome da tabela onde os dados serão gravados virá através de um campo do fluxo; 

- Return auto-generated key:  retorna a chave (ID) da linha que foi inserida na tabela, como um identity/sequence;

- Name of auto-generated key field: especifica o nome do campo que receberá esta chave que foi gerada na linha inserida na tabela, conforme explicado no item anterior;

- Botão SQL: gera o código (e só executa quando você desejar) que criará ou alterará a tabela de destino dos dados, como por exemplo, adicionando índices, acrescentando e removendo colunas desnecessárias, etc.

Como padrão, todos os campos que estiverem entrando no step serão inseridos na tabela definida no Table Output, por isso, precisamos filtrar os campos desnecessários no fim do fluxo, ou certificar-se de que todos os campos que estão entrando no step tenham uma coluna correspondente com o mesmo nome na tabela de destino.

A alternativa mais viável é fazer um mapeamento dos campos que entram no step com as colunas existentes na tabela.  Para isso, observe a guia Database fields, mostrada na figura abaixo.

 

  

A coluna Table field define a coluna da tabela que receberá o campo explicitado na coluna Stream field.

O botão Enter field mapping exibe uma janela para que o usuário mapeie os campos mais rapidamente.

Esperamos que com estas informações todos possam trabalhar tranquilamente extraindo e inserindo dados em tabelas, o que constitue parte fundamental em um processo de ETL.

Populando fatos e dimensões no PDI (Kettle)

Dando continuidade ao uso de componentes que podem nos ajudar na etapa de estagiamento, que é uma das etapas na construção de um DW, vamos mostrar os componentes que poderiam ser úteis para popularmos as tabelas dimensão e a tabela fato.

Em uma tabela dim_cliente ou dim_produto, por exemplo, não existe mistério.   As tabelas dimensão são responsáveis por guardar as descrições, ou seja, nada de métricas por aqui.    O importante, é entender que além das descrições, guardaremos o código dos clientes e dos produtos na base relacional (base utilizada pelo sistema, pelas tabelas Cliente e Produtos), e além disso, teremos um NOVO código para cada um desses valores.

Aconselho iniciarmos o ID da tabela dim_cliente, por exemplo, com um número bem acima de 1.   Dessa forma, evitaremos facilmente coincidir os códigos da tabela relacional e da dimensão.

Poderíamos modelar nossa tabela dim_cliente com as colunas:

• ID_CLIENTE (codigo do cliente para esta tabela, preferencialmente começando por um número alto)
• NM_CLIENTE (campo com a descrição, ou seja, o nome do cliente)
• CD_CLIENTE_RELACIONAL (codigo do cliente na base relacional)

Mas para que usaremos esta coluna (cd_cliente_relacional) no nosso DW?   Como o foco deste post não é explicar os conceitos de modelagem de um DW, até porque o material existente sobre o tema é extenso, basta-nos dizer que, ao popularmos nossa tabela fato com os eventos, como as vendas de um produto para os clientes de nossa loja, por exemplo, extrairemos os dados da base relacional e verificaremos quais os novos codigos de cada cliente fazendo a comparação pelo código da base relacional, que também está gravado na dimensão (do contrário teríamos que fazer esta comparação pelo nome, o que não é aconselhável).

A tabela Fato, que pode ser uma fato_vendas, por exemplo, teria as colunas:

• ID
• ID_CLIENTE (cliente que comprou)
• ID_TEMPO (código indicando a data da compra)
• QUANTIDADE (quantidade de produtos que o cliente comprou neste período)

E como poderíamos ter um código para o tempo?  Podemos modelar nossa dimensão tempo da seguinte forma:

• ID_TEMPO (id da tabela)
• DATA (data completa, ex: 01/01/2011)
• ANO (inteiro indicando o número da data)
• MES (idem)
• DIA (idem)

Poderíamos ter as colunas hora, minuto e até segundo, se a necessidade de agruparmos os dados na tabela fato fosse diferente, mas em nosso exemplo, utilizaremos o dia como menor valor.

O  figura abaixo ilustra um ETL que popula a tabela dim_tempo instantaneamente, sendo necessário apenas indicar uma data inicial, o critério para soma (dias, horas, minutos, etc) e o número a somar (100 dias, 100 horas, 100 minutos, etc).   Apenas fornecendo estas informações, teremos nossa tabela dim_tempo populada em segundos, considerando até milhares de anos.

Para fazer o download do arquivo KTR, para manipulá-lo em sua própria máquina, clique aqui.

O componente Gera Linhas é um Generate Rows, utilizado apenas para definir quantas linhas vamos colocar em nossa tabela dim_tempo.    Neste caso, colocaremos 1000 linhas, o que significa que criaremos uma linha para cada dia, começando do dia 01/01/2011.

O componente Adiciona Dias é um Add Sequence, utilizado para gerar uma sequencia numérica e adicioná-la ao fluxo, ou seja, cada linha receberá um número, começando de 1 e incrementado em 1, conforme definido dentro do componente.

O componente Get Data Inicial recebe uma data inicial, o número que será somado à data e como ela será incrementada (em dias, anos, meses, etc).   Em nosso exemplo está definido que será em dias.

O componente Cria ID é um outro Add Sequence, que desta vez gerará um ID para nossa dim_tempo.

O Componente JS Quebra Tempo é um componente para scripts em javascript.    Neste caso estamos utilizando-o para "particionar" a data, separando o dia, mês e ano que serão inseridos de forma numérica em nossa dim_tempo.

Por último temos um Table Output renomeado para Popula Dim_Tempo, que receberá as linhas provenientes do fluxo para gravar na tabela dim_tempo.

Para entender melhor o funcionamento de cada componente, aconselho a fazer o download do mesmo através do link acima.

Chegamos finalmente ao objetivo principal de nosso post.    

Considerando que temos em nossa base relacional uma tabela que relacione cada venda efetuada pelo sistema, cadastrando o código do cliente e a data da venda, por exemplo, faremos agora, de forma simples, a carga destes dados e seu devido agrupamento para dentro de nossa tabela fato.

Como a tabela fato registra apenas métricas e códigos, indicando a ligação com as tabelas de dimensão, utilizaremos o componente Database Lookup diversas vezes para procurar o código das tabelas de dimensão que correspondem ao valor de cliente e tempo da base relacional, além do componente Group By, responsável por agrupar a quantidade de vendas por dia, por exemplo, feitas para cada cliente registrado em nosso DW, na tabela dim_cliente.

Veja na figura abaixo como ficou nosso processo de carga da tabela fato_vendas:

Para fazer o download deste ETL, clique aqui.

O exemplo utilizado aqui é extremamente simples e não reflete a realidade, visto que nunca iríamos modelar um DW e criar uma tabela de fato vendas (por exemplo) sem levar em conta os produtos vendidos, as regiões, lojas, fornecedores, etc.

Nosso objetivo, que esperamos ter sido alcançado, é dar uma visão inicial de como utilizar os componentes que nos ajudarão a popular os fatos e as dimensões, abordando principalmente a dimensão tempo, fruto de diversas dúvidas e discussões.

No próximo post voltaremos a explicar de forma detalhada sobre mais alguns componentes do PDI.

Utilizando o Database Lookup

Para definirmos de forma simples o comportamento de um componente de lookup, poderíamos dizer que estes funcionam como uma cláusula SELECT com WHERE.     Basicamente tentamos recuperar um ou mais valores baseados na comparação entre colunas e seus valores.
Ex:  Select NOME, ENDEREÇO From tabela Where ID = (valor numérico)

O componente Database Lookup, como o próprio nome sugere, faz uma busca de valores em uma tabela de um banco de dados.     Para usar este componente, assim como o Table Input, também será necessário ter ao menos uma conexão configurada.

O comportamento do Stream Lookup é parecido, porém a busca de valores é feita dentro do próprio fluxo. de dados.  Esta afirmação sugere que o componente receba duas fontes de dados diferentes.    No Database Lookup isso não é necessário, pois uma das fontes de dados é a própria tabela.

O Database Lookup é extremamente útil em diversas ocasiões.   Entre elas, podemos destacar principalmente a fase de estagiamento de um DW.
Utilizamos como exemplo o preenchimento de uma tabela FatoVendas, onde precisaremos:

• Buscar o código dos clientes na base relacional
• Compará-lo com a tabela de Dimensão de Clientes
• Buscar o seu novo código correspondente na Dimensão e, então, 
• Preencher a linha da tabela fato com o código correspondente, ou seja, o cliente que participou de uma determinada venda.  

Para isso, será necessário:

1. Fazer um lookup na tabela Dim_Clientes, passando o código do cliente que veio da base relacional e comparando-o com um campo cd_cliente_relacional da tabela Dim_Cliente, que possui a referencia do codigo do cliente na base relacional.
2. Teremos, então, como retorno, o campo ID_CLIENTE, do tipo Integer, que é o ID daquele cliente dentro do DW, conforme mostra a figura abaixo.

Importante lembrar que um valor (cliente, por exemplo), NUNCA deve ter o mesmo código na base relacional e na dimensão de um DW.    Esta prática não é aconselhada.

Os números da figura acima correspondem:

1. Escolher a conexão que será utilizada
2. Escolher a tabela onde serão procurados os valores
3. Puxar todos os valores da tabela e deixá-los em cache.   Ideal nao habilitar quando há muitos valores na tabela, pois carregará muitos dados na memória
4. Campo da tabela a ser comparado
5. Campo do fluxo comparado ao campo da tabela
6. Campo a ser retornado caso a comparação anterior seja verdadeira.   É possível definir o tipo do campo e um valor "Default", caso os valores comparados não existam.

Além do comparador de igualdade (=), temos também <>, <, >, <=, >=, ISNULL, BETWEEN, LIKE e IS NOT NULL).
Como a busca de dados pelo componente Database Lookup nao utiliza o NOLOCK, existe o risco de deadlock quando a busca é pesada, longa e há várias outras operações acontecendo na mesma tabela ao mesmo tempo (Inserts, updates, etc).
Para isso, recomendo um Select feito em um Table Input, utilizando o NOLOCK, e aí então, utilizar o Stream Lookup para buscar os valores desejados.    Dessa forma, o Kettle coloca as linhas em memória, não sendo necessário utilizar e acessar o banco diversas vezes durante o fluxo de dados.
Este exemplo está ilustrado na figura a seguir:

O comportamento do componente Stream Lookup, assim como suas opções e formas de uso serão discutidos no próximo post.

Configurando Conexões - Parte 2

Sempre que trabalhamos em um ambiente de desenvolvimento, costumamos trabalhar com um determinado número de bancos de dados diferentes, ou até mesmo SGBDs diferentes, que normalmente referenciam a alguma(s) aplicação(ões).

Uma outra alternativa que temos, além do uso de variáveis na configuração das conexões, para desenvolvermos um processo de ETL de forma mais rápida e dinâmica, evitando replicar diversas vezes as mesmas conexões a cada transformation ou job criado, é o compartilhamento (share) de conexões.

Para habilitar esta opção, basta clicarmos com o botão direito na conexão desejada, e em seguida selecionar a opção Share, conforme mostra a imagem a seguir:

Toda vez que uma conexão é compartilhada, ela estará indicada em negrito, conforme mostra a figura abaixo:

 

Tendo feito isto, qualquer transformation ou job criado a partir deste momento, terá como padrão todas as conexões compartilhadas anteriormente.

Além disso, caso seja necessário excluir ou alterar todas as conexões compartilhadas em sua máquina, basta acessar o arquivo shared.xml, que encontra-se na pasta .kettle.    Essa pasta é criada dentro da pasta Users/nome_usuario/

Lembrando apenas que o fato de excluir o arquivo shared.xml, elimina o compartilhamento, porém a conexão criada se mantém.

Em outros posts explicaremos outras configurações avançadas sobre as conexões.

Configurando Conexões

O Pentaho Data Integration permite que conexões sejam configuradas com uma infinidade de bancos diferentes.    Não existe segredo ao configurarmos as conexões que serão utilizadas, mas podemos destacar alguns detalhes interessantes:

1. É possível estabelecer conexões com quase 40 bancos diferentes, entre eles os mais conhecidos;
2. É possível criar conexões tanto em transformations quanto em jobs;
3. Conexões utilizando o Windows Authentication precisam de configurações mais avançadas, que serão explicadas em outro post;
4. Os campos definidos para as configurações das conexões podem receber variáveis ao invés dos valores diretamente;
5. Podemos criar diversas conexões de diversos bancos diferentes dentro do mesmo arquivo.

O item 4 pode ser muito útil, quando desenvolvemos processos em um ambiente de desenvolvimento e o mesmo necessita passar por ambientes de homologação até chegar no ambiente de produção.    Utilizando variáveis, os valores da conexão podem ser retirados de um arquivo, como um xml por exemplo, o que facilita muito no momento de alterar as configurações.    Não existe qualquer necessidade de abrirmos a transformation ou o job para alterá-las.

Se você nunca criou uma conexão, basta seguir as instruções a seguir:

Na parte superior esquerda da ferramenta, acesse a opção View e logo após o item Conexões, com um duplo clique ou com o botão direito e acessando a opção Novo.

A próxima imagem demonstra como é a janela que será aberta:

Não existe qualquer complicação quando configuramos uma conexão simples.  Outras opções mais avançadas também podem ser acessadas, conforme o menu na parte esquerda da janela.    Até a possibilidade de clustering é possível com o Pentaho Data Integration.

Caso desejemos substituir os valores por variáveis, as configurações ficarão conforme a imagem a seguir.

Um detalhe muito importante, que não pode ser percebido pela imagem, é que até o próprio campo Password também poderá receber o valor de uma variável, como ${PASSWORD}, por exemplo.

A partir de agora, estaremos aptos a configurar conexões, parte essencial  no desenvolvimento de um  processo de ETL.

O próximo post mostrará como podemos compartilhar conexões, ou seja, deixar uma mesma conexão disponível para qualquer arquivo que for criado dentro de uma máquina.

Table Input

Utilizar o componente (step) Table Input é simples, mas podemos destacar algumas funcionalidades que podem ajudar-nos a utilizar este step como solução em diversas etapas de nosso ETL.

Para adicionar o componente ao processo, basta selecionar a opção "DESIGN" na parte superior esquerda da tela, abrir as opções de steps de "INPUT", clicar no componente TABLE INPUT  e arrastá-lo até a área de trabalho do programa.

Para abrir as opções do step, basta clicar duas vezes no componente, ou clicar com o botão direito e na opção "EDITAR STEP".

A figura abaixo mostra as opções do componente:

Vamos aos itens destacados:

1- Connection:   Conexão que será utilizada para a execução da query.   Explicaremos com mais detalhes como configurar uma conexão em um post posterior.    Se utilizarmos um componente que pretende extrair ou inserir dados em um BD, obviamente precisaremos de, no mínimo, uma conexão configurada.   Havendo apenas uma conexão configurada na Transformation, esta opção virá automaticamente com o nome dela.   Havendo mais de uma, será necessário escolher.

2- SQL: espaço para o código SQL.    Importante lembrar que o componente "TABLE INPUT" espera sempre um retorno de dados, ou seja, caso deseje adicionar um insert, update ou delete no código, criar ou deletar tabelas, não há problema, desde que, ao final da query, haja um select para adicionar os dados do banco ao fluxo.

3- Replace Variables in Script: caso deseje substituir o nome de uma coluna, um valor na clausula WHERE ou qualquer etapa do código por uma variável proveniente de um step, transformation ou job anterior, basta habilitar esta opção.   A variável inserida no código deve estar escrita como ${VARIAVEL}.     Ex: select * from TABLE where id = ${ID}.

4- Insert data from step: caso deseje utilizar um valor (e não uma variável) proveniente do step imediatamente anterior dentro da mesma transformation, basta habilitar esta opção escolhendo o step anterior ligado ao Table Input.   Para colocar o valor dentro da query, basta utilizar ponto de interrogação.     Ex:   select * from TABLE where id = ? 

OBS 1:  Se o valor for um string ou uma data, o ponto de interrogação deve seguir o padrão sql normal, ou seja, deve ser colocado entre aspas simples. Ex:  select * from TABLE where name = '?' 

OBS 2: A quantidade de pontos de interrogação deve ser exatamente igual ao número de campos (colunas) provenientes do step anterior.   Se no step anterior estivermos passando 3 campos para o Table Input e a opção descrita no item 4 for ativada, precisaremos ter 3 pontos de interrogação utilizados dentro da query.

5 - Execute for each row:  por ser um componente de input e inserir dados (linhas) dentro do fluxo, este step costuma ser executado no início da transformation e apenas uma vez.   A opção EXECUTE FOR EACH ROW garante que o código será executado por cada linha que passar pelo fluxo.   Esta opção só poderá ser marcada se o item 4 também for.  Desta forma, o Table Input poderá ter steps que o precedem, recebendo os valores (campos e linhas) destes steps.

6- Preview: com o código finalizado, podemos fazer um PREVIEW para garantir que não há erros no código, visualizando um número determinado de linhas.

Outras opções:

Enable lazy conversion: evita conversões de tipos de dados desnecessários e pode resultar numa melhora significante de performance.

Limit size:  número de linhas que serão lidas da(s) tabela(s).   O zero (0), significa que todas as linhas serão lidas.

Kettle ou Pentaho Data Integration?

O Pentaho Data Integration (PDI), também conhecido como Kettle, é uma ferramenta de código aberto, desenvolvida em Java, para Extração, Transformação e Carga (ETL) de dados, ferramenta esta integrante da suíte Pentaho de Business Inteligence (BI).

Todos os processos são criados com uma ferramenta gráfica, que pode ser usada independentemente ou integrada à outras ferramentas do Pentaho.

Dentre as diversas funções, o Pentaho Data Integration, como ferramenta de ETL, pode ser usado principalmente para:

• Extração: coletar dados de diversas fontes. Podem ser arquivos de diferentes formatos ou das mais distintas bases de dados;
• Transformação: mover e modificar dados, limpando, denormalizando, agregando e enriquecendo esses dados durante o processo;
• Carga: armazenar os dados em seu destino final.  Também podem ser arquivos de diversos formatos ou um outro banco de dados.   Normalmente são armazenados em um Data Warehouse.

A ferramenta de ETL que hoje faz parte da suíte do Pentaho, conhecida como Kettle, foi desenvolvida em 2002 por Matt Casters e hoje, integrada ao Pentaho, recebe a contribuição de um número cada vez maior de pessoas, inclusive de seu próprio criador.   A sigla K.E.T.T.L.E, significa "Kettle Extraction, Transport, Transformation and Loading Environment", ou seja, Ambiente Kettle de Extração, Transporte, Transformação e Carga.

O Pentaho Data Integration é constituído de várias aplicações distintas, a saber:

• Spoon: ferramenta gráfica responsável por modelar o fluxo de dados desde sua entrada até sua saída.  Nela é possível criar jobs e transformations;
• Pan: ferramenta de linha de comando que executa as transformações modeladas no ''Spoon'' ;
• Kitchen: ferramenta de linha de comando que executa os jobs modelados no ''Spoon''; 
• Carte: é um servidor web que permite executar transformations e jobs remotamente. 

As chamadas Transformations são responsáveis pelo tráfego dos dados propriamente dito, dados estes que podem ser extraídos de diversas fontes (tabelas de um BD, planilhas, documentos de texto, arquivos csv, arquivos xml, etc), tratados com um número vasto de componentes e inseridos/exportados para a mesma variedade de saídas (as mesmas das fontes de entrada).

Os Jobs podem ser responsáveis por executar um conjunto de transformations e outros jobs internos, assim como manipular e transferir arquivos, enviar e receber emails e executar uma série de validações.

Cada componente de uma transformation ou job, é chamado de Step e é conectado ao próximo componente através de uma linha, chamada Hop.   Os componentes (steps) de uma transformation, por exemplo, podem ter um Hop que liga-o a outro step, que continuará o fluxo de dados, ou a um outro step, que fará o tratamento dos dados caso estes apresentem algum erro ou inconsistência no step anterior.   Desta forma, a ferramenta ainda permite que o fluxo não seja interrompido caso haja dados inconsistentes, permitindo fácil identificação e continuidade nas rotinas desenvolvidas.

Caso um usuário deseje ajudar no desenvolvimento da ferramenta, assim como no desenvolvimento ou melhoria de novos componentes, basta que o mesmo baixe o código fonte da aplicação, disponível no site do Pentaho, e faça as alterações desejadas.

Um grande número de componentes/plugins customizados estão inclusive disponíveis para download.