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):

CREATE MASTER KEY ENCRYPTION 
BY PASSWORD = 'zaq1@WSX';
go

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:

CREATE DATABASE SCOPED CREDENTIAL ElasticCred
WITH IDENTITY = 'test',
SECRET = 'zaq1@WSX';  
go

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ć:

CREATE EXTERNAL DATA SOURCE MyElasticDBQueryDataSrc WITH
    (TYPE = RDBMS,
    LOCATION = 'sqsqlserver.database.windows.net',
    DATABASE_NAME = 'sqsqldb2',
    CREDENTIAL = ElasticCred,
) ;
go

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:

CREATE SCHEMA SalesLT
GO

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:

CREATE TYPE [dbo].[Name] FROM [nvarchar](50) NULL
GO

CREATE TYPE [dbo].[Phone] FROM [nvarchar](25) NULL
GO

CREATE TYPE [dbo].[NameStyle] FROM [bit] NOT NULL
GO

CREATE TYPE [dbo].[Flag] FROM [bit] NOT NULL
GO

CREATE TYPE [dbo].[OrderNumber] FROM [nvarchar](25) NULL
GO

CREATE TYPE [dbo].[AccountNumber] FROM [nvarchar](15) NULL
GO

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:

CREATE EXTERNAL TABLE [SalesLT].[Customer](
	[CustomerID] [int] NOT NULL,
	[NameStyle] [dbo].[NameStyle] NOT NULL,
	[Title] [nvarchar](8) NULL,
	[FirstName] [dbo].[Name] NOT NULL,
	[MiddleName] [dbo].[Name] NULL,
	[LastName] [dbo].[Name] NOT NULL,
	[Suffix] [nvarchar](10) NULL,
	[CompanyName] [nvarchar](128) NULL,
	[SalesPerson] [nvarchar](256) NULL,
	[EmailAddress] [nvarchar](50) NULL,
	[Phone] [dbo].[Phone] NULL,
	[PasswordHash] [varchar](128) NOT NULL,
	[PasswordSalt] [varchar](10) NOT NULL,
	[rowguid] [uniqueidentifier] NOT NULL,
	[ModifiedDate] [datetime] NOT NULL
)WITH (DATA_SOURCE = MyElasticDBQueryDataSrc)
GO


CREATE EXTERNAL TABLE [SalesLT].[SalesOrderDetail](
	[SalesOrderID] [int] NOT NULL,
	[SalesOrderDetailID] [int] NOT NULL,
	[OrderQty] [smallint] NOT NULL,
	[ProductID] [int] NOT NULL,
	[UnitPrice] [money] NOT NULL,
	[UnitPriceDiscount] [money] NOT NULL,
	[LineTotal]  [numeric](38,6) NOT NULL,
	[rowguid] [uniqueidentifier] NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
)WITH (DATA_SOURCE = MyElasticDBQueryDataSrc)
GO

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:

CREATE EXTERNAL TABLE [dbo].[SalesOrderDetailExternal](
	[SalesOrderID] [int] NOT NULL,
	[SalesOrderDetailID] [int] NOT NULL,
	[OrderQty] [smallint] NOT NULL,
	[ProductID] [int] NOT NULL,
	[UnitPrice] [money] NOT NULL,
	[UnitPriceDiscount] [money] NOT NULL,
	[LineTotal]  [numeric](38,6) NOT NULL,
	[rowguid] [uniqueidentifier] NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
)WITH 
(
	DATA_SOURCE = MyElasticDBQueryDataSrc,
	SCHEMA_NAME = N'SalesLT',  
    OBJECT_NAME = N'SalesOrderDetail'
)
GO

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.

• sys.tables – zawiera kolumnę is_external pozwalającą odnaleźć tabele zewnętrzne,
• sys.external_tables – dedykowana tabel zawierające informacje o zewnętrznych tabelach,
• sys.external_data_sources – zawierające wszystkie zewnętrzne źródła danych.

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:

CREATE STATISTICS MyStats
ON [SalesLT].[SalesOrderDetail](SalesOrderID)
GO

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

SELECT * FROM [SalesLT].[SalesOrderDetail]
WHERE SalesOrderID = 71774

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:

SELECT  
 st.text,qs.creation_time
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
order by qs.creation_time desc

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

DECLARE @productVersion VARCHAR(20);
SELECT @productVersion = CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20));
IF CONVERT(INT, LEFT(@productVersion, CHARINDEX('.', @productVersion) - 1)) >= 12
    EXEC sp_executesql 
         N'SET CONTEXT_INFO 0xDEC7E180F56D3946A2F5081A9D2DAB360000431EFCF73E2D5A8ECAB89E049F14644AA3721108C64058A7';
SET ANSI_NULLS ON;
SET ANSI_WARNINGS ON;
SET ANSI_PADDING ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
SET DATEFIRST 7;
SET DATEFORMAT MDY;
SET LANGUAGE N'us_english';
SELECT [T1_1].[SalesOrderID] AS [SalesOrderID], 
       [T1_1].[SalesOrderDetailID] AS [SalesOrderDetailID], 
       [T1_1].[OrderQty] AS [OrderQty], 
       [T1_1].[ProductID] AS [ProductID], 
       [T1_1].[UnitPrice] AS [UnitPrice], 
       [T1_1].[UnitPriceDiscount] AS [UnitPriceDiscount], 
       [T1_1].[LineTotal] AS [LineTotal], 
       [T1_1].[rowguid] AS [rowguid], 
       [T1_1].[ModifiedDate] AS [ModifiedDate]
FROM
(
    SELECT [T2_1].[SalesOrderID] AS [SalesOrderID], 
           [T2_1].[SalesOrderDetailID] AS [SalesOrderDetailID], 
           [T2_1].[OrderQty] AS [OrderQty], 
           [T2_1].[ProductID] AS [ProductID], 
           [T2_1].[UnitPrice] AS [UnitPrice], 
           [T2_1].[UnitPriceDiscount] AS [UnitPriceDiscount], 
           [T2_1].[LineTotal] AS [LineTotal], 
           [T2_1].[rowguid] AS [rowguid], 
           [T2_1].[ModifiedDate] AS [ModifiedDate]
    FROM [SalesLT].[SalesOrderDetail] AS T2_1
    WHERE([T2_1].[SalesOrderID] = CAST((71774) AS INT))
) AS T1_1;

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:

SELECT 
	 YEAR(SOH.[OrderDate]) AS Year
	,AVG([UnitPrice]) AS AvgUnitPrice
	,SUM([OrderQty]) AS OrderQty
FROM [SalesLT].[SalesOrderHeaderInternal] AS SOH
JOIN [SalesLT].[SalesOrderDetail] AS SOD
ON SOD.[SalesOrderID]=SOH.[SalesOrderID]
JOIN [SalesLT].[Customer] AS C
ON C.[CustomerID]=C.[CustomerID]
GROUP BY
	YEAR(SOH.[OrderDate])
ORDER BY Year

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ć:

EXEC sp_execute_remote  
    N'MyElasticDBQueryDataSrc',  
    N'SELECT COUNT(*) FROM [SalesLT].[Product]'

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

CREATE PROC dbo.uspGetProducts
AS
SELECT * 
FROM  [SalesLT].[Product]
GO

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:

Msg 46823, Level 16, State 1, Line 210
Error retrieving data from sqsqlserver.database.windows.net.sqsqldb2.  
The underlying error message received was: 'Cannot open server 'sqsqlserver' 
requested by the login. Client with IP address 'NN.NNN.NNN.NN' 
is not allowed to access the server.  
To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule 
on the master database to create a firewall rule for this IP address or address range.  
It may take up to five minutes for this change to take effect.'.

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:

• Elastic query overview
• Query across cloud databases with different schema