SELECT Explanation, Example FROM Pro.Knowledge
FacebookRSS

Wyszukiwanie ciągłych zakresów, dziur, problemy wysp w SQL

Weryfikacja ciągłości zakresu, identyfikacja dziur oraz scalanie przedziałów, przydaje się do rozwiązywania różnych zadań. W systemach transakcyjnych, szczególnie mocno obciążonych przez wielu użytkowników, mogą wystąpić sytuacje gdy funkcja IDENTITY nie zapewnia bezwzględnej ciągłości identyfikatorów. Dotyczy to również obiektów wprowadzonych w SQL Server 2012, znanych już od dawna w Oracle – sekwencji.

Artykuł ten, nie będzie jednak o bezpiecznym nadawaniu kolejnych, ciągłych identyfikatorów (polecam triggery INSTEAD OF INSERT). Zaprezentuję tu kilka wskazówek i praktycznych przykładów jak wykrywać nieciągłości, identyfikować dziury oraz scalać zakresy.

Niezwykle pomocne okazują się tutaj funkcje szeregujące bazujące na funkcji okna OVER() oraz wspólne wyrażenia tablicowe.


Wykrywanie nieciągłości w zbiorze

Na początek coś bardzo prostego. Trochę podstaw, bez których trudno jest rozwiązywać bardziej skomplikowane przykłady.

Załóżmy, że mamy zbiór elementów ponumerowanych w zakresie od 1-1000, inkrementowanych co 1. Każdy ciągły, jednostajnie rosnący zbiór wartości, powinien zwrócić ten sam wynik dla takiego wyrażenia:

Wartość_elementu  – ( Inkrementacja * Nr_elementu_w_szeregu ) = Const.


Wykorzystując funkcję ROW_NUMBER() i powyższą zasadę, sprawdźmy ciągłość dla poniższego zbioru:

Use tempdb
GO
 
set nocount on 
go
 
IF ( OBJECT_ID('dbo.zakres') is not null)  
drop table dbo.zakres;
 
create table dbo.zakres
( 
	Vlan int
);
 
DECLARE @zmienna int = 1
 
WHILE (@zmienna<=1000)
BEGIN
	insert into  dbo.zakres values (@zmienna)
	set @zmienna = @zmienna + 1
END;
 
-- kwerenda sprawdzająca
with CheckCont as (
	-- dla inkrementowanego o 1
	select distinct T1.VLAN  - (1*(ROW_NUMBER() OVER (ORDER BY  VLAN))) as Diff
	from dbo.zakres t1
)
Select CASE WHEN Count(Diff)> 1 then 'Discontinuous' else 'Continuous' end as Result
from CheckCont

Zakresy_ciagle_01

Jak widać bez niespodzianek – jest ok. Skasujemy teraz kilka losowych rekordów i ponowie zweryfikujemy ciągłość elementów, tą samą kwerendą sprawdzającą.

DELETE FROM dbo.zakres
WHERE VLAN IN (
SELECT TOP 3 VLAN from dbo.zakres ORDER BY NEWID() 
)

Zakresy_ciagle_02


Scalanie zakresów

Scenariusz, z którym ostatnio się spotkałem to scalanie zakresów VLAN w konfiguracji interfejsów urządzeń sieciowych (routerów). Informacje o VLANach są przechowywane w tabeli, w której każdy rekord, odpowiada pojedynczemu VLANowi, przypisanemu do danego portu, określonego urządzenia. To co trzeba wykonać, to agregacja (sklejenie zakresów) do skrótowej postaci przyjaznej dla użytkownika.

Wygenerujmy sobie prostą tabelę, z nieciągłą numeracją w kolumnie VLAN :

IF ( OBJECT_ID('dbo.zakres') is not null)  drop table dbo.zakres;
create table dbo.zakres
( 
	Vlan int
);
 
DECLARE @zmienna int = 1, @los int =0, @cnt int =1
 
WHILE (@cnt<=10)
BEGIN
 
	SET @los = case when   CONVERT(INT, (11) * RAND()) > 8  then 1 else 0 end
 
	insert into dbo.zakres values (@zmienna + @los)
	SELECT @zmienna = @zmienna +1+ @los, @cnt +=1
END;
 
select * from dbo.zakres

Zakresy_ciagle_03

Zadaniem jest wykrycie i scalenie zakresów rekordów o ciągłej numeracji. Stosując się do reguły ciągłości, którą pokazałem w poprzednim przykładzie, sprawdźmy rezultat tej formułki dla kolejnych wierszy. Widać, że wynik może posłużyć jako bezwzględny identyfikator określonego, ciągłego zakresu. W naszym przypadku mamy 4 zakresy, w tym dwa wieloelementowe.

Zakresy_ciagle_04

Kluczowym znów będzie numerowanie rekordów i późniejsze ich grupowanie względem wyliczonego w ten sposób identyfikatora zakresu. Dwa podejścia – stare, sprzed SQL Server 2005 z zapytaniem skorelowanym :

with PoNumerowane as
(
	select VLAN ,  
           (select count(*) from dbo.zakres t2 where t2.VLAN  <= t1.VLAN )  as IdRek
	from dbo.zakres t1
)
select VLAN - IdRek as ZakresID, MIN(VLAN) as VLAN_START, MAX(VLAN) as VLAN_END
from PoNumerowane
group by VLAN - IdRek

Oraz lepsze, zdecydowanie bardziej wydajne, rozwiązanie z użyciem funkcji ROW_NUMBER() :

with Ponumerowane as (
	select VLAN , ROW_NUMBER() OVER(order by VLAN)  as IdRek,
	 VLAN - ROW_NUMBER() OVER(order by VLAN) as IdZakres
	from dbo.zakres t1 
)
select IdZakres, MIN(VLAN) as VLAN_START, MAX(VLAN) as VLAN_END
from Ponumerowane
group by IdZakres

Zakresy_ciagle_05
Takie rozwiązanie można ubrać w funkcję użytkownika i prezentować scalone zakresy liczb w przyjaznej formie, np. dodatkowo agregując je do postaci skalarnej (FOR XML PATH).


Wyszukiwanie brakujących elementów w zbiorze

Na koniec sytuacja, gdy interesuje nas to, czego nie ma w tabeli. Mogą to być braki numerów zamówień, albo dziury w ciągłym zakresie adresacji urządzeń IP, nadające się do ponownego przydzielenia. Identyfikację brakujących elementów, na przykładzie zbioru inkrementowanego o 1, możemy wykonać na przykład tak :

with cte as (
 
	SELECT (select MIN(VLAN) from dbo.zakres) as VLAN, 
(select MAX(VLAN) from dbo.zakres) as MaxVal
	UNION ALL
 
	-- zakładamy inkrementację + 1 
	SELECT VLAN + 1 , MaxVal
	FROM CTE  c 
	where VLAN < MaxVal
)
select c.VLAN as MissingVlan from cte c left join dbo.zakres t on c.VLAN = t.VLAN
where t.VLAN is null
OPTION (MAXRECURSION 0)

Zakresy_ciagle_06

Grafy, drzewa i hierarchie w SQL

Z grafami można spotkać się nad wyraz często. Takie najprostsze to struktura katalogów na dysku czy hierarchia przełożony-podwładny w firmie.

Bardziej skomplikowane znajdziesz w serwisach społecznościowych – np. drzewa znajomości. W sieciach teleinformatycznych będą to struktury połączeń pomiędzy węzłami. W nawigacji GPS – najkrótsze ścieżki od punktu A-Z itd.

Z punktu widzenia baz danych i tworzenia zapytań do tego typu struktur – zadania z grafami nie są trywialne. Szczególnie, gdy chcemy je rozwiązać za pomocą pojedynczej kwerendy.
W artykule tym, zaprezentuję kilka praktycznych scenariuszy wraz z rozwiązaniami bazującymi na SQL Server 2012. Mam nadzieję, że jeśli szukasz gotowych rozwiązań – uda Ci się zaadoptować prezentowane tutaj przykłady do swoich zastosowań.

W każdym z nich, wyzwaniem było napisanie rozwiązania w postaci pojedynczej kwerendy. Podejście proceduralne jest prostsze w zrozumieniu i może prowadzić do bardziej efektywnych rozwiązań (materializowanie pośrednie, indeksy na tabelach tymczasowych).

Trochę teorii

Patrząc na matematyczną teorię grafów, możemy mieć do czynienia z różnymi typami tych struktur. Na początek zajmiemy się grafami prostymi, wprowadzając przy okazji kilka podstawowych pojęć. Zbudujemy sobie tło do bardziej zaawansowanych scenariuszy.


Graf prosty (acykliczny, nieskierowany)

Graf prosty – to drzewo bez cykli, krawędzie nie są skierowane (nie ma ograniczeń kierunkowych) a pomiędzy każdą parą dowolnych wierzchołków, jest tylko jedna ścieżka. Dodanie jakiejkolwiek nowej krawędzi, spowoduje powstanie cyklu a usunięcie istniejącej doprowadzi do niespójności (podział grafu).

Grafy_Drzewa_SQL_01

W przypadku, gdy jeden z wierzchołków, jest wyszczególniony (korzeń), mówimy o drzewie ukorzenionym. Grafy proste, ukorzenione są łatwe w implementacji w relacyjnych bazach. Tworzenie zapytań do takich struktur nie stwarza większych problemów. Tego typu graf, to na przykład organizacja systemu plikowego lub hierarchia pracownicza – przełożony/podwładny.

W typowej organizacji plików i katalogów zakładamy, że dany obiekt (plik lub katalog) nie może być w dwóch różnych miejscach jednocześnie (pomijając skróty, które mogą tworzyć cykle – o tym później). Do każdego węzła, jest tylko jedna ścieżka, prowadząca ,od korzenia grafu. Patrząc na tą strukturę, całość stanowi jedno spójne drzewo – graf prosty, ukorzeniony.

Grafy_Drzewa_SQL_02

Podobnie w strukturze zatrudnienia. Dany pracownik zazwyczaj nie może mieć na tym samym poziomie dwóch bezpośrednich szefów. Droga eskalacji od podwładnego do przełożonego najwyższego szczebla, jest jedna.

Implementacja takiej struktury w bazie danych może być bardzo prosta. Realizuje się ją zazwyczaj, jako SELF JOIN, czyli złączenie tabeli samej ze sobą.

USE tempdb
GO
 
CREATE TABLE dbo.Folders (
    ID INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
    Folder VARCHAR(100) NOT NULL,
    Parent INT NULL REFERENCES dbo.Folders( ID ) 
);
 
INSERT INTO dbo.Folders ( Folder ) VALUES ('C:');
 
INSERT INTO dbo.Folders ( Folder, Parent )
VALUES  ('Inetpub',1),('Program Files',1),('Windows',1),('wwwroot',2),
('Adobe',3),('MS SQL',3),('Data',7),('system32',4),('Data',9);
 
select * from dbo.Folders

Grafy_Drzewa_SQL_03

Zapytania SQL do takiego grafu, również nie są szczególnie skomplikowane. Wystarczy wykorzystać rekurencję wspólnych wyrażeń tablicowych (CTE Common Table Expressions) i przeszukiwać drzewo w głąb od korzenia do liści.

WITH Paths AS 
(
    SELECT Id, Folder, CAST(null AS VARCHAR(8000)) AS Parent,
           CAST(Folder + '\' AS VARCHAR(8000)) FullFolderName, 0 as Poziom
    FROM dbo.Folders
    WHERE Parent IS NULL   
 
    UNION ALL
 
    SELECT f.Id, f.Folder, p.FullFolderName, 
           CAST(ISNULL(p.Parent, '')  + p.Folder + '\' 
		+  f.Folder + '\'  AS VARCHAR(8000)) , p.Poziom +1
    FROM dbo.Folders f INNER JOIN Paths p
                                ON f.Parent= p.ID
)
SELECT * FROM Paths
Order by FullFolderName

Grafy_Drzewa_SQL_04

Takie podejście, załatwia również sprawę, w sytuacji, gdy mamy do czynienia ze zbiorem grafów prostych (las drzew). Zakotwiczenie CTE, identyfikuje wszystkie grafy i rekurencyjnie rozpina ja analogicznie jak poprzednio. Drzewa możemy ponumerować, do późniejszej analizy.

-- dorzućmy nowe drzewo - dysk D:
Insert into dbo.Folders(Folder) Values('D:');
 
Insert into dbo.Folders ( [Folder], [Parent] )
VALUES('Music',11),('Docs',11),('Mike Oldfield',12),('Tres Lunas',14);
 
WITH Paths AS 
(
    SELECT Id, Folder, CAST(null AS VARCHAR(8000)) AS Parent,
CAST(Folder + '\' AS VARCHAR(8000)) FullFolderName, 0 as Poziom, 
ROW_NUMBER() OVER(ORDER BY FOLDER) as TreeNo
    FROM dbo.Folders
    WHERE Parent IS NULL   
 
    UNION ALL
 
    SELECT f.Id, f.Folder, p.FullFolderName, 
           CAST(ISNULL(p.Parent, '')  + p.Folder + '\' 
		+  f.Folder + '\'  AS VARCHAR(8000)) , p.Poziom +1, TreeNo
    FROM dbo.Folders f INNER JOIN Paths p
                                ON f.Parent= p.ID
)
SELECT * FROM Paths
Order by FullFolderName

Grafy_Drzewa_SQL_05

W SQL Server od wersji 2008, mamy dostępny specjalny typ danych HierarchyId. Pozwala on na tworzenie bardziej zaawansowanych struktur w porównaniu do SELF JOINów. Za jego pomocą, można bardzo łatwo zamodelować scenariusz w którym istotna jest kolejność węzłów na danym poziomie hierarchii.


Graf nieskierowany z cyklami

Sytuacja staje się bardziej złożona, gdy mamy do czynienia z grafami, które posiadają cykle. Jest to typowy przykład architektury sieci teleinformatycznej, w której istnieją redundantne ścieżki w celu zapewnienia protekcji. Innym przykładem z którego korzystasz na co dzień to po prostu siatka połączeń drogowych pomiędzy miastami.

Grafy_Drzewa_SQL_06

Wyszukiwanie ścieżek pomiędzy dowolnymi węzłami takiego grafu, musi uwzględniać możliwość zapętlenia. Dla każdej pary węzłów, może pojawić się wiele ścieżek. Zależnie od stopnia złożoności grafu (liczby węzłów i krawędzi) analiza możliwych dróg, może stanowić nie lada wyzwanie.

Grafy_Drzewa_SQL_07

Skrypt z danymi do przeprowadzenia dalszych ćwiczeń od pobrania tutaj.

Implementacja takiego grafu w relacyjnej bazie danych, może być oparta o dwie tabele – węzłów i krawędzi między nimi. Na początek znajdźmy wszystkie ścieżki z punktu A-Z.

WITH Edges AS (
	-- graf nieskierowany więc bierzemy pod uwagę krawędzi dwukierunkowo
        -- A-Z i Z-A
	SELECT Node1 as Start, Node2 as Koniec, Metric from EDGE
	UNION
	SELECT Node2, Node1 , Metric from EDGE	
),
Paths as 
(
	-- rekurencyjne CTE
        SELECT start , koniec ,  
        CAST('.' + start + '.' + koniec + '.' as varchar(max)) as paths, 1 as Dist
	FROM Edges
 
	UNION ALL
 
	SELECT f.start, t.koniec , CAST(f.paths + t.koniec + '.' as varchar(max)), 
	F.dist+1
	FROM Paths as F join Edges T on 
	--  wykluczenie cykli, czyli nie przechodzimy 
        -- przez żaden wierzchołek dwukrotnie
		case when F.paths like '%.' + T.koniec  + '.%' then 1 else 0 end = 0
		 and f.koniec = T.start
)
SELECT *  
FROM  Paths
WHERE Start = 'A' and Koniec = 'Z'
ORDER BY Dist

Grafy_Drzewa_SQL_08

Powyższe podejście, pomimo że w jednej kwerendzie, jest słabe wydajnościowo i przy dużej liczbie (tysiące) węzłów / krawędzi działa po prostu bardzo wolno. Wystarczy spojrzeć na plan wykonania – przypomina zapytanie prawdziwie skorelowane. Alternatywnym rozwiązaniem jest podejście proceduralne, w którym tworzymy drzewa rozpinające.


Graf skierowany z cyklami i metrykami krawędzi

Wyszukiwanie najkrótszej drogi z punktu A-Z, zazwyczaj wymaga analizy jakości poszczególnych odcinków. Sytuacja taka ma zastosowanie zarówno w sieciach drogowych (autostrady, drogi lokalne), jak również informatycznych. W tych drugich, brane są pod uwagę zazwyczaj parametry jakościowe, czyli pasmo, opóźnienia, jitter. O ile w sieciach teleinformatycznych kierunkowość zazwyczaj jest tematem pomijalnym, o tyle drogi jednokierunkowe, to norma.

Bazując na danych z poprzedniego przykładu, wyznaczmy raz jeszcze najlepszą ścieżkę z A-Z, biorąc pod uwagę kierunkowość krawędzi oraz metryki. Nasz graf będzie wyglądał teraz tak :

Grafy_Drzewa_SQL_09

W tej sytuacji widać, że pomiędzy węzłami A i Z powinny zostać znalezione tylko dwie ścieżki. Ponadto nie istnieje żadna ścieżka powrotna (Z > A). Zmodyfikujemy odrobinę zapytanie z poprzedniego przykładu :

WITH Edges AS (
	-- graf skierowany więc bierzemy drogi takie jakie są
	SELECT Node1 as Start, Node2 as Koniec, Metric from EDGE
),
Paths as 
(
	SELECT start , koniec ,  
        CAST('.' + start + '.' + koniec + '.' as varchar(max)) as paths, 1 as Dist, Metric
	FROM Edges 
 
	UNION ALL
 
	select f.start, t.koniec , CAST(f.paths + t.koniec + '.' as varchar(max)), 
	F.dist+1, F.Metric + T.Metric
	from Paths as F join Edges T on 
                 case when F.paths like '%.' + T.koniec  + '.%' then 1 else 0 end = 0
		 and f.koniec = T.start
),
PathRanking as (
select   *, 
	DENSE_RANK() OVER(Partition BY Start, Koniec Order by Metric, Dist) as RNK
from Paths)
Select * from PathRanking
where start = 'A' and koniec = 'Z';

Grafy_Drzewa_SQL_10

Obydwie kosztują tyle samo (suma metryk jest identyczna), ale droga bezpośrednia A>Z ma mniej skoków, stąd zostaje wybrana jako najlepsza.


Las drzew (grafów) nieskierowanych, nieukorzenionych

Bazując na powyższej metodzie, możemy pokusić się o rozwiązanie jeszcze jednego problemu. Zadaniem nietrywialnym, jest analiza zbioru grafów nieskierowanych bez korzenia. Tego typu struktury, spotkać możesz na przykład w serwisach społecznościowych.

Grafy_Drzewa_SQL_11

Wyobraźmy sobie prostą tabelę powiązań (znajomości) pomiędzy użytkownikami. Pytanie w jaki sposób, określić czy dana osoba należy do jakiegoś kręgu (grupy znajomości mogą się tworzyć samoczynnie, bez dodatkowych relacji). Daną grupę, wyznaczają wszystkie osoby, które są w jakikolwiek sposób ze sobą powiązane (nawet pośrednio). Czyli tak naprawdę, musimy zidentyfikować przypisanie każdej krawędzi do określonego drzewa.

Rozwiązaniem w postaci jednej kwerendy, może być modyfikacja znanego już algorytmu wyznaczania ścieżek.

WITH Znajomi as 
(
	select * from (
	VALUES  ('a','b'),('a','i'),('b','i'),('c','j'),('d','j'),
		('e','k'),('f','k'),('g','l'),('h','l'),('e','l')--,('a','e')
 
	) as Tab(a,b)
 
), 
Edge as (
 
	select l.a as Start , cast( l.b  as varchar) as Koniec, 1 as dist 
	from Znajomi l
	union all 
		select cast( l.b  as varchar) , l.a, 1
	from Znajomi l
),
Paths as 
(
	select start , koniec ,  CAST('.' + cast(start as varchar(10))+ '.' 
		+ cast(koniec  as varchar(10)) + '.' as varchar(max)) as path, dist
	from Edge
 
	UNION ALL
 
	select f.start, t.koniec , 
		CAST(f.path + cast(t.koniec as varchar(10)) + '.' as varchar(max)), F.dist+1
	from Paths as F join Edge T on 
	case when F.path like '%.' + cast(T.koniec as varchar(10)) + '.%' then 1 else 0 end = 0
		and f.koniec = T.start
)
, 
Grupy as (
 
   select c.a, c.b , SUBSTRING(max(path),2,CHARINDEX('.',max(path),2)-2) as GRID
   from  Znajomi c left join Paths    r  on r.Path like '%' + c.a + '.' +  
   cast( c.b  as varchar) + '%' or r.Path like '%' + cast( c.b  as varchar) + '.' + c.a  + '%' 
   group by c.a, c.b 
)
select a, b, DENSE_RANK() OVER(order by GRID) as Grupa from Grupy

Grafy_Drzewa_SQL_12

Zauważ, że wykorzystałem tu pewne fundamentalne i oczywiste założenie. W danym drzewie, mamy możliwość dojścia do każdego węzła. W przeciwnym razie, dany graf byłby niespójny i stanowiłby tak naprawdę dwa lub więcej drzew.

W związku z tym, w ostatnim CTE (Grupy), grupuję po maksymalnej (równie dobrze mogłaby to by być minimalna) ścieżce, która zawiera identyfikatory węzłów. Na podstawie tego największego identyfikatora węzła, określam przynależność do grupy.


Podsumowanie

Zaprezentowane w tym artykule rozwiązania to tylko jedne z możliwych podejść. Proceduralnie można by je było rozwiązać znacznie prościej i szybciej. Jeśli masz propozycję rozwiązania tych zadań w bardziej efektywny sposób za pomocą jeden kwerendy – zapraszam do dyskusji !

Tematyka grafów jest znacznie szerzej (świetnie) opisana w książce Itzika Ben Gana „Inside Microsoft SQL Server 2008: T-SQL Querying” – polecam wszystkim.

Wielokrotne grupowanie – GROUPING SETS, ROLLUP, CUBE

Tworząc raporty czy zestawienia bezpośrednio z bazy transakcyjnej, wchodzimy na teren analityki biznesowej BI (Business Intelligence) w zwykłej bazie OLTP (OnLine Transaction Processsing). W środowiskach komercyjnych, granica pomiędzy OLTP a bazami OLAP (OnLine Analytical Processing) jest czasem całkowicie zatarta. Zdarza się tak w sytuacji gdy po prostu nie ma wdrożonych, dedykowanych rozwiązań BI. Całość raportowania odbywa się wtedy wprost z bazy transakcyjnej.

Analityka biznesowa (raportowanie), cechuje się wieloma operacjami przekształcania danych i przekuwania ich na konkretną wiedzę. Takie zapytania bywają kosztowne jeśli chodzi o wydajność. Dlatego żeby nie obciążać systemu transakcyjnego, wykonywana są w osobnych strukturach. Jest to powód dla którego tworzone są hurtownie danych i wszelkiej maści rozwiązania BI.

W artykule tym opisuje rozszerzenia T-SQL pozwalające na wielokrotne grupowanie (wstęp do kostek analitycznych). Opisywane tu metody, bazują na zagadnieniach dotyczących grupowania danych i łączenia pionowego zbiorów, prezentowanych w tym kursie we wcześniejszych artykułach.


Wielokrotne grupowanie

W T-SQL mamy dostępnych kilka rozszerzeń składni, pozwalających na sięgnięcie do wiedzy analitycznej. Obok funkcji szeregujących i analitycznych, jednymi z ciekawszych są trzy funkcje pozwalające na wykonywanie wielokrotnych grupowań w zwykłej (pojedynczej) kwerendzie.

Wyobraźmy sobie taki scenariusz (działając na danych z bazy Northwind) :

Chcemy utworzyć raport, przedstawiający informacje o sprzedaży w latach 1997-1998 na kilku poziomach szczegółowości. Zagregowane dane na temat liczby zleceń, chcemy wyświetlić względem lat, kwartałów i na koniec całościowo (jako najwyższy poziom agregacji).

Wielokrotne_grupowanie_01

Tego typu rozbicie wielopoziomowej agregacji, można wykorzystać np. w aplikacji raportującej, w której mamy możliwość „drążenia danych”, czy po prostu w zwykłym raporcie. Powyższy przykład to nic innego jako fragment kostki analitycznej z kilkoma wymiarami czasu.

Znając już operacje na zbiorach (UNION) i możliwości grupowania (GROUP BY), mógłbyś to zadanie rozwiązać za pomocą tych elementarnych konstrukcji, np. w taki sposób :

USE Northwind
GO
 
-- Query1
SELECT 1 as Poziom, NULL as Rok, NULL as Kw , count(OrderId) as OrderQty
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
 
UNION
 
SELECT 2, Year(Orderdate) as Rok, NULL, count(OrderId) as OrderQty
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY Year(Orderdate) 
 
UNION
 
SELECT 3,Year(Orderdate)  as Rok, DatePart(q,Orderdate), count(OrderId) as OrderQty
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY Year(Orderdate)  , DatePart(q,Orderdate)
ORDER BY 2,1

Zaprezentowane powyżej rozwiązanie, łączenia wyników grupowań tej samej kwerendy w różnych kombinacjach, jest mało efektywne.

Z punktu widzenia przetwarzania są to trzy osobne kwerendy, które odpytują te same dane (dublujące się odczyty) i procesowane są niezależnie. Z pomocą przychodzą nam rozszerzenia T-SQL, pozwalające zrealizować tego typu scenariusze znacznie prościej i wydajniej.


ROLLUP

ROLLUP rozszerza funkcjonalność klauzuli GROUP BY, o możliwość tworzenia tzw. kostek analitycznych połówkowych. Funkcja ta, przyjmuje jako parametry, analogicznie jak w zwykłym grupowaniu, atrybuty (nazwy kolumny) tabel wejściowych lub ich przekształcenia.

Jeśli zastosujesz ROLLUP (a,b,c), wykonane zostaną grupowania dla kolejnych kombinacji atrybutów :

  • GROUP BY (a,b,c)
  • GROUP BY (a,b)
  • GROUP BY (a)
  • oraz po całości, czyli GROUP BY () – co w praktyce zapisujemy bez jawnego grupowania (wszystkie kolumny tworzą część danych surowych)

ROLLUP to równoważnik realizacji N+1 grupowań. W przypadku podania trzech (a,b,c) atrybutów, zostaną wykonane 3 + 1 = 4 operacje. Co warto podkreślić, wydajnościowo będzie to znacznie bardziej efektywne zapytanie, niż osobne grupowania i łączenia zbiorów za pomocą UNION, pokazane w poprzednim przykładzie (Query1).

Zobaczmy teraz rozwiązanie naszego scenariusza, czyli na trzech poziomach za pomocą ROLLUP :

-- Query2
SELECT Year(Orderdate)  as Rok, DatePart(q,Orderdate)  as Q , count(OrderId) as OrderQty
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY ROLLUP( Year(Orderdate)  , DatePart(q,Orderdate) )

Wielokrotne_grupowanie_02

Sam zapis kwerendy, jak również jej plan wykonania (Query2) przy tym podejściu jest znacznie prostszy i co ważniejsze – bardziej efektywny. Wystarczy zerknąć na porównanie wydajności zapytań :

Wielokrotne_grupowanie_03

a także statystyki odczytów :

Wielokrotne_grupowanie_04

Odnosząc się do poprzedniego przykładu –3 krotnie została zmniejszona liczba odczytów (tu też widać, że każda kwerenda w UNION, była realizowana niezależnie).

Sam wynik może jeszcze nie być do końca satysfakcjonujący (brakuje informacji o poziomach agregacji), ale tym zajmiemy się w dalszej części tego artykułu – przy okazji omówienia funkcji GROUPING_ID().


CUBE

Drugą funkcjonalnością wielokrotnego grupowanie to CUBE czyli pełna kostka analityczna. Stosujemy ją podobnie jak ROLLUP w poleceniu GROUP BY. Jej działanie to wykonanie grupowania we wszystkich wymiarach. Czyli jeśli podamy 3 atrybuty, to wykonanych zostanie 2^3 = 8 operacji.

Chciałbym przy okazji przypomnieć, jedną z podstawowych zasad pisania zapytań. Zawsze pobieramy tylko takie dane, jakie w danym momencie potrzebujemy. CUBE wykonuje dla nas 2^N-tej grupowań – to kosztuje. Odnieśmy się do naszego przykładu i zamieńmy ROLLUP na CUBE :

SELECT Year(Orderdate)  as Rok, DatePart(q,Orderdate)  as Q , count(OrderId) as OrderQty
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY CUBE(Year(Orderdate)  , DatePart(q,Orderdate))

Wielokrotne_grupowanie_05

Dla dwóch atrybutów w CUBE(), wykonane zostały 4 grupowania. W porównaniu do ROLLUP, pojawił się dodatkowy poziom, tylko po kolumnie Q – kwartały. Niezależnie od roku, mamy informacje o całkowitej liczbie zleceń w kwartałach.

W praktyce, CUBE stosujemy w procesach ETL, rzadko w zwykłych widokach czy zapytaniach, właśnie ze względu na ilość grupowań (pełna kostka).


GROUPING SETS

Na koniec zostawiłem wisienkę na torcie, czyli najbardziej elastyczny i chyba najczęściej wykorzystywany w praktyce sposób na wielokrotne grupowanie.

Za pomocą GROUPING SETS możemy określić konkretne poziomy grupowań. ROLLUP i CUBE opisane wcześniej miały z góry narzuconą liczebność grup. W tym przypadku, możemy jawnie określić zbiory atrybutów po których wykonamy agregacje. Jest to więc także bardziej wydajna, bo oszczędna metoda, gdy nie potrzebujemy tylu wymiarów, ile dają dwie poprzednie metody. Z tego też powodu, jest chętnie stosowana na przykład w widokach, czy zwykłych kwerendach.

Odpowiednikiem ROLLUP i rozwiązaniem naszego scenariusza za pomocą GROUPING SETS będzie poniższa kwerenda :

SELECT Year(Orderdate)  as Rok, DatePart(q,Orderdate)  as Q , count(OrderId) as OrderQty
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY GROUPING SETS
(
	(),
	(Year(Orderdate)) ,
	(Year(Orderdate)  , DatePart(q,Orderdate))
 
)
ORDER BY Rok, Q

Sama struktura jest intuicyjna i wymaga tylko określenia zbiorów grupujących. Będą to atrybuty tabel wejściowych lub ich przekształcenia, analogicznie jak w zwykłym grupowaniu, po których będą wykonywane kolejne agregacje.

Załóżmy, że nie interesuje nas grupowanie podsumowujące całą sprzedaż w zadanym okresie (po całości), ale chcemy wyświetlić tylko raport na dwóch poziomach szczegółowości . Zagregowane dane na temat ilości zamówień w poszczególnych latach i kwartałach.

SELECT Year(Orderdate)  as Rok, DatePart(q,Orderdate)  as Q , count(OrderId) as OrderQty
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY GROUPING SETS
(
	(Year(Orderdate)) ,
	(Year(Orderdate)  , DatePart(q,Orderdate))
 
)
ORDER BY Rok, Q

Wielokrotne_grupowanie_06

W takim scenariuszu, najlepiej właśnie użyć GROUPING SETS – zgodnie z zasadą, pobierania tylko takich danych jakie są nam potrzebne.


Funkcje GROUPING() oraz GROUPING_ID()

Z przedstawionymi powyżej trzema metodami grupowania wielokrotnego, skojarzone są dwie specjalne funkcje skalarne. W grupowaniu wielokrotnym, nie mamy jawnie podanej informacji, na jakim poziomie dana operacja się odbywa. Za pomocą tych funkcji, możemy zidentyfikować, czy dana kolumna, tworzy sekcję grupującą czy nie. Jest to szczególnie przydatne, w ostatecznym sortowaniu wyniku, aby np. otrzymać taki raport jak w pierwszym przykładzie tego artykułu.

Funkcja GROUPING(), przyjmuje jako parametr, dowolny atrybut (zazwyczaj nazwę kolumny), używany w grupowaniu. Zwraca wartość 0 lub 1, w zależności od tego, czy dana kolumna, wchodzi w skład sekcji grupującej (0) czy nie(1). Trochę to na odwrót w przyjętej powszechnie logice, ale można się przyzwyczaić.

Zobaczmy jej działanie na przykładzie :

SELECT Year(Orderdate)  as Rok, DatePart(q,Orderdate)  as Q , count(OrderId) as OrderQty , 
 
 
	GROUPING( Year(Orderdate) ) as [Grupowanie po roku],
	GROUPING( DatePart(q,Orderdate) )  as [Grupowanie po kwartale],
	GROUPING( Year(Orderdate) ) +  GROUPING( DatePart(q,Orderdate) ) as Poziom 
 
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY ROLLUP(Year(Orderdate)  , DatePart(q,Orderdate))
ORDER BY Rok, Poziom DESC

Wielokrotne_grupowanie_07

Na najwyższym poziome grupowania, czyli bez wnikania w wartości lat i kwartałów, zarówno atrybut YEAR(OrderDate) jak i DATEPART(q,OrderDate) nie tworzą sekcji grupującej. Funkcja GROUPING() dla tych atrybutów zwraca wartość 1.

Analogicznie w wierszu drugim i siódmym, mamy grupowanie tylko po latach, bez wnikania w wartości kwartałów. Jest to odpowiednik GROUP BY YEAR(OrderDate). Wartość GROUPING(YEAR(OrderDate)) będzie w tym przypadku 0, ponieważ ten atrybut tworzy sekcję grupującą.

Jak widać na tym przykładzie, za pomocą GROUPING(), możemy obliczyć wartość identyfikującą poziom grupowania.

Drugą funkcją, która już bez zbędnych ceregieli wyznaczy nam od razu identyfikator, jest GROUPING_ID().

Wynik takiej funkcji jest zawsze w postaci bitowej zamienionej na dziesiętną. Poniżej przykład obliczania jej wartości dla ROLLUP ( a, b, c ).

Wielokrotne_grupowanie_08

Odnieśmy się na koniec do naszego przykładu. Będziemy mieli dwa atrybuty grupujące, zatem wartości GROUPING_ID, będzie dla ROLLUP (a , b) zwracała wartość 0, 1 oraz 3.

SELECT Year(Orderdate)  as Rok, DatePart(q,Orderdate)  as Q , count(OrderId) as OrderQty , 
 
 
	GROUPING( Year(Orderdate) ) as [Grupowanie po roku],
	GROUPING( DatePart(q,Orderdate) )  as [Grupowanie po kwartale],
	GROUPING_ID( Year(Orderdate) ,  DatePart(q,Orderdate) ) as PoziomBinarny
 
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY ROLLUP(Year(Orderdate)  , DatePart(q,Orderdate))
ORDER BY Rok, PoziomBinarny DESC

Wielokrotne_grupowanie_09


Podsumowanie

Wielokrotne grupowanie za pomocą GROUPING SETS, ROLLUP I CUBE sprawdza się szczególnie w procesach ETL (Extract, Transform and Load). Rozszerza także możliwości zwykłych kwerend, skracając ich zapis i poprawiając wydajność w porównaniu do tradycyjnych metod wykorzystujących pojedyncze grupowanie i łączenie zbiorów za pomocą UNION.

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.