🥇Wprowadzenie do grafowych baz danych SQL Server 2017

W przeddzień rozpoczęcia kursu „MS SQL Server Developer“ przygotowaliśmy dla Ciebie jeszcze jedno przydatne tłumaczenie.

Bazy danych grafowe to istotna technologia dla specjalistów w dziedzinie baz danych. Staram się na bieżąco śledzić innowacje i nowe technologie w tej dziedzinie i po pracy z relacyjnymi oraz NoSQL bazami danych widzę, że rola baz danych grafowych rośnie. W pracy z złożonymi danymi hierarchicznymi tradycyjne bazy danych oraz NoSQL są mało efektywne. Często, w miarę wzrostu liczby poziomów powiązań i rozmiaru bazy, obserwuje się spadek wydajności. A w miarę komplikacji powiązań rośnie liczba JOIN.

Oczywiście w modelu relacyjnym istnieją rozwiązania do pracy z hierarchiami (na przykład przy użyciu rekurencyjnych CTE), ale to wciąż pozostaje obejściem. Funkcjonalność baz danych grafowych w SQL Server umożliwia łatwe przetwarzanie wielu poziomów hierarchii. Uproszczona zostaje zarówno model danych, jak i zapytania, a co za tym idzie, zwiększa się ich efektywność. Znacząco skraca się również objętość kodu.

Bazy danych grafowe to wyrazisty język do przedstawiania złożonych systemów. Technologia ta jest już dość szeroko stosowana w branży IT w takich obszarach jak media społecznościowe, systemy przeciwdziałania oszustwom, analiza sieci IT, rekomendacje społeczne, a także rekomendacje produktów i treści.

Funkcjonalność baz danych grafowych w SQL Server nadaje się do scenariuszy, w których dane są silnie ze sobą powiązane i mają wyraźnie określone relacje.

Grafowa model danych

Graf to zbiór węzłów (node) oraz krawędzi (edge). Węzły reprezentują byty, a krawędzie – powiązania, w atrybutach których może znajdować się informacja.

Grafowa baza danych modeluje byty w postaci grafu, tak jak to jest zdefiniowane w teorii grafów. Struktury danych to węzły i krawędzie. Atrybuty to właściwości węzłów i krawędzi. Powiązanie to połączenie węzłów.

W przeciwieństwie do innych modeli danych, w bazach danych grafowych na pierwszym miejscu stawiane są powiązania między bytami. Dlatego nie ma potrzeby obliczania powiązań przy użyciu kluczy obcych lub w inny sposób. Można tworzyć złożone modele danych, korzystając wyłącznie z abstrakcji węzłów i krawędzi.

W dzisiejszym świecie modelowanie relacji wymaga coraz bardziej złożonych metod. Do modelowania relacji SQL Server 2017 oferuje możliwości baz danych grafowych. Wierzchołki i krawędzie grafu przedstawiane są jako nowe typy tabel: NODE i EDGE. Do zapytań do grafu służy nowa funkcja T-SQL o nazwie MATCH(). Ponieważ ta funkcjonalność jest wbudowana w SQL Server 2017, można ją wykorzystać w istniejących bazach danych bez potrzeby ich konwersji.

Korzyści z modelu grafowego

Obecnie biznes i użytkownicy oczekują aplikacji, które radzą sobie z coraz większymi wolumenami danych, przy tym wymagając wysokiej wydajności i niezawodności. Przedstawienie danych w postaci grafu oferuje wygodne narzędzia do obsługi złożonych relacji. Takie podejście pozwala rozwiązać wiele problemów i pomaga uzyskać wyniki w ramach określonego kontekstu.

Wygląda na to, że w przyszłości wiele aplikacji może skorzystać z wykorzystania baz danych grafowych.

Modelowanie danych: od modelu relacyjnego do modelu grafowego

Przykład

Rozważmy przykład struktury organizacyjnej z hierarchią pracowników: pracownik podlega menedżerowi, menedżer — starszemu menedżerowi i tak dalej. W zależności od konkretnej firmy w tej hierarchii może być dowolna liczba poziomów. Jednak wraz ze wzrostem liczby poziomów obliczenie relacji w relacyjnej bazie danych staje się coraz trudniejsze. W tym modelu dość trudno jest przedstawić hierarchię pracowników, hierarchię w marketingu czy relacje w sieciach społecznościowych. Zobaczmy, jak za pomocą SQL Graph można rozwiązać problem przetwarzania różnych poziomów hierarchii.

Dla tego przykładu stwórzmy prosty model danych. Utworzymy tabelę pracowników EMP o identyfikatorze EMPNO i kolumnie MGR, wskazującej na identyfikator przełożonego (menedżera) pracownika. Cała informacja o hierarchii jest przechowywana w tej tabeli i może być pobierana za pomocą kolumn. EMPNO i MGR.

Na następnym diagramie przedstawiona jest ta sama struktura organizacyjna z czterema poziomami zagnieżdżenia w bardziej znanej formie. Pracownicy to wierzchołki grafu z tabeli EMP. Encja „pracownik” jest sama ze sobą powiązana relacją „podlega” (ReportsTo). W terminach grafu relacja to krawędź (EDGE), która łączy węzły (NODE) pracowników.

Stwórzmy zwykłą tabelę EMP i dodajmy do niej wartości zgodnie z powyższą diagramem.

CREATE TABLE EMP
(EMPNO INT NOT NULL,
ENAME VARCHAR(20),
JOB VARCHAR(10),
MGR INT,
JOINDATE DATETIME,
SALARY DECIMAL(7, 2),
COMMISIION DECIMAL(7, 2),
DNO INT)
 
INSERT INTO EMP VALUES
(7369, 'SMITH', 'CLERK', 7902, '02-MAR-1970', 8000, NULL, 2),
(7499, 'ALLEN', 'SALESMAN', 7698, '20-MAR-1971', 1600, 3000, 3),
(7521, 'WARD', 'SALESMAN', 7698, '07-FEB-1983', 1250, 5000, 3),
(7566, 'JONES', 'MANAGER', 7839, '02-JUN-1961', 2975, 50000, 2),
(7654, 'MARTIN', 'SALESMAN', 7698, '28-FEB-1971', 1250, 14000, 3),
(7698, 'BLAKE', 'MANAGER', 7839, '01-JAN-1988', 2850, 12000, 3),
(7782, 'CLARK', 'MANAGER', 7839, '09-APR-1971', 2450, 13000, 1),
(7788, 'SCOTT', 'ANALYST', 7566, '09-DEC-1982', 3000, 1200, 2),
(7839, 'KING', 'PRESIDENT', NULL, '17-JUL-1971', 5000, 1456, 1),
(7844, 'TURNER', 'SALESMAN', 7698, '08-AUG-1971', 1500, 0, 3),
(7876, 'ADAMS', 'CLERK', 7788, '12-MAR-1973', 1100, 0, 2),
(7900, 'JAMES', 'CLERK', 7698, '03-NOV-1971', 950, 0, 3),
(7902, 'FORD', 'ANALYST', 7566, '04-MAR-1961', 3000, 0, 2),
(7934, 'MILLER', 'CLERK', 7782, '21-JAN-1972', 1300, 0, 1)

Na poniższym rysunku przedstawieni są pracownicy:

pracownik z EMPNO 7369 podlega 7902;
pracownik z EMPNO 7902 podlega 7566
pracownik z EMPNO 7566 podlega 7839

Teraz spójrzmy na przedstawienie tych samych danych w postaci grafu. Wierzchołek EMPLOYEE ma kilka atrybutów i jest powiązany sam ze sobą połączeniem „podlega” (EmplReportsTo). EmplReportsTo to nazwa tej relacji.

W tabeli krawędzi (EDGE) mogą również występować atrybuty.

Stwórzmy tabelę węzłów EmpNode

Składnia tworzenia węzła jest dość prosta: do wyrażenia CREATE TABLE dodaje się na końcu „AS NODE”.

CREATE TABLE dbo.EmpNode(
ID Int Identity(1,1),
EMPNO NUMERIC(4) NOT NULL,
ENAME VARCHAR(10),
MGR NUMERIC(4),
DNO INT
) AS NODE;

Teraz przekształcimy dane z normalnej tabeli w graf. Następujące INSERT wstawia dane z tabeli relacyjnej EMP.

INSERT INTO EmpNode(EMPNO,ENAME,MGR,DNO) select empno,ename,MGR,dno from emp

W tabeli węzłów w specjalnej kolumnie $node_id_* przechowywany jest identyfikator węzła w formacie JSON. W pozostałych kolumnach tej tabeli znajdują się atrybuty węzła.

Tworzymy krawędzie (EDGE)

Tworzenie tabeli krawędzi jest bardzo podobne do tworzenia tabeli węzłów, z wyjątkiem tego, że używane jest słowo kluczowe „AS EDGE”.

CREATE TABLE empReportsTo(Deptno int) AS EDGE

Teraz zdefiniujemy relacje między pracownikami, używając kolumn EMPNO i MGR. Na diagramie struktury organizacyjnej dobrze widać, jak to napisać INSERT.

INSERT INTO empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 1),
   	(SELECT $node_id FROM EmpNode WHERE id = 13),20);
INSERT INTO empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 2),
   	(SELECT $node_id FROM EmpNode WHERE id = 6),10);
INSERT INTO empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 3),
   	(SELECT $node_id FROM EmpNode WHERE id = 6),10)
INSERT INTO empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 4),
   	(SELECT $node_id FROM EmpNode WHERE id = 9),30);
INSERT INTO empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 5),
   	(SELECT $node_id FROM EmpNode WHERE id = 6),30);
INSERT INTO empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 6),
   	(SELECT $node_id FROM EmpNode WHERE id = 9),30);
INSERT INTO empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 7),
   	(SELECT $node_id FROM EmpNode WHERE id = 9),30);
INSERT INTO empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 8),
   	(SELECT $node_id FROM EmpNode WHERE id = 4),30);
INSERT INTO empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 9),
   	(SELECT $node_id FROM EmpNode WHERE id = 9),30);
INSERT INTO empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 10),
   	(SELECT $node_id FROM EmpNode WHERE id = 6),30);
INSERT INTO empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 11),
   	(SELECT $node_id FROM EmpNode WHERE id = 8),30);
INSERT INTO empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 12),
   	(SELECT $node_id FROM EmpNode WHERE id = 6),30);
INSERT INTO empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 13),
   	(SELECT $node_id FROM EmpNode WHERE id = 4),30);
INSERT INTO empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 14),
   	(SELECT $node_id FROM EmpNode WHERE id = 7),30);

Domyślna tabela krawędzi składa się z trzech kolumn. Pierwsza, $edge_id — identyfikator krawędzi w formacie JSON. Dwie pozostałe ($from_id i $to_id) przedstawiają powiązanie między węzłami. Ponadto krawędzie mogą mieć dodatkowe właściwości. W naszym przypadku są to Deptno.

Widoki systemowe

W widoku systemowym sys.tables pojawiły się dwie nowe kolumny:

is_edge
is_node

SELECT t.is_edge,t.is_node,*
FROM sys.tables t
WHERE name like 'emp%'

SSMS

Obiekty związane z grafami znajdują się w folderze Tabele Grafowe. Ikona tabeli węzłów oznaczona jest kropką, a tabele krawędzi — dwoma powiązanymi okręgami (co trochę przypomina okulary).

Wyrażenie MATCH

Wyrażenie MATCH pochodzi z CQL (Cypher Query Language). To efektywny sposób zadawania zapytań dotyczących właściwości grafu. CQL zaczyna się od wyrażenia MATCH.

Składnia

MATCH (<graph_search_pattern>)
 
<graph_search_pattern>::=
    {<node_alias> {
                 	{ <-( <edge_alias> )- }
               	| { -( <edge_alias> )-> }
             	<node_alias>
             	}
 	}
 	[ { AND } { ( <graph_search_pattern> ) } ]
 	[ ,...n ]
 
<node_alias> ::=
    node_table_name | node_alias
 
<edge_alias> ::=
    edge_table_name | edge_alias

Przykłady

Przyjrzyjmy się kilku przykładom.

Poniższe zapytanie wyświetla pracowników, którzy podlegają Smithowi i jego menedżerowi.

SELECT
E.EMPNO,E.ENAME,E.MGR,E1.EMPNO,E1.ENAME,E1.MGR
FROM
    empnode e, empnode e1, empReportsTo m
WHERE
    MATCH(e-(m)->e1)
and e.ENAME='SMITH'

Następujące zapytanie ma na celu wyszukiwanie pracowników i menedżerów drugiego poziomu dla Smitha. Jeśli usuniemy zdanie WHERE, w wyniku czego zostaną wyświetleni wszyscy pracownicy.

SELECT
E.EMPNO,E.ENAME,E.MGR,E1.EMPNO,E1.ENAME,E1.MGR,E2.EMPNO,e2.ENAME,E2.MGR
FROM
    empnode e, empnode e1, empReportsTo m ,empReportsTo m1, empnode e2
WHERE
    MATCH(e-(m)->e1-(m1)->e2)
and e.ENAME='SMITH'

I w końcu zapytanie dla pracowników i menedżerów trzeciego poziomu.

SELECT
E.EMPNO,E.ENAME,E.MGR,E1.EMPNO,E1.ENAME,E1.MGR,E2.EMPNO,e2.ENAME,E2.MGR,E3.EMPNO,e3.ENAME,E3.MGR
FROM
    empnode e, empnode e1, empReportsTo m ,empReportsTo m1, empnode e2, empReportsTo M2, empnode e3
WHERE
    MATCH(e-(m)->e1-(m1)->e2-(m2)->e3)
and e.ENAME='SMITH'

Teraz zmieńmy kierunek, aby uzyskać przełożonych Smitha.

SELECT
E.EMPNO,E.ENAME,E.MGR,E1.EMPNO,E1.ENAME,E1.MGR,E2.EMPNO,e2.ENAME,E2.MGR,E3.EMPNO,e3.ENAME,E3.MGR
FROM
    empnode e, empnode e1, empReportsTo m ,empReportsTo m1, empnode e2, empReportsTo M2, empnode e3
WHERE
    MATCH(e<-(m)-e1<-(m1)-e2<-(m2)-e3)

Podsumowanie

SQL Server 2017 sprawdził się jako kompleksowe rozwiązanie korporacyjne dla różnych zadań IT w biznesie. Pierwsza wersja SQL Graph jest bardzo obiecująca. Mimo pewnych ograniczeń, już teraz istnieje wystarczająco dużo funkcjonalności do badania możliwości grafów.

Funkcjonalność SQL Graph jest w pełni zintegrowana z silnikiem SQL. Jednak, jak już wspomniano, w SQL Server 2017 występują następujące ograniczenia:

Brak wsparcia dla polimorfizmu.

Obsługiwane są tylko jednostronne związki.
Nie można aktualizować kolumn $from_id i $to_id na krawędziach przez UPDATE.
Nie obsługiwane są zamknięcia tranzytywne (transitive closure), ale można je uzyskać za pomocą CTE.
Ograniczone wsparcie dla obiektów In-Memory OLTP.
Nie obsługiwane są tabeli tymczasowe (System-Versioned Temporal Table), tymczasowe lokalne i globalne tabele.
Typy tabel i zmienne tabelowe nie mogą być deklarowane jako NODE lub EDGE.
Nie obsługiwane są zapytania między bazami danych (cross-database queries).
Nie ma bezpośredniego sposobu ani żadnego kreatora (wizard) do przekształcania zwykłych tabel w grafowe.
Do wizualizacji grafów nie ma GUI, ale można korzystać z Power BI.