SQL Server Integration Services (SSIS): Trabalhando com o componente Merge Join

Com o componente Merge Join podemos combinar dados de duas ou mais fonte de dados, sejam bancos relacionais ou planilhas e inseri-los em um fluxo de trabalho, possibilitando o carregamento desses dados ao seu destino. O componente Merge Join possibilita a recuperação de dados de fontes como planilhas do Excel, arquivos de texto ou valores (CSV), tabelas de banco de dados ou outras fontes separadas por vírgulas. No entanto, cada fonte utilizada durante esse processo deve conter uma ou mais colunas que se relacionem com os dados dessa outra fonte. Podemos imaginar um cenário em que se deseja extrair informações sobre o produto de uma fonte e informações do fabricante de outra fonte.

Para unir esses dados em uma única tabela, tanto os dados do produto quanto do fabricante deverá possuir um identificador em comum, como um ID do fabricante, que pode ser ligado a um identificador semelhante nos dados do fabricante, semelhante ao relacionamento de chave estrangeira entre duas tabelas. Desta forma, associada com cada um dos produtos é o ID do fabricante que mapeia para o ID do fabricante nos dados do fabricante.

O Merge Join é semelhante à realização de um "join" em um comando Transact-SQL (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.

Neste artigo demonstraremos como utilizar o componente Merge Join para unir duas tabelas por meio de um único fluxo de dados através de bases de dados distintas, porém, localizadas em uma mesma instância, tendo como destino uma única tabela. Essa mesma tarefa poderia ser realizada extraindo dados de diferentes servidores, bastando para isto, ajustar os dados de conexão entre as instâncias e bases de dados.

Criando um pacote utilizando o SQL Server Integration Services (SSIS)

Utilizaremos a ferramenta de ETL (Extract Transform and Load) da Microsoft, essa opção está presente no ambiente de desenvolvimento SQL Server Data Tools (Business Intelligence Development Studio em versões anteriores a 2012). Também utilizaremos o Sistema Gerenciador de Banco de Dados (SGBD) SQL Server 2012 Enterprise Edition.

Para criar o pacote, clicamos em Inciar – Todos os Programas – Microsoft SQL Server 2012 – SQL Server Data Tools (Figura 1).

Com o ambiente de desenvolvimento aberto, vamos criar um novo projeto, para isso, clique em File – New – Project (Figura 2).

Vamos criar um novo projeto do Integration Services e atribuir o nome desse projeto de “MergeJoin” (Figura 3), clique em “Ok”.

Antes de iniciar o processo de extração devemos ter a certeza de que as fontes de dados que utilizaremos durante o nosso fluxo de operação estejam disponíveis.

Para esse exemplo iremos trabalhar com duas bases de dados que serão as nossas fontes, a primeira denominada DB_Customer e a segunda DB_Sales. É importante que as duas bases utilizem a mesma estrutura, para isso realizamos a cópia de algumas tabelas do AdventureWorks2012 (disponível na opção código fonte) e faremos a inserção dos dados nas bases de dados mencionadas acima.

Para realizar essa tarefa utilizaremos o componente “Execute SQL Task”, que será o responsável pela criação dos bancos e tabelas.

Primeiramente precisamos clicar e arrasta o componente “Execute SQL Task” para a área “Control Flow” (Figura 4).

Precisamos definir a nossa fonte de dados e criação das tabelas e das bases de dados, com um duplo clique sobre o componente a seguinte tela (Figura 5) é apresentada.

Para configurar a conexão com a nossa fonte de dados, vamos editar o campo “Connection” (1) e criar um script T-SQL no campo “SQL Statement” (2), que será o responsável pela criação das bases de dados e tabelas.

1. Configurando a Conexão com a Fonte de Dados (Connection)

   Clicando em “Connection” e em seguida “New Connection”, a tela da Figura 6 é exibida. Na janela Configure OLE DB Connection Manager clicamos em “New...” para criarmos uma conexão com a nossa base AdventureWorks2012.

   Ao clicar em “New...”, o gerenciador de conexão será aberto, nessa janela precisamos definir o nome do servidor que iremos nos conectar e a base de dados que iremos utilizar como fonte de dados (Figura 7). Após a configuração desses dois itens, clicamos em “OK” duas vezes para retornarmos a tela de propriedades do componente “Execute SQL Task Editor”.

2. Criar script para criação das bases de dados e tabelas

   No campo “SQL Statement”, ao clicar sobre o botão uma janela (Enter SQL Query) se abrirá (Figura 8). É nessa janela que iremos inserir o script para a criação da base e tabelas, e importação de dados, conforme Listagem 1.

   Listagem 1. Script para criação das bases de dados e tabelas

    
                               USE master
                               GO
                           
                               IF DB_ID('DB_Customer') IS NOT NULL
                               DROP DATABASE DB_Customer
                               GO
                           
                               CREATE DATABASE DB_Customer
                               GO
                           
                               IF DB_ID('DB_Sales') IS NOT NULL
                               DROP DATABASE DB_Sales
                               GO
                           
                               CREATE DATABASE DB_Sales
                               GO
                           
                               IF OBJECT_ID('DB_Customer.dbo.Customer') IS NOT NULL
                               DROP TABLE DB_Customer.dbo.Customer
                               GO
                           
                               SELECT TOP 1000
                               CustomerID,
                               StoreID,
                               AccountNumber,
                               TerritoryID
                               INTO DB_Customer.dbo.Customer
                               FROM AdventureWorks2012.Sales.Customer;
                           
                               IF OBJECT_ID('DB_Sales.dbo.Territory') IS NOT NULL
                               DROP TABLE DB_Sales.dbo.Territory
                               GO
                           
                               SELECT
                               TerritoryID,
                               Name AS TerritoryName,
                               CountryRegionCode AS CountryRegion,
                               [Group] AS SalesGroup
                           
                               INTO DB_Sales.dbo.Territory
                               FROM AdventureWorks2012.Sales.SalesTerritory
                               

   De acordo com a Listagem 1, os comandos SELECT…INTO foram utilizados para criar e popular a tabela Customer na base DB_Customer. Para isso, utilizamos os dados da tabelaCustomer localizada no banco de dados AdventureWorks2012, o mesmo procedimento foi utilizado na base de dados DB_Sales.

   Após a inserção do script da Listagem 1 na janela “Enter SQL Query”, clicamos em Ok, e novamente em Ok. Para testarmos o funcionamento dessa tarefa, clicamos sobre o componente “Execute SQL Task” com o botão direito do mouse e em seguida selecionamos a opção “Execute Task” (Figura 9).

   O resultado final deverá ser semelhante à Figura 10.

   Com a criação das bases concluídas, precisamos criar dois gerenciadores de conexão, uma para cada base (DB_Customer e DB_Sales). Para isso clicamos com o botão direito sobre a área de trabalho da aba “Connection Managers” e depois em “New OLE DB Connection” (Figura 11).

   Repetimos os mesmos passos do item 1, “Configurando a Conexão com a Fonte de Dados (Connection)”, a única alteração será a seleção da base de dados. Nesse caso, devermos selecionar as bases DB_Customer e DB_Sales, uma para cada gerenciador de conexão. O resultado esperado pode ser visualizado nas Figuras 12 e 13.

   O próximo passo, após adicionarmos os gerenciadores de conexão, será adicionarmos os fluxos de tarefas (Data Flow Tasks). Os componentesData FlowTask nos fornecem uma estrutura que nos permite adicionar componentes que possam transformar e mover os dados entre a origem e destino.

   Para adicionar o componente Data Flow Task, clicamos e arrastamos esse item para área de trabalho “Control Flow” (Figura 14).

   O componente Data FlowTask funciona como um container para outros componentes. Para acessarmos esse container, clicamos duas vezes sobre o componente. Após o duplo clique, a abaData Flowé exibida (Figura 15). Nesse momento podemos adicionar os componentes de fluxo de dados.

Adicionando fonte de dados ao Data Flow Task

Já criamos os gerenciadores de conexão anteriormente, no entanto precisamos adicionar dois componentes OLE DB (um para cada gerenciador de conexão), que farão o mapeamento de nossas fontes de dados para extrair os dados de nossas duas bases de dados.

Primeiramente, adicionaremos o componente “OLE DB Source” localizado na Toolbox e na sessão “Other Sources”; vamos atribuir a esses componentes o nome de “Sales” e “Customer” (Figura 16). Para renomear os componentes “OLE DB Source”, clicamos com o botão direito sobre o componente e em seguida na opção “Rename”.

Precisamos configurar esses componentes e para isso, dê um duplo clique sobre o componente para ter acesso a suas propriedades, por padrão, será aberto a tela de gerenciadores de conexão (Connection Manager).

Primeiramente precisamos selecionar um gerenciador de conexão que criamos anteriormente: na caixa de seleção “OLE DB Connection Manager” selecione o gerenciador que fará a conexão com a base “DB_Customer” (Figura 17).

O próximo passo será a seleção da tabela que iremos utilizar: no campo “Name of the table or the view:” selecione a tabela “dbo.Customer”. A tela de configuração do componente OLE DB deve ser semelhante à Figura 18.

Precisamos selecionar quais os campos da tabela que iremos utilizar. Na página “Columns” verifique se todas as colunas estão selecionadas. Essas colunas serão incluídas no componente “Output Data Flow”. Se não for necessário utilizar todas as colunas da tabela selecionada, basta desmarcar as colunas indesejadas da lista “Available External Columns”. Para esse exemplo utilizaremos todas as colunas (Figura 19).

Uma vez definido quais as colunas que iremos utilizar clique em “Ok” para fechar a janela “OLE DB Source Editor”. Para configurar o componente “Sales” (Figura 16), repetiremos o mesmo procedimento realizado para o componente “Customer”.

Adicionando o componente Merge Join

Agora que temos nossas fontes de dados configuradas, podemos adicionar o componente Merge Join. Para isso, clique e arraste esse componente até a sessão Data Flow (Figura 20).

Primeiramente realizaremos a conexão entre os componentes “Sales” ao componente Merge Join, então clicamos sobre o componente e quando fazemos isso, surgirão duas setas (azul e vermelha). Clicamos e seguramos a seta azul arrastando-a até o componente “Merge Join” (Figura 21).

Quando conectarmos os componentes, uma caixa de diálogo “Input Output Selection” aparecerá, serão exibidas duas opções: OutputeInput.

A seleção de saída “Output” carrega por default uma saída do tipo “OLE DB Source Output”, então deixemos essa opção selecionada. No campo Input, selecionaremos a opção “Merge Join Left Input”, conforme Figura 22.

Quando conectarmos o segundo componente (Customer) ao componente “Merge Join”, a caixa de diálogo “Input Output Selection” não aparecerá, pois a opção de “Input” haverá somente uma opção, a “Merge Join Right Input”. O resultado até esse momento deve ser semelhante ao exibido na Figura 23.

Perceba que recebemos um sinal de erro (círculo vermelho com um “x”): se tentarmos executar essa tarefa, receberemos a mensagem da Figura 24.

A razão desse erro é que primeiramente precisamos ordenar os dados que serão confrontados no componente Merge Join. Existem duas formas para resolver esse problema: organizando os dados através do componente OLE DB ou adicionando um componente de organização (“Sort”).

Veremos como aplicar essas duas formas, sendo uma para cada componente (Sales e Customer).

Organizando os dados através do componente OLE DB

Para organizar os dados através do componente OLE DB, precisamos primeiramente modificar a conexão para realizarmos uma consulta. Clique duas vezes sobre o componente “Customer” para abrir o editor OLE DB Source. No campo “Data access mode”, selecione a opção “SQL command”. Em seguida, na janela de texto do comando SQL, digite a instrução da Listagem 2.

SELECT CustomerID, StoreID, AccountNumber, TerritoryID
            FROM dbo.Customer
            ORDER BY TerritoryID 

A janela do gerenciador de conexãodeverá ser semelhante à Figura 25.

Uma vez que definimos a nossa consulta, clicamos em OKpara fechar o editor OLE DB. Devemos então usar a opção avançada do editor para organizar colunas específicas. Para acessar esse editor, clicamos com o botão direito sobre o componente “Customer” e selecionamos a opção “Show Advanced Editor” (Figura 26).

Quando a caixa de diálogo “Advanced Editor” aparecer, abra a aba “Input and Output Properties”. Nessa aba, selecione o item “OLE DB Source Output”, quando selecionamos esse item, uma janela “Common Properties” aparecerá ao lado direito, no campo “IsSorted” deixe a opção marcada como “True”, conforme figura 27.

Precisamos especificar qual coluna ou colunas que serão ordenadas. Para fazer isso, abriremos a pasta “Output Columns” localizada dentro do item principal (OLE DB Source Output), e selecione a coluna “TerritoryID”. Na janela ao lado direito (Common Properties) veremos as propriedades dessa coluna. Mude o valor da propriedade “SortKeyPosition”de0para1, conforme a Figura 28.

Quando realizamos essa alteração, estamos dizendo que os outros componentes do fluxo de dados serão classificados baseando-se na coluna “TerritoryID”. Realizaremos esse procedimento somente para o componente “Customer”.

Trabalhando com o componente “Sort”

Outra opção para ordenar os dados para trabalharmos com o Merge Join podemos utilizar o componente “Sort”. Quando trabalhamos com uma fonte de dados do tipo OLE DB, optamos por componentes que permitam o uso de scripts T-SQL. Entretanto, para outras fontes, como um arquivo de texto, não poderíamos utilizar essa opção. Por esse motivo, temos a opção de utilizar o componente “Sort”.

Vamos excluir as ligações entre os componentes “Sales” e “Merge Join”, para isso clique com o botão direito sobre a linha que conecta os componentes e em seguida, clique em “Delete”. Devemos agora arrastar o componente “Sort” para a área “Data Flow”, entre os componentes “Sales” e “Merge Join” e conectá-los, conforme a Figura 29.

Precisamos configurar o componente “Sort”, para isso clicamos duas vezes sobre o componente para acessar suas propriedades. Como podemos ver, na janela de propriedades do componente “Sort”, recebemos uma advertência pois não fornecemos nenhuma coluna de entrada “Input”. Havíamos definido a coluna “TerritoryID” como um campo chave para o componente “Customer”, precisamos utilizar esse mesmo campo para o componente “Sales”, pois essas duas tabelas devem possuir pelo menos um campo em comum (Figura 30).

Uma vez que definimos a coluna que será ordenada, clicamos em “Ok” para fechar a janela “Sort Transformation Editor”.

Percebemos que a função “Merge Join” pode ser utilizada através de duas abordagens, com ou sem a função “Sort”, embora, como mencionado anteriormente, se estivermos trabalhando com um componente OLE DB, não há a necessidade de se utilizar a função “Sort”, nessa situação, configurar o componente OLE DB geralmente é o método preferido.

Até o momento tempos nossos dados ordenados e nossas fontes de dados estão conectadas ao componente “Merge Join”. Precisamos configurar alguns itens desse componente, para isso, clicamos duas vezes sobre “Merge Join” (Figura 31).

Perceba que a primeira propriedade do componente “Merge Join”, são as opções de “Joins” que podemos realizar entre os componentes “Sales” e “Customer” através da lista de seleção “Join type”. Nessa lista podemos selecionar um item dos três tipos de “joins”:

• Left outer join: Esta consulta retornará todos os registros da tabela esquerda e suas correspondentes que existirem na tabela direita.
• Full outer join: Esta consulta retornará todos os registros das duas tabelas além dos registros correspondentes entre elas. Podemos utilizar a opção “Swap Inputs” para mudar a fonte de dados, dessa forma estaríamos criando uma junção “Right Join”.
• Inner join: Esta consulta retornará todos os registros da tabela esquerda que têm correspondência com a tabela direita.

Por exemplo, se quisermos incluir todas as linhas da tabela à esquerda (Sales), mas somente as linhas correspondentes a tabela direita (Customer), deveremos utilizar a opção “Left Outer Join”.

Na próxima sessão do Editor Merge Join Transformation, vemos duas tabelas “Sort” e “Customer”, a tabela “Customer”, exibe os dados da tabela “Customer”, enquanto que a tabela “Sort” exibe os dados da nossa fonte “Sales”. Como utilizamos o compontente “Sort”, ele será considerado como uma fonte de dados ao invés de “Sales”.

Perceba que há uma linha ligando a coluna “TerritoryID” entre as tabelas Sort e Customer, o SSIS automaticamente cria essa conexão com base nos dados que foram ordenados no fluxo de dados, nesse caso, nossa classificação foi realizada com base na coluna “TerritoryID” nas duas fontes de dados para que fosse possível realizarmos a junção entre as tabelas.

Precisamos definir quais as colunas que iremos utilizar, para este exercício, vamos incluir todas as colunas exceto a coluna “AccountNumber” na tabela Customer e a coluna TerritoryID da tabela “Sort”, o resultado dessa configuração pode ser visualizado na Figura 32.

Após a configuração do componente “Merge Join”, clicamos em “Ok” para fechar essa janela.

Finalmente podemos definir o destino dos dados resultantes da junção de duas tabelas através do componente “Merge Join”.

Adicionando um destino do tipo OLE DB

Nosso ultimo passo é configurar um componente de destino que receberá os dados das tabelas “Sales” e “Customer” em uma nova tabela, para isso, vamos utilizar o componente “OLE DB Destination”, para isso clique e arraste esse componente para a sessão “Data Flow”.

Precisamos conectar o componente “Merge Join” ao componente “OLE DB Destination”. Em seguida, clique duas vezes sobre componente “OLE DB Destination” para abrir o Editor.

Na janela do Editor OLE DB, especifique qual será o gerenciador de conexões do banco de dados que iremos utilizar, para esse exemplo, iremos trabalhar com o banco DB_Sales, criado em passos anteriores, lembrando que para utilizar uma outra base de dados, basta clicar em “New” e configurar essa nova conexão.

No campo “Data access mode”, selecione o item “Table or view - fast load”.

Em seguida, vamos criar uma tabela de destino para armazenar o nosso conjunto de resultados. Clique no botão “New” ao lado do campo “Name of the table or the view”, uma caixa de diálogo será aberta. Nessa caixa de diálogo, uma tabela é gerada automaticamente com base nas colunas do fluxo de dados (Merge Join).

Podemos renomear a tabela (ou qualquer outro elemento), daremos o nome de Merge_Output, conforme Figura 33.

Clique em “Ok” para fechar a caixa de diálogo “Create Table”. Retornando ao editorOLE DB Destination, vamos configurar o item “Mappings” para ter a certeza de que todas as colunas foram mapeadas corretamente (Figura 34).

Clique em “Ok” para fechar o editor OLE DB Destination. Com o nosso destino criado, o que precisamos fazer agora é executar o projeto.

Para executar o projeto clicamos no botão “Start Debugging” (seta verde), localizada no barra de menu, ou através da tecla de atalho F5.

O resultado final utilizando o mesmo script e fonte de dados desse exemplo deveremos recuperar 1000 linhas da fonte de dados “Customer” e 10 linhas da fonte de dados “Sales”. Tendo em vista que a função do componente Merge Join é unir dados de tabelas distintas, deveremos receber 1000 linhas em nosso destino (tabela Merge_Output). A Figura 35 mostra o fluxo de dados após a execução com sucesso.

Outra forma de verificar se de fato os dados foram transferidos para a tabela Merge_Output localizada no banco DB_Sales pode ser através do SQL Server Management Studio (SSMS), para isso execute umSELECTque receba todas as linhas da tabela Merge_Output. As primeiras linhas podem ser visualizadas na Figura 36.

Com isso, neste artigo aprendemos como trabalhar com a função “Merge Join” aplicada em um fluxo de trabalho através do pacote de dados do SSIS, onde se objetivou unir dados de duas tabelas em diferentes bases de dados, resultando em uma terceira tabela.