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.