SQL Server -indeks kolumnowy + partycjonowanie

W świecie hurtowni danych niezwykle istotnym czynnikiem jest wydajność zarówno wstawiania danych jak i ich odczytu. W SQL Server 2012 wprowadził niezwykle istotną konstrukcję, która pomaga nam osiągnąć złoty środek jeśli chodzi o INSERT jak i SELECT jaką jest bez wątpienia indeks kolumnowy czyli COLUMNSTORE. Jeszcze wcześniej wprowadzona w naszym ulubionym silniku bazodanowym została technologia pozwalająca partycjonować dane. Na przestrzeni lat obie technologie stały się niezwykle istotne i znalazły szerokie zastosowania w hurtowaniach danych i nie tylko. Czy mogą one współistnieć razem i przy okazji się uzupełniać? Oczywiście, że tak! W ramach niniejszego artykułu chciałbym pokazać wybrany przeze mnie scenariusz użycia obu technologii – zapraszam do lektury!

Na samym wstępie kilka słów niezbędnej teorii o indeksie kolumnowym, którego schemat możecie zobaczyć na poniższej grafice.

Dane w indeksie kolumnowym składowane są w odróżnieniu od standardowego indeksu opartego o B-Drzewo kolumnowo. Oznacza to, że jeżeli w naszych zapytaniach odpytujących wybierzemy kolumnę c1 oraz c2 to pozostałe kolumny nie zostaną pobrane z dysku. Mechanizm ten nazywamy eliminacją kolumn. Z drugiej zaś strony w ramach pojedynczej kolumny dane są przechowywane w pewnego rodzaju “porcjach” nazwanych segmentami. Pojedynczy segment może mieć od 102 400 do 1 048 576 wierszy. Warto w tym miejscu zwrócić uwagę na to, że im większy nasz indeks tym lepsze efekty kompresji jesteśmy w stanie uzyskać (ze względu na fakt, że dane kompresowane są per segment). Jeżeli nieosiągniemy minimalnego pułapu to nasze dane nie utworzą segmentu przez co nie będziemy czerpać krorzyści z kompresji i eliminacji. Ponadto gdy wstawiamy dane poniżej progu to użycie dziennika transakcyjnego nie jest już zaoptymalizowane pod kątem wstawiania danych. Przechodząc dalej powiedzmy, że każdy segment jest opisany metadanymi, upraszczając możemy powiedzieć, że mamy informacje o minimalnej i maksymalnej wartości jaka znajduje się w segmencie. Wyobraźmy sobie sytuację gdzie mamy w kolumnie daty, skąd wiadomo które daty są w których segmentach? Właśnie na podstawie metadanych!

Poniżej możecie zauważyć przykładową konfigurację gdzie zakładamy, że kolumna c1 zawiera datę i tak:

• pierwszy segment zawiera dane od 1 czerwca 2019 do 30 czerwca 2019,
• drugi segment zawiera dane od 1 lipca 2019 do 31 lipca 2019,
• trzeci segment zawiera dane od 1 sierpnia 2019 do 31 sierpnia 2019.

Odpowiadające sobie segmenty z różnych kolumn tworzą tzw. grupę wierszy. Porządek danych wewnątrz Columnstore’a determinuje jak będzie działać eliminacja segmentów dla poszczególnych kolumn. Oczywiście jeśli dane będą uporządkowane po kolumnie z datą to prawdopodobnie dla tej właśnie kolumny uzyskamy najlepszą eliminację, a nieco gorszą dla innych kolumn. Z tego też powodu porządek danych w indeksie powinien być przemyślany tak aby uzyskać najlepsza eliminację dla najczęściej odpytywanej kolumny. Do tego jeszcze wrócimy za chwilę, na ten moment powiedzmy sobie co się stanie gdy naszą tabelę z indeksem kolumnowym będziemy chcieli partycjonować.

Na poniższej grafice przedstawiłem przykładowy, partycjonowany indeks kolumnowy:

Każda partycja tak naprawdę dzieli nam indeks na osobne struktury. Na powyższej grafice możecie zauważyć, że klucz partycjonujący podzielił Columnstore na cztery osobne wystąpienia indeksu. Na obrazku można również zauważyć dwa klucze umownie przeze mnie nazwane Loading Key oraz Optimization Key. Pierwszy z nich oznacza kolumnę (lub kilka kolumn) po której przeładowujemy nasze dane. Zazwyczaj mamy jakiś punkt zaczepienia po którym przeładowujemy dane najczęściej jest to jakiś wyznacznik czasu jak chociazby rok, miesiąc, dzień itp. Biznesowo gdy mamy pewność, że w tabeli faktów może zmienić się jedynie ostatni rok kalendarzowy naszych danych to przeładowanie poprzednich lat nie ma żadnego sensu i na tym opiera się nasza strategia przeładowań w przedstawianym scenariuszu.

Optimization Key to klucz porządku danych wewnątrz Columnstore, który zapewnia eliminację segmentów. Jest to kolumna lub grupa kolumn najczęściej używanych do odpytywania tej tabeli przy założeniu, że dane mają być uporządkowane od atrybutu najbardziej selektywnego. Może to być np. numer transakcji, klienta bądź cokolwiek innego co jest dostatecznie selektywne i jednocześnie najczęściej wybierane w zapytaniach odpytujących. Warto wspomnieć, że przy tworzeniu Columnstore nie wskazujemy sortowania, ale oczywiście również na to mamy sposób. Przejdźmy zatem do demonstracji i przedstawmy kilka linijek kodu.

Na początku stwórzmy sobie testową bazę danych. Sam kod nie wyróżnia się niczym specjalnym, jednakże warto zwrócić uwagę, że nasza baza danych jest w trybie Simple – umożliwiamy zatem osągnięcie minimalnego logowania:

USE master 
GO

DROP DATABASE IF EXISTS SQLDay2019_CCIPartitioning
GO

CREATE DATABASE SQLDay2019_CCIPartitioning
GO

ALTER DATABASE SQLDay2019_CCIPartitioning
SET RECOVERY SIMPLE
GO

use SQLDay2019_CCIPartitioning
GO

W kolejnym kroku przejdziemy do partycjonowania naszej tabeli. Stwórzmy zatem funkcję partycjonującą po roku:

CREATE PARTITION FUNCTION udf_partitionByOrderDateKey(date) 
AS RANGE LEFT 
FOR VALUES(
          '20131231'
	 ,'20141231'
	 ,'20151231'
	 ,'20161231'
	 );
GO

Mając już funkcję przejdźmy do stworzenia schematu partycjonującego, który w tym konkretnym przypadku przypiszemy wszystkie partycje do jednej grupy plików (nic nie stoi na przeszkodzie żeby oczywiście rozmieścić pliki na różnych dyskach – będzie to oczywiście wartość dodana).

CREATE PARTITION SCHEME ups_partitionOrderDateKey
    AS PARTITION udf_partitionByOrderDateKey
    ALL TO ([PRIMARY])
GO

Mając już dostępne struktury pod partycjonowanie stwórzmy naszą tabelę testową, która będzie partycjonowaną stertą:

CREATE TABLE dbo.MainFactTable(
	[Order Key] [bigint] NOT NULL,
	[City Key] [int] NOT NULL,
	[Customer Key] [int] NOT NULL,
	[Stock Item Key] [int] NOT NULL,
	[Order Date Key] [date] NOT NULL,
	[Picked Date Key] [date] NULL,
	[Salesperson Key] [int] NOT NULL,
	[Picker Key] [int] NULL,
	[WWI Order ID] [int] NOT NULL,
	[WWI Backorder ID] [int] NULL,
	[Description] [nvarchar](100) NOT NULL,
	[Package] [nvarchar](50) NOT NULL,
	[Quantity] [int] NOT NULL,
	[Unit Price] [decimal](18, 2) NOT NULL,
	[Tax Rate] [decimal](18, 3) NOT NULL,
	[Total Excluding Tax] [decimal](18, 2) NOT NULL,
	[Tax Amount] [decimal](18, 2) NOT NULL,
	[Total Including Tax] [decimal](18, 2) NOT NULL,
	[Lineage Key] [int] NOT NULL
) ON ups_partitionOrderDateKey([Order Date Key])
GO

Powyższa tabela ma taką samą strukturę jak tabela z przykładowej bazy WideWorldImportersDW i na jej podstawie ją zasilimy ( jest to specjalna wersja tabeli w WideWorldImportersDw która zawiera nieco więcej wierszy – możecie to zrobić wstawiając te same dane kilka razy lub użyć techniki przedstawionej np. tutaj):

INSERT INTO dbo.MainFactTable WITH (TABLOCK)
SELECT *
FROM [WideWorldImportersDW].[Fact].[OrderHistoryExtended]
GO

Powyższej użyłem wskazówki TABLOCK aby osiągnąć minimalne logowanie, a co za tym idzie stosunkowo dobrą wydajność(pisałem już o tym w ostatnim czasie, artykuł znajdziecie tutaj). W następnym kroku zrobimy coś co jest kluczowe w stosunku do indeksu kolumnowego czyli posortujemy znajdujące się tam dane – zrobimy to tworząc indeks zgrupowany na najczęściej używanej kolumnie w zapytaniach odpytujących (wspomniany wcześniej Optimization Key):

CREATE CLUSTERED INDEX IX_MFT ON dbo.MainFactTable([Customer Key])
GO

Następnie jak już mamy dostępne uporządkowane dane to możemy indeks zgrupowany na kolumnowy:

CREATE CLUSTERED COLUMNSTORE INDEX IX_MFT 
ON dbo.MainFactTable
WITH (DROP_EXISTING=ON,MAXDOP=1)
GO

Wyłączyliśmy wielowątkowość używając hinta MAXDOP 1 ze względu na fakt, iż wiele równoległych wątków mogłoby nam “pomieszać” uporządkowane już dane. Wszystkie powyższe operacje oczywiście trochę trwają, ale jest to inicjalne ładowanie i dla 30 milionów danych na moim laptopie zajęło to kilkanaście minut. Oczywistym jest fakt, że wszystko zależy od logiki zawartej w ładowaniu, wolumenu i dostępnych zasobów sprzętowych, jednakże ładowanie inicjalne charakteryzuje się tym, że robimy go tylko raz 🙂

Jak już mamy wszystko gotowe możemy podejrzeć metadane, zacznijmy od partycji:

select * from sys.partitions
where OBJECT_ID=OBJECT_ID('dbo.MainFactTable')

Jak można zauważyć na powyższym zrzucie ekranowym, powstało 5 partycji z czego ostatnia jest pusta(otwarta). Jako algorytm kompresji wskazany został COLUMNSTORE co jest oczywiście zgodne z naszym założeniem. Przejdźmy dalej i podejrzyjmy metadane związane z grupami wierszy wewnątrz Columnsore:

select * from sys.column_store_row_groups
order by partition_number,row_group_id

W tym przypadku zwróćcie uwagę na kolumnę partition_number, row_group_id oraz total rows. Mamy grupy wierszy per partycja i każda grupa wierszy ma maksymalny rozmiar oprócz ostatniej, która zawiera wszystkie wiersze które “zostały” w danej partycji. najważniejsze jest to aby dane w każdej grupie wierszy miały status COMPRESSED to znaczy że było ich na tyle dużo, że zostały skompresowane do grupy wierszy, a nie zostały w tzw. Delta store. Powyższy zrzut ekranowy potwierdza wcześniej przedstawioną teorię mówiącą o tym, że mamy “tyle columnstore ile partycji”.

No dobrze ale jakie mamy w tym momencie zalety takiej struktury? Zobaczmy to na własne oczy! Dla testu uruchomimy nasze zapytanie, dodatkowo włączmy plan zapytania oraz statystyki IO:

SET STATISTICS TIME OFF;
SET NOCOUNT ON;

SELECT *
FROM dbo.MainFactTable
WHERE [Order Date Key] BETWEEN '20140101' AND '20141230'
AND [Customer Key]=1
GO

Plan zapytania nie wygląda jakoś szczególnie ale zawiera bardzo istotne informacje:

Widzimy, że odczytana była tylko jedna partycja o numerze 2. Miała zatem miejsce eliminacja partycji o której pisałem już wcześniej (artykuł znajdziecie tutaj). Ale to jeszcze nie wszystko! Gdy zajrzymy na zakładkę Messages zobaczymy następująco informację:

O co w tym wszystkim chodzi? Na podstawie metadanych najpierw zostały wyeliminowane partycje (na podstawie zapisie w naszym zapytaniu WHERE [Order Date Key] BETWEEN ‘20140101’ AND ‘20141230’), a następnie w tej partycji która pozostała w ramach Columnstore wyeliminowane zostały odpowiednie segmenty (osiem z nich, odczytany został tylko jeden – spowodowane zostało to zapisem [Customer Key]=1). Czyli jak możemy zauważyć, tylko na podstawie metadanych odrzuciliśmy znaczną część wierszy i odczytaliśmy tylko jeden segment! Jak długo ten odczyt trwał? Dosyć szybko bo w 1-2 sekundy zwrócone zostało 16 tysięcy wierszy z 30 milionów znajdujących się w tabeli. Efekt możecie zobaczyć poniżej:

Nasze dane zostały zasilone inicjalnie, jak zatem należy je doładowywać w sposób przyrostowy? Pokażmy to na konkretnym przykładzie. Na wstępie stwórzmy sobie tabelę przejściową (stagingową) która będzie przechowywała dane przyrostowe:

CREATE TABLE dbo.StagingTable(
	[Order Key] [bigint] NOT NULL,
	[City Key] [int] NOT NULL,
	[Customer Key] [int] NOT NULL,
	[Stock Item Key] [int] NOT NULL,
	[Order Date Key] [date] NOT NULL,
	[Picked Date Key] [date] NULL,
	[Salesperson Key] [int] NOT NULL,
	[Picker Key] [int] NULL,
	[WWI Order ID] [int] NOT NULL,
	[WWI Backorder ID] [int] NULL,
	[Description] [nvarchar](100) NOT NULL,
	[Package] [nvarchar](50) NOT NULL,
	[Quantity] [int] NOT NULL,
	[Unit Price] [decimal](18, 2) NOT NULL,
	[Tax Rate] [decimal](18, 3) NOT NULL,
	[Total Excluding Tax] [decimal](18, 2) NOT NULL,
	[Tax Amount] [decimal](18, 2) NOT NULL,
	[Total Including Tax] [decimal](18, 2) NOT NULL,
	[Lineage Key] [int] NOT NULL
) ON ups_partitionOrderDateKey([Order Date Key])
GO

Powyższa tabela ma taką samą strukturę jak tabela docelowa – jest partycjonowana ale nie posiada indeksu kolumnowego. Mamy zatem do czynienia z sytuacją podobną do ładowania inicjalnego. Różnicą tutaj jest to, że ładujemy dane przyrostowe, które w moim przypadku ograniczą się do wszystkich danych z roku 2016:

INSERT INTO dbo.StagingTable WITH (TABLOCK)
SELECT *
FROM [WideWorldImportersDW].[Fact].[OrderHistoryExtended]
WHERE [Order Date Key] BETWEEN '20160101' AND '20161231'
GO

W tym miejscu możemy zaimplementować dowolną logikę biznesową jaką mam potrzebę wdrożyć. W tym konkretnym przypadku mój “przyrost” zawiera niecałe 4 miliony wierszy. Dalsze kroki już znamy bo są analogiczne do ładowania incjalnego i sprowadzają się one do:

1) Stworzenia indeksu klastrowanego na kolumnie wybranej jako Optimization Key:

CREATE CLUSTERED INDEX IX_ST ON dbo.StagingTable([Customer Key])
GO

2) Zastąpienie wcześniejszego indeksu indeksem kolumnowym z wykorzystaniem MAXDOP=1

CREATE CLUSTERED COLUMNSTORE INDEX IX_ST 
ON dbo.StagingTable
WITH (DROP_EXISTING=ON,MAXDOP=1)
GO

Kiedy już mamy do dyspozycji tabelę z przyrostem oraz tabelę docelową o tej samej strukturze możemy przejść do rzeczy i wykonać podmianę partycji czyli partition switching (artykuł tutaj).

Przy tego typu operacji partycja docelowa musi być pusta, my mamy w stagingu dane za rok 2016 skąd mamy wiedzieć która partycja docelowa powinna być wyczyszczona? Wystarczy użyć specjalnej konstrukcji którą przedstawiłem już w artykule o partition switchingu:

with ctePartitionInfo
AS
(
SELECT $partition.udf_partitionByOrderDateKey('20130101') AS PartitionNo, 2013 AS PartitionYear
UNION ALL
SELECT $partition.udf_partitionByOrderDateKey('20140101') AS PartitionNo,2014 AS PartitionYear
UNION ALL
SELECT $partition.udf_partitionByOrderDateKey('20150101') AS PartitionNo, 2015 AS PartitionYear
UNION ALL
SELECT $partition.udf_partitionByOrderDateKey('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.MainFactTable'));

Z tego zapytania wynika nam, że partycja z danymi za rok 2016 to partycja numer 4:

Wyczyśćmy ją zatem komendą TRUNCATE:

TRUNCATE TABLE dbo.MainFactTable
WITH (PARTITIONS (4))

Teraz nie pozostaje nam nic innego jak podejrzeć struktury tabeli stagingowej oraz docelowej:

select * from sys.partitions
where OBJECT_ID=OBJECT_ID('dbo.StagingTable')

select * from sys.partitions
where OBJECT_ID=OBJECT_ID('dbo.MainFactTable')

W rezultacie tych zapytań dostaniemy pełną informację na temat metadanych partycji w tabelach które nas interesują:

Nie pozostaje nam nic innego jak wykonać switch obu partycji:

ALTER TABLE dbo.StagingTable
SWITCH PARTITION 4 TO dbo.MainFactTable PARTITION 4
GO

Ze względu na fakt, iż powyższa operacja jest operacją na metadanych wykonuje się naprawdę szybko. Nic teraz nie stoi na przeszkodzie aby sprawdzić raz jeszcze metadane:

Wszystko działa tak jak należy i dane znalazły się w tabeli docelowej. Wspomnę tylko, że nasz przyrost powinien zawierać minimum tyle wierszy ile jest wymagane aby utworzyć skompresowaną grupę wierszy czyli 102 400. Jeżeli nie jesteśmy osiągnąć tego pułapu w naszym przyroście to zwiększmy zakres aby go osiągnąć czyli np. przesuńmy zakres przyrostu z dziennego na tygodniowy itd. Dzięki temu podejściu powinniśmy osiągnąć najlepsze efekty. Tutaj może pojawić się również pytanie jak utrzymywać indeks kolumnowy w takiej konfiguracji. Odpowiedź jest prosta – jeżeli zadbamy o to aby nasze grupy wierszy były odpowiednio duże to już mamy dobrą strukturę. Jedyne o co możemy zadbać to fakt, że możemy przebudowywać grupy wierszy jak już zamknięmy okres przeładownia (np. koniec miesiąca) tak aby miały maksymalny rozmiar przez co lepszą kompresję jednakże wszystko zależy od konkretnego przypadku i wolumenu danych. Mam nadzieję, że ten artykuł Wam się spodobał i pokazał jak w prosty sposób można radzić sobie z dużymi zbiorami danych w hurtowniach danych łącząc wydajność wstawiania danych jak i ich odczytu przy pomocy indeksu kolumnowego i partycjonowania.