SELECT Explanation, Example FROM Pro.Knowledge
FacebookRSS

Zapytania rozproszone i zewnętrzne źródła danych

W SQL Server mamy dostępnych kilka metod, dających nam dostęp do danych zewnętrznych.
W artykule tym, przedstawiam możliwości funkcji OPENQUERY, za pomocą której możemy tworzyć zapytania „Ad Hoc” (spontanicznie) do innych zasobów.

Zaprezentuję także metody wykorzystujące obiekty serwerów powiązanych (Linked Servers). Te z kolei stosujemy, aby utworzyć „stałe” powiązanie z innymi zasobami i w wygodny sposób, regularnie sięgać do zewnętrznych źródeł.


Zapytania rozproszone (Distributed Queries)

Komunikacja w środowiskach rozproszonych odbywa się zawsze w oparciu o interfejs pośredniczący. Standardem są sterowniki ODBC (Open DataBase Connectivity), za pomocą których możemy łączyć się z innymi bazami danych. Każdy producent serwera bazodanowego, udostępnia zazwyczaj w pakiecie klienckim wszystkie wymagane komponenty. Jeśli masz potrzebę łączenia się np. z bazą MySQL czy Oracle, powinieneś zainstalować właściwe dla danego dostawcy biblioteki.

Microsoft, od lat rozwijał równolegle własną koncepcje warstwy pośredniczącej – tzw. dostawców OLE DB (Object Linking and Embedding Database). Biblioteki te, rozszerzają możliwości ODBC i dotyczą połączeń nie tylko do serwerów bazodanowych ale także dostępu do zasobów plikowych (Excel, Access czy nawet zwykłych, płaskich plików tekstowych).

Jeśli chodzi o komunikację z SQL Server, w wersjach 2005-2012, to właśnie dostęp za pośrednictwem Native Client OLE DB był sugerowaną (szybszą, o większych możliwościach) metodą dostępu do danych. Sterowniki Native Client, zawierają w sobie bibliotekę OLE DB oraz ODBC. Dla wielu z pewnością dużym zaskoczeniem, był nagły zwrot w kierunku standardu ODBC w SQL Server 2014. Native Client 11 OLE DB z SQL Server 2012 – jest ostatnim w tej architekturze sterownikiem (więcej na ten temat, możesz przeczytać nas stronach Microsoft dot. kierunku rozwoju oraz oficjalnego powrotu do standardu ODBC)

Komunikacja, pomiędzy klientami, instancją SQL Server (do wersji 11) i środowiskami rozproszonymi odbywa się za pośrednictwem OLE DB.
SQL_SERVER_DISTRIBUTED_QUERIES


Funkcja OPENROWSET

Za pomocą funkcji OPENROWSET, możemy tworzyć zapytania rozproszone (Distributed Queries), odpytujące praktycznie dowolne zbiory zewnętrzne. Jedynym ograniczeniem jest dostępność na serwerze właściwych bibliotek OLE DB lub sterowników ODBC. Funkcja OPENROWSET umożliwia nie tylko odpytywanie zasobów, ale również pozwala na wykonywanie za jej pośrednictwem poleceń typu DML (Insert, Update, Delete). Ponieważ jest to funkcja tabelaryczna, stosować możemy ją analogicznie jak zwykłą tabelę (np. łącząc ją z innymi zbiorami).

Wymagania początkowe – konfiguracja serwera

Warunkiem koniecznym na wykorzystanie tej funkcji, jest włączenie na poziomie serwera, możliwości pisania zapytania rozproszonych (Ad Hoc Distributed Queries). To zadanie wykonamy za pomocą instrukcji :

exec sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
 
exec sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE WITH OVERRIDE;
GO

Od tego momentu, możemy tworzyć zapytania „Ad hoc”, korzystające z OPENROWSET. W dalszej części zaprezentuję typowe przykłady zastosowań.

OPENROWSET w praktyce

Odpytywanie źródeł zewnętrznych za pomocą tej funkcji, wymaga podania kilku parametrów. Nie będę omawiać tu wszystkich możliwych opcji, zwrócę uwagę tylko na najważniejsze i najczęściej wykorzystywane w praktyce.

Typowe użycie funkcji OPENROWSET – odpytywanie innego SQL Servera :

SELECT *
FROM OPENROWSET(
         -- Provider_name
	'SQLNCLI',  				
 
         -- Connection String
	'SERVER=ServerZdalny\Instancja; TRUSTED_CONNECTION=yes;',
 
         -- Query
         'SELECT * FROM Northwind.dbo.Customers'
	) AS tabela

Pierwszym parametrem jest określenie dostawcy OLE DB (provider_name) za pośrednictwem którego wykonywane będzie połączenie.

Regułą jest stosowanie dedykowanego dostawcy dla określonego źródła. Jeśli nie jest on dostępny, możesz skorzystać z uniwersalnej biblioteki MSDASQL i połączenia via ODBC.

Listę dostawców OLE DB których masz zainstalowane na swoim serwerze, odnajdziesz nawigując w drzewie hierarchii obiektów w Management Studio :

OLE_DB_Providers

lub za pośrednictwem specjalnej procedury składowanej :

EXEC xp_enum_oledb_providers

OLE_DB_Providers2

W zależności z jakiego oprogramowania korzystasz, ta lista może się różnić.

Warto tutaj wspomnieć, że można utworzyć połączenia ze zdalnymi serwerami na różne sposoby. Już choćby z tej listy widać, że do SQL Server, można się odwołać za pomocą przynajmniej trzech dostawców – SQLOLEDB (ten jest stosowany domyślnie), SQLNCLI oraz uniwersalnego MSDASQL, wykorzystującego pośrednio połączenia ODBC. Wybór dostawcy, powinien być podyktowany konkretnymi potrzebami programistycznymi (więcej na temat różnic znajdziesz np. tutaj ).

Drugim parametrem jest connection string, czyli charakterystyczny dla danego dostawcy zbiór opcji, które określają warunki połączenia. W przypadku SQL Server, będzie to na pewno nazwa sieciowa serwera / instancji, tryb uwierzytelniania, czy wybór konkretnej bazy danych.

Trzecim parametrem jest nasza zdalna kwerenda. Tutaj od razu bardzo ważna uwaga – pamiętaj o tym, ze cały ten zbiór, zwracany przez to zdalne zapytanie, będzie transportowany do serwera z którego je wywołujesz. Może to być przyczyną problemów m.in. sieciowych, trwać długo czyli również przekładać się bezpośrednio na czas trwania zapytania, blokad etc… Zapytania rozproszone, powinny zwracać zawsze minimalny, konieczny zbiór danych. Chyba również z tego powodu, domyślnie możliwość ich wykonywania jest wyłączona.

Zapytania do bazy ORACLE

Jak to bywa, możemy zrealizować tego typu zapytanie ad hoc na kilka sposób. Poniżej parę przykładów – dla każdego coś miłego. Pierwsze trzy zakładają istnienie wpisu TNS w pliku {ORA_HOME}\Network\Admin\tnsnames.ora. W ostatnim przykładzie, jawnie podaje wszystkich parametry komunikacyjne do serwera zdalnego.

-- z providerem OLEDB Oraclowym + wpis w {ORA_HOME}\Network\Admin\tnsnames.ora
SELECT * 
FROM OPENROWSET(
	'OraOLEDB.Oracle',
	'NaszOracleSrv';'username';'paswword', 
	'SELECT ''works fine'' as Kol1 FROM Dual' )
 
-- z providerem MSowym do Oracle 
Select * 
FROM OPENROWSET(
	'MSDAORA',
	'NaszOracleSrv';'username';'paswword', 
	'SELECT ''works fine'' as Kol1 FROM Dual' )
 
-- a tu inaczej - wszystko w Connection Stringu + połączenie via ODBC z wpisem w {ORA_HOME}\Network\Admin\tnsnames.ora
SELECT * 
FROM OPENROWSET(
	'MSDASQL', 
	'DRIVER={Microsoft ODBC for Oracle}; UID=username; PWD=password; SERVER=NaszOracleSrv;', 
	'SELECT ''works fine'' as Kol1 FROM Dual' 
	) as Tabela
 
-- a tu inaczej - wszystko w Connection Stringu + połączenie via ODBC
SELECT * 
FROM OPENROWSET(
	'MSDASQL', 
	'DRIVER={Microsoft ODBC for Oracle}; UID=username; PWD=password; SERVER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.1.1)(PORT=1521)))(CONNECT_DATA=(SID=NaszOracleSID)));', 
	'SELECT ''works fine'' as Kol1 FROM Dual' 
	) as Tabela

Zapytania do baz MySQL i innych z użyciem MSDASQL + ODBC

Jeśli nie mamy dostępnego dedykowanego dostawcy OLE DB, możemy łączyć się ze zdalnym serwerem za pomocą MSDASQL i połączenia wykorzystującego sterowniki ODBC. Listę dostępnych driverów, zainstalowanych na maszynie, sprawdzisz w panelu administracyjnym ODBC :

ODBC_Drivers

W przypadku odpytania bazy MySQL z poziomu SQL Server, potrzebne będą sterowniki ODBC (jak widać powyżej, mam takie zainstalowane). Sama kwerenda jest bardzo podobna do poprzedniej. Zastosowałem inny sterownik ODBC (MySQL) i dlatego connection string, którego struktura jest określana przez producenta, różni się w stosunku do poprzedniej, gdy odpytywałem bazę Oracle.

  SELECT  * 
  FROM OPENROWSET(
	'MSDASQL',
       'Driver={MySQL ODBC 5.2 ANSI Driver}; 
  Server=MyServerMySQL; Database=MojaBaza; USER=Username; PASSWORD=alamakota',
	'SELECT * from tabelka limit 10' )

Za pomocą OPENROWSET, możemy odpytywać także zawartości plików. Mogą to być zarówno pliki tekstowe, strukturyzowane (CSV), dokumenty Excel czy „bazy danych” Access. Dość wygodnie, za pomocą OPENQUERY przeczytamy również całe pliki XML. Pobieranie danych z Excela oraz plików tekstowych, opisuje szczegółowo w kolejnych rozdziałach tego kursu.

Poniżej kilka prostych, ale praktycznych przykładów :

-- Czytanie zwykłego pliku txt
SELECT F1 as FileContent 
FROM OPENROWSET(
	'Microsoft.ACE.OLEDB.12.0', 
    'Text;Database=D:\data\;HDR=NO', 
    'SELECT *  FROM test.txt')
 
-- załadowanie pliku XML (można go przypisać do zmiennej
SELECT CONVERT(xml, BulkColumn ) as XmlDocs
FROM 
OPENROWSET (BULK 'D:\DaneXML\NBP\a051z140314.xml',SINGLE_BLOB) as T1

OPENQUERY_1


Serwery powiązane – Linked Servers

Do tej pory, korzystaliśmy z tzw. zapytań Ad hoc – spontanicznych kwerend, do pobrania określonych danych z zewnętrznego źródła. W środowiskach produkcyjnych, istnieją serwery, które regularnie odpytujemy. Pisanie za każdym razem, długich connection stringów, jest niewygodne.

Za pomocą obiektów serwerów powiązanych, możemy zrealizować integrację danych z różnych środowisk w wygodniejszy sposób.

Na wstępie warto jeszcze raz podkreślić, że zapytania rozproszone, niezależnie od metody (OPENROWSET, Linked Server) powinny być stosowane z rozwagą, bo mogą być przyczyną problemów wydajnościowych (sieć, czasy blokad etc.) w środowiskach transakcyjnych.

Świadomi tego :), możemy zabrać się za tworzenie obiektów serwerów powiązanych. Jeśli jesteś zwykłym użytkownikiem bazy danych, potrzebujesz do tego uprawnień serwerowych ALTER ANY LINKED SERVER.

Serwery powiązane, możesz tworzyć w T-SQL (służy do tego procedura sp_addlinkedserver) lub za pomocą wizarda w Management Studio.

Linked_server_01

Całość konfiguracji, ogranicza się zazwyczaj do określenia dostawcy OLE DB, nazw serwera, ewentualnie połączenia ODBC oraz ustawień związanych z bezpieczeństwem. W przypadku SQL Server, wystarczy podać nazwę sieciową instancji – zostanie utworzony serwer powiązany, domyślnie z uwierzytelnianiem zintegrowanym, bazujący na natywnym kliencie SQLNCLI OLE DB.

Pisanie zapytań z wykorzystaniem Linked Servers

Odwoływanie się w kwerendach rozproszonych do obiektów serwerów powiązanych, jest możliwe na dwa sposoby.
Za pomocą funkcji tabelarycznej OPENQUERY(), możemy wysyłać zapytanie do dowolnego obiektu Linked Server.

SELECT * 
FROM OPENQUERY(SerwerPowiazany,'SELECT * FROM TABELA')

W tej sytuacji, zostanie wykonane zapytanie na serwerze zdalnym w dokładnie takiej postaci, jak parameter query funkcji OPENQUERY. Czyli zostanie pobrana CAŁA zawartość odpytywanej tabeli i przesłana przez sieć do klienta. Porównaj takie dwa zapytania z filtracją rekordów :

-- źle
SELECT * 
FROM OPENQUERY(SerwerPowiazany,'SELECT * FROM TABELA')
WHERE KolID = 12345
 
-- dobrze :)
SELECT * 
FROM OPENQUERY(SerwerPowiazany,'SELECT * FROM TABELA WHERE KolID = 12345 ')

Zwrócą to samo, ale o ile lżejsze jest zapytanie drugie, z filtracją po stronie zdalnego serwera….
Funkcja OPENQUERY, pozwala również na wysyłanie poleceń typu DML.

Drugim sposobem, jest odwoływanie się w poleceniach SQL do obiektów (tabel, widoków czy procedur), po w pełni kwalifikowanej, czteroczłonowej nazwie. W ten sposób, możemy wywoływać także procedury składowane na zdalnym serwerze.

           <SERVER>.<BAZA_DANYCH>.<SCHEMAT>.<OBIEKT>

Zarówno pierwszy jak i drugi sposób, pozwala na pobieranie danych z zewnętrznych źródeł podobnie jak ze zwykłych tabel. Można łączyć je w zapytaniach, odwołując się jednocześnie do różnych serwerów (UWAGA na wydajność !!!).

SELECT c.*, o.OrderID  
FROM Server1.Northwind.dbo.Customers c 
		inner join Server2.Northwind.dbo.Orders o on c.CustomerID = o.CustomerID

Metadane związane z Linked Servers

Z obiektami Linked Servers, związanych jest kilka przydatnych widoków i procedur składowanych, które warto poznać. Listę dostępnych obiektów Linked Servers, loginów z nimi związanych, możesz obejrzeć poprzez widoki systemowe w bazie msdb (sys.servers, sys.linked_logins oraz sys.remote_logins).

Mamy także dostępnych kilka specjalnych procedur składowanych. Z bardziej praktycznych są sp_catalogs oraz sp_tables_ex, które pozwalają na podejrzenie wszystkich baz danych / tabel, dostępnych po przez dany obiekt (o ile dostawca OLE DB/ODB oferuje takie funkcjonalności). Poniżej przykład wywołania sp_catalogs do zdalnego SQL Servera o nazwie RFN.

  -- bazy danych
  sp_catalogs 'RFN'

Linked_server_02

Za pośrednictwem serwerów powiązanych, podobnie jak funkcji OPENROWSET, możemy odnosić się również do plików. Jest wiele dróg na import danych z plików. Jedno z nich to rozwiązanie oparte o Linked Servers. Poniżej przykład utworzenia obiektu „serwera”, będącego katalogiem z plikami. Do tego celu wykorzystam dostawcę Jet.OLEDB.4.0.

EXEC sp_addlinkedserver  PlikiWymiany, 
	'Jet 4.0', 
         'Microsoft.Jet.OLEDB.4.0',
         'D:\Dane\',
         NULL,
         'Text';

W katalogu D:\Dane\ – są umieszczone dwa pliki tekstowe :

LinkedServer_TXT_FILE_01

Na koniec zastosowanie procedury składowanej sp_tables_ex oraz odczytanie zawartości pierwszego z plików.

EXEC sp_tables_ex PlikiWymiany;
GO
 
SELECT * 
FROM PlikiWymiany...[test#txt];

LinkedServer_TXT_FILE_02


Podsumowanie

Opisane powyżej metody pracy w środowisku rozproszonym są z pewnością bardzo praktyczne i często stosowane. Korzystając z nich, pamiętaj o aspektach wydajnościowych. Pobieranie danych (czasem całych tabel) poprzez sieć ze zdalnych serwerów, z pewnością nie jest najlepszym pomysłem.

2 thoughts on “Zapytania rozproszone i zewnętrzne źródła danych

  1. Witam,
    Dostałem ostatnio w pracy SQL Server 2014 niestety okazało się że w providers nie ma Microsoft.Jet.OLEDB.4.0 ani Microsoft.ACE.OLEDB.12.0 czy jest jakiś sposób żeby dodać je do listy? Potrzebuję tego właśnie do łączenia się przez Linked Server z accessem, w poprzedniej edycji którą miałem – SQL 2005 miałem je i sporo rozwiązań mam zrobionych właśnie pod to narzędzie.

Leave a comment

Twój adres email nie zostanie opublikowany.

Uzupełnij równanie (SPAM protection) *