Azure Synapse Dedicated SQL Pool – hurtownia na miarę

Azure Synapse Analytics (ASA) jest kluczowym elementem jeśli chodzi o rozwiązania analityczne dostępne w chmurze Azure. Dlatego też postanowiłem napisać kilka słów o tej niezwykle interesującej platformie, wskazać istotne elementy, zastosowania oraz kilka technicznych aspektów związanych z codzienną pracą z tym narzędziem. W ramach dzisiejszego artykułu zrobimy krótki wstęp oraz wytłumaczymy podstawowe pojęcia architektoniczne związane z sercem całej platformy jaką jest Dedicated SQL Pool.

Na samym wstępie chciałbym podkreślić to, że Synapse Analytics to nie tyle pojedyncze narzędzie co cały ekosystem łączący wiele różnych elementów w wyniku czego mamy do dyspozycji zunifikowane środowisko analityczne. Być może brzmi to nieco enigmatycznie ale w skrócie chodzi o to, że platforma może zostać wykorzystana do przeróżnych celów oferując przy tym integrację pomiędzy różnymi komponentami, uproszczoną administrację, utrzymanie, współdzielenie metadanych i zbliżone doświadczenie interakcji niezależnie od tego z którym komponentem platformy pracujemy.

Co zatem możemy osiągnąć przy pomocy Synapse? Bardzo dużo! Przede wszystkim do dyspozycji mamy dwa silniki wchodzące w skład SQL Pools czyli bohater dzisiejszego artykułu Dedicated SQL Pool oraz Serverless SQL Pool (o którym nagrałem już kiedyś kilka wideo link). Dedicated SQL Pool znany wcześniej jako Azure SQL Data Warehouse przeznaczony jest do budowy hurtowni danych oparty o silnik relacyjny w technologii MPP (o tym w dalszej części artykułu). Serverless SQL to z kolei usługa nieposiadająca swojego storage na dane, a jedynie moc obliczeniową (compute) przy pomocy której jesteśmy w stanie odpytywać zasoby zapisane na data lake’u. Mimo, że w obu przypadkach głównym narzędziem deweloperskim jest TSQL i mimo, że oba mają pewne podobieństwa architektoniczne to są to zdecydowanie różne usługi zbudowane w odmienny sposób o cyzm być może napiszę kilka słów w przyszłości.

Wychodząc nieco ze świata około sqlowego w Synapse Analytics mamy do dyspozycji Spark‘a który jest przeznaczony do szeroko pojętych rozwiązań Big Data oraz ML. Oprócz tego mamy również Synapse Pipelines będący niczym innym jak zintegrowaną implementacją Azure Data Factory, czyli usługi która służy budowaniu różnego rodzaju procesów ETL oraz orkiestracji zadań. W przypadku tego narzędzia sytuacja jest o tyle ciekawa, że również tam możemy wykorzytać mechanizm Mapping Data flows do budowania procesów ETL (mój artykuł na ten temat), który pod spodem wykorzystuje Sparka. Wahlarz możliwości jak widać jest całkiem szeroki, a to jeszcze nie wszystko bo mamy jeszcze Synapse Data Explorer przeznaczony do analizy danych telemetrycznych oraz logóww zbliżony sposób do tego jak to ma miejsce w Log Analytics i przy wykorzystaniu tego samego języka jakim jest Kusto Query Language. Niewątpliwą zaletą całej platformy której niemożna pominąć jest to, że nad całością możemy pracować w zunifikowanym środowisku jakim jest nowe narzędzie webowe o nazwie Synapse Studio pozwalające w scentralizowany sposób tworzyć, korzystać oraz monitorować pracę powyższych usług.

Patrząc na powyższą listę możemy dojść do wniosku, że platforma jest mocno rozbudowana i rzeczywiście tak jest. W moim odczuciu ważnym aspektem wszystkich tych elementów jest to, że są one ze sobą mocno zintegrowane zarówno pod kątem funkcjonalności oraz zarządzania jak i mechanizmów bezpieczeństwa oraz integracji sieciowej. To właśnie ta integracja stanowi o sile całości i to dzięki niej możemy mówić o platformie, a nie luźno powiązanym zestawie usług. Poniższa grafika pochodząca z oficjalnej dokumentacji Microsoftu pokazuje wysoko poziomowe zestawienie narzędzi oraz możliwości Synapse Analytics:

To co może nam odrazu rzucić się w oczy to fakt, że inne komponenty chmury Azure, które nie są elementami samego Synapsa mogą z nim współpracować. Co to za narzędzia? Azure purview, który pozwala nam utrzymać w ryzach Data Governance oraz Data Lineage bez większego problemu jest w stanie współpracować z Synapse i odczytać metadane poszczególnych komponentów. Dane przygotowane w ramach Synapse mogą posłużyć celom raportowym w Power BI (usługa ta świetnie współpracuje również z Dedicated SQL Pool poprzez połączenie bezpośrednie Direct Query i sam DSP posiada mechanizmów wspierających taką współpracę o których powiem w najbliższej przyszłości w ramach odrębnego artykułu). Mamy również Azure Machine Learning pozwalający wykonywać eksperymenty ML i korzystać przy tym z danych zawartych w Synapse.

Jak już wspomniałem we wstępie głównym bohaterem niniejszego artykułu  jest Dedicated SQL Pool któremu przyjrzymy się nieco bliżej. Przejdźmy zatem do architektury usługi, której znajomość jest istotna po to aby móc we właściwy sposób z niej korzystać. Dedicated SQL Pool jest oparty o nasz ulubiony silnik bazodanowy czyli SQL Server jednakże wewnętrznie działa zdecydowanie inaczej co możecie zobaczyć poniżej:

Cały design opiera się o architekturę MPP (Massive Parallel Processing) czyli taką gdzie występuje kilka node’ów obliczających (Compute Node) oraz jeden node kontrolny (Control node) niejako nadzorujący ich pracę. Dzięki takiemu podejściu i odpowiednim rozmieszczeniu danych po node’ach jesteśmy w stanie przetwarzać dane o ogromnej skali, których obróbka ze względu na wolumen na zwykłym SQL Server albo byłaby bardzo trudna albo wręcz niemożliwa. To właśnie w tym miejscu warto powiedzieć sobie kiedy DSP będzie lepszy od zwykłego Azure SQL Database i na odwrót. Punktem zwrotnym przy podejmowaniu decyzji, które narzędzie powinno zostać wykorzystane jest przede wszystkim wolumen danych. Synapse powinien być rozważany wtedy kiedy kończą się możliwości Azure SQL – zwyczajowo słyszy się głosy, że jeśli nasz wolumen danych nieprzekracza 1TB to warto rozważyć odpowiedni tier Azure SQL, a powyżej warto pomyśleć o Synapse. Oczywiście jest jeszcze kilka czynników wyboru o których powiem później ale czynnik skali i wolumenu jest jednym z najistotniejszych.

Wracając do naszej architektury warto zaznaczyć, że liczba Compute Node’ów w naszej instancji zależy od tego jaki poziom wydajności naszego Synapse’a wybierzemy.Oczywistym jest fakt, że mając więcej node’ów obliczeniowych tym więcej danych i w bardziej wydajny sposób możemy przetworzyć. Na powyższym rysunku możemy zauważyć, że użytkownik czy też aplikacja kliencka wysyła zapytania TSQL bezpośrednio do Control Node, który to z kolei po wstępnej optymalizacji przekazuje ich realizację do Compute Node’ów. Można zatem powiedzieć, że Control Node pełni rolę koordynatora całej operacji. To właśnie control node optymalizuje zapytanie tak aby mogło być przetwarzane równolegle i to w tym miejscu budowany jest rozproszony plan wykonania zapytania. To właśnie rozdystrybuowanie danych pomiędzy Compute Node’y jest jednym z kluczowych czynników wpływających na wydajność rozwiązania. Dane potrzebne do realizacji części zapytania przez pojedynczy node mogą się znajdować na innym node dlatego też gdy zachodzi taka potrzeba node’y mogą wymieniać się danymi, a jest to możliwe dzięki dedykowanemu serwisowi o nazwie Data Movement Service (DMS), który dba o to, żeby dane trafiły w odpowiednie miejsce. W idealnym świecie dane powinny być tak rozmieszczone aby zminimalizować wymianę danych między node’ami jednakże nie zawsze jest to możliwe.

Jak już wspomniałem wyżej najważniejszą rolę w przetwarzaniu danych danych w Dedicated SQL Pool ma równoległość oraz rozdystrybuowanie danych. Kluczowe do zrozumienia tego w jaki sposób dane są przetwarzane jest pojęcie dystrybucji (Distribution). Dystrybucja jest to podstawowa jednostka przechowywania i przetwarzania danych. W momencie gdy wyślemy zapytanie do realizacji to jest ono dzielone na 60 zapytań, które będą wykonywane równolegle gdzie każde zapytanie operuje na pojedynczej dystrybucji. Pojedynczny Compute Node może operować na jednej lub więcej dystrybucji gdzie przy najmniejszej konfiguracji wydajnościowej jeden Node operuje ze wszystkimi 60 dystrybucjami, a maksymalnej konfiguracji przypada jeden node na jedną dystrybucję. Rzućmy okiem na możliwe opcje wydajnościowe Synapse Analitycs:

Wydajność naszej instancji jest wyrażana w abstrakcyjnych jednostkach Data warehouse Units (DWU). Jak widzicie na powyższej tabeli mamy całkiemu dużą liczbę dostępnych opcji i każdej z nich przypisana jest odpowiednia liczba Compute Node oraz ilość pamięci w GB. Tak więc najniższy tier to DW100c ma jeden compute node i 60GB pamięci, ze względu na fakt iż mamy tylko jeden node to wszystkie 60 dystrybucji będzie obrabiane przez ten pojedynczy node. Z kolei najwyższy tier DW30000c ma 60 compute node’ów oraz 18000 GB pamięci z prostej matematyki wynika, że jedna dystrybucja przypada na jeden node. Jak większość rzeczy w Azure tak i tutaj mamy możliwość skalowania naszej instancji – przy pomocy prostych komend jesteśmy w stanie zmieniać tier i np. gdy potrzebujemy większej wydajności np. podczas ładowania danych to jesteśmy w stanie wyskalować rozwiązanie np. do DW2000c,a po ładowaniu wrócić do mniejszego tiera jakim jest np. DW1000c. Z poszczególnymi tierami związane są również możliwe równoległe połączenia ale o nich za chwilę.

Kilka linijek wyżej wspomniałem o usłudze DMS pozwalającej na przesył danych pomiędzy compute node i że powinniśmy minimalizować taki ruch. Z tego też wynika kolejny bardzo ważny aspekt wydajnościowych związanych z Dedicated SQL Pool, a mianowicie które dane trafiają do której dystrybucji. Sama operacja przesyłu danych pomiędzy node’ami nazywa się Shuffle i to pojęcie należy zapamiętać bo może być widoczną oznaką problemów wydajnościowych. O tym jak dane będą rozmieszczone będziemy decydować podczas tworzenia poszczególnych obiektów tj. tabel (ewentualnie widoków zmaterializowanych). Na ten moment mamy do dyspozycji trzy modele dystrybucji danych:

• hash
• round-robin
• replicated

Dystrybucja Hash polega na tym, że poszczególny wiersz wybrany jako klucz są przetwarzane przez funkcję hashującą i na podstawie deterministycznego wyniku działania tej funkcji określony wiersz trafia do określonej dystrybucji. Mechanizm zbudowany jest w taki sposób, że pojedynczy wiersz trafia do pojedynczej dystrybucji, a same dane nie są rozmieszczone równomiernie. To właśnie to nierówne rozmieszczenie danych w niektórych przypadkach może powodować problemy wydajnościowe więc klucz wybrany do dystrybucji hash powinien mieć jak najwyższą selektywność. Funkcja hashująca jest deterministyczna tzn. że ta sama wartość zawsze da ten sam hash dzięki czemu mamy pewność, że te same wartości trafią do tej samej dystrbyucji. Poniżej możecie zobaczyć koncepcyję dystrybucji hash (dla przejrzystości zakładamy, że jeden compute node = jedna dystrybucja):

Zwróćcie proszę uwagę na fakt, iż ten sposób rozrzucenia danych może powodować, że poszczególne dystrybucje mogą mieć więcej, a niektóre mniej wierszy. W związku z tym niektóre node mogą mieć więcej wierszy do przeprocesowania, a niektóre mniej więc już na tym etapie możemy powiedzieć, że dobrze byłoby unikać kolumny jako klucza hashującego która ma bardzo duże odchylenia jeśli chodzi o liczebność. Ogólnie istnieje wiele różnych czynników i dobrych praktyk związanych z tym jak powinniśmy do tematu podejść ale to pokażemy sobie na podstawie konkretnego przykładu w kolejnym artykule.

Kolejnym sposobem dystrybucji jest Round-robin czyli po prostu równomierne rozmieszczenie od którego własciwie powinienem zacząć ponieważ jest to domyślny sposób rozrzucenia danych. Jak to się odbywa? Wiersze bez właściwie żadnej optymalizacji trafiają do kolejnych dystrybucji przez co operacje takie jak np. ładowanie danych przebiega bardzo szybko ale zapytania agregujące na tabelach rozdystrybuowanych w ten sposób mogą powodować ogromną liczbę ruchów danych pomiędzy node’ami czyli wspomniane wyżej shuffle co oczywiście ma negatywny wpływ na wydajność. Z tego wynika, że round-robin jest dobrym wyborem dla wszelkiego rodzaju tabel stagingowych ale nie jest najlepszy dla tabel stricte hurtownianych przeznaczonych bezpośrednio do analizy danych.

Ostatnią możliwością jaką mamy jeśli chodzi o dystrybucje danych jest Replicated czyli jak nietrudno się domyślić replikacja całości danych do wszystkich compute node. Po co robić coś takiego? A no wyobraźmy sobie, że mamy tabele wymiarów, które są stosunkowo małe i aby uniknąć ich rozrzucania pomiędzy node godzimy się na redundancję ale w zamian unikamy problemów wydajnościowych związanych z ciągłym zbieraniem danych w ramach shuffle. Oczywiście ta redundancja jest dla nas transparentna i nie powoduje w żadnym wypadku duplikacji danych – jest to jedynie mechanizm optymalizacyjny. Ten sposób dystrybucji danych bardzo często jest wykorzystywany dla małych tabel czy też tabel wymiarów. Warto mieć na uwadze fakt, iż wrzucając te same dane w wiele różnych miejsc (na kilka różnych compute node) spowalniamy nieco sam proces ładowania, aczkolwiek w wielu przypadkach jest to narzut całkowicie akceptowalny.

Mamy zatem całkiem dużo opcji do wykorzystania jeśli chodzi o dystrybucję danych, a jak w takim razie dane są przechowywane wewnątrz każdej z dystrybucji? Tutaj mamy do dyspozycji znane z SQL Server struktury tzn:

• columnstore index – indeks kolumny pozwalający silnie skompresować dane oraz odczytywać tylko ich porcje na podstawie metadanych. Świetnie nadaje się do wszelkiego rodzaju agregacji i zapytań analitycznych.
• heap – typowa bezindeksowa sterta do której możemy bardzo szybko załadować dane aczkolwiek nie jest najlepszym wyborem jeśli chodzi o odczyt ponieważ jej struktura nie sprzyja ani wyszukiwaniu pojedynczych rekordów ani do ich agregacji.
• clustered rowstore index – standardowy indeks porządkujący dane wewnątrz tabeli po określonym kluczu. Oparty o strukturę b-drzewa świetnie nadaje się do wyszukiwania pojedynczych rekordów.
• nonclustered rowstore index – standardowy indeks który podobnie jak poprzednik porządkuje dane po określonym kluczu z czym indeks ten jest osobną strukturą ze wskaźnikami do oryginalnej tabeli, a nie tabelą samą w sobie.

Oczywiście Dedicated SQL Pool wspiera również partycjonowanie jednakże tutaj należy uważać ponieważ zbyt małe partycje mogą wpłynąć na wydajność naszego rozwiązania. Dla przykładu tabela która jest rozdystrybuowana i dodatkowo ma na sobie założony columnstore indeks jest już w rzeczywistości mocno podzielona. Jeśli dodatkowo ją popartycjonujemy musimy się upewnić, że nie rozbijamy w ten sposób row group indeksu kolumnowego, gdzie mamy wymóg minimum 102 400 wierszy aby w ogołe taka struktura powstała. Tak więc w tym przypadku nadmierne partycjonowanie może skutkować bardzo złą wydajnością (zainteresowanych odsyłam do jednego z moich artykułów na ten temat – link). Dlatego też rozważania o partycjonowaniu warto rozpocząć gdy nasze tabele zaczynają osiągać miliardy wierszy.

Oprócz samego uporządkowania danych wewnątrz Dedicated SQL Pool mamy również kwestie ładowania danych. Tutaj kwestia jest niezwykle ciekawa bo te dobre praktyki, które znamy z SQL Server niezawsze są dobrymi praktykami w DSP. Dla przykładu weźmy np. BULK INSERT czyli sposób ładowania, który jest czymś pożądanym w świecie hurtowni danych opartych o SQL Server, a zdecydowanie nie jest czymś co chcielibyśmy widzieć w Synapse. W przypadku ładowania Synapse’a możemy dane ładować bezpośrednio do Compute Node’ów lub poprzez Control Node’a który już po swojej stronie rozdysponuje dane. Jak możecie się domyślać ładowanie poprzez Control Node’a jest zdecydowanie mniej wydajne i ograniczone jeśli chodzi o równoległość – typowy BULK INSERT właśnie takim czymś skutkuje. Jeśli chodzi o ładowanie bardziej wydajne mamy do dyspozycji mechanizm POLYBASE oraz nieco uproszczoną składnię COPY INTO, które ładują dane z plików zawartych na określonym Data Lake. O tym jak praktycznie wykorzystać jeden z tych dwóch mechanizmów powiemy sobie w ramach osobnego artykułu natomiast na ten moment możemy jedynie zaznaczyć, że to są zdecydowanie metody ładowania, które powinniśmy zapamiętać w kontekście Synapse. Bardzo często otrzymuje pytanie po co ładować dane do data lake skoro zaraz je załadujemy do Synapse – odpowiedź brzmi ponieważ jest to zdecydowanie bardziej wydajne! Nawet jeśli nie mamy typowego data lake to takie narzędzia jak Data Factory/Integrated Pipelines pozwalają “na chwilę” wrzucić coś na data lake żeby potem wydajnie wrzucić to do Synapse.

Z wyborem odpowiedniego tieru oprócz kwestii wydajnościowych wiąże się jeszcze jeden aspekt – chodzi mianowicie o zarządzanie workloadem i tego kto według jakiego priorytetu otrzyma dostęp do jakich zasobów, a kto będzie w oczekiwał na dostęp w kolejce. Temat ten postanowiłem poruszyć w tym artykule ponieważ dosyć często jest to mylone i błędnie interpretowane. Stosunkowo niedawno mieliśmy do czynienia z tzw. resource classes gdzie przyporządkowaliśmy użytkowników do określonej grupy o danej charakterystyce i to determinowało na jakich zasadach otrzymują oni dostęp do zasobów. Sposób ten nadal jest dostępny w DSP jednakże aktualnie workload management jest bardziej rozbudowany i  opiera się o trzy podstawowe pojęcia:

• workload classification – koncepcja przyporządkowująca konkretne żądania wysyłane do bazy do konkretnej grupy (workload group) i dzięki temu przyporządkowująca zasoby jakie mogą być użyte do wykonania tego żądania.
• workload importance – definicja tego które żądanie dostaje dostęp do zasobów w jakiej kolejności. Daje to możliwość zdefiniowania priorytetów wykonania.
• workload isolation – możliwość ekskluzywnego rezerwowania zasobów systemu dla konkretnych grup (workload group) przez co zasoby te nie mogą być wykorzystane przez inne grupy. Ponadto a w definicji grupy możemy zdefiniować zarezerować zasoby per pojedyncze zapytanie w ramach tej grupy.

Powyższe pojęcia i ustawienia determinują ustawienia naszego workloadu i całość jesteśmy w stanie ustawić zarówno z poziomu TSQL jak i portalu. Powyższe ustawienia mają również wpływ na to ile równoległych zapytań nasza instancja będzie w stanie obsłużyć. Jednakże istnieją minimalne wymogi ile pojedyncze zapytanie minimalnie musi zarezerwować zasobów – zostały one ujęte w poniższej tabeli:

Naszym zadaniem jest takie zbalansowanie zasobów aby móc jak najszybciej zwrócić zapytania do określonych odbiorców według określonego priorytetu. Przykładowo możemy przyjąć następujące grupy wykonujące zapytania na Synapse:

• ETL – wszelkie narzędzia ładujące i przebudowujące dane,
• adhoc – zapytania pisane bezpośrednio przez użytkowników,
• zapytania raportowe – zapytania wysyłane z poziomu narzędzi raportowych np. Power BI w trybie Direct Query,
• zapytania zasilające modele analityczne – zapytania wysyłane przez różne narzędzia w celu załadowania określonych modeli analitycznych np. Power BI w trybie Import albo zasilenie Data Marta na Azure SQL.
• zapytania monitorujące – zapytania wysyłane automatycznie przez narzędzia monitorujące lub przez członków zespołu utrzymaniowego.

Oczywiście grup może być zdecydowanie więcej i to zależy od konkretnych założeń. Dla każdej z takich grup możemy stworzyć osobny workload i przypisać priorytet tak, że np. zapytania monitorujące będą miały najniższy priorytet i nie będą pochłaniać więcej niż 5% zasobów, a zapytania adhoc będą zawsze traktowane priorytetowo, będą mogły pochłonąć do 30% całych zasobów, a ich timeout będzie wynosił 3 minuty. Temu właśnie służy opisywany mechanizm.

Pokażmy sobie mechanizm kolejkowania na konkretnym przykładzie. Poniższa grafika pokazuje aktualnie przetwarzane zapytania (załóżmy, że maksymalnie możemy przetworzyć pięć zapytań) oraz kolejkę wraz z przypisanymi priorytetami:

Powyższa sytuacja będzie trwać aż któreś z aktualnie wykonywanych zapytań się zakończy lub dostanie timeout – wtedy też do wykonania zostanie przekazane zapytanie z najwyższym priorytetem bez względu na fakt kiedy to zapytanie zostało wysłane:

W dalszej kolejności – przy pojawieniu się kolejnego wolnego miejsca na przetwarzanie wskakuje zapytanie z kolejnym priorytetem:

Dzieje się tak aż do wykonania wszystkich zapytań:

Mechanizm ten jest niezwykle przejrzysty i daje nam kontrolę nad tym co, kto i kiedy. W tym miejscu warto również wspomnieć o opcji skalowania oraz pauzowania. To nie jest oczywiście tak, że tworząc instancję Dedicated SQL Pool na stałe przypisujemy określony poziom DWU – właściwie w dowolnym momencie jesteśmy w stanie tier zwiększyć bądź zmniejszyć. Jeśli np. w trakcie godzin roboczych potrzebujemy więcej zasobów i większej równoległości to nic nie stoi na przeszkodzie aby zwiększyć tier, a po godzinach pracy go zmniejszyć lub wręcz całkowicie zapauzować. Sprzyja temu rozdzielenie w tym przypadku storage oraz compute gdzie pauzując compute będziemy płacić jedynie za dane przechowywane na Azure Storage. Jest to niezwykle przydatny mechanizm pozwalający znacząco obniżyć koszty usługi i osiągnąć zamierzony efekt wydajnościowy czy przedstawiony przed nami wymóg SLA.

Przedstawione w niniejszym artykule możliwości Synapse i Dedicated SQL Pool zdecydowanie nie zostały wyczerpane. Jest cały szereg możliwości, które celowo pominąłem gdyż celem tego artykułu nie było opisanie wszystkiego, a jedynie przedstawienie narzędzia dla tych którzy nie mieli jeszcze okazji go używać. W najbliższym czasie postaram się opisać kilka praktycznych przypadków związanych z samym Synapse, a Wam polecam zapoznanie się z dokumentacją i testowanie na własną rękę. Dzięki!