Partition switching w SQL Server

W przypadku hurtowni danych podczas ładowań danych do tabeli faktów bardzo często zdarza się, że chcemy zastąpić dane za określony okres (np. miesiąc)  świeżymi danymi z systemów źródłowych. Może to być wykonane na wiele sposobów, dla przykładu możemy usunąć z tabeli ostatni miesiąc komendą DELETE i wstawić nowe dane. Wiąże się to z wieloma niedogodnościami, po pierwsze operacja usunięcia będzie w pełni logowana, po drugie samo wyszukanie danych do usunięcia może być problematyczne. Dodatkowo możemy mieć problemy związane z lockami i blokadami. Powszechnie znanym faktem jest to, że jednymi z najszybszych operacji w SQL Server są operacje na metadanych, które oprócz tego, że nie “dotykają” danych są w optymalny sposób logowane w dzienniku transakcji. Czy możemy wykorzystać jakiś mechanizm tego typu podczas ładowania danych? Oczywiście, że tak! Odpowiedzią jest partition switching o którym chciałem parę słów napisać w ramach niniejszego artykułu.

Czym jest partition switching? Jest to nic innego jak podmiana partycji – przykładowy rysunek koncepcyjny możecie znaleźć poniżej:

Do tabeli A ładowane są dane za rok 2017 – składa się ona z jednej partycji przeznaczonej właśnie na dane z roku 2017. Po załadowaniu danych następuje przełączenie metadanych tj. metadane z tabeli B nie wskazują już pustej partycji tylko partycję wypełnioną danymi za rok 2017:

Oczywiście fizycznie nie występuje żaden ruch danych dzięki czemu ta operacja jest bardzo szybka, żeby nie powiedzieć natychmiastowa. Abu móc w ten sposób przełączać partycje musi być spełnione kilka wymogów – najważniejsze z nich to:

• docelowa partycja musi być pusta (nie ma możliwości użycia tego mechanizmu jeśli tabela docelowa zawiera dane)
• tabela/partycja źródłowa muszą być partycjonowane po tym samym kluczu i mieć tą samą strukturę
• tabela/partycja źródłowa muszą znajdować się w tej samej grupie plików

Tak jak wspomniałem nie wszystkie wymogi jednakże na ten moment to nam wystarczy. Sprawdźmy omawiany mechanizm na konkretnym przykładzie – stwórzmy sobie dwie tabele będące kopią wybranej tabeli. Nazwijmy je kolejno Source oraz Target:

SELECT *
INTO dbo.Source
FROM fact.Sale;
GO
SELECT *
INTO dbo.Target
FROM fact.Sale;
GO

Następnie stwórzmy schemat oraz funkcję partycjonującą:

CREATE PARTITION FUNCTION udf_partitionByYear(date) 
AS RANGE RIGHT 
FOR VALUES(
     '20130101'
    ,'20140101'
    ,'20150101'
    ,'20160101');
GO

CREATE PARTITION SCHEME ups_partionByYear 
    AS PARTITION udf_partitionByYear
    ALL TO ([PRIMARY])
GO

Kiedy mamy już składowe nie pozostaje nam nic innego jak stworzyć na obu tabelach indeks klastrowany ze wskazaniem na schemat partycjonujący:

CREATE CLUSTERED INDEX CIX_Source ON dbo.Source ([WWI Invoice ID])
ON ups_partionByYear ([Invoice Date Key])
GO

CREATE CLUSTERED INDEX CIX_Target ON dbo.Target ([WWI Invoice ID])
ON ups_partionByYear ([Invoice Date Key])
GO

W następnym kroku odpytajmy metadane naszych partycji aby zobaczyć podsumowanie oraz numery partycji przypisanych do konkretnych lat po, których partycjonowaliśmy (oczywiście część zawartą w cte można wyliczyć dynamicznie jednakże póki co wartości statyczne wyliczone w taki sposób nam wystarczą):

with ctePartitionInfo
AS
(
SELECT $partition.udf_partitionByYear('20130101') AS PartitionNo, 2013 AS PartitionYear
UNION ALL
SELECT $partition.udf_partitionByYear('20140101') AS PartitionNo,2014 AS PartitionYear
UNION ALL
SELECT $partition.udf_partitionByYear('20150101') AS PartitionNo, 2015 AS PartitionYear
UNION ALL
SELECT $partition.udf_partitionByYear('20160101') AS PartitionNo, 2016 AS PartitionYear
)
SELECT OBJECT_NAME(sp.object_id) AS tableName,
       sp.partition_number AS partitionNumber,
	  sp.rows,
	  cpi.partitionYear
FROM sys.partitions AS sp
LEFT JOIN ctePartitionInfo AS cpi
ON cpi.PartitionNo=sp.partition_number
WHERE sp.object_id IN(OBJECT_ID('dbo.Target'), OBJECT_ID('dbo.Source'));

Wynik naszego zapytania przedstawia się następująco:

Mamy w tym momencie dokładnie dwie takie same tabele. Przypuśćmy, że z tabeli docelowej (target) chcemy się pozbyć danych z roku 2016. Obecnie w SQL Server 2016 mamy do dyspozycji specjalną odmianę polecenia TRUNCATE TABLE, która potrafi wyczyścić dane pojedynczej partycji, osiągniemy to w następujący sposób:

TRUNCATE TABLE dbo.Target
WITH (PARTITIONS (5))

Numer 5 w powyższym zapytaniu to nic innego jak numer partycji. Odpytajmy nasze metadane raz jeszcze i przekonajmy się czy nasza partycja została wyczyszczona:

Póki co wszystko działa zgodnie z planem. W tym miejscu może pojawić się pytanie co gdy nie mamy SQL Server 2016 i polecenia TRUNCATE czyszczącego partycje? Mamy parę tricków, które dokonają dokładnie tego samego (jeden z nich został opisany przez Kamila Nowińskiego tutaj). Przypuśćmy, że chcemy teraz przełączyć partycję z danymi za rok 2016 z tabeli Source do tabeli Target, osiągniemy to w bardzo łatwy i szybki sposób następującym poleceniem:

ALTER TABLE dbo.Source 
SWITCH PARTITION 5 TO dbo.Target PARTITION 5
GO

Zapytanie wykonało się błyskawicznie – sprawdźmy metadane:

Powyższy zrzut w bardzo fajny sposób obrazuje, że właśnie dokonaliśmy podmiany metadanych. Partycje jak i dane pozostały na swoim miejscu, a zmieniło się jedynie ich przyporządkowanie do tabel.

Oczywiście powyższy scenariusz nie jest jedynym możliwym. Możemy również podmieniać tabelę bez jawnego partycjonowania ze względu na fakt, iż tak naprawdę każda tabela ma minimum jedną partycję. Sprawdźmy to na kolejnym przykładzie – na początku tworzymy dwie tabele, jedna z nich będzie pusta (dbo.Target2), a druga będzie zawierała określone dane (dbo.Source2):

SELECT *
INTO dbo.Source2
FROM fact.Sale
WHERE [Invoice Date Key] BETWEEN '20160101' AND '20161231';
GO
SELECT TOP 0 *
INTO dbo.Target2
FROM fact.Sale;
GO

Po wykonaniu powyższych zapytań podejrzyjmy metadane obu tabel:

Potwierdziła się nasza teoria, że każda tabela posiada minimum jedną partycję. Teraz sprawdźmy czy uda nam się podmienić partycje pomiędzy oboma tabelami. W tym przypadku nie musimy podawać konkretnych numerów partycji (chociaż oczywiście możemy) ponieważ dla SQL Servera jest to oczywiste:

ALTER TABLE dbo.Source2 
SWITCH TO dbo.Target2 
GO

Cała operacja przebiegła pomyślnie:

Myślę, że w tym miejscu warto przedstawić kilka scenariuszy, w których możliwe jest przełączanie partycji. Tak więc możemy przełączać z:

• tabeli niepartycjonowanej do tabeli partycjonowanej
• tabeli partycjonowanej do tabeli niepartycjonowanej
• tabeli partycjonowanej do tabeli partycjonowanej
• tabeli niepartycjonowanej do tabeli niepartycjonowanej

Czyli mamy całkiem spore pole do popisu i pewną swobodę w definiowaniu naszych struktur ponieważ każdy scenariusz jest możliwy do implementacji jeśli tylko wymienione wcześniej wymagania zostały spełnione.

Dodatkowo warto wspomnieć o tym, że podobnie jak operację TRUNCATE, tak również PARTITION SWITCTH  możemy kontrolować w transakcji i w razie potrzeby wycofać:

SELECT OBJECT_NAME(sp.object_id) AS tableName,
       sp.partition_number as partitionNumber,
	  sp.rows
FROM sys.partitions AS sp
WHERE sp.object_id IN(OBJECT_ID('dbo.Target2'), OBJECT_ID('dbo.Source2'));

BEGIN TRAN
ALTER TABLE dbo.Target2
SWITCH TO   dbo.Source2

SELECT OBJECT_NAME(sp.object_id) AS tableName,
       sp.partition_number as partitionNumber,
	  sp.rows
FROM sys.partitions AS sp
WHERE sp.object_id IN(OBJECT_ID('dbo.Target2'), OBJECT_ID('dbo.Source2'));

rollback

SELECT OBJECT_NAME(sp.object_id) AS tableName,
       sp.partition_number as partitionNumber,
	  sp.rows
FROM sys.partitions AS sp
WHERE sp.object_id IN(OBJECT_ID('dbo.Target2'), OBJECT_ID('dbo.Source2'));

Zgodnie z tym co zakładaliśmy operację przełączania można bez żadnego problemu wycofać. Właściwość ta jest bardzo ważna gdyż dzięki niej możemy wycofać wszystkie zmiany jakie wprowadziliśmy w hurtowni danych jeśli wystąpił błąd itp. Sam osobiście spotkałem się z systemem gdzie właśnie wycofywanie partition switchingu było podstawą utrzymania integralności danych w hurtowni. Czy można używać  tego mechanizmu w systemach OLTP? Czy ma to jakiś sens? Oczywiście może mieć sens jednakże warto pamiętać, że podczas przełączania partycji zarówno na tabeli źródłowej jak i docelowej zakładany jest schema-modify lock, który w przypadku długo trwających transakcji może być problemem i zwiększyć czasy oczekiwania na rezultat.

To by było na tyle jeśli chodzi o opisywany mechanizm. Całość nie powinna sprawić problemu, wystarczy nawet podstawowa wiedza na ten temat aby sprawnie umieć posługiwać się przełączaniem partycji. Jedyne co warto powiedzieć w tym miejscu to fakt, że najważniejsza w przypadku implementacji jakiegokolwiek mechanizmu związanego z partycjami jest strategia, którą chcemy użyć it jej znalezienie jest najtrudniejszym czynnikiem.