Azure SQL Elastic Query – Linked server w świecie chmury

Mimo licznych problemów Linked Server jest bardzo popularną funkcjonalnością w świecie SQL Server. Pozwala ona w prosty sposób połączyć się z jednego serwera bazodanowego do innego i używać SQLa do łączenia czy też przerzucania danych pomiędzy serwerami. Ze względu na fakt, iż chmura Azure już na dobre zadomowiła się w wielu przedsiębiorstwach, deweloperzy którzy upodobali sobie Linked Server mogą zadać pytanie czy coś takiego jest możliwe w Azure SQL? Odpowiedź brzmi – nie, nie ma czegoś takiego przynajmniej w standardowym wydaniu chmurowym naszej ulubionej bazy (na dzień pisania tego artykułu możliwe jest utworzenie LS na Managed Instance oraz oczywiście na maszynie wirtualnej ze standardowym SQL Server – link). Oprócz tego niektóre implementacje rozwiązań opartych o SQL Server często i gęsto korzystają z cross-database queries czyli zapytań odnoszących się do danych w innych bazach danych – ta funkcjonalność bezpośrednio również nie jest wspierana jednakże nie macie się co martwić ponieważ mamy pewną alternatywę. W wielu scenariuszach zarówno Linked Server jak i zapytania między bazami danych możemy zastąpić poprzez tzw. Elastic Query o której chciałbym dziś opowiedzieć kilka słów.

Elastic query pozwala nam się podłączyć do innej bazy danych poprzez stworzenie zewnętrznego źródła danych. Funkcjonalność ta jest szczególnie przydatna w przypadku gdy mówimy o rozwiązaniu opartym o partycjonowania wertykalne lub horyzontalne czyli tzw. sharding.

Partycjonowanie wertykalne polega na tym, iż mamy zestaw dwóch lub więcej baz danych gdzie w każdej z nich umieszczone zostaną dane o różnym schemacie. Tak jak możecie zauważyć na poniższym zrzucie ekranowym jedna baza zawiera dane produktowe, inna dane HRowe itd. Dzięki Elastic Query jesteśmy w stanie odpytywać dane fizycznie znajdujące się w różnych bazach danych i łączyć ze sobą – do tego celu możemy wykorzystać centralną bazę (control node) gdzie po stworzeniu odpowiednich struktur będziemy w stanie tworzyć np. zapytania raportowe opierające się na danych z różnych źródeł:

Oczywiście powyższe podejście determinuje posiadanie bazy pełniącej rolę centralnego huba łączącego dane z wielu źródeł jednakże mamy tutaj pełną dowolność ponieważ równie dobrze możemy stworzyć struktury w taki sposób aby wszystkie dane były dostępne z poziomu każdej z baz:

Powyższe podejście jest szczególnie przydatne gdy mamy N różnych baz zawierających dane które my musimy ze sobą łączyć i odpytywać. Samo nasuwającym się przykładem użytkowym są z całą pewnością wszelkiego rodzaju rozwiązania raportowe oraz/lub hurtowni danych budowanych w oparciu o Azure SQL Database.

Innym scenariuszem gdzie możemy wykorzystać Elastic Query jest łączenie danych rozdzielonych w ramach partycjonowania wertykalnego czyli wspomnianego wcześniej shardingu:

W tym konkretnym przypadku mamy po raz kolejny wiele baz danych jednakże każda z nich zawiera obiekty o tej samej strukturze, tzn. w każdej z nich możemy znaleźć np. identyczną pod kątem struktury tabelę sprzedażową, słownik klientów itd. Poszczególne bazy różnią się od siebie w ten sposób, że zawierają inne dane. Dane mogą być rozdzielone w ten sposób z wielu różnych powodów jak np. względy wydajnościowe gdzie każdy klient czy jednostka organizacyjna będzie finansować swój własny node (bazę) lub też wskazania prawne gdzie dane mimo posiadania tej samej struktury muszą być trzymane osobno. Oba powyżej przedstawione podejścia opierają się o zestaw funkcjonalności wbudowanych w Azure SQL o nazwie Elastic Tools i to właśnie w skład tych narzędzi wchodzi Elastic Query. O samym partycjonowaniu z całą pewnością jeszcze zdążymy sobie powiedzieć, dziś natomiast postaram się przetestować Elastic Queyr.

Zanim przejdziemy do właściwej demonstracji przedstawie Wam konfigurację testową, którą będę się posługiwał. Mam dwie bazy Azure SQL, które współdzielą zasoby w ramach Elastic Pool posiadającej 50 eDTU:

W tym miejscu warto zaznaczyć, że w ramach Elastic Query możemy odwoływać się do dowolnej bazy Azure SQL, a nie tylko do tych które współdzielą zasoby w ramach Elastic Pool. Jak widzicie bazy nie są zbyt duże jednakże wystarczające żeby zademonstrować możliwości Elastic Query. Baza sqslqdb jest pusta, a baza sqsqldb2 zawiera przykładową bazę AdventureWorksLT, w celach testowych będziemy odwoływać się z pustej bazy do bazy z danymi.

Podłączmy się zatem do Azure SQL Database gdzie pierwszym krokiem w kierunku  wykorzystania Elastic Query będzie stworzenie klucza (jeśli jeszcze go nie mamy):

Jest to standardowy główny klucz szyfrowania znany z SQL Server. Hasło szyfrujące ten klucz jest nieobowiązkowe jednakże ja go użyłem. Po wykonaniu tej operacji możemy stworzyć DATABASE SCOPED CREDENTIAL czyli zapisane poświadczenia jakie będą używane do połączenia do bazy danych do której będziemy się chcieli łączyć w ramach Elastic Query. Powyżej stworzyliśmy MASTER KEY właśnie po to aby zabezpieczyć CREDENTIAL, który właśnie tworzymy:

Będę się łączył używając najprostszego SQL Auth dlatego wskazałem login oraz hasło, którym będziemy się posługiwać. Kiedy mamy już CREDENTIAL możemy stworzyć EXTERNAL DATA SOURCE czyli wskazanie bazy danych  do której będziemy się łączyć:

Cała operacja sprowadza się to do:

  • podania typu zewnętrznego źródła danych w postaci relacyjnej bazy danych RDBMS,
  • lokalizacji czyli adresu serwera,
  • nazwy bazy danych,
  • obiektu typu CREDENTIAL stworzonego w poprzednim kroku.

Tworząc te wszystkie obiekty możecie odnieść wrażenie, że analogiczne kroki podejmujemy używając technologii POLYBASE i jest to całkiem uzasadnione skojarzenie ponieważ oba narzędzia mają wiele cech wspólnych. Przechodząc dalej Mając źródło danych będziemy mogli powoli tworzyć obiekty odpowiadające tabelom w połączonym źródle danych. W moim przypadku łączę się do bazy AdventureWorksLT więc stworzę schemat bazy danych tam właśnie używany:

Tabele w ramach AdventureWorks używają typów użytkownika w swojej definicji więc niestety będę musiał je powtórzyć w mojej bieżącej bazie:

Niejawna konwersja nie jest wspierana więc muszę zadbać o całkowitą zgodność typów pomiędzy bazami.

W momencie gdt mam już wszystkie niezbędne obiekty mogę przystąpić do stworzenia zewnętrznej bazy danych czyli w nomenklaturze SQL będzie to EXTERNAL TABLE:

Jak już wspomniałem wyżej tworzenie tabel zewnętrznych sprowadza się do podania identycznej struktury jak tabela do której będziemy się łączyć oraz do wskazania w którym źródle danych dana zewnętrznym (external data source) tabela się znajduje. Domyślnie rzecz biorąc nazwa tabeli oraz jej struktura musi być identyczna jak tabeli źródłowej inaczej wszelkie zapytania odpytytujące tabelę zewnętrzną zakończą się niepowodzeniem.

Istnieje możliwość nadania innej nazwy lub umieszczenia jej w innym schemacie niż oryginalna źródłowa – jedyne co wtedy musimy zrobić to w definicji External Table musimy podać dwa dodatkowe parametry czyli SCHEMA_NAME, OBJECT_NAME np. w taki sposób:

Po stworzeniu wszystkich niezbędnych obiektów możemy je podejrzeć w odpowiednich węzłach eksploratora obiektów w Management Studio:

Pełny dostęp do metadanych mamy również przez tabele i widoki systemowe np.

Osobiście polecam aby nazywać tabele tak samo jak w źródle chyba, że jesteśmy zmuszeni postąpić inaczej. Podejście niesie ze sobą wiele pozytywnych skutków jak chociażby to, że jesteśmy w stanie odpytywać obiekty w taki sam sposób zarówno z poziomu oryginalnej bazy danych jak i naszego huba. Odpytywanie zewnętrznej tabeli z punktu widzenia składni nie różni się absolutnie niczym od odpytywania standardowej tabeli:

Plan zapytania wygląda tak jak byśmy odpytywali Linked Server – również w tym przypadku możemy mieć rozbieżności w statystykach:

Próba stworzenia statystyk na tabelach zewnętrznych w ramach elastic query niestety na moment pisania tego artykułu nie jest dostępne:

Warto jednak pamiętać, że omawiany mechanizm wspiera Predicate Pushdown czyli możliwość “przerzucania” na źródło filtracji np. poniższe zapytanie:

Również po stronie źródłowej bazy danych możemy wyśledzić zapytanie wysłane z poziomu innej bazy wykorzystując m.in sys.dm_exec_query_stats:

wygląda ono w następujący sposób:

Nic nie stoi na przeszkodzie aby tworzyć nieco bardziej zaawansowane zapytania łączące tabele z kilku baz – dla przykładu mam tutaj jedną tabelę lokalną (SalesLT.SalesOrderHeaderInternal) którą stworzyłem w międzyczasie i połączyłem z tabelami zewnętrznymi:

Mogą tutaj powstać pewne problemy z niedopasowaniem statystyk jednakże mimo wszystko mogą one być zminimalizowane przez wbudowane mechanizmy Intelligent /daptive Query Processing, a przy mniejszych zbiorach danych może to w cale nie być problem:

Dodatkowo jeśli byśmy chcieli wykonać całe zapytanie na źródłowej bazie to możemy wykorzystać wbudowaną procedurę sp_execute_remote do której wskazujemy którego źródła chcemy użyć i jakie zapytanie wykonać:

Przy pomocy sp_execute_remote możemy również bez problemu wywoływać procedury czy też funkcje:

Ostatnią rzeczą na jaką chciałbym zwrócić waszą uwagę to fakt, że aby dostać się do bazy w ramach Elastic Query to na poziomie serwera tejże bazy musimy umożliwić połączenie dla usług Azure. Bardzo łatwo to zrobić za pomocą portalu:

W przeciwnym przypadku otrzymamy następujący błąd:

Oprócz tego Elastic Query charakteryzuje się kilkoma dodatkowymi cechami:

  • brak dodatkowych kosztów związanych z używaniem funkcjonalności,
  • poprzez external tables nie możemy wstawiać czy też modyfikować danych, jedynie odczytywać (jednakże przy pomocy sp_execute_remote jest taka możliwość),
  • External Tables mogą być oparte jedynie o tabele lub widoki znajdujące się w innym Azure SQL,
  • rozproszone zapytania nie są objęte transakcją jednkże jest możliwe użycie tzw. Elastic Transaction (link),
  • użytkownicy mają dostęp do źródłowych obiektów do których dostęp ma tworzony DATABASE SCOPED CREDENTIAL.

Z omawianym mechanizmem wiąże się również wspomniany we wstępie sharding czyli partycjonowanie horyzontalne ale o tym temacie postaram się opowiedzieć w osobnym artykule – pozdrawiam serdecznie i zapraszam do subskrypcji oraz do polubienia na facebooku.

Linki:

Leave a Reply

Your email address will not be published. Required fields are marked *