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.

Zapytania do danych XML w SQL

Zakres zagadnień dotyczących obecności XML’a w SQL Server jest szeroki. Pisanie zapytań do dokumentów XML, to bardzo często wykorzystywana w praktyce umiejętność. Dlatego też, postanowiłem umieścić rozdział wprowadzający do XML w ramach tego kursu.

Na początek, zaprezentuję ogólną koncepcję standardu XML i jego typowe zastosowania w praktyce. Pokażę gotowe recepty, przykłady – w jaki sposób pisać zapytania SQL do dokumentów XML.

W artykule tym omawiam podstawowe zastosowania funkcji OPENXML() oraz wbudowanych metod typu danych XML. Związanych z nimi wyrażeń XPath i elementów języka XQuery w aspekcie przeszukiwania dokumentów XML i przekształcania ich na postać relacyjną.


Czym jest XML

XML (ang. Extensible Markup Language) określany jest często jako „język”, ale podobnie jak SQL, nie jest to język programowania. XML to najbardziej popularny standard (język) opisu danych. Jego komercyjne pojawienie się pod koniec lat 90-tych, spowodowało wręcz eksplozję zastosowań.

Stosuje się go wszędzie tam, gdzie chcemy przekazać jakieś informacje. W ogólności, na XML możemy spojrzeć jak na opakowanie. To nic innego jak metadane opisujące i nadające sens przesyłanym informacjom.

XML to zbiór zasad, które pozwalają definiować dowolne, ale ściśle ustandaryzowane, hierarchiczne dokumenty. XML (czyli standard) posiada wiele rozszerzeń powiązanych z celem do którego został stworzony.

Jedną z głównych zalet XML, to możliwość silnego typizowania dokumentów (schematy XSD). Możemy dokonywać weryfikacji ich poprawności pod względem określonego schematu. Definiować elementy czy atrybuty jako obowiązkowe lub opcjonalne. Narzucać ograniczenia dla wartości – podobnie jak w konstrukcjach CHECK CONSTRAINT (np. zakresy). Standard ten jest bardzo elastyczny i niezależny od platformy.

Wymianę danych pomiędzy systemami najwygodniej realizować za jego pomocą, choć czasem może być postrzegany jako mocno nadmiarowy. Obecnie większość urządzeń udostępnia na przykład swoje dane konfiguracje, czy zbierane informacje w postaci plików XML.


XML w SQL Server

Podstawowe funkcjonalności związane z XML, pojawiły się już w SQL Server 2000 (np. funkcja OPENXML czy FOR XML). W późniejszych edycjach (od wersji 9 – 2005), XML stał się pełnoprawnym, natywnie wspieranym typem danych. Jego implementacja w SQL Server to szereg dedykowanych funkcjonalności takich jak wbudowane metody tworzenia dokumentów XML, specjalne typy indeksów czy mechanizmy przeszukiwania FLWOR (XQuery).

Przetwarzanie obiektów XML, jest wykonywane w oparciu o jego hierarchiczną strukturę drzewa. Jest to znacznie bardziej efektywny sposób niż metody przeszukiwania tekstów.

Natywny typ danych XML, możemy stosować w różnych miejscach np.

  • przy określaniu typu kolumn w definicjach tabel – do składowania dokumentów XML w bazie relacyjnej.
  • w definicji zmiennych – do przetwarzania dokumentów.
  • jako wartości zwracanych przez funkcję lub parametrów procedur składowanych.

Mamy możliwość tworzenia dokumentów XML bezpośrednio z postaci relacyjnej. Służą do tego polecenia FOR XML, rozszerzające składnię języka SQL. Ale nie o nich jest ten artykuł :)

W drugą zaś stronę – z postaci XML do postaci relacyjnej, możemy dokonać przekształceń za pomocą funkcji OPENXML() lub wbudowanych metod typu XML. Wykorzystując wyrażenia XPath i język zapytań XQuery. W artykule tym ograniczam się właśnie do omówienia sposobów, pobierania interesujących nas danych z dokumentów XML.


Przykład dokumentu XML

Jednymi z publicznie dostępnych dokumentów XML, na których zaprezentuję kilka przykładów zapytań, są publikowane przez NBP, aktualne kursy walut. Jest to typowe zastosowanie tego standardu, służące do przekazywania danych pomiędzy różnymi systemami. Pobierać je może każdy i niezależnie od platformy, przetwarzać zgodnie z własnymi potrzebami.

Fragment takiego pliku poniżej :

Odpytywanie_XML_w_SQL_01

Kilka słów na temat samej konstrukcji. Generalnie XML, daje dużą swobodę użytkownikowi w definiowaniu własnych dokumentów. Możemy określać dowolną strukturę za pomocą elementów oraz związanych z nimi atrybutów

<Element Atrybut=”wartość_atrybutu”> wartość_elementu </Element>

Każdy z nich może mieć przypisaną wartość, a ich typ, zakres czy charakter (obowiązkowy / opcjonalny) może być określony schematem.

Poprawny dokument XML musi posiadać korzeń. W naszym przykładzie jest to element nadrzędny o nazwie <tabela_kursow>. XML jest zawsze ściśle określony, stąd wrażliwy jest na małe / wielkie litery (case sensitive) oraz konieczność zamykania znaczników elementów :

 <nazwa_elementu> wartość </nazwa_elementu>

Poza tymi podstawowymi elementami konstrukcji, w typowym dokumencie XML określane są standardy kodowania, przestrzenie nazw (namespaces), czy powiązania do schematów, opisujących jego strukturę. W naszym przykładzie, mamy bardzo prosty dokument i w jego definicji te elementy nie są określone.


Przeszukiwanie pliku XML w SQL

Pierwszym przykładem, będzie odpytywanie pliku XML z poziomu T-SQL za pomocą funkcji OPENXML i prostych wyrażeń XPath. Wykorzystam do tego dokument XML, który możemy pobrać ze strony zasobów NBP – średnie kursy walut opublikowane 14 marca 2014.

Załóżmy, że chcemy przekształcić taki dokument na postać relacyjną – czyli zasilić naszą bazę, aktualnymi kursami walut.

Schemat tabeli, przechowującej kursy walut w postaci relacyjnej, będzie wyglądał tak :

USE TEMPDB
GO
 
CREATE TABLE [dbo].[KursyWalut](
	[Kod_Waluty] [char](3) NOT NULL,
	[Data] [date] NOT NULL,
	[Kurs_Sredni] [smallmoney] NOT NULL,
	[Przelicznik] [smallint] NOT NULL DEFAULT 1,
 
	 CONSTRAINT [PK_KursyWalut] PRIMARY KEY CLUSTERED 
	(
		[Kod_Waluty] ASC,
		[Data] ASC
	)
)

Oczywiście budując aplikację, należałoby zasilać bazę codziennie. Można to zrealizować na kilka sposobów, korzystając z paczek SSIS, własnych funkcji CLR lub pisząc proste skrypty w Visual Basic’u. W naszym przykładzie, zakładam, że mamy dostępny dokument XML na lokalnym serwerze na dysku D.

Załadowanie pliku XML do zmiennej w T-SQL

Pierwszym krokiem będzie wrzucenie pliku XML na serwer i załadowanie go do zmiennej z poziomu T-SQL. Wykorzystamy do tego celu funkcję OPENROWSET().

DECLARE @xmldata XML
 
SELECT @xmldata=BulkColumn
FROM 
OPENROWSET (BULK 'D:\DaneXML\NBP\a051z140314.xml',SINGLE_BLOB) as T1
 
SELECT @xmldata as Dokument

Odpytywanie_XML_w_SQL_02

Cały dokument, został załadowany do zmiennej typu XML. Możemy go wrzucić bezpośrednio do odpowiedniej kolumny w tabeli lub wyciągnąć z niego tylko istotne dla nas informacje.

Obiekty typu XML, wyświetlane są w Management Studio w postaci linku. Możemy podejrzeć taki dokument, po prostu klikając w taki link.
Odpytywanie_XML_w_SQL_03


Czytanie pliku XML za pomocą OPENXML()

Funkcja OPENXML jest dość wiekowa i dostępna była już w SQL Server 2000. Są z nią bezpośrednio związane dwie specjalne procedury składowane : sp_xml_preparedocument oraz sp_xml_removedocument.

OPENXML pozwala na proste szatkowanie dokumentu XML do postaci relacyjnej. Wyciąganie interesujących danych z XML i np. ładowanie ich do tabeli.

Algorytm pracy z tą funkcją obejmuje 3 kroki :

  1. Przygotowanie dokumentu – czyli załadowanie całego (!) do pamięci CACHE serwera. Służy do tego procedura sp_xml_preparedocument, do której przekazujemy jako parametr, dokument XML. Zwraca ona „uchwyt” do dokumentu – wskaźnik do pamięci CACHE pod którym jest on dostępny.
  2. Właściwe przeszukiwanie dokumentu za pomocą OPENXML, wykorzystując proste wyrażenia XPath do nawigacji po węzłach.
  3. Sprzątanie pamięci CACHE – zwalnianie zasobów za pomocą sp_xml_removedocument. Pamiętajmy o tym, żeby nie pozostawiać śmieci.

Z kolei funkcja OPENXML() przyjmuje 3 (opcjonalnie 4) parametry :

  • Pierwszym jest „uchwyt” (wskaźnik) do dokumentu w pamięci Cache Servera (zwracany przez sp_xml_preparedocument).
  • Drugim, ścieżka (XPath), wskazująca na punkt odniesienia jaki będzie stosowany w pobieraniu danych. Do tego miejsca w strukturze, będziemy mogli odnosić się w bloku WITH() tej funkcji.
  • Trzecim, flaga wskazująca na typ obiektów (elementy, atrybuty czy ich mix), których wartości chcemy zwrócić.
  • Czwartym, opcjonalnym jet definicja przestrzeni nazw

Dodatkowo, z funkcją OPENXML, powiązany jest blok WITH(), w którym możemy nawigować po elementach i atrybutach (z wykorzystaniem XPath), wyciągając z XMLa interesujące nas wartości.

Napiszmy więc skrypt, pobierający dane z pliku XML. Zgodnie z opisanym wcześniej algorytmem, przygotujemy dokument do obróbki, wyciągniemy interesujące nas informacje i wrzucimy je do tabeli dbo.KursyWalut. Na koniec zwolnimy zajęte przez nasz dokument zasoby w pamięci CACHE.

DECLARE @xmldata XML
 
-- 0. Załadowanie pliku do zmiennej
SELECT @xmldata=BulkColumn
FROM 
OPENROWSET (BULK 'D:\DaneXML\NBP\a051z140314.xml',SINGLE_BLOB) as T1
 
-- 1. Przygotowanie dokumentu
DECLARE @docHandle int
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmldata
 
-- 2. OPENXML - przyjmujemy za punkt odniesienia /tabela_kursow/pozycja
-- interesują nas wartości elementów (flaga = 2) i ładujemy do tabeli dbo.KursyWalut
 
INSERT INTO dbo.KursyWalut
SELECT 
	kod_waluty, Data_publikacji,
	cast ( Replace(kurs_sredni , ',','.') as smallmoney ) as Kurs, przelicznik
FROM
OPENXML(@docHandle, '/tabela_kursow/pozycja', 2)
WITH
(	
	data_publikacji varchar(10) '../data_publikacji',   -- poziom w górę
	kod_waluty	char(3) ,
	kurs_sredni	varchar(100) ,
	przelicznik int
)
 
-- 3. Sprzątanie pamięci 
EXEC sp_xml_removedocument @docHandle
GO
 
SELECT * from dbo.KursyWalut

Fragment zawartości tabeli dbo.KursyWalut, po załadowaniu danych z pliku XML :

Odpytywanie_XML_w_SQL_04

Nawigacja po węzłach za pomocą wyrażeń XPath, jest dość intuicyjna i podobna do określania ścieżki np. w systemie plików czy obiektach HTMLowych. Zresztą standard HTML wywodzi się z tej samej rodziny (SGML).


Przeszukiwanie XML za pomocą natywnych metod

Zaprezentowana funkcja OPENXML, jest bardzo uboga w porównaniu z pełną implementacją typu danych XML.

Typ XML, posiada pięć wbudowanych metod, które dają szerokie możliwości jego przeszukiwania i modyfikacji. Wszystkie bazują na języku zapytań XQuery i związanych z nim wyrażeniach FLWOR. Działanie pierwszych trzech, które stosuje się typowo do wyciągania informacji z dokumentów XML zaprezentuję na przykładach.

Typ danych XML posiada następujące, natywne metody :

  • value() – zwraca pojedynczą wartość, wynik zapytania XQuery w postaci skalarnego typu danych.
  • query() – zwraca fragment (wycinek) dokumentu XML.
  • nodes() – szatkuje dokument XML na wiele mniejszych, podrzędnych.
  • exists() – przeszukuje dokument XML pod kątem istnienia w nim określonych elementów. Zazwyczaj stosowana w budowaniu warunków, wyrażeń logicznych w WHERE.
  • modify() – służy do aktualizacji dokumentu XML.

Zapytanie do dokumentu XML, pokazane w poprzednim przykładzie za pomocą funkcji OPENXML(), możemy zrealizować wykorzystując znacznie szybsze (!), natywne metody value() oraz nodes(). Ich przewagę widać na pierwszy rzut oka. Są czytelniejsze i bardziej wygodne. Prawdziwą swoją moc, pokażą gdy zastosujesz dodatkowo, specjalne typy indeksów XML.

Alternatywne zapytanie do dokumentu XML, pobierające dane za pomocą metod wbudowanych w typ XML, będzie wyglądało tak :

DECLARE @xmldata XML
 
SELECT @xmldata=BulkColumn
FROM 
OPENROWSET (BULK 'D:\DaneXML\NBP\a051z140314.xml',SINGLE_BLOB) as T1
 
 
-- Zastosoowanie XQuery
-- meotda nodes() dzieli 
 
SELECT  
	Tabliczka.element.value('kod_waluty[1]','char(3)') as kod_waluty,
	Tabliczka.element.value('../data_publikacji[1]','varchar(10)') as data_publikacji,
	Cast( Replace(Tabliczka.element.value('kurs_sredni[1]','varchar(10)'),',','.') as smallmoney)  as kurs_sredni,
	Tabliczka.element.value('przelicznik[1]','int') as przelicznik
      -- , Tabliczka.element.value('.././@typ[1]','varchar(10)') as Typ
 
FROM 
	@xmldata.nodes ('/tabela_kursow/pozycja') as Tabliczka(element)
 
-- metoda query() zwraca fragment dokumentu XML
SELECT  @xmldata.query('/tabela_kursow/pozycja[kod_waluty="USD"]/kurs_sredni') as Query
 
-- metoda value() – wartość skalarną
 SELECT  @xmldata.value('(/tabela_kursow/pozycja[kod_waluty="USD"]/kurs_sredni)[1]',
                                                              'varchar(10)') as Value

Odpytywanie_XML_w_SQL_05

Działanie metody nodes() jest podobne do funkcji tabelarycznych, dlatego możemy ją stosować we FROM. Poszatkowała nam ona dokument XML, na zbiór mniejszych elementów. Żeby lepiej sobie to wyobrazić, każdy z nich mógłby wyglądać mniej więcej tak :

    <nazwa_waluty>dolar amerykański</nazwa_waluty>
    <przelicznik>1</przelicznik>
    <kod_waluty>USD</kod_waluty>
    <kurs_sredni>3,0481</kurs_sredni>

Upraszcza to sposób przeszukiwania i odwoływania do fragmentów dokumentu XML.

Co bardzo ważne – tablica elementów (Tabliczka) powstałych na wskutek działania nodes(), nie stanowi osobnych dokumentów. Każdy z nich jest przetwarzany w kontekście dokumentu głównego. Lepszą interpretacją działania nodes() jest utworzenie wektora węzłów (wskaźników) wewnątrz dokumentu głównego.

W ramach węzłów stworzonych przez tą funkcję, możemy sięgać do dowolnych elementów np. wyżej w hierarchii – wartości data_publikacji czy atrybutu typ (jest on zakomentowany w tym przykładzie).

Dwie kolejne kwerendy to proste przykłady zastosowania metod query() oraz value() do przeszukiwania elementów o określonej wartości.


Przeszukiwanie dokumentów XML z określoną przestrzenią nazw

Większość plików XML ma określoną przestrzeń nazw. Obydwie zaprezentowane w tym artykule metody pobierania danych z dokumentów XML, obsługują oczywiście przestrzenie nazw.

Korzystając z natywnych metod, możemy je definiować na różne sposoby. Każde z poniższych zapytań, zwróci poprawny rezultat :

USE AdventureWorks2008
GO
 
-- sposób 1
SELECT  
 hj.Resume.value(
 'declare namespace MyNS="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
 (/MyNS:Resume/MyNS:Name/MyNS:Name.First)[1]' ,'nvarchar(100)')  as FirstName,
 hj.Resume.value(
 'declare namespace MyNS="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
 (/MyNS:Resume/MyNS:Name/MyNS:Name.Last)[1]' ,'nvarchar(100)')  as LastName
FROM [HumanResources].[JobCandidate] hj;
 
-- sposób 2
WITH XMLNAMESPACES (N'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume' as MyNS)
SELECT
 hj.Resume.value(
 '(/MyNS:Resume/MyNS:Name/MyNS:Name.First)[1]' ,'nvarchar(100)')  as FirstName,
 hj.Resume.value(
 '(/MyNS:Resume/MyNS:Name/MyNS:Name.Last)[1]' ,'nvarchar(100)') as LastName
FROM [HumanResources].[JobCandidate] hj;
 
-- sposób 3
WITH XMLNAMESPACES (DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume' )
SELECT hj.Resume.value(
 '(/Resume/Name/Name.First)[1]' ,'nvarchar(100)')  as FirstName,
  hj.Resume.value(
 '(/Resume/Name/Name.Last)[1]' ,'nvarchar(100)') as LastName
FROM [HumanResources].[JobCandidate] hj

Odpytywanie_XML_w_SQL_07

Z kolei definiowanie przestrzeni nazw w przykładzie wykorzystującym funkcję OPENXML, sprowadza się do przekazania tej informacji w momencie przygotowania dokumentu, czyli wywołania procedury sp_xml_preparedocument. Przyjmuje ona opcjonalnie dodatkowy parametr.

Ponieważ funkcja OPENXML(), jest ściśle skojarzona z procedurami składowanymi, zastosowanie jej w celu osiągnięcia podobnego rezultatu (czytania wielu obiektów XML), wymaga działania na kursorach.

Widać więc, że nie jest to wygodna ani efektywna metoda, przeszukiwania dokumentów XML składowanych w tabeli. Być może wydawać się prostsza lub dla niektórych, (historycznie) bardziej bliska. Polecam jednak metody natywne, bo są bardziej uniwersalne i z pewnością szybsze.

Przetworzenie pierwszego dokumentu XML z określoną przestrzenią nazw za pomocą OPENXML, może wyglądać tak :

DECLARE @idoc int, @rootxmlns varchar(100)
DECLARE @doc xml 
 
SELECT TOP 1 @doc=Resume
FROM [HumanResources].[JobCandidate] hj
 
SET @rootxmlns = '<root xmlns:MyNS="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume" />'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc, @rootxmlns
 
SELECT    *
FROM       OPENXML (@idoc, '/MyNS:Resume/MyNS:Name ',1)
WITH
(	
	FirstName nvarchar(100)  './MyNS:Name.First',
	LastName nvarchar(100) './MyNS:Name.Last'
)
 
EXEC sp_xml_removedocument @idoc
GO

Odpytywanie_XML_w_SQL_08


Podsumowanie

Na temat języka zapytań XQuery oraz samego XML’a można napisać wiele artykułów. Z założenia, rozdział ten miał być zwięzłym wprowadzeniem i prezentować gotowe rozwiązania kwerend do dokumentów XML. Dlatego ograniczyłem się tylko do kilku podstawowych przykładów z wykorzystaniem funkcji OPENXML oraz metod typu danych XML – nodes(), value() oraz query(). Jeśli szukasz szerszej wiedzy na ten temat XML – zapraszam na moje kursy.

BULK INSERT – import danych z pliku do bazy SQL Server

Polecenie BULK INSERT jest rozszerzeniem języka T-SQL, funkcjonalnym odpowiednikiem narzędzia bcp z parametrem in. Za jego pomocą możemy wykonać szybki, masowy import danych z pliku tekstowego do istniejącej tabeli w bazie danych – z poziomu skryptu T-SQL. Idealnie nadaje się więc do automatyzacji zadań, definiowanych np. w JOBach, procedurach składowanych, czy skryptach adhoc, mających na celu import danych z plików tekstowych do bazy.

Artykuł ten ma na celu pokazanie najczęściej stosowanych parametrów, praktyczne przykłady użycia BULK INSERT.


Import surowej zawartości pliku tekstowego do tabeli

W najprostszym zastosowaniu, możemy zaimportować dowolny plik tekstowy do istniejącej tabeli w bazie SQL Server. Bardziej skróconej wersji zastosowania BULK INSERT nie ma.

create table dbo.test
(
   Full_Row varchar(1000)
)
 
 
BULK INSERT dbo.test
	FROM   'C:\temp\plik.txt'

Jest to najkrótsza i najskromniejsza komenda BULK INSERT. W tym przypadku, zastosowane będą domyślne znaczniki końca wartości kolumny (atrybutu) tzw. FIELDTERMINATOR którym będzie symbol /t czyli tabulator. Drugim istotnym symbolem – końca wiersza – ROWTERMINATOR będzie domyślnie złączenie /n/r, next row + carriage return. Ponieważ ładujemy wszystko do tabeli tymczasowej, która posiada tylko jedną kolumnę (Full_Row), domyślny znacznik FIELDTERMINATOR, nie będzie zastosowany. Każdy wiersz czytanego pliku, będzie ładowany do nowego wiersza tej tabelki.

Powyższa składnia jest o tyle przyjemna, że można w ten sposób wrzucić do tabeli tymczasowej zawartość dowolnego pliku tekstowego w zasadzie bez rozróżniania jego struktury, biorąc pod uwagę tylko koniec wiersza.


Import strukturyzowanej zawartości pliku tekstowego do tabeli

W typowej sytuacji importu, przechowujemy dane zorganizowane w określony sposób. Plik tekstowy reprezntuje, zazwyczaj tabelaryczny zbiór elementów, opisany za pomocą atrybutów (kolumn), których wartości rozdzielone są znakiem specjalnym (FIELDTERMINATOR).

Importując dane, musimy zadbać aby liczba kolumn, typy danych, były zgodne pomiędzy tabelą docelową a zawartością pliku. W przypadku niedopasowania typu danych oraz braku możliwości wykonania niejawnej konwersji typu danych, otrzymamy komunikat :

Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 3 (kolumna).

Skupmy się jednak na składni BULK INSERT. Żeby jawnie określić znaki terminujące wartości kolumn oraz wiersza (elementu), stosujemy parametry FIELDTERMINATOR oraz ROWTERMINATOR. Określane są one w bloku WITH.

Zawartość pliku który będziemy chcieli zaimportować niech wygląda tak :
BULK_INSERT_01
Jak widać, są w nim dwa wiersze, składające się z dwóch oraz trzech wartości. W takim razie do dzieła :

use TempDB
go
 
IF OBJECT_ID('dbo.test') is not null drop table dbo.test
 
create table dbo.test
(
	Field1 varchar(1000),
	Field2 varchar(1000)	
)
 
-- tutaj zaczyna się import z pliku C:\temp\test.txt do tabeli dbo.test
BULK INSERT dbo.test
	FROM   'C:\temp\test.txt'
	WITH
	(
		FIELDTERMINATOR = ',',
		ROWTERMINATOR = '\n', 
		CODEPAGE =  'ACP' -- | 'OEM' | 'RAW' | 'code_page'  
	)
 
select * from dbo.test

BULK_INSERT_02

W powyższym przykładzie zastosowałem parametr opcjonalny CODEPAGE, który określa stronę kodową znaków. ACP czyli kodowanie ANSI. W zależności od zawartości importowanego pliku, możemy zastosować inną, odpowiednią dla naszego środowiska stronę kodową (code_page).

Zauważ, że liczba atrybutów (kolumn) w pliku jest różna. W tej sytuacji nastąpi próba dopasowania struktury pliku do tabeli docelowej. W wierszu drugim, wartość czwarty,piąty została potraktowana jako jedna (druga kolumna).

Gdy importowane dane zawierają różne liczby kolumn oraz operacja niejawnego dopasowania jest możliwa – zostanie to wykonane. Należy jednak uważać na tego typu wyjątki, operacje importu/eksportu powinny być ściśle określone aby uniknąć pomyłek. Najlepiej w tym celu stosować dodatkowego parametru określającego plik formatu FORMATFILE (fmt). Tworzymy go np. za pomocą bcp i zawiera on informację o strukturze przechowywanych danych.

W przypadku braku możliwości dopasowania, lub jeśli np n-ty wiersz importowanych danych będzie zawierał mniej kolumn niż oczekuje tego struktura tabeli docelowej – otrzymasz następujacy, niewiele mówiący komunikat o błędzie.

Msg 4832, Level 16, State 1, Line 13
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 13
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 13
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

Pozostałe przydatne opcje BULK INSERT

FIRSTROW – od którego wiersza z pliku importować (wartość integer).

LASTROW – który wiersz ma być tym ostatnim (wartość integer).

KEEPNULLS – w trakcie importu możesz się spotkać z sytuacją, że będziesz importował dane zawierające null w kolumnach, na których jest zdefiniowana wartość domyslna (DEFAULT VALUE). W większości przypadków operacja eksportu i importu, powinna być symetryczna. Dlatego jeśli chcesz być pewien, że importowane dane będą dokładnie odzwierciedlone w tabeli docelowej (nie zostanie zamieniony NULL na DEFAULT VALUE) – skorzystaj z tej opcji.

KEEPIDENTITY – zachowuje oryginalne wartości IDENTITY w tabeli do której importujemy, nawet jeśli dane dla tej kolumny są określone. Właściwość wyłączania mechanizmów integralności wewnętrznej danych (constraints, foreign_keys) jest charakterystyczna dla importu masowego.

Poniższy przykład obrazuje zachowanie KEEPIDENTITY.
BULK_INSERT_03

use tempdb
go
 
IF OBJECT_ID('dbo.test') is not null drop table dbo.test
 
create table dbo.test
(
    -- na kolumnie id jest IDENTITY, powinno więc ono nadawać
    -- kolejne numery wierszy poczynając od 1
	id int IDENTITY(1,1),
	Field varchar(1000)
 
)
GO
 
BULK INSERT dbo.test
	FROM   'C:\temp\test2.txt'
	WITH
	(
		FIELDTERMINATOR = ',',
		ROWTERMINATOR = '\n',
		KEEPIDENTITY
	)
-- 3 razy zostanie wykonany BULK INSERT - GO 3 
GO 3 
 
select * from dbo.test

BULK_INSERT_04
Jak widać, pomimo istnienia IDENTITY na kolumnie Id – numeracja jest zgodna z danymi importowanymi z pliku.

FIRE_TRIGGERS – domyślnie w operacji BULK INSERT wszystkie triggery związane z operacją INSERT na danej tabeli są wyłączone. Operacja BULK INSERT jest importem masowym. W domyśle stosowany do zasilenia bazy maksymalnie szybko, dużymi ilościami danych. Często zależy nam na tym, aby wszelkiego rodzaju spowalniacze były domyślnie wyłączone – właśnie z powodu apsketu wydajnościowego.

Jeśli jednak od działania triggerów zależy spójność danych i powinny zostać wywołane, trzeba jawnie określić ich włączenie tym parametrem.

Pamiętać trzeba, że w SQL Server, triggery wywoływane są w kontekście całego batcha. Jeśli cały import jest wykonywany w jednym logicznym kroku (batch’u), triggery będą wywołane raz dla wszystkich wierszy. Jeśli import podzielony zostanie na n-batch’y (opcje BATCHSIZE i KILOBYTES_PER_BATCH), triggery wykonają się n razy na n partiach ładowanych wierszach.

Poniżej mały przykład pokazujący, że bez FIRE_TRIGGER w trakcie importu BULK INSERT, zdefiniowane triggery, na tabeli do której importujemy, nie działają.

use tempdb
go
 
IF OBJECT_ID('dbo.test') is not null drop table dbo.test
IF OBJECT_ID('dbo.test_arch') is not null drop table dbo.test_arch
 
create table dbo.test
(
	id int identity(1,1),
	Field1 varchar(1000) 	
)
GO
 
create table dbo.test_arch
(
	id int identity(1,1),
	Field_Arch varchar(1000)
)
GO
 
-- na tabeli dbo.test tworzymy trigger, który w ciemno kopiuje
-- wszystko co jest insertowane do dbo.test do tabeli dbo.test_arch
CREATE TRIGGER arch
	on dbo.test
AFTER INSERT
AS
BEGIN
 	insert into dbo.test_arch
	select 'new_' + Field1 from inserted
END
GO
 
BULK INSERT dbo.test
	FROM   'c:\temp\test2.txt'
	WITH
	(
		FIELDTERMINATOR = ',',
		ROWTERMINATOR = '\n'
	)
 
select * from dbo.test
select * from dbo.test_arch

BULK_INSERT_05

Tym razem BULK INSERT z opcją FIRE_TRIGGER

BULK INSERT dbo.test
	FROM   'c:\temp\test2.txt'
	WITH
	(
		FIELDTERMINATOR = ',',
		ROWTERMINATOR = '\n',
		FIRE_TRIGGERS		
	)
 
select * from dbo.test
select * from dbo.test_arch

BULK_INSERT_06

CHECK_CONSTRAINTS – podobnie jak triggery, domyślnie wszelkie ograniczenia sprawdzające, w tym również klucze obce, są ignorowane w trakcie wykonywania importu masowego. Wynika to z definicji – import masowy ma być wykonywany maksymalnie szybko, dane powinny być wcześniej zweryfikowane.

Po imporcie wszelkie ograniczenia na tabeli są oznaczane flagą not-trusted. Może skutkować to poważnymi konsekwencjami, nie tylko z punktu widzenia zachowania spójności danych, ale także degradacją wydajności wykonywanych zapytań. Po imporcie masowym, wszelkie ograniczenia (constraints oraz foreign_keys) powinny zostać zweryfikowane i oznaczone jako trusted.

Jeśli chcesz szybko znaleźć informacje o niezaufanych ograniczeniach wykonaj po imporcie następujące zapytanie :

SELECT OBJECT_NAME(parent_object_id) AS table_name, name, is_disabled  , is_not_trusted
FROM sys.check_constraints 
Where is_not_trusted = 1 or is_disabled = 1
UNION ALL
SELECT OBJECT_NAME(parent_object_id) AS table_name, name, is_disabled  , is_not_trusted
FROM sys.foreign_keys
Where is_not_trusted = 1 or is_disabled = 1
ORDER BY table_name

Da ono odpowiedź o wszystkich niezaufanych lub wyłączonych ograniczeniach. Żeby teraz na nowo aktywować/sprawdzić i oznaczyć jako zaufane, należy wykonać polecenie z double check.

-- sprawdzi i oznaczy jako zaufane (o ile nie będzie w kolumnach nieprawidłowych wartości)
-- wszystkie ograniczenia na tabeli dbo.tabela
ALTER TABLE dbo.tabela WITH CHECK CHECK CONSTRAINT all

Jeśli chcesz wykonać aktualizację na wszystkich tabelach w bazie, możesz do tego celu zastosowwać nieudokumentowną, ale użyteczną procedurkę sp_msforeachtable :

exec sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'

KILOBYTES_PER_BATCH oraz BATCHSIZE – określają podział importu na n części, wykonywanych w osobnych transakcjach. Paczki (batche) mogą być określone przez liczbę rekordów (BATCHSIZE) lub wielkość bezwzględną wyrażoną w KB.

TABLOCK – zakłada blokadę na całej tabeli do której importujemy dane. Przyspiesza to czas wykonania importu.

ORDER – określa sposób sortowania przed importem, aby również przyspieszyć jego wykonanie. Ma to szczególne znaczenie, gdy importujemy do tabeli z indeksem klastrowym, wtedy dodając posortowane elementy zgodnie z jego definicją, operacje przebudowy i utrzymania indeksu w trakcie importu są zoptymalizowane.

FORMATFILE – określa ścieżkę do pliku przechowującego informacje o metadanych importowanej struktury. Plik formatu możesz utworzyć za pomocą narzędzia bcp, jako część eksportu. Przydaje się w szczególności, gdy np. wykesportowane dane mają zamienione kolejności kolumn w stosunku do struktury tabeli do której je importujemy lub poprostu ich liczba się różni.

Import i eksport danych do SQL Server za pomocą BCP

BCP narzędzie do importu i eksportu masowego

Za pomocą narzędzia BCP, dostępnego z linii poleceń, możemy dokonywać importu / eksportu danych z bazy SQL Server do plików i odwrotnie. Korzystanie z tego narzędzia wymaga standardowych uprawnień, które trzeba posiadać aby móc pobrać dane z bazy i zapisać je w pliku. Będzie to możliwość połączenia się z serwerem (autentykacja) oraz do określonej bazy danych SQL Server (autoryzacja). Ponadto konieczny będzie dostęp do tabel lub widoków z których będziemy korzystali, oraz do systemu plików, w odpowiednim zakresie czytanie lub modyfikacji w zależności od kierunku operacji.

Narzędzie BCP (bulk copy) jest dostępne we wszystkich wersjach SQL Server (również Express). Dzięki temu że jest wywoływane z linii poleceń, wygodnie stosować je w skryptach automatyzujących pracę serwera. Jest też jedną z najbardziej wydajnych (najszybszych) metod przenoszenia danych pomiędzy bazą danych a systemem plików.


Eksport danych z bazy SQL Server do pliku

Przy eksporcie z bazy, konieczne będą uprawnienia do łączenia się z serwerem, bazą danych a także do wykonywania polecenia SELECT na obiektach z których chcemy pobierać dane. Dodatkowo potrzebować będziemy uprawnień do zapisu / modyfikacji plików w podanym katalogu docelowym. Poniżej zaprezentuję najważniejsze funkcjonalności BCP w praktycznych zastosowaniach – jeśli chcesz poznać wszystkie opcje – polecam MSDN / BOL.

Eksport całej zawartości tabeli do pliku CSV (tekstowego)

Pierwszym przykładem zastosowania bcp, będzie eksport całej zawartość tabeli Person.Person z bazy AdventureWorks2008. W tym celu, określam kierunek migracji danych – parametrem out (czyli export), z serwera, którego nazwę wskazuje flaga –S MyServer\SQLEXPRESS, do pliku tekstowego ExportedTable.csv w katalogu c:\temp. Do serwera połączymy się za pomocą Trusted Connection (flaga –T) czyli zintegrowanej autentykacji Windows.

C:\>bcp AdventureWorks2008.Person.Person out C:\temp\ExportedTable.csv -S MyServer\SQLEXPRESS -T –c

BCP_Export_01

Jeśli na serwerze na którym wykonujemy powyższy skrypt, byłaby tylko jedna domyślna instancja SQL Server, moglibyśmy sobie podarować parametr –S.
Parametr –c, określa format w jakim będzie wykonywany eksport i określa on wszystkie wartości jako znakowe (typ CHAR).

Określenie separatora

Domyślnym separatorem wartości kolumn (atrybutów) dla każdego rekordu, będzie w przypadku użycia formatu -c, znak \t (tabulator). Zakończeniem wiersza złączony symbol \r\n oznaczający \n następny wiersz (next row) + \r od początku (carriage return inaczej CR). Jeśli nie zdefiniujemy ich jawnie (flagami separatora –t oraz końca rekordu –r) to będą stosowane w trakcie eksportu, w taki właśnie sposób.

Kolejny przykład pokazuje jak połączyć się z bazą za pomocą uwierzytelniania SQL oraz jak określić jawną definicję separatora (nadpisuje on domyślny tabulator).

Aby użyć konkretnego konta SQL (np. użytkownika ExpAcc z hasłem AlaM@K0ta), musimy określić nazwę konta –U (username) oraz jego hasło –P. Separator, tym razem będzie określony za pomocą znaku ‘,’ często stosowany w plikach CSV.

C:\>bcp AdventureWorks2008.Person.Person out C:\temp\ExportedTable.csv -S MyServer\SQLEXPRESS –U ExpAcc –P AlaM@K0ta –c –t,

Wybór separatora jest podyktowany środowiskiem do którego będziemy później importować te dane, ale także charakterem przechowywanych danych. Z punktu widzenia bcp, separatorem może być dowolny ciąg do 10 znaków. Równie dobrze moglibyśmy zapisać parametr –t jako –t@@@@.

Czasem stosując zwykły przecinek czy średnik, nie zapewnimy odpowiedniego oznaczenia końca wartości. Aby zobrazować problem, przeanalizuj poniższy przykład.

 create table dbo.test
 (
	id int identity(1,1),
 	opis1 varchar(100),
	opis2 varchar(100)
 )
 
 insert into dbo.test values ('jeden,dwa','trzy'),('cztery','pięć')
 
select * from dbo.test

BCP_Export_08
Teraz wykonam eksport, określając separator jako przecinek. Zauważ, że pierwszy rekord zawiera ten znak w polu opis1.

C:\>bcp tempdb.dbo.test out c:\temp\test.txt -S MyServer\SQLEXPRESS -T -c -t,

Starting copy...

2 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1      Average : (2000.00 rows per sec.)

C:\>bcp tempdb.dbo.test in c:\temp\test.txt -S MyServer\SQLEXPRESS -T -c -t, -E

Starting copy...

2 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1      Average : (2000.00 rows per sec.)

C:\>

W poleceniu importu, zastosowałem dodatkowo flagę, której wcześniej nie omawiałem -E czyli keep identity, która pomimo istnienia kolumny IDENTITY, zachowuje wartości z eksportu. Sprwadźmy co znajduje się w testowej tabeli, po tak wykonanym procesie :
BCP_Export_09
Ważne jest aby wybór separatora był przemyślany. Jeśli zastosowany znak, będzie występował w eksportowanych wartościach, wynik importu będzie błędny.

Kodowanie znaków UNICODE w BCP

Stosowana powyżej metoda, nie zapewnia odpowiedniej obsługi znaków kodowanych w UNICODE czyli typów nchar/nvarchar (kodowanych na dwóch bajtach). Jeśli chcesz wyeksportować takie dane, trzeba użyć flagi –w, która określa wynikowy format kodowania znaków na UNICODE.
W ramach testu utworzę tabelkę :

use tempdb;
 
Create table dbo.Unicode_polskie
(
	opis nvarchar(10) 	
);
 
insert into dbo.Unicode_polskie values(N'Piękny'),(N'Dzień')

W przypadku eksportu znaków typu UICODE, musimy wykorzystać flagę -w określający kodowanie na dwóch bajtach :

C:\>bcp tempdb.dbo.Unicode_polskie out C:\temp\UniExport.csv -S MyServer\SQLEXPRESS -T -w

Eksport metadanych do pliku XML za pomocą BCP

W poprzednich przykładach wynikiem eksportu jest płaski plik z danymi w którym nie ma możliwości rozróżnienia typów danych (np. flaga –c, określała, że każda kolumna była typu znakowego). Informacje o strukturze, mogą być istotne w procesie odwrotnym – importu do innej bazy.

Pełen eksport – to dane oraz metadane, czyli informacji o strukturze zbioru, przechowywanego w pliku tj. typy danych atrybutów. Do tego celu musimy użyć dodatkowego pliku nazywanego plikiem formatu (format file).

Format file – zawiera opis struktury eksportowanego / importowanego zbioru. Narzędzie BCP umożliwia jego automatyczne utworzenie.

C:\>bcp AdventureWorks2008.Person.Person format nul -T -S MyServer\SQLEXPRESS  -c -x -f C:\temp\FileFormatPersonPerson.xml

W ten sposób, pełna informacja o wyeksportowanym zbiorze zawarta jest w dwóch plikacj – pliku z danymi (csv) i pliku ze strukturą zbioru (xml).

Eksport wyniku kwerendy do pliku

Eksportując dane, zazwyczaj interesuje nas pewien fragment zbioru – czyli wynik określonej kwerendy. BCP oprócz pokazanych wcześniej możliwości eksportu całego zbioru, umożliwa określenie kwerendy za pomocą operatora queryout. Załóżmy, że chcemy zapisać w pliku CSV wynik następującej kwerendy :

select p.FirstName, p.LastName, e.JobTitle, e.HireDate 
from Person.Person p inner join HumanResources.Employee e 
         on p.BusinessEntityId = e.BusinessEntityId
where LastName = 'Miller'

Poniższe użycie bcp, wyeksportuje do pliku wynik zapytania SQL.

C:\>bcp "select p.FirstName, p.LastName, e.JobTitle, e.HireDate from Person.Person p inner join HumanResources.Employee e on p.BusinessEntityId = e.BusinessEntityId where LastName='Miller'" 
queryout  C:\temp\ExportedQuery.csv -S MyServer\SQLEXPRESS -T -d AdventureWorks2008 -c -t;

Wszystkie flagi zastosowane w tym poleceniu były omówione wcześnniej – poza flagą –d określająca w jakiej bazie danych ma zostać wykonana kwerenda. Moglibyśmy użyć trój-członowej nazwy obiektów w kwerenedzie (AdventureWorks2008.Person.Person) – wynik byłby taki sam.

Istenienie tej flagi wynika między innymi z faktu, że każdy użytkownik ma domyślnie ustawioną bazę danych do której łączy się podczas nawiązywania nowej sesji z serwerem.

Eksport danych do pliku XML za pomocą BCP

W poprzednim przykładzie pokazałem, że można eksportować wynik dowolnej kwerendy do pliku. Eksport danych w postaci XML z bazy SQL Server za pomocą bcp sprowadza się do wykorzystania rozszerzenia języka T-SQL, klauzuli FOR XML.

Polecenie FOR XML (w czterech trybach RAW,AUTO,EXPLICIT i PATH) służy do przekształcenia wyniku kwerendy z postaci relacyjnej (tabelarycznej), na postać hierarchiczną XML. Jej opis jest dobrym tematem na osobny artykuł, w tym przykładzie zastosouje jedynie jej najuboższy tryb (RAW). Definicja naszej kwerendy, kształtujaca XML będzie następująca :

SELECT ProductID, Name, ListPrice 
FROM Production.Product 
where Name like '%CHAIN%' 
FOR XML RAW, root('Content')

W wyniku otrzymamy poprawny plik XML :
BCP_Export_03

Pełna składnia polecenia bcp, która nam wyeksportuje ten plik automatycznie, będzie wyglądała tak :

bcp "SELECT ProductID, Name, ListPrice FROM Production.Product where Name like '%CHAIN%' FOR XML RAW, root('Content') " queryout "c:\temp\sample.xml" -S MyServer\SQLEXPRESS -T -d AdventureWorks2008 –w –r -t

Import danych z pliku do bazy SQL Server

Operacje w przeciwnym kierunku, importowania danych z pliku do bazy SQL, określa parametr in w poleceniu bcp. Składnia jest praktycznie identyczna.

Każda operacja importu jest realizowana jako dodawanie zbioru wierszy do już istniejącej tabeli (obiekt docelowy musi istnieć). W naszym przykładzie na początek utworzę identyczną strukturę tabeli docelowej jak ta, z której eksportowałem dane.

-- kopiowanie podstawowej struktury tabeli 
SELECT * INTO AdventureWorks2008.Person.PersonArch
from AdventureWorks2008.Person.Person where 1=2

Następnie poleceniem BCP załadujemy do nowej tabeli całą zawartość pliku, wyeksportowanego w przykładzie pierwszym.

C:\>bcp AdventureWorks2008.Person.PersonArch in C:\temp\ExportedTable.csv -S MyServer\SQLEXPRESS -T –c

Wartości NULL

Istotną kwestią w operacjach typu import, jest zachowanie związane z wartościami pustymi oraz nieokreślonymi (NULL). Są one rozróżniane w eksportowanym pliku, choć na pierwszy rzut oka nie można stwierdzić (zajrzyj do zawartości eksportowanego pliku). Operacje OUT i IN w sumarycznym wyniku muszą być spójne. Sprawdźmy zatem czy faktycznie są.

USE tempdb
GO
 
CREATE TABLE dbo.test
(
	id int identity(1,1),
	opis varchar(10),
	opis2 varchar(10),
);
 
INSERT INTO test VALUES('jeden','dwa'),('pięć','trzy'),('','cztery'),
(null,null),('',null),('',''),(' ','spacja')
 
-- skopiowanie struktury
SELECT * INTO dbo.test2 FROM dbo.test WHERE 1=2
 
-- co będziemy chcieli eksportować
SELECT * FROM dbo.test

BCP_Export_05

Mamy zatem zdefniowaną tabelę dbo.test z której będziemy eksportowali oraz dbo.test2 w tej samej bazie, do której będziemy importować dane. Wykonajmy następujące operacje out oraz in .

Najpierw EXPORT.

C:\Users\jakub.kasprzak>bcp tempdb.dbo.test out c:\temp\test.csv 
  -S MyServer\SQLEXPRESS -T -w -t;

Zawartość wyeksportowanego pliku nie zdradza informacji o NULLach, są one jednak odpowiednio kodowane jako znaki specjalne.
BCP_Export_06

Teraz IMPORT :

 
C:\Users\jakub.kasprzak>bcp tempdb.dbo.test2 in c:\temp\test.csv 
  -S MyServer\SQLEXPRESS -T -w -t;

Sprawdźmy teraz zawrtości tabel – wynik będzie identyczny.

-- stąd eksportowałem
select * from dbo.test
 
-- tutaj importowałem
select * from dbo.test2

Problemem, w tym procesie exportu/importu, mogą okazać się wartości domyślne zdefiniowane w kolumnach tabeli do której importujemy dane.

Przejdźmy jeszcze raz przez cały proces, ale zmodyfikujemy definicję kolumny opis2 w tabeli dbo.test2, dodając jej wartość domyślną.

-- na początek czyszczenie tabeli dbo.test2
TRUNCATE TABLE dbo.test2
-- dodanie wartości domyślnej dla kolumny opis2
ALTER TABLE dbo.test2 ADD DEFAULT ('DEFAULT') FOR opis2

Wynik operacji IMPORT/EXPORT, tym razem będzie różny. Wszędzie tam gdzie jest w kolumnie opis2 NULL, przypisana zostanie wartość domyślna ‘DEFAULT’.
BCP_Export_07

Jeśli chcemy zachować wartości NULL z eksportu, trzeba zastosować flagę -k (keep nulls) w poleceniu BCP. W ten sposób możemy zagwarantować poprawność procesu OUT/IN czyli EXPORT + IMPORT da ten sam rezultat.

C:\Users\jakub.kasprzak>bcp tempdb.dbo.test2 in c:\temp\test.csv 
  -S MyServer\SQLEXPRESS -T -w -t; -k

BCP w praktyce – wskazówki

Wydajność

Przy dużych importach, kiedy zależy nam na czasie i minimalizacji okna serwisowego, warto wziąć pod uwagę szereg dodatkowych czynności, które mogą pomóc w optymalizacji procesu importu.
Rozważyć należy wyłączenie mechanizmów spowalniających operacje INSERT związanych z zapewnieniem integralności i spójności danych. Mam tu na myśli ograniczenia (CONSTRAINTS), procedury wyzwalane (TRIGGERS), czy klucze obce. Ich obecność i rola w bazach jest nie do przecenienia, jednak ich istnienie wpływa negatywnie na wydajność operacji związanych z importem.

Drugą kwestią są indeksy, które również bezpośrednio wpływają na degradację operacji INSERT, UPDATE i DELETE. Każda taka czynność pociąga za sobą konieczność aktualizacji danych w indeksie. Stosowanym w praktyce rozwiązaniem jest usuwanie indeksu i jego odbudowa. Ma to sens wtedy, gdy do przygotowanej struktury zawierającej niewielką liczbę danych importujemy duże ilości rekordów zasilających. Alternatywą do tego działanie jest import już posortowanych danych, zgodnie z definicją indeksu klastrowego, w celu minimalizacji wpływu na jego przebudowę i aktualizację.

Kolejna kwestia to potencjalne obciążenie serwera ze względu na zarządzanie wielodostępem do danych, które w SQL Server zapewniają blokady. Są one zakładane na różnych poziomach np. wiersza, strony, tabeli. Cel jest taki, żeby zminimalizować liczbę blokad, tym samym operacji które muszą być wykonane równolegle do importu. Zakładając blokadę na poziomie tabeli, możemy również zwiększyć wydajność operacji typu BULK. W tym celu możemy posłużyć się dodatkową flagę związaną ze wskazówkami dla silinika relacyjnego –h (hints).

Logowanie transakcji

Importując dane do bazy w modelu odzyskiwania Full Recovery Model, liczyć należy się z obciążeniem logu transakcyjnego. Każdy insert, będzie logowany. Trzeba to przewidzieć i zapewnić odpowiednią obsługę pliku logu (opcja Autogrow, wolne miejsce na dysku).
Jeśli nasze środowisko nie wymaga, tak precyzyjnego logowania, przy dużej liczbe masowych importów, sugerowana jest zmiana na tryb Bulk-ogged.

Import danych z Excela do bazy SQL Server za pomocą T-SQL

Czytanie plików Excel za pomocą T-SQL jest bardzo użyteczne, ale potrafi dostarczyć też problemów, szczególnie na etapie przygotowania środowiska. Sam spędziłem parę ładnych godzin na debugowaniu błędu związanego z czytaniem pliku (chodziło o dostęp do katalogów temp), dlatego postanowiłem zebrać wszystko co ważne w jednym miejscu. O ile sama składnia OPENROWSET jest dość łatwa, o tyle potencjalne problemy związane z ustawieniami środowiskowymi czy właściwościami interfejsów OLE DB mogą pożreć nam sporo czasu na ich rozwiązanie.
Znajdziesz tutaj najważniejsze informacje, które mogą się przydać do czytania plików Excel z poziomu T-SQL a także opis najczęściej pojawiających się błędów i ich rozwiązań.


Funkcja OPENROWSET

Do czytania plików Excel z poziomu T-SQL, idealnie nadaje się funkcja OPENROWSET. Szczególnie wtedy gdy chcemy szybko wrzucić zawartość jakiegoś Excela do bazy aby wykonać na informacjach w nim zawartych jakichkolwiek działań lub zależy nam po prostu aby wykonać import za pomocą skryptu T-SQL.
Jest to funkcja tabelaryczna, zwracająca w wyniku zbiór wierszy, który możemy traktować jak zwykłą tabelę. OPENROWSET podobnie jak inne obiekty tabelaryczne (widoki, zmienne tabelaryczne, tabele tymczasowe etc.) może być użyty tylko w klauzuli FROM zapytania.
Aby móc korzystać z OPERNROWSET potrzebujemy włączonej możliwości wykonywania kwerend rozproszonych. Jest to jedno z ‘advanced optinos’ naszego serwera – warunek konieczny do dalszych przykładów :

-- włączenie na serwerze obsługi kwerened rozproszonych
exec sp_configure 'show advanced options',1
reconfigure
 
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

Teraz w zależności od wersji w jakiej został zapisany plik Excela treść naszego zapytania może wyglądać następująco :

-- Przykład z excel zapisanym w formacie zgodnym z OFFICE 1997-2003 .xls 
-- stary sterownik OLEDB 4.0. Na wymarciu :) sugeruję stosowanie ACE.
 
select * FROM OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\EXCEL1997-2003_TO_READ.xls', 
'select * from [Arkusz1$]');
 
-- ten sam plik zapisany w trybie zgodności 1997-2003 xls czyli OFFICE 2003 
-- nowy uniwersalny provider ACE OLE DB. Odwolanie do Excel 8.0
 
SELECT * FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0', 
'Excel 8.0;Database=D:\EXCEL1997-2003_TO_READ.xls', 
'SELECT  * FROM [Arkusz1$] ');
 
-- plik zapisany w OFFICE 2010 xlsx nowy uniwersalny provider ACE OLE DB
-- istotne jest odwołanie do Excel 12.0;
 
SELECT * FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0',                      -- provider_name
'Excel 12.0;Database=D:\EXCEL2010_TO_READ.xlsx', -- provider_string
'SELECT * from [Arkusz1$]')                      -- query lub sam [Arkusz1$], wtedy bez '

Powyższe zapytania działają, jeśli środowisko w którym pracujemy jest odpowiednio skonfigurowane. W dalszej części tego artykułu, znajdziesz wszystkie najważniejsze aspekty związane z konfiguracją serwera, składnią OPENROWSET oraz najczęstszymi problemami z jakimi możesz się zetknąć przy próbie czytania z Excela.

Składnia OPENROWSET

W pokazanych przykładach, funkcja OPENROWSET przyjmuje 3 parametry :
OPENROWSET( provider_name, provider_string, query).

Parametr ‘provider_name

Tu podajemy nic innego jak nazwę (friendly name) interfejsu OLE DB. W przykładach użyłem dwóch interfejsów OLE DB za pomocą których możliwe jest uzyskanie dostępu do plików Excel. Pierwszy to „stary” JET.OLEDB.4.0 który czyta Excela tylko do wersji 2003 (xls) i którego nie polecam stosować (czasem jednak nie mamy wyboru, więc stąd ten przykład).
Drugi, sugerowany to uniwersalny Microsoft.ACE.OLEDB.12.0 dla plików zapisanych w wersjach 1997-2010. Jeśli nie jesteś pewien czy masz go zainstalowanego – sprawdź sterowniki np. w ODBC (biblioteka ACEODBC.DLL).
Reading_excel_odbc_providers
W moim przypadku mam dostępny Microsoft.ACE.OLEDB.12.0 w wersji 14.
Jeśli nie masz jej dostępnej – można go pobrać i zainstalować np. stąd : Microsoft Access Database Engine 2010 Redistributable.
UWAGA ! Jeśli zainstalowałeś ten interfejs, konieczne jest nadanie dla niego stosowanych uprawnień – opisane jest to poniżej – w paragrafie Problemy.

Parametr ‘provider_string

W provider_string, definiujemy zbiór (kolekcję) parametrów tzw. extended properties. Obowiązkowe są dwa – wersja pliku oraz ścieżka. Wersje plików Excel czytane przez Microsoft.ACE.OLEDB.12.0 :

Typ pilkuExtended Properties

Excel 97-2003 Workbook (.xls) “Excel 8.0”
Excel 2007-2010 Workbook (.xlsx) “Excel 12.0 Xml”
Excel 2007-2010 Macro-enabled workbook (.xlsm) “Excel 12.0 Macro”
Excel 2007-2010 Non-XML binary workbook (.xlsb) “Excel 12.0”

Do celów demonstracyjnych najważniejszych parametrów, użyję pliku excel (zapisany w MS Office 2010 jako xlsx). Zawartość pliku demo :
excel_file_example1
Wykonanie kwerendy czytającej tego excela z minimalną wymaganą liczbą parametrów :

SELECT * FROM OPENROWSET(
-- provider_name
'Microsoft.ACE.OLEDB.12.0', 
-- provider_string 
'Excel 12.0;Database=D:\EXCEL2010_TO_READ.xlsx', 
-- query
'SELECT  * FROM [Arkusz1$]');

Reading_excel_01
Jak widać wynik powyżej różni się od faktycznej zawartości pliku – jest to zależne od ustawień parametrów opcjonalnych .

Najważniejsze z opcjonalnych extended properties to :
HDR yes/no – (default = yes) czy ma czytać nazwy kolumn z pierwszego wiersza. Jeśli ustawimy na no – nazwy kolumn będą utworzone jako F1,F2 etc.
IMEX – ImportMixedTypes – interfejsy OLE DB, które wykorzystujemy, mają właściwość „zgadywania” typu danych w kolumnie. Właściwość IMEX odnosi się tylko do kolumn zawierających mieszane typy danych i stara się automatycznie określić typ na podstawie pierwszych N wartości (wartość N określa parametr MaxScanRows). Jeśli na tej podstawie zostanie wybrany np. typ integer, to wszystkie wartości nie pasujące do tego typu, zostaną zamienione na NULL, co zazwyczaj nie jest oczekiwanym zjawiskiem :).

W naszym przykładzie, w kolumnie KolC mamy 2 wartości tekstowe i 3 daty, to w wyniku zapytania otrzymamy kolumnę typu datetime a wszystko co nie pasuje do tego typu zostanie zamienione na NULL (tak jak miało to miejsce w przykładzie powyżej).
W zdecydowanej większości przypadków, gdy chcemy sami określać typ i czyścić dane – ustawiamy parametr IMEX=1, wtedy każda kolumna będzie miała narzucony typ danych jako text :

SELECT * FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0', 
-- provider_string z IMEX
'Excel 12.0;IMEX=1;Database=D:\EXCEL2010_TO_READ.xlsx', 
'SELECT  * FROM [Arkusz1$]');

Reading_excel_02

Parametr ‘query

W tym parametrze określamy zakres danych jakie chcemy wyciągać. Odpytywany plik Excel, może posiadać kilka arkuszy – do konkretnego odwołujemy się po nazwie + $. Aby określić zakres czytanych komórek można to zrobić np. tak Arkusz1$A1:C3 – w wyniku czego otrzymamy tylko dwa wiersze (plus header). Query może przyjmować podstawowe komendy obsługiwane przez danego providera np. grupowanie danych lub być poprostu nazwą arkusza [Arkusz1$] (bez apostrofów).

SELECT * FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0', 
'Excel 12.0;IMEX=1;Database=D:\EXCEL2010_TO_READ.xlsx', 
-- query jako zapytanie
'SELECT kolC, MAX(KolA) as maxi FROM [Arkusz1$A1:C3] group by KolC'

excel_file_example2
W wyniku dostaniemy :
Reading_excel_03
Warto zwrócić uwagę na fakt konwersji na właściwy typ danych w KolC (datetime), nawet przy ustawionym IMEX=1, ponieważ wartości pobierane z tej kolumny są spójne –same daty (bierzemy pod uwagę tylko dwa pierwsze wiersze z danymi A1:C3).

Problemy

Niestety jak to w życiu bywa, wszystko jest ok., ale nie działa. Akurat w przypadku czytania Excela z poziomu SQL zdarza się to nadzwyczaj często bo istnieje wiele uwarunkowań środowiskowych, które mogą nam skutecznie przeszkodzić w zabawie.
Najczęstsze przyczyny problemów z czytaniem excela za pomocą OPENROWSET:

  • Jeśli korzystamy z OLE DB ACE – konieczne przydzielenie odpowiednich uprawnień dla tego dostawcy czyli po instalacji konieczne jest wykonanie :
    USE [master]  
    GO  
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1  
    GO  
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1  
    GO
  • Plik do którego się odwołujemy musi być zamknięty aby móc go czytać z poziomu T-SQL przez OPENROWSET . Niestety SQL Server potrzebuje pliku na wyłączność. Jeśli jest już otwarty, otrzymamy komunikat :
    Msg 7399, Level 16, State 1, Line 2
    The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
    Msg 7303, Level 16, State 1, Line 2
    Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
  • Konto na którym działa usługa SQL Server musi mieć dostęp do zasobów które chcesz czytać, wynika to z konieczności obsługi różnych metod uwierzytelniania (Windows lub SQL Authentication).
  • Problem związany z SQL Server x86 – użytkownik, który wykonuje kwerendę musi mieć dostęp do katalogów tymczasowych używanych przez SQL Server. Jest to podyktowane tym, że tworzona jest kopia tymczasowa pliku do którego się odwołujemy w OPENROWSET w katalogu temp. Potrzebny dostęp r/w do katalogów wskazywanych przez zmienne %TEMP% oraz %TMP% konta na którym działa SQL Server.
    Microsoft sugeruje w tym temacie dwa rozwiązania, które sprowadzają się do tego samego – nadanie uprawnień do aktualnie używanych katalogów TEMP dla użytkowników lub utworzenie nowego, dedykowanego katalogu tymczasowego np. c:\TEMP i ustawienie zmiennych środowiskowych dla konta na którym działa SQL Server, aby %TEMP% i %TMP% wskazywały właśnie na ten nowy zasób.
    W obu przypadkach nadajemy uprawnienia R/W dla wszystkich zainteresowanych użytkowników do tego katalogu oraz full rights dla konta SQL Server.
  • Inne spotykane problemy to kompatybilność sterowników – sprawdź czy zainstalowana wersja OLE DB jest odpowiednia dla twojego serwera x86 / x64.

Niestety obsługa błędów w OLE DB ACE została potraktowana bardzo ogólnie i w większości przypadków tu opisywanych, treść błędu będzie podobna – unknown error.