E ai meu amigo como vai?
Hoje não vou enrolar muito vocês, vamos ser mais objetivo e ir direto ao nosso assunto, sem delongas. Primeira pergunta: Durante o seu processo de análise de dados, quando está analisando os dados de um arquivo excel você já precisou transformar as linhas desse arquivo em colunas? E fazer ao contrário, transformar colunas em linhas?
No excel isso é bem simples de realizar, podemos montar uma tabela dinâmica e entregar esse resultado fácil fácil.
Agora pense em outro cenário. Você está realizando consultas dentro do seu banco de dados, seja ele qual for, e precise por exemplo realizar a mesma operação de transformar linhas em colunas ou vice-versa, como fazer isso?
Então, diante dessas perguntas tenho certeza que você já sabe que estaremos abordando hoje a técnica de Pivot e Unpivot. Podemos estar realizando isso pelo próprio client do SGBD por exemplo SSMS ou Oracle Develper, mais hoje vamos usar os componentes Unpivot e Pivot do SSIS.
Isso mesmo, um assunto mega bacana que vai enriquecer seus conhecimentos. Bora começar então?
Segue aqui comigo!
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, vamos utilizar duas, uma para conexão como nosso arquivo CSV e outra para conectar com o SQL Server.
Vamos começar configurando o a conexão com o arquivo CSV.
(1) Aba Connection Manage;
(2) Clicar com o botão direito do mouse e escolher New Flat File Connection;
Após selecionar esse tipo de conexão, vamos partir para configuração.
(1) Informar um nome para a conexão;
(2) Primeira configuração será na aba General;
(3) Selecionar o local onde está o nosso arquivo;
(4) Configurações referente ao nosso arquivo (Se ele é delimitado ou não, se possui um qualificador de texto, se a primeira linha do arquivo contém ou não o cabeçalho das colunas etc);
(5) Clicar em OK para confirmar.
Agora vamos para outra aba.
(1) O nome que você informou na tela anterior é carregado em todas as abas seguintes, não precisa informar;
(2) Vamos configurar a aba Columns;
(3) Selecionar o delimitador de linhas;
(4) Selecionar o delimitador de colunas;
(5) Uma prévia dos dados. Observe que o cabeçalho foi carregado pois na tela anterior informamos que a primeira linha do arquivo contém o cabeçalho;
(6) Caso nenhuma configuração a mais precise ser feita, podemos concluir a configuração clicando em OK.
Seguindo na configuração do nosso Source...
(1) O nome que você informou na tela anterior é carregado em todas as abas seguintes, não precisa informar;
(2) Vamos configurar a aba Advanced;
(3) Nome da colunas;
(4) Nesse passo conseguimos visualizar informações referente ao nome da coluna, datatype, tamanho, tudo isso pode ser alterado;
(5) Nesse ponto é possível incluir ou eliminar uma coluna;
(6) Caso nenhuma configuração a mais precise ser feita, podemos concluir a configuração clicando em OK.
Agora vamos para o último ponto da configuração. Na aba Preview como o próprio nome diz, é uma prévia dos dados e além disso você pode também definir a quantidade de linhas que serão descartadas.
(1) O nome que você informou na tela anterior é carregado em todas as abas seguintes, não precisa informar;
(2) Vamos configurar a aba Preview;
(3) Podemos definir a quantidade de linhas que serão descartadas;
(4) Prévia dos dados;
(5) Clicar em OK para finalizar a configuração.
Após a criação do conector com o arquivo, vamos criar a conexão com o banco pois ela será usada em nosso destino.
(1) Aba Connection Manage;
(2) Clicar com o botão direito do mouse e escolher New OLE DB Connection;
Na próxima tela vamos iniciar a definição da conexão, tipo de conector, servidor, tipo de conexão, usuário e senha caso necessário e banco de dados. 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;
(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.
Pronto! Conexões criadas! Vamos configurar agora os componentes que serão usados no processo de carga.
4 - Aplicando o Unpivot nos dados de um arquivo
4.1 - 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 e em seguida clicar em Rename;
(3) Conexões disponíveis.
Após incluir nosso componente de Data Flow, vamos clicar duas vezes e selecionar os componentes que iremos usar!
4.1.1 - Configurando o Componente Source Assistant
Conforme imagem anterior, vamos fazer uso de 4 componentes! Uma para a nossa origem de dados, uma para aplicar a técnica de Unpivot, ou seja, criar colunas em linhas, uma para converter os dados e uma para gravar os dados em nosso destino.
Bora começar pelo Source Assistant!
(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.
Simples assim! Bora pro próximo!
4.1.2 - Configurando o Componente Unpivot
Nesse momento vamos trabalhar no componente de destaque desse post, o Unpivot.
O que pretendo trazer hoje para você é uma atividade muito comum na área dos dados. Quem nunca precisou pegar os dados de uma planilha dispostos em colunas, ou seja, conforme nosso exemplo, vendas de uma rede lojas que possui uma coluna para cada mês do ano e diante disso, precise transformar esse dados em linhas.
Assim é a forma como você recebe os dados.
Mais precisa trabalhar com eles assim!
E como fazer isso pelo SSIS? Muito simples, acompanha aqui...
(1) Em SSIS Toolbox, vamos selecionar o componente Unpivot;
(2) Ligar o componente de Source com o componente Unpivot;
(3) Duplo clique sobre o componente Unpivot;
(4) Quando selecionamos as colunas, eles são carregadas na coluna Pivot Key Vault. Observe que existe uma coluna chamada Destination Column que é destinada ao dado da coluna que será transformada em linha. Caso não informe um nome para essa coluna (5), o dado não é carregado. Para que seja carregado, basta informar um nome para a coluna de saída;
(6) Existe também a possíbilidade de informar um nome para a coluna Pivot Key Value, caso não informe, será apresentado no output do componente com o nome Pivot Key Value;
(7) Clicar em OK para confirmar.
Analise o output e a comparação que realizo com o arquivo!
Genial meu jovem mancebo!
Bora avança que está ficando bom!
4.1.3 - Configurando o Componente Derived Column
Após incluir nosso componente de Source, o componente de Unpivot, vamos trabalhar com o componente Derived Column para poder tratar alguns dados!
(1) Em SSIS Toolbox, vamos selecionar o componente Derived Column, vamos dar um duplo clique sobre ele e observe a tela que será carregada no centro da tela;
(2) Vamos tratar os dados referente ao nome da Loja e ao Mês pois eles foram carregados como Unicode do arquivo. A conversão é bem simples, vamos transforma-los em um dado do tipo String;
(3) Quando convertemos um dado para string ele deve possuir o seguinte layout: (TIPO_DO_DADO, TAMANHO_DO_DADO, ENCODING). Em nosso exemplo seguindo essa regra, em TIPO_DO_DADO vamos informar DT_STR;
(4) Em TAMANHO_DO_DADO vamos definir 100;
(5) E para o ENCODING, 1252 que é referente a ANSI Latin 1;
(6) Podemos definir um fluxo de tratamento de erro caso ocorra durante a transformação porém não iremos fazer essa abordagem nesse post;
(7) Clicar em OK para confirmar caso esteja tudo pronto.
4.1.4 - Configurando o Componente Destination Assistant
Após a configuração dos 3 componente de Source, Unpivot e Derived Column, vamos finalizar nosso fluxo com a configuração do nosso componente de Destination.
Bora lá então.
(1) Em SSIS Toolbox, vamos selecionar o componente Destination 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 ao destino do arquivo é apresentada;
(6) Clicar em OK para confirmar.
Componente incluído com sucesso, vamos realizar a criação da tabela que irá receber os dados que iremos importar. Após a imagem, deixo o script de criação da tabela!
/****** Object: Table [dbo].[VENDAS] Script Date: 10/12/2021 20:37:54 ******/
IF EXISTS (SELECT 1 FROM SYS.tables T, sys.schemas S WHERE T.SCHEMA_ID = S.SCHEMA_ID AND T.NAME = 'VENDAS' AND S.NAME = 'dbp')
DROP TABLE [dbo].[VENDAS]
GO
/****** Object: Table [dbo].[VENDAS] Script Date: 10/12/2021 20:37:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[VENDAS](
[NOME_LOJA] [varchar](100) NULL,
[MES] [varchar](100) NULL,
[VALOR] [decimal](23, 3) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GOApós a criação da tabela no banco, o próximo passo é o mesmo que realizamos quando configuramos o Source. Bora cabra bom!!!!
(1) Duplo clique no componente OLE DB Destination;
(2) Na aba Connection Manager;
(3) Selecionar a conexão com o banco de dados;
(4) Nesse passo é possível realizar a criação de uma nova conexão;
(5) Selecionar o modo de carga de dados;
(6) É possível criar uma tabela caso não exista;
(7) Selecionar em qual tabela os dados serão gravados;
(8) Podemos visualizar se existe algum registros em nosso destino;
(9) Clicar em OK para finalizar a configuração.
Próximo passo é para realizar o mapeamento das colunas da origem com o destino.
(1) No componente OLE DB Destination;
(2) Na aba Mappings;
(3) Mapeamento entre a origem e o destino;
(4) Grid com o mapeamento;
(5) Clicar em OK para finalizar a configuração.
Antes de gravar no destino, vamos colocar um debug para analisar os dados.
(1) Clicar sobre a linha azul do componente e selecionar Enable Data Viewer;
(2) Clicar na aba Data Viewer;
(3) Clicar para habilitar o debug;
(4) De todas as colunas aqui ficam as que não serão apresentadas;
(5) Colunas que foram selecionadas;
(6) Clicar em OK para finalizar a configuração.
Finalizada a configuração do debug, vamos executar o fluxo!
Pronto! Ai está, tudo conforme planejado!
Pronto, dados inseridos no destino!
4.1.5 - 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) Informar o nome desse passo dentro do nosso fluxo;
(5) Selecionar a conexão, vamos usar a que criamos anteriormente;
(6) Incluir um script de TRUNCATE TABLE;
(7 e 8) Clicar em OK para finalizar.
Observe agora os dados em nosso banco de dados.
Muito bom aspira! Dados carregados e transformação de colunas em linhas realizada com sucesso.
Agora vamos falar sobre o segundo ponto do nosso post.
Imagina que na sua empresa você tenha recebido os dados conforme a tela acima. Uma coluna com o nome da Loja, uma com o Mês e a outra com o valor e precise distribuir esses valores de mês e valor que estão em linha para colunas.
Essa ação chamamos de Pivot. Veja como que fica!
5 - Aplicando o Pivot em uma tabela
5.1 - 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 e em seguida clicar em Rename;
(3) Conexões disponíveis.
Vou ser amigo de nove e adiantar o assunto! Apresento para você como que será o resultado final desse nosso trabalho (1).
5.1.1 - Configurando o Componente Source Assistant
Conforme imagem anterior, vamos fazer uso de 3 componentes! Um para a nossa origem de dados, um para o destino dos dados e um que irá permitir que façamos transposição dos dados de linha para coluna.
(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 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;
(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) Selecionar a origem, ou tabela ou script, depende do passo anterior;
(7) É possível visualizar uma prévia dos dados;
(8) E clicar em OK para finalizar.
5.1.2 - Configurando o Componente Pivot
O próximo passo é onde realizamos a transposição da nossa tabela. Existe três pontos cruciais nessa atividade. Vamos devagar!
(1) Em SSIS Toolbox, vamos selecionar o componente Pivot;
Após duplo clique no componente vamos para a tela que será carregada no centro da nossa tela;
(3) Set Key - Responsável por receber os valores agrupados;
(2) Pivot Key - Coluna que será usada como cabeçalho;
(4) Pivot Value - Valor que será especificado para a coluna que foi transforma em cabeçalho.
(5) Definir os valores que serão transformados em coluna;
(6) Clicar em Generate Columns Now;
(7) Coluna que serão criadas no output do componente;
(8) Após clicar em OK os dados serão carregados no próximo passo;
(9) Colunas serão apresentadas aqui.
Após o passo (8) os nomes das colunas são carregadas conforme tela abaixo.
5.1.3 - Configurando o Componente Destination Assistant
Após a configuração dos 2 componente de Source e Pivot, vamos finalizar nosso fluxo com a configuração do nosso componente de Destination.
Bora lá então.
(1) Em SSIS Toolbox, vamos selecionar o componente Destination 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 ao destino do arquivo é apresentada;
(6) Clicar em OK para confirmar.
De modo semelhante ao realizado acima, vamos precisar criar uma tabela para receber nossos dados. Após a imagem, deixo o script de criação da tabela!
/****** Object: Table [dbo].[VENDAS_MES] Script Date: 10/12/2021 21:32:34 ******/
IF EXISTS (SELECT 1 FROM SYS.tables T, sys.schemas S WHERE T.SCHEMA_ID = S.SCHEMA_ID AND T.NAME = 'VENDAS_MES' AND S.NAME = 'dbp')
DROP TABLE [dbo].[VENDAS_MES]
GO
/****** Object: Table [dbo].[VENDAS_MES] Script Date: 10/12/2021 21:32:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[VENDAS_MES](
[NOME_LOJA] [varchar](100) NULL,
[JANEIRO] [decimal](23, 3) NULL,
[FEVEREIRO] [decimal](23, 3) NULL,
[MARCO] [decimal](23, 3) NULL,
[ABRIL] [decimal](23, 3) NULL,
[MAIO] [decimal](23, 3) NULL,
[JUNHO] [decimal](23, 3) NULL,
[JULHO] [decimal](23, 3) NULL,
[AGOSTO] [decimal](23, 3) NULL,
[SETEMBRO] [decimal](23, 3) NULL,
[OUTUBRO] [decimal](23, 3) NULL,
[NOVEMBRO] [decimal](23, 3) NULL,
[DEZEMBRO] [decimal](23, 3) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Após a criação da tabela no banco, o próximo passo é o mesmo que realizamos quando configuramos o Source. Bora cabra bom!!!!
(1) Realizar a ligação entre os componentes;
(2) Duplo clique no componente OLE DB Destination;
(3) Na aba Connection Manager;
(4) Selecionar a conexão com o banco de dados;
(5) Nesse passo é possível realizar a criação de uma nova conexão;
(6) Selecionar o modo de carga de dados;
(7) Selecionar em qual tabela os dados serão gravados;
(8) É possível criar uma tabela caso não exista;
(9) Podemos visualizar se existe algum registros em nosso destino;
(10) Clicar em OK para finalizar a configuração.
Próximo passo é para realizar o mapeamento das colunas da origem com o destino.
(1) Duplo clique no componente OLE DB Destination e após abrir a tela clicar na aba Mappings;
(2) Mapeamento entre a origem e o destino;
(3) Grid com o mapeamento;
(4) Clicar em OK para finalizar a configuração.
Antes de gravar no destino, vamos colocar um debug para analisar os dados.
(1) Clicar sobre a linha azul do componente e selecionar Enable Data Viewer;
(2) Clicar na aba Data Viewer;
(3) Clicar para habilitar o debug;
(4) Colunas que foram selecionadas;
(5) Clicar em OK para finalizar a configuração.
Podemos visualizar na tela abaixo que os dados ficaram exatamente iguais da nossa planilha.
5.1.4 - 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) Informar o nome desse passo dentro do nosso fluxo;
(5) Selecionar a conexão, vamos usar a que criamos anteriormente;
(6) Incluir um script de TRUNCATE TABLE;
(7 e 8) Clicar em OK para finalizar.
Vamos analise como que ficou o nosso fluxo após a execução!
Sensacional! Dados carregados conforme definimos, mais um sucesso para comemorar!
Agora aquele plus!
Caso queira realizar essa mesma operaçãod e Pivot e Unpivot via SSMS no SQL Server, basta executar os comandos abaixo.
Comando Unpivot
-------------------------------------------------------------------------
-- COMANDO UNPIVOT
-------------------------------------------------------------------------
SELECT
[NOME_LOJA]
,[MES]
,[VALOR]
FROM
(
SELECT DISTINCT
[NOME_LOJA]
, [JANEIRO]
, [FEVEREIRO]
, [MARCO]
, [ABRIL]
, [MAIO]
, [JUNHO]
, [JULHO]
, [AGOSTO]
, [SETEMBRO]
, [OUTUBRO]
, [NOVEMBRO]
, [DEZEMBRO]
FROM [dbo].[VENDAS_MES]
) src
UNPIVOT
(
[MES] FOR VALOR IN ([JANEIRO]
, [FEVEREIRO]
, [MARCO]
, [ABRIL]
, [MAIO]
, [JUNHO]
, [JULHO]
, [AGOSTO]
, [SETEMBRO]
, [OUTUBRO]
, [NOVEMBRO]
, [DEZEMBRO])
) upvtComando Pivot
-------------------------------------------------------------------------
-- COMANDO PIVOT
-------------------------------------------------------------------------
SELECT
[NOME_LOJA]
, [JANEIRO]
, [FEVEREIRO]
, [MARÇO]
, [ABRIL]
, [MAIO]
, [JUNHO]
, [JULHO]
, [AGOSTO]
, [SETEMBRO]
, [OUTUBRO]
, [NOVEMBRO]
, [DEZEMBRO]
FROM
(
SELECT DISTINCT
[NOME_LOJA]
,[MES]
,[VALOR]
FROM [dbo].[VENDAS]
) src
PIVOT
(
SUM([VALOR]) FOR [MES] IN ([JANEIRO]
, [FEVEREIRO]
, [MARÇO]
, [ABRIL]
, [MAIO]
, [JUNHO]
, [JULHO]
, [AGOSTO]
, [SETEMBRO]
, [OUTUBRO]
, [NOVEMBRO]
, [DEZEMBRO])
) pvt
ORDER BY [NOME_LOJA]Os arquivos usados até aqui estão disponíveis em:
Script de Create das tabelas dbo.VENDAS e dbo.VENDAS_MES
09_SCRIPT_CREATE_TABLE_VENDAS.sql
10_SCRIPT_CREATE_TABLE_VENDAS_MES.sql
11_SCRIPT_COMANDO_PIVOT_UNPIVOT.sql
Pacote SSIS - Row Sampling
11_Importa_Cargos_Pivot.dtsx
11_Importa_Cargos_Unpivot.dtsx
Arquivo de Dados
vendas.csv
6 - Conclusão
Muito bem meu amigo!
Mais um assunto finalizado. Hoje trouxe para você uma técnica que aplico muito em meus fluxos de dados, seja via SSIS, TSQL script ou até mesmo via Python.
Espero que tenham gostado. Nos vemos em breve!
Fique com Deus!
Comments