Fala Guerreiro, preparado para mais uma bateria de estudos? No post de hoje vamos aprender a relacionar dois datasets (pode ser tabelas, arquivos txt, csv e até mesmo planilhas, lembrando que cada fonte utilizada durante esse processo deve conter uma ou mais colunas que se relacionem com os dados dessa outra fonte) como se tivéssemos trabalhando desenvolvendo uma query e realizando uma operação de Join.
Quando usamos o componente Merge Join, somos obrigado a utilizar também o componente Sort para ordenar os dados antes de realizar o relacionamento. Fazendo uma espécie de rebuild nos índices para arrumar a casa, permitindo assim um melhor desempenho.
O Merge Join é semelhante à realização de um Join em um comando T-SQL. Entretanto, quando estamos trabalhando com o SSIS, temos a possibilidade de extrair dados de diferentes locais e tipos de arquivos, devemos manter isso em mente para compreender esse processo. Além disso, a maior parte do trabalho é executado na memória, o que pode aumentar a performance em alguns cenários.
Um outro ponto bem importante a se destacar é que o processo de ORDER BY de um banco de dados trabalha sobre o TempDB, esse banco dependendo do grande volume de dados irá sofrer uma sobrecarga de trabalho, sem falar que demandará também um alto consumo de memória, então analise muito antes de usar, faça um estudo bem detalhado sobre o seu processo.
Esse componente é bastante usado dentro de um fluxo de carga do SSIS. Uma outra possibilidade para desenvolver essa mesma atividade é através do componente Lookup, este componente será abordado em breve.
Antes de avançar para o código, vamos fazer uma breve introdução de como iremos trabalhar nesse post.
1 - Criação de um projeto SSIS - Sempre que trabalhamos com SSIS, existe a necessidade de realizar a criação de um projeto no Visual Studio, caso tenha criado nos posts anteriores, pode utilizar sem problemas, porém caso não tenha trate de criar pois é um item obrigatório! Dentro desse projeto vamos armazenar todos os pacotes de ETL;
2 - Criação de um pacote de ETL - Quando falamos em pacotes de ETL, estamos nos referindo ao conjunto de fluxos que será usado para trafegar com a nossa informação da sua origem até seu destino. Recomento que caso esteja acompanhando nossa série, crie um pacote para cada item, fica mais fácil o entendimento;
3 - Criando/Configurando as conexões - Vamos utilizar nesse nosso post uma conexão do tipo OLE DB pois iremos consumir e gravar dados do Microsoft SQL Server, ou seja, Source e Destinantion irão apontar para o mesmo local, a única diferença será a tabela que será consumida e iremos gravar em dois destinos diferentes. Esse é o passo onde definimos as conexão;
4 - Criando nosso Fluxo de Dados
4.1 - Componentes que serão usados - Nesse primeiro exemplo, vamos trabalhar com Sete componentes: Source Assistant (2), responsável pela conexão com a origem. Vamos consumir duas tabelas do Microsoft SQL Server, iremos usar também o componente Sort (2) eles serão usados para ordenar as duas origens, um componente Merge Join, ele será usado para realizar o relacionamento entre os dois Source, um componente Aggregate, usado para agrupar os dados por cargo e sumarizar a quantidade de vagas e por último o componente Destination Assistant que será usado para definir o destino dos dados.
4.2 - Criando nosso destino dos dados (Tabela) - Vamos criar uma tabela para receber os dados;
5 - Visualizando os dados carregados - E por último, vamos visualizar como que ficaram os dados carregados em nossa tabela.
Bora começar então!
1 - Criando um Projeto no SQL Server Integration Services
Vou considerar que você não tem nenhum projeto criado no Visual Studio do SQL Server Integration Service. Um ponto bem importante desse estudo é que não irei falar como que você realiza a configuração do seu ambiente, entenderei que você já tenha feito isso em outro momento.
Para criar um projeto é bem simples. Vamos abrir o Visual Studio e seguir os próximos passos.
(1) Selecionar File;
(2) New;
(3) Project;
(4) Caso tenha um projeto criado basta clicar em Open Project.
Após clicar em New Project, a tela abaixo será apresentada.
(1) Escolher Integration Services;
(2) Integration Services Project;
(3) Informar um nome para o Projeto;
(4) Definir o local (diretório) que o projeto será gerado;
(5) Informar uma nomenclatura para a sua solução;
(6) Caso queira que um diretório para o projeto seja criado basta marcar essa opção;
(7) Clicar em OK para realizar a criação;
2 - Criando nosso Pacote de ETL
Primeiro ação será a criação de um pacote do SSIS. Essa criação é bem simples, acompanha comigo.
(1) Clicar com o botão direito do mouse sobre SSIS Package;
(2) Clicar em New SSIS Package;
Além dessa possibilidade, outras ações podem ser realizadas.
Importar um Arquivo dtsx - basta clicar em Add Existng Package;
Realizar o Upgrade dos seus pacotes - basta clicar sobre Convert Deployment Model ou Upgrade All Package;
Realizar um Import and Export - basta clicar sobre SSIS Import and Export Wizard;
Ordenar seus pacotes - basta clicar em Sort by name.
Após a criação do nosso arquivo dtsx, vamos para o desenho do nosso fluxo.
(1) Nome do arquivo dtsx;
Para alterar basta clicar sobre ele e apertar o botão F4 e em file name informar o nome do seu pacote.
Ou clicar sobre o pacote e apertar F2
(2) Aba Control Flow;
(3) Inserir um componente de Data Flow Task;
(4) Realizar duplo clique ou clicar em Data Flow.
Ao clicar em Data Flow, vamos começar a brincadeira! Dessa vez vamos fazer iguais aos filmes de Hollywood começando do final. Apresento para você como que será o resultado final desse nosso trabalho (1).
Vamos trabalhar então para produzir mais esse conteúdo.
3 - Criando as Conexões
Primeiro passo será realizar a configuração das nossas conexões.
(1) Aba Connection Manage;
(2) Clicar com o botão direito do mouse e escolher New OLE DB Connection;
(1) Na aba Connection Manager, vamos clicar com o botão direito e selecionar OLE DB Connection Manager. Na tela que será apresentada, vamos clicar em New;
(2) Selecionar Native OLE DB\SQL Server Native Client 11.0;
(3) Informar o nome do servidor\instância;
(4) Selecionar o método de conexão;
(5) Selecionar o banco de dados. Clicando na setinha pra baixo é possível visualizar todos os bancos existentes em nosso servidor;
(6) Clicar em Test Connection para validar a conexão (Sempre comento que esse passo é desnecessário, pois ela já foi validada no passo anterior);
(7) Clicar em OK para finalizar.
Vamos configurar agora os componentes que serão usados no processo de carga.
4 - Criando o Fluxo de Dados
Na aba Control Flow, vamos selecionar seguir os seguintes passos.
(0) Podemos observar que a conexão criada está configurada e disponível;
(1) Em SSIS Toolbox o componente Data Flow Task;
(2) Sobre o componente Data Flow Task, vamos clicar com o botão direito;
(3) Clicar em Rename.
Na próxima tela conseguimos visualizar o esqueleto (1) do nosso motor.
4.1 - Configurando o Componente Source Assistant
Conforme imagem anterior, vamos fazer uso de 7 componentes! Dois para a nossa origem de dados, dois para ordenação, uma, um que permite que façamos o relacionamento entre as duas origens, uma para agregar os valores e uma para gravar os dados em nosso destino.
(1) Em SSIS Toolbox, vamos selecionar o componente Source Assistant;
(2) Feito isso a tela ao centro será carregada;
(3) Como em nossa origem iremos usar duas tabelas do nosso banco de dados, vamos selecionar SQL Server;
(4) Selecionar a conexão que criamos;
(5) Observe que algumas informações referente a origem dos dados é apresentada;
(6) Clicar em OK para confirmar.
Após a inclusão do componente de Source, vamos selecionar de onde (tabela) os dados deverão ser carregados.
(1) Clicar duas vezes sobre o componente OLE DB Source, na aba Connection Manager;
(2) Selecionar a conexão que criamos anteriormente;
(3) Nesse passo também é possível realizar a criação de uma nova conexão;
(4) Nesse ponto podemos selecionar se a nossa origem será uma tabela ou view, caso escolha, no passo seguinte você deverá selecionar em uma lista qual a origem e também pode ser selecionado um script, caso seja escolhida essa opção, no passo seguinte você deverá informar o script de consulta;
(5) Selecionar a origem, ou tabela ou script, depende do passo anterior;
(6) É possível visualizar uma prévia dos dados;
(7) E clicar em OK para finalizar.
E como vamos precisar de dois Source, iremos repetir a mesma configuração.
(1) Em SSIS Toolbox, vamos selecionar o componente Source Assistant;
(2) Feito isso a tela ao centro será carregada;
(3) Como nossa origem será uma tabela, vamos selecionar SQL Server;
(4) Selecionar a conexão que criamos;
(5) Observe que algumas informações referente a origem dos dados é apresentada;
(6) Clicar em OK para confirmar.
Após a inclusão do componente de Source, vamos selecionar de onde (tabela) os dados deverão ser carregados.
(1) Clicar duas vezes sobre o componente OLE DB Source;
(2) Na aba Connection Manager;
(3) Selecionar a conexão que criamos anteriormente;
(4) Nesse passo também é possível realizar a criação de uma nova conexão;
(5) Nesse ponto podemos selecionar se a nossa origem será uma tabela ou view, caso escolha, no passo seguinte você deverá selecionar em uma lista qual a origem e também pode ser selecionado um script, caso seja escolhida essa opção, no passo seguinte você deverá informar o script de consulta;
(6) Podemos validar o script, incluir parâmetros;
(7) É possível visualizar uma prévia dos dados;
(8) E clicar em OK para finalizar.
Podemos também realizar essa configuração escolhendo dessa vez a tabela. Observe a diferença no exemplo acima onde escrevemos a query para retornar os dados. É exatamente a mesma coisa, a única diferença consiste em que no primeiro escrevemos um comando SELECT e no outro selecionamos um objeto (tabela).
(1) Clicar duas vezes sobre o componente OLE DB Source na aba Connection Manager;
(2) Selecionar a conexão que criamos anteriormente;
(3) Nesse passo também é possível realizar a criação de uma nova conexão;
(4) Nesse ponto podemos selecionar se a nossa origem será uma tabela ou view, caso escolha, no passo seguinte você deverá selecionar em uma lista qual a origem e também pode ser selecionado um script, caso seja escolhida essa opção, no passo seguinte você deverá informar o script de consulta;
(5) Selecionar a origem, ou tabela ou script, depende do passo anterior;
(6) É possível visualizar uma prévia dos dados;
(7) E clicar em OK para finalizar.
A diferença entre os dois modelos está bem aqui. Repare que no primeiro caso onde escrevo a query não existe a necessidade de selecionar quais colunas iremos trabalhar, isso já foi feito quando escrevemos o SELECT, agora nesse outro caso já é um pouco diferente, pois selecionamos a tabela, ou seja, todas as colunas. Seguindo as boas práticas e visando performance, devemos trabalhar apenas com aquilo que vamos precisa e nada a mais. Diante disso, na tela abaixo vamos selecionar as colunas da nossa origem.
(1) Na aba Columns;
(2) Selecionar as colunas que iremos trabalhar;
(3) Grid com as colunas selecionadas, caso queira, na coluna Output Column é possivel alterar ou renomear as colunas;
(4) Clicar em OK para confirmar.
Após a configuração dos dois componente de Source, vamos adicionar os componentes que fazem a ordenação.
4.2 - Configurando o Componente Sort
Sempre que trabalhamos com o Merge Join, faz-se necessário a utilização de um componente de Sort, ou seja, vamos incluir dois componentes Sort em nosso fluxo.
É bem simples, acompanha aqui comigo.
Em SSIS Toolbox, vamos selecionar Sort e arrastar até a nossa tela.
(0) Duplo clique no componente Sort;;
(1) Selecionar qual a coluna será ordenada. Aqui vai uma observação, como iremos trabalhar com o componente Merge Join, a sequencia deverá ser a mesma em ambos os componentes de Sort, pois é através dessa informação que será estabelecido o relacionamento entre as colunas;
(2) Coluna selecionada para ordenação. Observem a coluna Sort Type, nela você pode definir como que será a ordenação, crescente ou decrescente, na coluna Sort Order é a sequencia de ordenação. É idêntico ao ORDER BY dentro de um comando SELECT;
(3) Clicar em OK para confirmar.
Mesma coisa para o segundo componente de Sort.
(0) Duplo clique no componente Sort;
(1) Selecionar qual a coluna será ordenada;
(2) Coluna selecionada para ordenação;
(3) Clicar em OK para confirmar.
Próximo passo será a inclusão do componente de Merge Join.
4.3 - Configurando o Componente Merge Join
O seu comportamento é exatamente o mesmo de um Join em um comando SELECT. Observe aqui comigo.
(1) Após dois cliques no componente Merge Join a tela abaixo será apresentada. Em Join type é o local onde definimos o tipo de relacionamento que vamos estabelecer entre os dois Source;
(2) Colunas de ambos os Source. Observe que ao clicar no componente, o relacionamento entre eles já vem estabelecido. Isso ocorreu devido a configuração que você realizou no passo anterior;
(3) Nesse grid é como se vocês estivesse selecionando das tabelas (Source) o que pretende carregar;
(4) Feito isso, basta clicar em OK para confirmar.
Vamos agora incluir o componente Aggregate para realizar a consolidação dos dados.
4.4 - Configurando o Componente Aggregate
(1) Duplo clique sobre o componente Aggregate, selecionar as colunas;
(2) No grid abaixo vamos selecionar a operação que será aplicada sobre cada coluna;
(3) Clicar em OK para confirmar.
Pronto, estamos quase finalizando.
4.5 - Configurando o Componente Destination Assistant
Vamos incluir nesse momento o nosso componente de Destino dos dados. Porém antes disso, precisamos criar a nossa tabela que irá receber nossos dados.
(1) Conectar em nosso servidor de banco de dados;
(2) Selecionar o banco de dados e clicar em New Query;
(3) Comando para criação da nossa tabela.
Agora sim vamos fazer a inclusão do nosso Destination.
(1) Em SSIS Toolbox, vamos selecionar o componente Destination Assistant;
(2) Feito isso a tela ao centro será carregada;
(3) Como o destino será uma tabela, vamos selecionar SQL Server;
(4) Selecionar a conexão que criamos;
(5) Observe que algumas informações referente ao destino do arquivo é apresentada;
(6) Clicar em OK para confirmar.
Na próxima tela vamos selecionar qual tabela irá receber os dados.
(1) Duplo clique no componente OLE DB Destination na aba Connection Manager;
(2) Selecionar a conexão com o banco de dados;
(3) Nesse passo é possível realizar a criação de uma nova conexão;
(4) Selecionar o modo de carga de dados;
(5) Selecionar em qual tabela os dados serão gravados;
(6) É possível criar uma tabela caso não exista;
(7) Podemos visualizar se existe algum registros em nosso destino;
(8) Clicar em OK para finalizar a configuração.
O próximo passo é realizar o mapeamento da origem com o destino.
(1) Componente OLS DB Destination, na aba Mappings;
(2) Relacionamento entre origem e destino;
(3) É possível selecionar esse mapeamento por igualdade de nomes;
(4) Nesse grid existe a exposição do mapeamento das colunas;
(5) Estando tudo OK, basta clicar para confirmar.
Muito bem jovem mancebo, sua configuração chegou ao fim. Vamos observar como que ficou.
Para finalizar nossa configuração, vamos incluir um componente que será usado por limpar sempre o nosso destino antes da carga.
4.6 - Configurando o Componente Execute SQL Task
(1) Clicar na aba Control Flow, depois clicar em SSIS Toolbox e selecionar o componente Execute SQL Task;
(2) Nome do componente;
(3) Nesse passo vamos configurar apenas a aba General do componente Execute SQL Task;
(4) Incluir um script de TRUNCATE TABLE;
(5) Clicar em OK para finalizar.
Hora de ver se essa joça funciona!
(1) Fluxo dos dados;
(2) Registros inseridos;
(3) Vamos conectar em nosso servidor;
(4) Selecionar nosso banco de dados;
(5) Comando de consulta dos dados;
(5) Resultado.
Os arquivos usados até aqui estão disponíveis em:
Script de Create Table dbo.tb_vagas_cargo
08_SCRIPT_CREATE_TABLE_tb_vagas_cargo.sql
Pacote SSIS - Row Sampling
10_Importa_Cargos_Merge_Join_Insert.dtsx
5 - Conclusão
Bem meu amigo, mais um trabalho chegou ao fim!
Aprendemos no post de hoje como trabalhar com o componente Merge Join em um fluxo de trabalho através do pacote de dados do SSIS, onde foi possível demonstrar como é possível unir dados de duas tabelas em diferentes bases de dados, que também poderia ser outras fontes de dados, resultando em uma terceira tabela.
Espero que tenham gostado, espero encontra-lo no próximo.
Grande abraço e fique com Deus!
Comments