🥇Увядзенне ў графавыя базы дадзеных 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 можна вырашыць праблему з апрацоўкай розных узроўняў іерархіі.

Для гэтага прыкладу зробім простую мадэль звестак. Створым табліцу супрацоўнікаў Эмі з ідэнтыфікатарам EMPNO і калонкай М.Г.Р., Якая паказвае на ідэнтыфікатар кіраўніка (мэнэджара) супрацоўніка. Уся інфармацыя аб іерархіі захоўваецца ў гэтай табліцы і можа быць запытана з дапамогай калонак. EMPNO и М.Г.Р..

На наступнай дыяграме намалявана гэтак жа самая мадэль аргструктуры з чатырма ўзроўнямі ўкладзенасці ў больш звыклым выглядзе. Супрацоўнікі - гэта вяршыні графа з табліцы Эмі. Сутнасць "супрацоўнік" звязана сама з сабою сувяззю "падпарадкоўваецца" (ReportsTo). У тэрмінах графа, сувязь - гэта рабро (EDGE), якое звязвае вузлы (NODE) супрацоўнікаў.

Давайце створым звычайную табліцу Эмі і дадамо туды значэння ў адпаведнасці з вышэйпрыведзенай дыяграмай.

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 устаўляе дадзеныя з рэляцыйнай табліцы Эмі.

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 и М.Г.Р.. Па дыяграме аргструктуры добра бачна як напісаць 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);

Табліца рэбраў па змаўчанні складаецца з трох слупкоў. Першы, $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

выраз МАТЧ узята з CQL (Cypher Query Language). Гэта эфектыўны спосаб запыту да ўласцівасцей графа. CQL пачынаецца з выраза МАТЧ.

сінтаксіс

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

прыклады

Давайце паглядзім на некалькі прыкладаў.

Прыведзены ніжэй запыт адлюстроўвае супрацоўнікаў, якім падпарадкоўваецца Smith і яго мэнэджар.

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. Калі прыбраць прапанову ДЗЕ, то ў выніку будуць адлюстроўвацца ўсе супрацоўнікі.

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.

Чытаць яшчэ:

Як SQL Server выкарыстоўвае bitmap-фільтры

Крыніца: habr.com

Увядзенне ў графавыя базы дадзеных SQL Server 2017