🥇Въведение в графовите бази данни на SQL Server 2017

В навечерието на старта на курса «MS SQL Server Developer» подготвихме за вас още един полезен превод.

Графови бази данни – важна технология за специалистите по бази данни. Стремя се да следя иновациите и новите технологии в тази област и, след работа с релационни и NoSQL бази данни, виждам, че ролята на графовите бази данни става все по-голяма. При работа със сложни йерархични данни традиционните бази данни, а дори и NoSQL, са малко ефективни. Често, с увеличаване на броя на нивата на връзките и размера на базата, наблюдаваме спад в производителността. А с усложняването на взаимовръзките нараства и броят на JOIN.

Разбира се, в релационния модел има решения за работа с йерархии (например, с помощта на рекурсивни CTE), но това все пак остава заобиколен подход. В същото време функционалността на графовите бази данни в SQL Server позволява лесно обработване на няколко нива на йерархия. Както моделът на данни, така и запитванията стават по-прости, което увеличава тяхната ефективност. Значително намалява обемът на кода.

Графовите бази данни представляват изразителен език за представяне на сложни системи. Тази технология вече се използва широко в ИТ индустрията в области като социални мрежи, антивирусни системи, анализ на ИТ мрежи, социални препоръки, препоръки за продукти и съдържание.

Функционалността на графовите бази данни в SQL Server е подходяща за сценарии, в които данните са силно свързани помежду си и имат ясно определени отношения.

Графова модел на данни

Графът е множество върхове (възли, node) и ръбове (взаимовръзки, edge). Върховете представляват същности, а ръбовете – връзки, в атрибутите на които може да се съдържа информация.

Графовата база данни моделира същностите под формата на граф, както е определено в теорията на графите. Структурите на данните са върховете и ръбовете. Атрибутите са свойствата на върховете и ръбовете. Връзката е свързване на върхове.

В отличие от другите модели на данни, в графовите бази данни взаимовръзките между същностите са с приоритет. Поради това не е необходимо да се изчисляват връзките с помощта на външни ключове или по други начини. Могат да се създават сложни модели на данни, използвайки само абстракции на върховете и ръбовете.

В съвременния свят моделирането на взаимовръзките изисква все по-сложни методи. За моделирането на свързаности SQL Server 2017 предлага възможности на графови бази данни. Върховете и ръбовете на графа се представят под формата на нови типове таблици: NODE и EDGE. За заявки към графа се използва нова функция T-SQL, наречена MATCH(). Тъй като тази функционалност е вградена в SQL Server 2017, тя може да се използва във вашите съществуващи бази данни без необходимост от каквато и да е конверсия.

Ползата от графовата модел

В момента бизнесът и потребителите изискват приложения, които работят с все по-голям обем данни, очаквайки същевременно висока производителност и надеждност. Представянето на данните под формата на граф предлага удобни средства за обработка на сложни връзки. Този подход позволява да се решат много проблеми и помага за получаване на резултати в определен контекст.

Изглежда, че в бъдеще много приложения ще могат да се възползват от използването на графови бази данни.

Моделиране на данни: от релационния модел към графовия

Пример

Нека разгледаме пример за организационна структура с йерархия на служителите: служителят подчинява на мениджър, мениджърът — на старши мениджър и така нататък. В зависимост от конкретната компания в тази йерархия може да има всякакво количество нива. Но с увеличаването на броя на нивата изчислението на връзките в релационната база данни става все по-сложно. В нея е доста трудно да се представи йерархията на служителите, йерархията в маркетинга или връзките в социалните мрежи. Нека видим как с помощта на SQL Graph можем да решим проблема с обработката на различни нива на йерархия.

За този пример ще създадем проста модел на данни. Ще създадем таблица на служителите EMP с идентификатор EMPNO и колона MGR, посочваща идентификатора на ръководителя (мениджера) на служителя. Цялата информация за йерархията се съхранява в тази таблица и може да бъде извикана чрез колоните EMPNO и MGR.

На следващата диаграма е изобразен същият модел на организационна структура с четири нива на вложеност по по-познат начин. Служителите са върховете на графа от таблицата EMP. Съществото „служител” е свързано само със себе си посредством връзката „подчинява се” (ReportsTo). В термините на графа, връзката е ръб (EDGE), който свързва възлите (NODE) на служителите.

Нека създадем обикновена таблица EMP и да добавим стойности в съответствие с горепосочената диаграма.

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)

На показаната по-долу илюстрация са представени служителите:

служител с EMPNO 7369 подчинява 7902;
служител с EMPNO 7902 подчинява 7566
служител с EMPNO 7566 подчинява 7839

Сега да видим представянето на същите данни под форма на граф. Върхът EMPLOYEE има няколко атрибута и е свързан сам със себе си с връзката "подчинява" (EmplReportsTo). EmplReportsTo е името на връзката.

В таблицата ребра (EDGE) също могат да присъстват атрибути.

Нека създадем таблица на възлите EmpNode

Синтаксисът за създаване на възел е доста прост: в края на израза CREATE TABLE се добавя "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;

Сега да преобразуваме данните от обикновената таблица в графова. Следващият INSERT вмъква данни от релационната таблица EMP.

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

В таблицата на възлите в специалната колона $node_id_* се съхранява идентификаторът на възела под формата на JSON. В останалите колони на тази таблица се намират атрибутите на възела.

Създаваме ребра (EDGE)

Създаването на таблица на ребрата е много подобно на създаването на таблица на възлите, с изключение на това, че се използва ключовата дума "AS EDGE".

CREATE TABLE empReportsTo(Deptno int) AS EDGE

Сега да определим връзките между служителите, използвайки колоните EMPNO и MGR. По диаграмата на организационната структура е добре видно как да напишем INSERT.

ВЪВЕДЕТЕ в empReportsTo ЗНАЧЕНИЯ ((SELECT $node_id FROM EmpNode WHERE ID = 1),
   	(SELECT $node_id FROM EmpNode WHERE id = 13),20);
ВЪВЕДЕТЕ в empReportsTo ЗНАЧЕНИЯ ((SELECT $node_id FROM EmpNode WHERE ID = 2),
   	(SELECT $node_id FROM EmpNode WHERE id = 6),10);
ВЪВЕДЕТЕ в empReportsTo ЗНАЧЕНИЯ ((SELECT $node_id FROM EmpNode WHERE ID = 3),
   	(SELECT $node_id FROM EmpNode WHERE id = 6),10)
ВЪВЕДЕТЕ в empReportsTo ЗНАЧЕНИЯ ((SELECT $node_id FROM EmpNode WHERE ID = 4),
   	(SELECT $node_id FROM EmpNode WHERE id = 9),30);
ВЪВЕДЕТЕ в empReportsTo ЗНАЧЕНИЯ ((SELECT $node_id FROM EmpNode WHERE ID = 5),
   	(SELECT $node_id FROM EmpNode WHERE id = 6),30);
ВЪВЕДЕТЕ в empReportsTo ЗНАЧЕНИЯ ((SELECT $node_id FROM EmpNode WHERE ID = 6),
   	(SELECT $node_id FROM EmpNode WHERE id = 9),30);
ВЪВЕДЕТЕ в empReportsTo ЗНАЧЕНИЯ ((SELECT $node_id FROM EmpNode WHERE ID = 7),
   	(SELECT $node_id FROM EmpNode WHERE id = 9),30);
ВЪВЕДЕТЕ в empReportsTo ЗНАЧЕНИЯ ((SELECT $node_id FROM EmpNode WHERE ID = 8),
   	(SELECT $node_id FROM EmpNode WHERE id = 4),30);
ВЪВЕДЕТЕ в empReportsTo ЗНАЧЕНИЯ ((SELECT $node_id FROM EmpNode WHERE ID = 9),
   	(SELECT $node_id FROM EmpNode WHERE id = 9),30);
ВЪВЕДЕТЕ в empReportsTo ЗНАЧЕНИЯ ((SELECT $node_id FROM EmpNode WHERE ID = 10),
   	(SELECT $node_id FROM EmpNode WHERE id = 6),30);
ВЪВЕДЕТЕ в empReportsTo ЗНАЧЕНИЯ ((SELECT $node_id FROM EmpNode WHERE ID = 11),
   	(SELECT $node_id FROM EmpNode WHERE id = 8),30);
ВЪВЕДЕТЕ в empReportsTo ЗНАЧЕНИЯ ((SELECT $node_id FROM EmpNode WHERE ID = 12),
   	(SELECT $node_id FROM EmpNode WHERE id = 6),30);
ВЪВЕДЕТЕ в empReportsTo ЗНАЧЕНИЯ ((SELECT $node_id FROM EmpNode WHERE ID = 13),
   	(SELECT $node_id FROM EmpNode WHERE id = 4),30);
ВЪВЕДЕТЕ в empReportsTo ЗНАЧЕНИЯ ((SELECT $node_id FROM EmpNode WHERE ID = 14),
   	(SELECT $node_id FROM EmpNode WHERE id = 7),30);

Таблицата на ребрата по подразбиране се състои от три колони. Първата, $edge_id — идентификатор на реброто в JSON формат. Двете други ($from_id и $to_id) представляват връзката между възлите. Освен това, ребрата могат да имат допълнителни свойства. В нашия случай това е Deptno.

Системни изгледи

В системния изглед sys.tables появиха се две нови колони:

is_edge
is_node

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

SSMS

Обектите, свързани с графи, се намират в папката Graph Tables. Иконата на таблицата на възлите е обозначена с точка, а таблиците на ребрата — с два свързани кръга (което малко прилича на очила).

Изразът MATCH

Израз MATCH е взет от CQL (Cypher Query Language). Това е ефективен начин за запитване на свойствата на графа. CQL започва с израза MATCH.

Синтаксис

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

Примери

Нека погледнем няколко примера.

Дадената по-долу заявка показва служителите, на които е подчинен Смит и неговия мениджър.

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'

Следващото запитване е предназначено за намиране на служители и мениджъри от второ ниво за Smith. Ако премахнем предложението WHERE, в резултат ще се покажат всички служители.

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'

И накрая, запитването за служители и мениджри от трето ниво.

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'

Сега нека променим посоката, за да получим началниците на Smith.

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)

Заключение

SQL Server 2017 се е утвърдил като пълноправно корпоративно решение за различни ИТ-задачи в бизнеса. Първата версия на SQL Graph е многообещаваща. Дори с някои ограничения, вече има достатъчно функционалност за проучване на възможностите на графите.

Функционалността на SQL Graph е напълно интегрирана в SQL Engine. Въпреки това, както вече беше споменато, в SQL Server 2017 има следните ограничения:

Няма поддръжка на полиморфизъм.

Поддържат се само еднопосочни връзки.
Не може да се актуализират колоните $from_id и $to_id на ръбовете чрез UPDATE.
Не се поддържат транситивни затваряния (transitive closure), но могат да се получат с помощта на CTE.
Поддръжката на In-Memory OLTP обекти е ограничена.
Не се поддържат времеви таблици (System-Versioned Temporal Table), времеви локални и глобални таблици.
Таблични типове и таблични променливи не могат да бъдат обявявани като NODE или EDGE.
Не се поддържат запитвания между бази данни (cross-database queries).
Няма директен начин или някакъв магьосник (wizard) за преобразуване на обикновени таблици в графови.
Няма GUI за визуализиране на графи, но можете да използвате Power BI.