🥇Hoʻolauna i ka Graph Databases SQL Server 2017

Ma mua o ka hoʻomaka ʻana o ka papa "Ka mea hoʻomohala MS SQL Server" Ua hoʻomākaukau mākou i kekahi unuhi pono no ʻoe.

He ʻenehana koʻikoʻi nā ʻikepili kiʻi no ka poʻe loea waihona. Ke ho'āʻo nei au e hoʻomau i nā mea hou a me nā ʻenehana hou ma kēia wahi a, ma hope o ka hana ʻana me nā ʻikepili relational a me NoSQL, ʻike wau i ka lilo ʻana o ka ʻikepili kiʻi i mea nui. I ka hana ʻana me ka ʻikepili hierarchical paʻakikī, ʻaʻole pono wale nā ʻikepili kuʻuna, akā ʻo NoSQL pū kekahi. ʻO ka manawa pinepine, e like me ka piʻi ʻana o nā pae pili a me ka nui o ka waihona, hoʻemi ka hana. A i ka lilo ʻana o nā pilina paʻakikī, hoʻonui pū ka helu o JOIN.

ʻOiaʻiʻo, loaʻa i ka model relational nā hoʻonā no ka hana ʻana me nā hierarchies (no ka laʻana, me ka hoʻohana ʻana i nā CTE recursive), akā he mau workarounds kēia. I ka manawa like, hiki i ka hana o nā ʻikepili kiʻi SQL Server iā ʻoe ke lawelawe maʻalahi i nā pae he nui o ka hierarchy. Hoʻomaʻamaʻa ʻia ke kumu hoʻohālike ʻikepili a me nā nīnau, a no laila e hoʻonui ʻia ko lākou pono. Hoʻemi nui ʻia ka nui o ke code.

He ʻōlelo hōʻike no ka hōʻike ʻana i nā ʻōnaehana paʻakikī. Ua hoʻohana nui ʻia kēia ʻenehana i ka ʻoihana IT ma nā wahi e like me ka ʻoihana pūnaewele, nā ʻōnaehana anti-fraud, ka nānā ʻana i ka ʻoihana IT, nā ʻōlelo paipai, nā huahana a me nā ʻōlelo aʻoaʻo.

He kūpono ka hana ʻikepili kiʻi ma SQL Server no nā hiʻohiʻona kahi i pili nui ai ka ʻikepili a loaʻa nā pilina i wehewehe maikaʻi ʻia.

Ke Ana Hoʻohālike Kiʻi

ʻO ka pakuhi he pūʻulu o nā vertices (nodes) a me nā ʻaoʻao (pilina, lihi). Hōʻike nā vertices i nā hui, a ʻo nā ʻaoʻao e hōʻike ana i nā pilina i loaʻa i nā ʻano ʻike.

Hoʻohālike ka ʻikepili kiʻi kiʻi i nā mea e like me ka pakuhi e like me ka wehewehe ʻana i ka manaʻo pakuhi. ʻO ka hoʻonohonoho ʻikepili he poʻo a me nā ʻaoʻao. ʻO nā hiʻohiʻona nā waiwai o nā piko a me nā ʻaoʻao. ʻO ka pilina kahi pilina o nā vertex.

ʻAʻole e like me nā kumu hoʻohālike ʻikepili ʻē aʻe, ʻo ka ʻikepili kiʻi ka mea nui i ka pilina ma waena o nā hui. No laila, ʻaʻohe pono e helu i nā pilina me ka hoʻohana ʻana i nā kī haole a i ʻole nā mea ʻē aʻe. Hiki ke hana i nā hiʻohiʻona ʻikepili paʻakikī me ka hoʻohana ʻana i nā abstractions vertex a me nā kihi.

I kēia ao hou, pono ka hoʻohālikelike ʻana i nā ʻano hana paʻakikī. No ka hoʻohālikelike ʻana i nā pilina, hāʻawi ʻo SQL Server 2017 i nā mana ʻikepili kiʻi. Hōʻike ʻia nā piko a me nā ʻaoʻao o ka pakuhi e like me nā ʻano papaʻaina hou: NODE a me EDGE. Hoʻohana nā nīnau kiʻi i kahi hana T-SQL hou i kapa ʻia MATCH (). No ka mea ua kūkulu ʻia kēia hana i loko o SQL Server 2017, hiki ke hoʻohana ʻia i kāu ʻikepili i kēia manawa me ka ʻole o ka hoʻololi ʻana.

Nā pōmaikaʻi o ke kumu hoʻohālike

I kēia lā, koi nā ʻoihana a me nā mea hoʻohana i nā noi e mālama i nā ʻikepili hou aʻe, ʻoiai e manaʻo ana i ka hana kiʻekiʻe a me ka hilinaʻi. Hāʻawi ka hōʻike ʻana i ka ʻikepili ma ke ʻano he pakuhi i kahi ala kūpono no ka mālama ʻana i nā pilina paʻakikī. Hoʻoponopono kēia ala i nā pilikia he nui a kōkua i ka loaʻa ʻana o nā hopena i loko o kahi pōʻaiapili i hāʻawi ʻia.

Me he mea lā e pōmaikaʻi nā noi he nui mai ka hoʻohana ʻana i nā ʻikepili kiʻi i ka wā e hiki mai ana.

Hoʻohālike ʻikepili: mai ka pilina pili a i ke kumu hoʻohālike

Pākuhi:

E nānā kākou i kahi laʻana o kahi hoʻonohonoho hoʻonohonoho me kahi hierarchy o nā limahana: hōʻike kahi limahana i kahi manakia, hōʻike ka luna i kahi luna kiʻekiʻe, a pēlā aku. Ma muli o ka ʻoihana kikoʻī, aia paha kekahi mau pae i kēia hierarchy. Akā, i ka piʻi ʻana o nā pae, ʻoi aku ka paʻakikī o ka helu ʻana i nā pilina ma kahi waihona pili. He mea paʻakikī ke noʻonoʻo i ka hierarchy o nā limahana, hierarchy i ke kūʻai aku a i ʻole nā pilina ma nā ʻoihana pūnaewele. E ʻike pehea e hiki ai iā SQL Graph ke hoʻoponopono i ka pilikia o ka lawelawe ʻana i nā pae like ʻole o ka hierarchy.

No kēia hiʻohiʻona e hana mākou i kahi hoʻohālike data maʻalahi. E hana kakou i papaaina o na limahana PMS me ka ID EMPNO a me ke kolamu Ua haʻi aku ʻo M.G.R., e hōʻike ana i ka ʻike o ka luna o ka limahana (manakia). Mālama ʻia nā ʻike āpau e pili ana i ka hierarchy ma kēia pākaukau a hiki ke nīnau ʻia me nā kolamu EMPNO и Ua haʻi aku ʻo M.G.R..

Hōʻike ke kiʻikuhi ma lalo i ke ʻano hoʻohālike hoʻonohonoho hoʻonohonoho like me nā pae ʻehā o ka pūnana ma kahi ʻano kamaʻāina. ʻO nā limahana nā piko o ka pakuhi mai ka papaʻaina PMS. Hoʻopili ʻia ka hui "limahana" iā ia iho e ka pilina "hoʻouna" (ReportsTo). Ma nā huaʻōlelo kiʻi, kahi loulou kahi kihi (EDGE) e hoʻopili ai i nā nodes (NODE) o nā limahana.

E hana kākou i papaʻaina maʻamau PMS a hoʻohui i nā waiwai ma laila e like me ke kiʻi ma luna.

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)

Hōʻike ke kiʻi ma lalo nei i nā limahana:

limahana me EMPNO 7369 hoʻolohe 7902;
limahana me EMPNO Hoʻolohe ʻo 7902 i ka 7566
limahana me EMPNO Hoʻolohe ʻo 7566 i ka 7839

I kēia manawa, e nānā kākou i kahi hōʻike kiʻi o ka ʻikepili like. He nui nā ʻano o ka EMPLOYEE vertex a pili iā ia iho e ka pilina "subordinates" (EmplReportsTo). ʻO EmplReportsTo ka inoa o ka pilina.

Hiki i ka papa ʻaoʻao (EDGE) ke loaʻa nā ʻano.

E hana kākou i papa node EmpNode

He maʻalahi ka syntax no ka hana ʻana i kahi node: i ka ʻōlelo OLELO HOOLAHA hoʻohui ʻia i ka hopena "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;

I kēia manawa, e hoʻololi kākou i ka ʻikepili mai ka papa maʻamau i ka papaʻaina. Aʻe HOOLAHA hoʻokomo i ka ʻikepili mai kahi papa pili PMS.

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

Ma ka papa o nā nodes i kahi kolamu kūikawā $node_id_* mālama ʻia ka mea hōʻike node e like me JSON. Aia nā kolamu i koe o kēia pākaukau i nā ʻano o ka node.

Ke hana nei i nā kihi (EDGE)

He like loa ka hana ʻana i papa ʻaoʻao me ka hoʻokumu ʻana i ka papa node, koe wale nō ka hoʻohana ʻana i ka huaʻōlelo "AS EDGE".

CREATE TABLE empReportsTo(Deptno int) AS EDGE

I kēia manawa e wehewehe kākou i nā pilina ma waena o nā limahana e hoʻohana ana i nā kolamu EMPNO и Ua haʻi aku ʻo M.G.R.. Hōʻike maopopo ke kiʻi hoʻonohonoho hoʻonohonoho i ke kākau ʻana HOOLAHA.

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);

ʻEkolu kolamu ka papa ʻaoʻao paʻamau. Ka mua, $edge_id - ʻike maka ma ke ʻano JSON. ʻO nā mea ʻelua ʻē aʻe ($from_id и $to_id) hōʻike i ka pilina ma waena o nā nodes. Eia kekahi, hiki i nā kihi ke loaʻa nā waiwai ʻē aʻe. I kā mākou hihia Deptno.

Nānā Pūnaewele

Ma ka ʻōnaehana nānā sys.tables elua kolamu hou i puka mai:

is_edge
ʻo_node

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

ssms

Aia nā mea pili i nā kiʻi i loko o ka waihona Graph Tables. Hōʻailona ʻia ka ʻona papa ʻaina node me kahi kiko, a ua kaha ʻia ka ikona papa ʻaina me nā pōʻai pili ʻelua (e like me nā aniani).

ʻŌlelo hoʻokūkū

Hōʻike ʻO MATCH lawe ʻia mai CQL (Cypher Query Language). He ala maikaʻi kēia e nīnau ai i nā waiwai o ka pakuhi. Hoʻomaka ʻo CQL me kahi ʻōlelo ʻO MATCH.

Ka Mooolelo O

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

examples

E nānā kākou i kekahi mau laʻana.

Hōʻike ka nīnau ma lalo nei i nā limahana i hōʻike ʻia e Smith a me kāna luna.

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'

ʻO ka nīnau aʻe no ka loaʻa ʻana o nā limahana a me nā luna ʻelua no Smith. Inā wehe ʻoe i ka ʻōlelo kahi, a laila e hōʻike ka hopena i nā limahana a pau.

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'

A ʻo ka mea hope loa, he noi no nā limahana a me nā luna ʻekolu.

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'

I kēia manawa e hoʻololi kākou i ke ala e kiʻi ai i nā luna o 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)

hopena

Ua hoʻokumu ʻo SQL Server 2017 iā ia iho he hopena ʻoihana piha no nā hana ʻoihana IT like ʻole. ʻO ka mana mua o SQL Graph he mea hoʻohiki loa. ʻOiai me kekahi mau palena, ua lawa ka hana e ʻimi ai i nā hiki o nā kiʻi.

Hoʻohui piha ʻia ka hana SQL Graph i loko o SQL Engine. Eia naʻe, e like me ka mea i hōʻike mua ʻia, aia ka SQL Server 2017 i nā palena aʻe:

ʻAʻohe kākoʻo polymorphism.

Kākoʻo ʻia nā pilina unidirectional.
ʻAʻole hiki i nā Edges ke hōʻano hou i nā kolamu $from_id a me $to_id ma o UPDATE.
ʻAʻole kākoʻo ʻia nā pani pani ʻana, akā hiki ke loaʻa iā lākou me CTE.
Kākoʻo ʻia nā mea In-Memory OLTP.
ʻAʻole kākoʻo ʻia nā papa ʻaina (System-Versioned Temporal Table), nā papa ʻaina kūloko a me ka honua.
ʻAʻole hiki ke haʻi ʻia nā ʻano pākaukau a me nā mea hoʻololi papa ma ke ʻano he NODE a i ʻole EDGE.
ʻAʻole kākoʻo ʻia nā nīnau ʻikepili cross-data.
ʻAʻohe ala pololei a kupua paha e hoʻohuli i nā papa maʻamau i nā papa kiʻi.
ʻAʻohe GUI e hōʻike i nā kiʻi, akā hiki iā ʻoe ke hoʻohana i ka Power BI.

E heluhelu hou:

Pehea e hoʻohana ai ʻo SQL Server i nā kānana bitmap

Source: www.habr.com

Introduction to Graph Databases SQL Server 2017