Olá meu amigo, você que vem acompanhando nosso blog deve lembrar que na última publicação falamos sobre o componente Union All, um componente bem útil que permite fazer a junção entre duas ou mais fontes de dados.
No post de hoje amos falar sobre o componente Aggregate. Para quem nunca ouviu falar dele, esse componente dentro de um fluxo de carga de dados de um ETL substituiu o comando GROUP BY em uma query quando necessitamos sumarizar valores, contar uma determinada quantidade linhas são retornadas para uma determinada situação, calcular uma média e por incrível que pareça, pode também realizar o trabalho do clausula DISTINCT.
Analisa aqui pra ficar mais claro. Fazendo uma comparação com uma query, ele tem a mesma aplicação quando por exemplo precisamos saber quantos alunos são do sexo masculino e quantos do sexo feminino, veja o exemplo abaixo de como faríamos a escrita dessa query.
-- Total de Alunos do Sexo MASCULINO e FEMININO
SELECT DS_SEXO, COUNT(CD_SEXO) TOTAL_ALUNO
FROM dbo.TURMA
GROUP BY DS_SEXOQuando você tiver trabalhando com esse componente o seu trabalho será o mesmo. Antes de inserir os registros no destino, você irá selecionar a coluna que será agrupada, em nosso caso a DS_SEXO e em qual irá contar e nosso exemplo CD_SEXO.
Uma outra abordagem comentada acima, é fazer uso da clausula DISTINCT. Da mesma forma como aplicamos dentro de uma query, é possível fazer com o uso do componente.
-- Seleção distinta de CD_SEXO e DS_SEXO sem usar o DISTINCT
SELECT CD_SEXO , DS_SEXO
FROM dbo.TURMA
GROUP BY CD_SEXO , DS_SEXOE por último, podemos também realizar a saída de vários destinos em um único fluxo. A grande vantagem é que se você tivesse realizando isso via query, precisaria realizar duas leituras no seu Source e com o componente Aggregate você faz apenas uma e aplica as regras dentro do componente.
Vamos iniciar os trabalhos, porém não posso deixar de 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 as duas que serão gravadas. 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 Três componentes: Source Assistant, responsável pela conexão com a origem. Vamos consumir uma tabela do Microsoft SQL Server, iremos usar também o componente Aggregate pois vamos realizar a agregação das informações 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.
Mão a obra! Pois camarão que dorme a onda leva...
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;
A próxima configuração será referente ao nosso destino, ou seja, onde os dados serão gravados.
Como destino, escolhemos o Microsoft SQL Server, acompanha comigo.
(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 e selecionar o método de conexão;
(4) Selecionar o banco de dados. Clicando na setinha pra baixo é possível visualizar todos os bancos existentes em nosso servidor;
(5) Clicar em Test Connection para validar a conexão (Sempre comento que esse passo é desnecessário, pois ela já foi validada no passo anterior);
(6) 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 3 componentes! Um para a nossa origem de dados, uma para o destino dos dados e um que permite que façamos a agregação dos dados, ou seja, ele é o objetivo desse post.
(1) Em SSIS Toolbox, vamos selecionar o componente Source Assistant;
(2) Feito isso a tela ao centro será carregada;
(3) Como nossa origem será um arquivo, vamos selecionar SQL Server;
(4) Selecionar a conexão que criamos;
(5) Observe que algumas informações referente a origem do arquivo é 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 table or 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) E clicar em OK para finalizar.
Próximo passo é selecionar as colunas que estaremos trabalhando.
(1) Na aba Columns;
(2) Colunas disponíveis;
(3) Colunas selecionadas;
(4) Clicar em OK para confirmar.
4.2 - Configurando o Componente Aggregate
Vamos falar um pouco sobre o nosso personagem principal, o componente Aggregate.
(1) Na aba SSIS Toolbox, vamos clicar sobre Aggregate;
(2) Ele será carregado em nosso fluxo;
(3) Fluxo de Dados.
Após a inclusão do componente vamos fazer a configuração.
(1) Duplo clique no componente Aggregate;
(2) Possuímos duas opções de configuração (Advanced e Basics), vamos falar mais a frente;
(3) Selecionar as colunas que vamos trabalhar;
(4) Colunas selecionadas no passo anterior e na coluna Operation a opção que será aplicada para cada uma delas (Podendo ser Group By, Max, Min, AVG, Count, Count Distinct);
(5) Clicar em OK para confirmar.
Feita a configuração acima, conseguimos agora avançar para a configuração nosso Destination.
4.3 - Configurando o Componente Destination Assistant
(1) Em SSIS Toolbox, vamos selecionar o componente Destination Assistant;
(2) Feito isso a tela ao centro será carregada;
(3) Como nossa origem será um arquivo, 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.
Da mesma forma que no Source selecionamos a origem da informação, nesse passo vamos selecionar o nosso Destino.
(1) Duplo clique no componente OLE DB Destination;
(2) Na aba Connection Manager;
(3) Selecionar a conexão com o banco de dados;
(4) Selecionar o modo de carga de dados;
(5) Selecionar em qual tabela os dados serão gravados;
(6) Podemos visualizar se existe algum registros em nosso destino;
(7) Clicar em OK para finalizar a configuração.
O próximo passo é fazer a correlação entre as colunas da origem e o destino.
(1) Continuando no componente OLE DB Destination;
(2) Aba Mappings;
(3) Coluna da Origem, em nosso caso as colunas do componente Aggregate;
(4) Colunas que receberão os dados;
(5) Relacionamento de origem com destino
(6) Clicar em OK para confirmar.
Agora sim, fluxo do Data Flow completo (1), está quase finalizado!
Último passo para finalizar a primeira parte do nosso post é incluir um componente que será usado para limpar o nosso destino antes da carga.
4.4 - Configurando o Componente Execute SQL Task
(1) Clicar na aba Control Flow;
(2) Clicar em SSIS Toolbox e selecionar o componente Execute SQL Task;
(3) Duplo clique no componente;
(4) No componente Execute SQL Task;
(5) Na aba General do componente Execute SQL Task;
(6) Informar o nome desse passo dentro do nosso fluxo;
(7) Selecionar a conexão, vamos usar a que criamos anteriormente;
(8) Clicar no botão para inserir o script;
(9) Incluir um script de TRUNCATE TABLE;
(10/11) Clicar em OK para finalizar.
A hora é essa, vamos testar nosso fluxo.
(1) Fluxo em execução;
(2) Vamos validar os dados carregados. Para isso vamos conectar em nosso servidor de banco de dados;
(3) Selecionar o banco de dados;
(4) Tabela que recebeu os dados;
(5) Consulta para listar os dados;
(6) Dados Carregos!
Sucesso meu amigo! Muito bem! Parabéns!!
4.5 - Alterando o Componente Aggregate, adicionando fluxos!
Agora vamos voltar dos passos e parar na tela abaixo.
Pois bem, lá atrás, falei que voltaria a falar sobre o item (2).
Nesse ponto existe a possibilidade de definir vários fluxos que podem ser direcionados após a execução do nosso componente Agrregate.
Para o exemplo, vou alterar para Basics e adicionar um outro fluxo que irá sumarizar o total de vagas por cargo.
Acompanha como que funciona.
Primeiro vamos voltar em nosso Source e acionar na aba Columns a coluna QT_VAGAS.
(1) Duplo clique no componente Source;
(2) Aba Columns;
(3) Selecionar a coluna QT_VAGAS;
(4) A coluna será carregada no grid;
(5) Clicar em OK para confirmar.
Vamos nesse ponto alterar nosso componente para adicionar outro fluxo.
(1) Duplo clique no componente Aggregate e alterar para Basic;
(2) Selecionar as colunas para o Fluxo;
(3) Após seleção as colunas serão carregadas no grid, na coluna Operation vamos selecionar a opção para cada coluna;
(4) O fluxo será criado nesse ponto e na coluna Aggregation Name vamos definir um nome para identificar essa etapa;
(5) Clicar em OK para confirmar a criação.
E para finalizar vamos criar o segundo fluxo.
(1) Duplo clique no componente Aggregate e alterar para Basic (realizado no passo anterior);
(2) Selecionar as colunas para o Fluxo;
(3) Após seleção as colunas serão carregadas no grid, na coluna Operation vamos selecionar a opção para cada coluna;
(4) O fluxo será criado nesse ponto e na coluna Aggregation Name vamos definir um nome para identificar essa etapa;
(5) Clicar em OK para confirmar a criação.
Em resumo, um fluxo será criado para carregar os cargos e o outro para contabilizar a quantidade de vagas por cargo.
Vamos criar um nova tabela para receber esses dados.
CREATE TABLE dbo.total_cargo(
CD_CARGO VARCHAR(200)
,DS_CARGO VARCHAR(200)
,QT_VAGAS int
)Após a criação da nossa tabela, vamos voltar para o nosso fluxo.
(1) Inclusão de dois componentes de destination;
(2) Clicar sobre o componente de Aggregate;
(3) Realizar a ligação entre o componente Aggregate e o Destination;
(4) Selecionar o fluxo Total VAGAS por CARGO;
(5) Clicar em OK para confirmar.
Após estabelecer o elo entre os objetos, vamos configurar o componente de Destination.
(1) Duplo clique no componente OLE DB Destination;
(2) Na aba Connection Manager;
(3) Selecionar a conexão com o banco de dados;
(4) Selecionar o modo de carga de dados;
(5) Selecionar em qual tabela os dados serão gravados;
(6) Podemos visualizar se existe algum registros em nosso destino;
(7) É possível realizar a criação de uma nova conexão;
(8) É possível criar uma tabela caso não exista;
(9) Clicar em OK para finalizar a configuração.
Vamos repetir o mesmo passo para estabelecer o link entre o outro fluxo e o outro destino.
(1) Duplo clique no componente OLE DB Destination;
(2) Na aba Connection Manager;
(3) Selecionar a conexão com o banco de dados;
(4) Selecionar o modo de carga de dados;
(5) Selecionar em qual tabela os dados serão gravados;
(6) Podemos visualizar se existe algum registros em nosso destino;
(7) Clicar em OK para finalizar a configuração caso não tenha nenhum configuração para ser feita.
E caso alguma outra configuração precise ser feita, podemos seguir os passos:
(8) É possível realizar a criação de uma nova conexão;
(9) É possível criar uma tabela caso não exista;
Configuração realizada, vamos seguir para a execução.
(1) Fluxo executado com sucesso;
(2) Para visualizar os dados carregados, vamos conectar em nosso servidor;
(3) Selecionar o banco de dados;
(4/5) Tabelas que receberão os dados;
(6) Consulta nas tabelas;
(7) Dados carregados.
Os arquivos usados até aqui estão disponíveis em:
5 - Conclusão
Muito bem meu amigo, mais um post finalizado. O objetivo de hoje foi apresentar para você um componente que permite aplicar as funções de agregação da linguagem SQL MIN, MAX, SUM, COUNT e AVG dentro do seu fluxo de ETL.
Esse componente é muito usado principalmente quando estamos construindo tabelas fatos que necessitam que os dados sejam sumarizados e agrupados por assuntos.
No próximo post vou abordar a aplicação do componente Row Count, muito usado quando queremos validar processos de carga pois permitir um controle maior sobre a quantidades de registros inseridos e atualizados.
Espero que goste desse post e claro quero encontrar com você na próxima semana. Um excelente dia para ti e fique com Deus!
Comments