Excel Input

O armazenamento de dados em planilhas do Excel é muito comum e muito importante em diversas situações.    Através do componente Excel Input, podemos manipular os dados gravados nestas planilhas, aproveitando inclusive o título das colunas, caso existam.

Observe abaixo as opções do componente:

Aba Files:

Começaremos pela primeira aba, a aba Files.

Nesta aba selecionaremos os arquivos que serão processados.   Poderemos escolher um ou mais arquivos diretamente do diretório onde se encontram, ou o nome / caminho do arquivo podem chegar através de um parâmetro ou campo.

Quando os arquivos forem escolhidos, os mesmos aparecerão na coluna File/Directory.    Na coluna Wildcard(RegExp), poderemos colocar uma expressão regular que valide os arquivos que queremos importar.

Se desejarmos receber o nome de um arquivo por meio de um campo do fluxo, marcaremos a opção “Accept filenames from previous step”, selecionaremos o step que está ligado ao Excel Input e definiremos o campo que possui o nome/caminho do(s) arquivo(s).

Após determinar os arquivos e pastas, poderemos ver os que fazem parte de nossa seleção através do botão “Show filename(s)...”

Aba Sheets:

Na aba Sheets, definiremos quais planilhas entre os arquivos selecionados usaremos para extrair os registros e inseri-los ao fluxo.

Aba Contents (Conteúdo):

Na aba Content, definiremos outras opções, entre elas:

• Header: se o cabeçalho das colunas será inserido ao fluxo;
• No empty rows: se linhas vazias entrarão no fluxo;
• Stop on empty row: para o processo caso encontre uma linha vazia;
• Limit: limite de linhas que entrará no fluxo;
• Spread sheet type: formato do arquivo que contém a(s) planilha(s), ou seja, xls, xlsx ou ods.

Aba Error Handling:

A aba Error Handling possui algumas opções para o caso de incidência de erros durante o processamento deste step.   Através das opções disponíveis, podemos decidir qual ação o fluxo deverá tomar caso haja um erro originado especificamente por este step.

Aba Fields:

Na aba Fields, escolheremos entre os campos das planilhas selecionadas aqueles que entrarão no fluxo.   Ao definirmos os campos, poderemos escolher os tipos, tamanho, precisão, formato, etc.

Observe a figura abaixo e perceba que através do botão “Get fields from header row...” podemos adicionar todas as colunas das planilhas automaticamente, sem necessidade de digitá-los um por um.

Text File Input

O step Text File Input é utilizado para ler diversos tipos de arquivos texto.    O formato mais comum entre eles é o Comma Separated Values (CSV).   Caso o arquivo texto tenha qualquer outra extensão, será necessário que o mesmo possua campos de tamanho fixo.

Este componente, assim como o Excel Input, permite que o usuário selecione uma lista de arquivos a serem lidos, ou uma lista de diretórios de onde serão lidos os arquivos, podendo ou não obedecer uma expressão regular.      Além disso, o step também poderá receber diretórios ou nomes de arquivos provenientes de steps anteriores, seja por variável, seja por um campo do fluxo, permitindo desta forma que o manuseio dos arquivos seja ainda mais genérico.

Veremos a seguir as opções específicas, ou seja, o diferencial deste componente em relação aos outros steps de Input.

Filetype – o usuário poderá definir entre um arquivo do tipo CSV ou um arquivo de tamanho fixo;

Separator – caso o arquivo seja do tipo CSV, é provável que você defina o separador dos campos.   Normalmente a vírgula ou o ponto-e-vírgula são mais utilizados;

Enclosure – caso os campos estejam dentro de um par de caracteres (parênteses ou aspas duplas por exemplo), o caractere deve ser definido aqui;

Escape – define o caractere que irá ignorar o caractere definido como separator, caso este seja necessário dentro do texto.

Ex:  Se o separator for uma aspas simples, e uma coluna tiver o texto ‘Gota d´água’, será necessário que o arquivo CSV venha com o texto ‘Gota d\´água’ para que o componente entenda este texto como um campo só ao invés de dois.   Neste caso, a barra serve como Escape;

Header – define se haverá um cabeçalho e quantas linhas correspondem a este cabeçalho;

Footer – segue a mesma regra do Header também para o rodapé;

No empty rows – impede que as linhas vazias sejam enviadas para o fluxo;

Include filename in output – define se o nome do arquivo também será enviado ao fluxo;

Filename field name – define o nome do campo que levará o nome do arquivo pelo fluxo;

Rownum in output – define se o número da linha no arquivo será enviada ao fluxo;

Row number field name – define o nome do campo que levará o número da linha;

Rownum by file? – define se o número das linhas será reiniciado a cada arquivo lido;

Format – formato do arquivo.    Pode ser DOS, UNIX ou mixed;

Encoding – especifica o encoding que será utilizado;

Limit – Especifica o número de linhas que serão lidas por arquivo.  0 (zero) significa a leitura de todas as linhas.

Na aba Fields, veremos mais algumas especificidades do componente.    Caso tenhamos escolhido o tipo de arquivo CSV na aba Content, basta clicar no botão “Obtém Campos” e, automaticamente, os campos identificados de acordo com o cabeçalho (Header) e divididos pelo caractere definido como Separator serão exibidos.

Caso o tipo de arquivo definido na aba Content seja Fixed, ao clicarmos no botão “Obtém Campos”, visualizaremos a seguinte tela:

Ao clicarmos em qualquer ponto da visualização do arquivo, definiremos a quantidade de caracteres e/ou espaços que formarão os campos.   A seta vermelha indica o limite do campo.   Para adicionar uma seta, basta clicar no ponto desejado.   Para retirar a limitação, basta clicar novamente sobre a seta.

Ao seguirmos pelo botão “Next”, definiremos o nome de cada campo que acabamos de definir.   Perceba que na parte direita da janela visualizaremos os dados do intervalo de espaço que definimos anteriormente.

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.