Particionando Tabelas em SQL Server

O recurso de particionamento de tabelas do SQL Server pode ser muito útil durante o projeto de um data warehouse, pois permite otimizar a alocação de disco através da segmentação grandes tabelas de Fato em grupos de arquivos fisicamente isolados. Com esta funcionalidade, os discos podem ser otimizados para consulta massiva e esquemas de RAID que beneficiem a leitura podem ser usados em partes da tabela que são consultadas com mais frequência.

Em uma tabela de Fatos, sempre existe uma ou mais colunas que marcam a ocorrência de um evento, um atributo de data pode ser usado para o particionamento.

Por exemplo:
- todas as linas com data anterior a 2012 estão alocadas na primeira partição
- todas as linas com data anterior a 2013 estão alocadas na segunda partição
- todas as linas com data anterior a 2014 estão alocadas na terceira partição
- todas as linas com data 2015 ou superior, estão alocadas na quarta partição














* Se a coluna tiver algum valor nulo, este estará alocado na primeira partição.

Função de Particionamento

Para configurar o particionamento no SQL Server, o primeiro passo depois de definir a tabela e coluna que será usada como referência, será a criação da função de particionamento. Ela serve basicamente para definir os limite de cada partição.

-- Cria uma função de particionamento para ser usada em coluna do tipo DATE usando a opção RIGHT
CREATE PARTITION FUNCTION pfSales (DATE)
AS RANGE RIGHT FOR VALUES
('2013-01-01', '2014-01-01', '2015-01-01');

Range LEFT ou RIGHT

O exemplo acima usa a opção RANGE RIGHT para informar a função que deve ser considerada como parte da partição o valor informado e todos os valores maiores do que ele, na opção LEFT, seria o inverso.

Na imagem fica mais claro, na sequência como seria a partição com LEFT e RIGHT

Se fossemos usar a opção LEFT seria:

-- Cria uma função de particionamento para ser usada em coluna do tipo DATE usando a opção LEFT
CREATE PARTITION FUNCTION pfSales (DATE)
AS RANGE LEFT FOR VALUES
('2012-12-31', '2014-31-12', '2015-31-12');

Esquema de Partição

Com a função de particionamento criada, agora é possível definir o esquema que irá mapear os ranges às partições que serão alocadas aos filegroups (previamente criados e associados a arquivos de dados).

-- Cria o esquema que utiliza os limites definidos na função de particionamento e mapeia os filegroups para cada RANGE criado

CREATE PARTITION SCHEME myRangePS
    AS PARTITION pfSales
    TO (test1fg, test2fg, test3fg, test4fg) ;

Tabela Particionada

Com a função e o esquema criados, agora é hora de criar a tabela e apontar para o esquema configurado anteriormente.

-- Cria uma tabela particionada e utiliza a coluna col1 para segmentar de acordo com o esquema 
CREATE TABLE PartitionTable (id int PRIMARY KEY, col1 date))
    ON myRangePS (col1) ;
GO

Em resumo o processo consiste em criar os filegroups, definir a função, o esquema e criar a tabela utilizando o esquema.

Com isso os dados serão alocados em arquivos de dados específicos de acordo com os valores da col1.

Grupos de arquivos e arquivos usados pela partição.

https://www.cathrinewilhelmsen.net/2015/04/12/table-partitioning-in-sql-server/
https://docs.microsoft.com/pt-br/sql/relational-databases/partitions/create-partitioned-tables-and- indexes?view=sql-server-2017

Iterações em Conjuntos de Dados Com Cursores e Sem Cursores

Em SQL existem duas formas de fazer iterações sobre as linhas de uma tabela, acessando a cada ciclo um registro específico.


1-Acesso iterativo/procedural através do uso de CURSOR: Um objeto que carrega a tabela em memória e permite a iteração linha a linha através de variáveis.

2-Set-Based declarativo: Acessa os dados utilizando os princípios relacionais que aplicam as regras da teoria de conjuntos, que opera com tabelas como conjuntos de entradas

A linguagem de consulta SQL é diferente das linguagens de programação tradicionais, ela é essencialmente declarativa e não procedural, isso significa que você deve se preocupar em definir o que você quer e não como obter o que você quer.

Na teoria, é sempre indicado utilizar abordagem que utiliza operações Set-Based em tabelas e evitar operações com linhas individuais com cursores devido a problemas de performance.

Realizar operações com cursores acaba quebrando o princípio da teoria de conjuntos com o qual os bancos de dados relacionais foram criados, pois além de definir o quê será retornado é necessário definir como operar sobre esse dado.

Por outro lado, quando é usada a abordagem de conjuntos, não é possível garantir a ordenação precisa das linhas retornadas a menos que seja usado cuidadosamente a cláusula ORDER BY, para garantir o retorno de valores únicos para a ordenação. Já com iterações com cursores é possível garantir o controle total da ordenação.

Iteração com Cursores

Cursores são estruturas da linguagem T-SQL que permitem o processamento das linhas retornadas por uma consulta (SELECT), através de estruturas complexas de programação, como repetições ou  comandos condicionais.
O exemplo básico de cursor consiste em uma repetição (loop) onde um mesmo conjunto de comandos é executado para todas as linhas do retorno de uma consulta.

A sequência de declaração: DECLARE > OPEN > FETCH (loop) > CLOSE > DEALLOCATE

DECLARE @curcustid AS INT;

-- Cursor para percorrer os nomes dos objetos
DECLARE cust_cursor CURSOR FAST_FORWARD FOR 

  SELECT custid   FROM Sales.Customers;

  -- Abrindo Cursor para leitura
 OPEN cust_cursor;

-- Lendo a primeira linha e atribuindo às variáveis que serão atualizadas a cada iteração
FETCH NEXT FROM cust_cursor INTO @curcustid;

-- Percorrendo linhas do cursor (enquanto houver)
WHILE @@FETCH_STATUS = 0
BEGIN 

    EXEC Sales.ProcessCustomer @custid = @curcustid;

    FETCH NEXT FROM cust_cursor INTO @curcustid;
END;

-- Fechando Cursor para leitura
CLOSE cust_cursor;

-- Desalocando o cursor
DEALLOCATE cust_cursor;


Algumas considerações:
Um cursor deve estar sempre associado a uma consulta, especificada ao declarar o cursor.
O comando FETCH popula as variáveis recebidas como parâmetro com os valores da próxima linha da consulta a ser lida. O número de variáveis passadas como parâmetro deve ser igual ao número de colunas retornadas na consulta associada ao cursor.
A variável global @@FETCH_STATUS retorna o resultado da última operação FETCH executada por um cursor na conexão.
O status 0 significa que o comando FETCH retornou uma linha, qualquer outro resultado significa que não houve linha retornada.
Cursores são estruturas relativamente lentas se comparadas ao desempenho de consultas do banco. O uso descuidado desse método pode causar sérios problemas de performance.

Set-Based

Para obter o mesmo efeito e cumprir o mesmo tipo de tarefa usando somente instruções padrão sql sem usar cursores, você pode usar as cláusulas ORDER BY em conjunto com TOP(1) e um loop com WHILE.

Exemplo de iteração sem usar cursor:

DECLARE @curcustid AS INT;

--Seleciona o cliente com o menor número de código
SET @curcustid = (SELECT TOP (1) custid FROM Sales.Customers ORDER BY custid);

--Itera enquanto houver clientes com valores maiores que o atual
WHILE @curcustid IS NOT NULL
BEGIN
      EXEC Sales.ProcessCustomer @custid = @curcustid;
   
    --Atribui o valor de código do próximo cliente
     SET @curcustid = (SELECT TOP (1) custid FROM Sales.Customers WHERE custid > @curcustid ORDER BY custid);
END;
GO

Nestes casos é necessário criar indice para a coluna custid, caso contrário a tabela será toda escanada e a performance será pior do que um cursor.

Carga Incremental com SQL e Integration Services - SSIS/TSQL Incremental Load

O processo de carga e transformação em um modelo de data warehouse apresenta alguns desafios que podem ser abordados de diferentes formas, dependendo principalmente do volume de dados envolvido. Nos casos em que o volume é pequeno e o tempo de carga é baixo, uma das estratégias mais comum é a carga FULL. Nesta abordagem, o banco de dados do DW é truncado, todos seus registros são apagados e recarregados com informações atualizadas, mantendo assim a integridade entre a fonte e o DW. Apesar de ser uma técnica que mantem a integridade, ela acaba carregando dados que não foram alterados, sem necessidade, gerando um gargalo em bancos de dados maiores.

Neste artigo vamos abordar algumas alternativas para realizar uma carga incremental.

Basicamente, todos os exemplos seguem a mesma lógica que se baseia em comparar uma base de ORIGEM com uma base de DESTINO (DW) e:

1-Detectar novos registros. Registros que existem na origem mas não existem no destino
2-Detectar registros atualizados: Registros que existem em ambos bancos mas possui algum campo com valor diferente
3-Opcionalmente deletar registros no destino que foram deletados ou não existem mais na origem.


Para nossos exemplos, vamos criar algumas tabelas e inserir alguns dados:

--Criar bancos de origem e destino
CREATE DATABASE [SSISIncrementalLoad_Source]
CREATE DATABASE [SSISIncrementalLoad_Dest]

--Criar uma tabela no banco de origem
CREATE TABLE dbo.tblSource
(ColID int NOT NULL
,ColA varchar(10) NULL
,ColB datetime NULL constraint df_ColB default (getDate())
,ColC int NULL
,constraint PK_tblSource primary key clustered (ColID))

--Criar uma tabela no banco de destino
CREATE TABLE dbo.tblDest
(ColID int NOT NULL
,ColA varchar(10) NULL
,ColB datetime NULL
,ColC int NULL)

--Inserir alguns dados na origem
USE SSISIncrementalLoad_Source
GO
INSERT INTO dbo.tblSource (ColID,ColA,ColB,ColC) VALUES(0, 'A', '200180101', -1)
INSERT INTO dbo.tblSource (ColID,ColA,ColB,ColC) VALUES(1, 'B', '20180102', -2)
INSERT INTO dbo.tblSource (ColID,ColA,ColB,ColC) VALUES(2, 'N', '20180103', -3)


--Inserir alguns dados no destino
USE SSISIncrementalLoad_Dest
GO
--Inserir uma linha não alerada
INSERT INTO dbo.tblDest(ColID,ColA,ColB,ColC) VALUES(0, 'A', '200180101', -1)
--Inserir linha alterada
INSERT INTO dbo.tblDest (ColID,ColA,ColB,ColC) VALUES(1, 'C', '20190101', -2)


--Consultar novas linhas
SELECT s.ColID, s.ColA, s.ColB, s.ColC
FROM SSISIncrementalLoad_Source.dbo.tblSource s
LEFT JOIN SSISIncrementalLoad_Dest.dbo.tblDest d ON d.ColID = s.ColID
WHERE d.ColID IS NULL

Usando SQL Padrão para identificar linhas novas e alteradas:

Para iniciar, vamos aos exemplos básicos de como inserir, dados novos e atualizados:

Para inserir novas linhas, o segredo está na cláusula WHERE onde são filtradas linhas que não existem no destino, logo são linhas novas.

--Inserir no destino apenas as linhas novas
INSERT INTO SSISIncrementalLoad_Dest.dbo.tblDest (ColID, ColA, ColB, ColC)
SELECT s.ColID, s.ColA, s.ColB, s.ColC
FROM SSISIncrementalLoad_Source.dbo.tblSource s
     LEFT JOIN SSISIncrementalLoad_Dest.dbo.tblDest d ON d.ColID = s.ColID
WHERE d.ColID IS NULL

No processo de atualização, é feito um JOIN para identificar apenas as linhas que as colunas da origem e destino são diferentes e a atualização é feita apenas nestes casos.

--Atualizar dados no destino com as linhas alteradas.
UPDATE d
SET
   d.ColA = s.ColA
   ,d.ColB = s.ColB
   ,d.ColC = s.ColC
FROM SSISIncrementalLoad_Dest.dbo.tblDest d
     INNER JOIN SSISIncrementalLoad_Source.dbo.tblSource s ON s.ColID = d.ColID
WHERE (
(d.ColA != s.ColA)
OR (d.ColB != s.ColB)
OR (d.ColC != s.ColC)
)

Implementação do padrão utilizando o SSIS

Agora que vimos o padrão em sql, vamos criar um processo ETL utilizando o Integration Services.

Crie um pacote e inclua um objeto control flow do tipo data flow. Utilize as tarefas de Lookup, Condicional split e Row Command para realizar a a tarefa.

1-Criar uma conexão Origem OLE DB: Conexão com tabela de origem

2-LEFT JOIN : Criar uma tarefa de Lookup apontando para a tabela de destino

Fazer a junção entre origem e destino através da coluna ColID e incluir o sulfixo Dest_ nas novas colunas retornadas. Isso irá ajudar na diferenciação.

Como por padrão a tarefa Lookup faz Inner Join e não Left Join, temos que marcar a opção de ignorar as falhas de saída. Isso vai fazer com que os casos que não houver correspondência exata entre os IDs de origem e destino, seja retornado NULL na coluna da tabela de destino. O valor NULL será usado para identificar a ausência do registro na tabela de destino.

3-Divisão Condicional: Criar uma tarefa "Condicional Split" para separar o fluxo de novas linhas e linhas atualizadas.

New Rows: Quando a coluna ID do destino for null, isso significa que o lookup não achou, logo é novo

Changed Rows: Compara as colunas da origem e destino para identificar as diferenças, se houver alguma retorna.

Unchanged: Todos os demais casos.

4-Destino OLE DB: Crie uma tarefa de destination para enviar e inserir o fluxo de novas linhas diretamente para a tabela de destino.

5-Update Destino Linha-por-linha: Inserir Linhas Alteradas com a tarefa OLE DB Command. Conecte o fluxo de linhas alteradas em uma tarega OLE DB Command.

Essa tarefa executa um comando sql para cada linha do fluxo de dados.

Configure as propriedades e informe o comando UPDATE com os parâmetros para serem mapeados na sequência.

Mapeando os Parâmetros
Fazer o mapeamento do fluxo de entrada de dados com o comando sql.

As colunas da tabela destino serão atualizadas com o conteúdo da tabela de origem com base no ID (mapeado na clausula WHERE).

A sequencia que os parâmetros aparecem no comando é a sequência de numeração do nome do parâmetro.

Esse padrão de carga incremental funciona bem, mas pode ser demorado para cargas muito volumosas, para isso existe uma variação para cargas maiores que realizam operações em conjuntos de dados e não em linha por linha, como no exemplo acima.

Padrão ETL Incremental Set-Based

Este exemplo utiliza uma tabela intermediaria stage para enviar as linhas alteradas e fazer a atualização em conjunto.

1- Truncate Stage Update: O primeiro passo é garantir que a tabela stage esteja truncada, sem linha alguma. Para isso utilize um control flow para executar um comando sql

2-Incremental Load Flow: O segundo passo, implementa o mesmo processo de carga padrão descrito anteriormente, mas ao invés de executar a atualização linha por linha, a tabela stage será populada com o fluxo de dados de linhas atualizadas.

3- Set-Based Update: A terceira etapa executa um comando SQL para realizar a atualização a tabela destino com os dados da stage

Pronto, desta maneira o processo de atualização é feito em conjunto, o processo desta forma é muito mais eficiente.

Carga Incremental utilizando o comando MERGE do T-SQL

Como alternativa ainda existe o comando MERGE disponível no T-SQL do SQL Server a partir de 2012.

Ele aplica a mesma lógica em um código bem enxuto e de fácil leitura. Ele define a origem e destino, identifica a coluna de junção nos casos de linhas alteradas, especifica as colunas que serão comparadas e ainda traz a possibilidade de fazer a exclusão.


MERGE INTO [dbo].[tblDest] AS TGT --Alvo
USING [dbo].[tblSource] AS SRC --Fonte

--Faz a comparação das linhas de SRC com TGT
ON SRC.ColID = TGT.ColID

--Quando a linha na fonte for igual a linha que já existe no alvo e tiver algum campo com valor diferente entre elas
WHEN MATCHED AND ( TGT.ColA <> SRC.ColA OR TGT.ColB <> SRC.ColB OR TGT.ColC <> SRC.ColC)
 THEN
   UPDATE
      SET TGT.ColA = SRC.ColA,
          TGT.ColB = SRC.ColB,
          TGT.ColC = SRC.ColC

--Quando a linha da fonte não existir no alvo, insere como nova linha
WHEN NOT MATCHED
    THEN
      INSERT
      VALUES(SRC.ColID, SRC.ColA, SRC.ColB, SRC.ColC)

--Quando existe alguma linha no alvo que não existe na fonte. DELETE (opcional)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;

Estas são algumas técnicas relacionadas ao processo de carga ETL incremental, muito útil em situações onde é necessário manter a integridade entre a origem e o destino mas não é possível realizar uma carga full devido ao volume de dados.


Fonte:
https://andyleonard.blog/2007/07/ssis-design-pattern-incremental-loads/
http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/72492/
https://docs.microsoft.com/pt-br/sql/t-sql/statements/merge-transact-sql?view=sql-server-2017