🥇Intshayelelo kwi-SQL Server ka-2017 yeeDatha zeDatha zeGrafu

Ngaphambi kokuqala kwekhosi "MS SQL Server Developer" Sikulungiselele enye inguqulelo eluncedo.

Oovimba bogcino-lwazi begrafu bubuchwephesha obubalulekileyo kubasebenzi bedatabase. Ndizama ukugcina izinto ezintsha kunye nobuchwepheshe obutsha kule ndawo kwaye, emva kokusebenza nobudlelwane kunye ne-NoSQL yolwazi, ndibona indima yogcino-lwazi lwegrafu ibaluleke kakhulu. Xa usebenza ngedatha eyinkimbinkimbi ye-hierarchical, kungekhona kuphela i-database yendabuko engasebenziyo, kodwa kunye ne-NoSQL. Ngokuqhelekileyo, njengoko inani lamanqanaba oqhagamshelwano kunye nobukhulu besiseko sedatha sanda, ukusebenza kuyancipha. Kwaye njengoko ubudlelwane busiba nzima ngakumbi, inani le-JOIN liyanda.

Ngokuqinisekileyo, imodeli yobudlelwane inezisombululo zokusebenza kunye ne-hierarchies (umzekelo, ukusebenzisa ii-CTEs eziphindaphindayo), kodwa ezi zisasebenza. Kwangaxeshanye, usebenziso lwesiseko sedata segrafu ye-SQL Server ikuvumela ukuba uphathe ngokulula amanqanaba amaninzi olawulo. Zombini imodeli yedatha kunye nemibuzo zenziwe lula, kwaye ke ngoko ukusebenza kwazo kuyanda. Isixa sekhowudi sincitshiswe kakhulu.

Oovimba beenkcukacha beegrafu lulwimi olucacileyo lokumela iinkqubo ezinzima. Le teknoloji sele isetyenziswa ngokubanzi kushishino lwe-IT kwiindawo ezifana neenethiwekhi zentlalo, iinkqubo zokulwa nobuqhetseba, uhlalutyo lwenethiwekhi ye-IT, iingcebiso zentlalo, imveliso kunye neengcebiso zomxholo.

Umsebenzi wedatha yegrafu kwi-SQL Server ifanelekile kwiimeko apho idatha idityaniswe kakhulu kwaye inobudlelwane obuchazwe kakuhle.

Imodeli yedatha yegrafu

Igrafu yiseti yee-vertices (i-nodes) kunye ne-edges (ubudlelwane, i-edge). I-Vertices imele amaziko, kwaye imiphetho imele ubudlelwane obuneempawu ezinokuthi ziqulathe ulwazi.

Iimodeli zesiseko sedatha yegrafu amaqumrhu njengegrafu njengoko kuchaziwe kwithiyori yegrafu. Ulwakhiwo lwedatha luthe nkqo kunye nemiphetho. Iimpawu ziimpawu ze-vertices kunye nemiphetho. Umdibaniso ludibaniso lweenkqo.

Ngokungafaniyo neminye imifuziselo yedatha, oovimba beenkcukacha beegrafu babeka phambili ubudlelwane phakathi kwamaqumrhu. Ngoko ke, akukho mfuneko yokubala ubudlelwane usebenzisa izitshixo zangaphandle okanye nayiphi na enye indlela. Kuyenzeka ukwenza imifuziselo yedatha entsonkothileyo usebenzisa kuphela i-vertex kunye ne-edge abstractions.

Kwihlabathi lanamhlanje, ubudlelwane bomzekelo bufuna iindlela ezintsonkothileyo. Ukwenza umzekelo wobudlelwane, i-SQL Server 2017 inikezela ngezakhono zedatha yegrafu. I-vertices kunye nemiphetho yegrafu imelwe njengeentlobo ezintsha zeetafile: i-NODE kunye ne-EDGE. Imibuzo yegrafu isebenzisa umsebenzi omtsha we-T-SQL obizwa ngokuba yiMATCH (). Ekubeni lo msebenzi wakhelwe kwi-SQL Server 2017, ingasetyenziselwa kwiinkcukacha zakho ezikhoyo ngaphandle kwesidingo salo naluphi na ukuguqulwa.

Iinzuzo zemodeli yegrafu

Namhlanje, amashishini kunye nabasebenzisi bafuna izicelo ezibamba idatha eninzi nangaphezulu, ngelixa belindele ukusebenza okuphezulu kunye nokuthembeka. Ukumela idatha njengegrafu inikezela ngeendlela ezifanelekileyo zokuphatha ubudlelwane obunzima. Le ndlela yokufundisa isombulula iingxaki ezininzi kwaye inceda ukufumana iziphumo ngaphakathi kwimeko ethile.

Kubonakala ngathi izicelo ezininzi ziya kuxhamla ekusebenziseni ugcino lwedatha lwegrafu kwixesha elizayo.

Ukwenziwa kwedatha: ukusuka kubudlelwane ukuya kwimodeli yegrafu

Umzekelo:

Makhe sijonge umzekelo wesakhiwo sombutho kunye noluhlu lwabaqeshwa: umsebenzi unika ingxelo kumphathi, umphathi unika ingxelo kumphathi ophezulu, njalo njalo. Ngokuxhomekeke kwinkampani ethile, kunokubakho naliphi na inani lamanqanaba kolu luhlu. Kodwa njengoko inani lamanqanaba linyuka, ukubala ubudlelwane kwisiseko sedatha yobudlelwane kuba nzima ngakumbi. Kunzima kakhulu ukuba nomfanekiso-ngqondweni woluhlu lwabaqeshwa, i-hierarchy kwintengiso okanye unxibelelwano kwiintanethi zentlalo. Makhe sibone ukuba i-SQL Graph inokusombulula njani ingxaki yokuphatha amanqanaba ahlukeneyo olawulo.

Kulo mzekelo siya kwenza imodeli yedatha elula. Masenze itafile yabasebenzi I-EMP kunye ne-ID EMPNO kunye nekholamu M.G.R., ebonisa isazisi somphathi womqeshwa (umphathi). Lonke ulwazi malunga noluhlu lwemigangatho lugcinwe kule theyibhile kwaye lunokubuzwa kusetyenziswa iikholomu EMPNO и M.G.R..

Lo mzobo ulandelayo ubonisa imodeli yesakhiwo sombutho esinamanqanaba amane okuzalela ngendlela eqhelekileyo. Abasebenzi ziinkqutyana zegrafu ekwitheyibhile I-EMP. Iqumrhu elithi "umqeshwa" liqhagamshelwe kulo ngokwalo ngoqhagamshelo "submits" (ReportsTo). Ngokwemigaqo yegrafu, ikhonkco ngumphetho (EDGE) odibanisa iindawo (NODE) zabasebenzi.

Masenze itafile eqhelekileyo I-EMP kwaye wongeze amaxabiso apho ngokuhambelana nomzobo ongentla.

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)

Lo mfanekiso ungezantsi ubonisa abasebenzi:

umsebenzi nge EMPNO i-7369 ithobela i-7902;
umsebenzi nge EMPNO I-7902 ithobela i-7566
umsebenzi nge EMPNO I-7566 ithobela i-7839

Ngoku makhe sijonge umboniso wegrafu wedatha efanayo. I-vertex yoMQESHWA ineempawu ezininzi kwaye idityaniswe kuyo ngokwayo ngobudlelwane "abangaphantsi" (EmplReportsTo). EmplReportsTo ligama lobudlelwane.

Itafile yomphetho (EDGE) inokuqulatha iimpawu.

Masenze i-node table ye-EmpNode

Isivakalisi sokwenza i-node silula kakhulu: kwintetho YENZA ISICWANGCISO yongezwa ekupheleni "NJENGOKO 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;

Ngoku masiguqule idatha ukusuka kwitheyibhile eqhelekileyo ukuya kwitafile yegrafu. Okulandelayo BHALA ifaka idatha kwitheyibhile yobudlelwane I-EMP.

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

Kwitheyibhile yee-nodes kwikholamu ekhethekileyo $node_id_* Isichongi sendawo yokusebenzela sigcinwa njenge JSON. Imihlathi eshiyekileyo yale theyibhile iqulathe iimpawu zenodi.

Ukudala imiphetho (EDGE)

Ukudala itheyibhile yomda kufana kakhulu nokudala itafile ye-node, ngaphandle kokuba igama elingundoqo liyasetyenziswa "NJENGOKO".

CREATE TABLE empReportsTo(Deptno int) AS EDGE

Ngoku makhe sichaze unxibelelwano phakathi kwabasebenzi usebenzisa iikholamu EMPNO и M.G.R.. Umzobo wesakhiwo sombutho ubonisa ngokucacileyo indlela yokubhala BHALA.

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

Itheyibhile yomphetho omiselweyo iqulathe iikholamu ezintathu. Ekuqaleni, $edge_id - isazisi somphetho kwifomu ye-JSON. Abanye ababini ($from_id и $to_id) bonisa uqhagamshelwano phakathi kweendawo zokuhlala. Ukongezelela, imiphetho ingaba neepropati ezongezelelweyo. Kwimeko yethu kunjalo Deptno.

Indlela yokuJonga

Kumbono wenkqubo sys.tables Iikholamu ezimbini ezintsha zivele:

yi_edge
yi_node

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

I-SSMS

Izinto ezinxulumene neegrafu zifumaneka kwifolda yeeThebhile zeGrafu. I-icon yetafile ye-node iphawulwe ngechaphaza, kwaye i-icon yetafile yomda iphawulwe ngezangqa ezibini ezidibeneyo (efana neeglasi).

MATCH imbonakalo

Inkcazo QHUBA ithathwe kwiCQL (Cypher Query Language). Le yindlela esebenzayo yokubuza iimpawu zegrafu. I-CQL iqala ngentetho QHUBA.

I-Syntax

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

U mzekelo

Makhe sijonge imizekelo embalwa.

Lo mbuzo ungezantsi ubonisa abasebenzi ababika kubo uSmith kunye nomphathi wakhe.

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'

Lo mbuzo ulandelayo ngowokufumana abasebenzi abakwinqanaba lesibini kunye nabaphathi bakaSmith. Ukuba ususa isivakalisi KUPHI, ke isiphumo siya kubonisa bonke abasebenzi.

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'

Kwaye ekugqibeleni, isicelo sabasebenzi benqanaba lesithathu kunye nabaphathi.

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'

Ngoku masitshintshe indlela ukuze sifumane abaphathi bakaSmith.

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)

isiphelo

I-SQL Server 2017 izenzele njengesisombululo esipheleleyo seshishini kwimisebenzi eyahlukeneyo ye-IT yoshishino. Inguqulelo yokuqala ye-SQL Graph ithembisa kakhulu. Nangona kukho imida, sele kukho umsebenzi owaneleyo wokuphonononga ubunakho beegrafu.

Ukusebenza kweGrafu yeSQL kuhlanganiswe ngokupheleleyo kwi-SQL Engine. Nangona kunjalo, njengoko sele kuchaziwe, i-SQL Server 2017 inezithintelo ezilandelayo:

Akukho nkxaso yepolymorphism.

Kuxhaswe uqhagamshelo lwendlela enye kuphela.
Imiphetho ayinakuhlaziya i-$from_id kunye ne-$to_id columns nge-UPDATE.
Ukuvalwa okuguquguqukayo akuxhaswanga, kodwa kunokufunyanwa kusetyenziswa i-CTE.
Inkxaso ye-In-Memory OLTP izinto zilinganiselwe.
Iitheyibhile zexeshana (Itheyibhile yeSistim-Eguqulelwe kwiTemporal), iitheyibhile zexeshana zasekhaya nezehlabathi azixhaswa.
Iintlobo zetheyibhile kunye neziguquguquko zetafile azikwazi ukubhengezwa njenge-NODE okanye i-EDGE.
Imibuzo ye-Cross-database ayixhaswanga.
Akukho ndlela ngqo okanye iwizadi yokuguqula iitafile eziqhelekileyo kwiitafile zegrafu.
Akukho GUI yokubonisa iigrafu, kodwa ungasebenzisa iPower BI.