Segundo job: PostgreSQL para Planilha Excel

Objetivos

Neste artigo você aprenderá a:

  1. Definir Metadados e Conexões com bancos de dados PostgreSQL;
  2. Carregar os dados do banco de dados mapeado em uma planilha Excel (XLS).

Introdução

Observações: Caso se tenha dúvidas em relação à criação de um repositório local, de um projeto e/ou de um job, por favor, veja os três primeiros passos do artigo Primeiro job: Arquivo delimitado para MySQL.

Requisito: Antes de começar, você irá precisar restaurar a base de dados  empresa, que será a origem de nossos dados.

IMPORTANTE: O arquivo em anexo encontra-se em formato .doc. Peço que você renomeie a extensão dele para .backup e para restaurá-lo siga os passos abaixo.

Nota: A versão do PostgreSQL utilizada no exemplo é a 8.4 (http://www.postgresql.org/download/)

Via pgAdmin III

  1. Clicar com o botão direito em Banco de Dados;
  2. Selecionar a opção ‘Novo Banco de Dados’;
  3. Definir os dados desse novo banco (nome, dono, codificação, etc.);
  4. Clicar com o botão direito no banco criado;
  5. Selecionar a opção Restaurar;
  6. Clicar no botão  ” … “;
  7. Informar o caminho onde se encontra o arquivo .backup, clicar em  Abrir e logo após em OK.

Via Comando

  1. Digitar o comando: pg_restore -d [NomeBase] /[Caminho do arquivo.bkp]

Nota: Os colchetes são apenas para ilustração.

Definindo Metadados

Nessa seção iremos descrever o mapeamento da origem do nosso Job (PostgreSQL, Base de Dados Empresa) e o mapeamento do destino (Planilha Excel).

PostgreSQL

Na seção Metadata (repositório da ferramenta), clique com o botão direito em Db Connections e selecione a opção Create Connection. No assistente que aparecer defina o nome da conexão e aperte na opção “Next“.

Nesta etapa você definirá o tipo do banco de dados e os detalhes da conexão. Em Db Type selecione PostgreSQL para que as outras opções sejam disponibilizadas e então defina os metadados de sua conexão conforme a imagem apresentada. Qualquer dúvida siga os passo do detalhamento abaixo.

Detalhamento dos parâmetros:

  1. Login: usuário do banco de dados;
  2. Password: senha do banco de dados;
  3. Server: IP do banco (se a conexão for local, pode usar localhost em caso de conexão local);
  4. Port: porta do banco de dados (já vem preenchido por padrão 5432, caso sua conexão seja em outra porta basta alterar);
  5. DataBase: nome da base de dados. Seguindo nosso exemplo o nome será Empresa;
  6. Schema: nome do schema do banco de dados. Esse parâmetro é opcional. Caso você não tenha criado um schema novo para a base de dados deve-se usar o schema public.

Após informar esses parâmetros clique em Check para verificar se a conexão com o banco de dados foi estabelecida. Caso a mensagem “Nome da Conexão connection successful” apareça, clique em OK e logo após Finish.


Nota: Caso uma mensagem de erro apareça ao testar a conexão, verifique se os parâmetros informados estão corretos e tente novamente.

Diante disso, você recuperará as tabelas da base de dados Empresa. Em Db Connections clique com o botão direito na conexão que você criou e selecione a opção Retrieve Schema. Com a  janela Schema aberta, clique em Next. Aparecerá um lista de todas as tabelas que fazem parte da base de dados Empresa. Selecione o botão Select All, logo após  Next e Finish.

Nota: Para verificar se as tabelas foram recuperadas, expanda a seção Table schemas (diretório abaixo da conexão criada no exemplo). Devem aparecer duas tabelas  ( tb_pessoa e td_cargo ).

Planilha Excel

Caso o Job criado para o exemplo não esteja aberto, por favor, abra. Logo após, vá até a Paleta da ferramenta, selecione as opções File -> Output , o componente tFileOutputExcel e o arraste até a área do Design Workspace. Para configurar a sua planilha de destino siga os passos abaixo.

  1. Clique uma vez em cima do icone do seu tFileOutputExcel;
  2. Logo abaixo da área do Design Workspace clique na aba Component e certifique-se que você se encontra na opção Basic settings do componente.
  3. No parâmetro File Name, clique na opção “…” para definir o diretório que você deseja salvar o arquivo e informar o nome dele.
  4. Marque a opção include header, para que na primeira linha de nossa planiha apareçam os nomes dos campos que estamos carregando.
  5. Clique no ícone salvar ou aperte Ctrl S para salvarmos as alterações.


Pronto!!!  Seu arquivo de destino já está configurado.

Nota: O Talend já coloca por default o caminho do workpace como diretório de saída dos arquivos (C:/Talend/TOS-Win32-r53616-V4.1.2/workspace). Nada impede que você altere conforme desejar.

Implementando o Job

Nessa etapa iremos implementar o processo de carga. Para isso o Job criado como exemplo deve estar aberto. Por favor, siga os passos listados abaixo.

  1. Clique, segure e arraste as duas tabelas criadas em Db Connections para a área do Design Workspace. Você pode selecionar vários itens utilizando a tecla Ctrl e arrastar de uma só vez. 
  2. Para cada item arrastado, uma janela irá surgir lhe perguntando que tipo de componente você deseja criar a partir desse item. Selecione para todos a opção tPostgresqlInput.
  3. Para cada tabela do Design Workspace, clique com o botão direito, selecione as opções Row -> Main e arraste a seta até o componente tMap. A ordem das tabelas deve ser seguida, primeiro tb_pessoa e depois td_cargo.
  4. Clique duas vezes no componente tMap e a janela de mapeamento irá aparecer. No lado esquerdo dessa janela ficam as tabelas da origem, o meio representa a transformação que os dados poderão sofrer e no lado direito o destino onde os dados serão armazenados (ETL).  Todas as configurações realizadas no componente tMap serão detalhadas abaixo.
  • Para recuperar as informações obtidas através do relacionamento entre as duas tabelas clique na opção “…” da tabela row2 da coluna idt_cargo e digite “row1.cod_cargo”.


  • Para criar  saída dos dados clique no ícone ” + ” e informar o nome da expressão.


  • Clique nas colunas das tabelas de origem e arraste para a saída criada. Arrate apenas os seguintes campos: mat_pessoa, nme_pessoa, nme_cargo, dsc_cargo e tel_ramal.
  • Após realizado o mapeamento clique na opção Apply e OK.


Após seguir os passos orientados anteriormente clique com o botão direito, selecione as opções Row -> [Nome da expressão de destino utilizada] e arraste a seta até o componente tFileOutputExcel e salve o projeto mais uma vez.

Certifique-se que seu Job esteja similar a ilustração abaixo.

Executando o job

  1. Clique na aba Run e depois no botão Run. Você também pode utilizar a tecla F6.
  2. Vá atéo diretório de destino que você escolheu e abra o arquivo Excel para visualizar os dados.

Conclusão

Neste artigo foi demonstrado como você realiza um processo de ETL. Extraindo os dados de uma base de dados no SGBD PostgreSQL, mostrando o cruzamento dos dados de duas tabelas e gerando um arquivo excel como saida. Dessa forma, esperamos ter mostrado as facilidades da ferramenta Talend. Até a próxima!!!

Anúncios

Primeiro job: Arquivo delimitado para MySQL

Neste artigo você aprenderá a:

  1. Criar um repositório local;
  2. Criar um projeto;
  3. Criar um job;
  4. Definir Metadados e Conexões com bancos de dados;
  5. Carregar os dados de um arquivo de texto em um banco de dados;

Introdução

Requisito: Antes de começar, você irá precisar deste arquivo, que será a origem de nossos dados.

Como já foi demonstrado neste artigo, é possível importar um projeto de demonstração a partir da janela de projetos, porém hoje iremos criar nosso projeto do zero e então o nosso primeiro Job.

Após efetuar o download da ferramenta, não será necessária a instalação, apenas descompacte na raiz do disco (o caminho curto fará diferença). Dentro do diretório do Talend, há diversas opções de binários para diversos sistemas e arquiteturas, e para cada um destes binários há um arquivo .ini, onde são configuradas as opções de memória da JVM.

Para o Windows há três executáveis: x86, x86_64 e WPF. O WPF é um subsistema para renderização de interface gráfica que utiliza DirectX que está para o Windows assim como o GTK está para Linux, porém não recomendo sua utilização pois ele ainda está em fase de portabilização no Eclipse sendo apenas uma herança para o Talend.

Para Linux há também três alternativas: x86, x86_64 e PPC (power pc) e todas utilizam o GTK.

Escolha o binário de acordo com a arquitetura de seu processador para que possamos partir para a criação de nosso projeto.

Nota: Você deve ter notado a forte ligação entre o Talend e o Eclipse, acontece que o Talend é desenvolvido como um plugin para o Eclipse, porém não é possível obtê-lo à parte.

Criando um repositório local

Então iremos criar um primeiro projeto em branco, mas antes disso é necessário criar um repositório onde serão armazenados nossos projetos.

As versões gratuitas permitem apenas a criação de repositórios locais, portanto no campo Workspace defina o diretório onde seus projetos serão armazenados, dê um nome a seu repositório e defina um e-mail válido.

Criando um projeto

Após criado o repositório, na tela de projetos poderemos criar um projeto selecionando a opção “Create a new local project” e “Go!“. Dê um nome a seu projeto, adicione uma descrição ao mesmo caso queira e escolha a linguagem utilizada (java ou perl), em nossos tutoriais utilizaremos a linguagem java. Feito isso clique em “Finish” e depois abra o projeto.

Nota: Ao abrir seu primeiro projeto o Talend provavelmente irá pedir seu e-mail, isto é opcional caso deseje obter newsletters sobre a ferramenta.

Criando um Job

A primeira tela a ser exibida na suite é a de boas vindas, feche-a e na próxima janela você terá à sua esquerda o repositório de seu projeto.

Nota: Uma caixa de diálogo com a informação Generation Engine irá aparecer, é recomendável que você aguarde a conclusão, se quiser clique em Run in background mas não cancele este processo.

Dentro de repositório, clique com o botão direito em Job Designs e selecione a opção Create Job. Um assistente irá surgir onde você poderá atribuir nome, propósito, descrição, versão e status ao novo job.

Ao concluir a criação do job, no centro de sua tela, na seção conhecida como Design Workspace um novo job em branco irá surgir.

Definindo Metadados

Arquivo

Para realizarmos a carga de nosso arquivo de texto em um banco de dados, precisamos primeiramente definir os metadados de nosso arquivo e nosso banco de dados.

Dentro do repositório, expanda a seção Metadata, lá você verá os metadados do projeto separados por tipo.

Primeiro vamos definir os metadados de nosso arquivo, por se tratar de um arquivo de texto delimitado por ponto e vírgula, clique com o botão direito em File Delimited e selecione a opção Create file delimited. No assistente que se abrir, dê um nome para o arquivo e clique em Next. Na próxima janela defina o caminho para o arquivo e sua codificação.

Na próxima etapa poderemos definir detalhes sobre a estrutura de nosso arquivo, como caracteres separadores de campos e linhas, linhas a saltar no início ou fim do arquivo ou mesmo limitar o número de linhas a serem lidas. Para o nosso arquivo, iremos definir um salto de 5 linhas iniciais, pois são comentários no cabeçalho do arquivo. Além disso, iremos marcar a opção Set heading row as column names para que a primeira linha após as 5 primeiras ignoradas seja considerada como nome das colunas.

A próxima etapa é onde definiremos os nomes das colunas, chaves primárias, tamanho, precisão e tipo de dado dos campos de nosso arquivo. Como estamos definindo metadados para um arquivo de texto, o tamanho, precisão ou chave primária não farão diferença, mas é interessante definir, pois poderemos herdar estas informações para componentes de saída.

Nota 1: O assistente varre as primeiras 10 linhas do arquivo para tentar detectar o tamanho dos campos, mas é importante você se certificar destes tamanhos, pois como são apenas as 10 primeiras linhas, você pode obter erros como o seguinte:

Data truncation: Data too long for column ‘RegTime’ at row 54

Observe que o erro ocorreu na linha 54, impossível que o assistente detectasse previamente o tamanho do campo. Este é um erro que você irá obter neste arquivo de exemplo caso deixe os tamanhos conforme o assistente detectar. Portanto configure o tamanho dos campos conforme na imagem anterior.

Nota 2: Para campos do tipo Date, você deve definir um padrão para a data conforme ela está disposta no arquivo, como você pode verificar na imagem anterior. Para mais informações use esta página.

Agora que já definimos os metadados de nosso arquivo, vamos definir os metadados de nossa conexão com o banco de dados.

Banco de Dados

Neste exemplo iremos utilizar o MySQL. Ainda na seção Metadata, clique com o botão direito em Db Connections e selecione a opção Create Connection. No assistente que se abrir defina o nome da conexão e salte para a próxima etapa.

Nesta etapa você definirá o tipo do banco de dados e os detalhes da conexão. Em Db Type selecione MySQL para que as outras opções sejam disponibilizadas e então defina os metadados de sua conexão conforme na imagem.

Clique em Check para verificar se os detalhes estão corretos e caso sim clique em Finish.

Como iremos utilizar esta conexão apenas como saída, não será necessária a definição de metadados de tabelas.

Carregando os dados

Esta etapa é extremamente difícil e é importante que você preste muita atenção.

Passo1: Clique, segure e arraste os dois itens criados em Metadata para a área do Design Workspace. Você pode selecionar vários itens utilizando a tecla Ctrl e arrastar de uma só vez.

Para cada item arrastado, uma janela irá surgir lhe perguntando que tipo de componente você deseja criar a partir desse item.

Para o arquivo delimitado, selecione tFileInputDelimited e para o mysql selecione tMysqlOutput. Observe que cada componente criado nesta área possui um visual diferente.

Passo 2: Agora clique e segure com o botão direito no componente tFileInputDelimited e arraste até o componente tMysqlOutput.

Selecione o componente tMysqlOutput. Veja que na parte inferior da suite você possui algumas visões. Clique na aba Component.

Nesta aba você pode definir várias opções para o componente, mas como estamos herdando as opções do repositório, não será necessária muita configuração.

Vá até o campo Table e dê um nome à tabela destino, por exemplo, “cliente”. Logo abaixo você verá as caixas Action on Table e Action on Data.

Em Action on Table você pode definir se o job irá criar a tabela, se ele irá truncar quaisquer dados existentes ou se ele deverá realizar um drop a cada execução. Selecione a opção Drop if exists and create para que ele exclua a tabela e crie novamente a cada execução.

Nota: A ação “Default” é não fazer nada, ou seja, a tabela deverá existir antes da execução do job.

Em Action on Data você define a ação com os dados, ou seja, se o job deverá realizar exclusões, atualizações ou inserções de dados.

Nota: Há duas opções que parecem redundantes: Insert or Update e Update or Insert, a diferença entre estas opções é performática: caso você saiba que seu job irá executar mais inserts do que updates, você deve selecionar a opção Insert or Update, para que ele primeiro realize os inserts e depois os updates. Caso saiba que ele vai realizar mais updates, então Update or Insert.

Executando o job

Clique na aba Run e depois no botão Run. Você também pode utilizar a tecla F6.

Conclusão

Neste artigo foi demonstrado como o Talend facilita nas tarefas básicas como criação de metadados, possibilitando que você se dedique mais ao que realmente importa.