🥇Isingeniso Sedathabheyisi Yegrafu ye-SQL Server 2017

Ngaphambi kokuqala kwesifundo "MS SQL Server Developer" Sikulungiselele okunye ukuhumusha okuwusizo.

Imininingo egciniwe yamagrafu iwubuchwepheshe obubalulekile bochwepheshe besizindalwazi. Ngizama ukulandela izinto ezintsha kanye nobuchwepheshe obusha kule ndawo futhi ngemva kokusebenza nemininingwane yolwazi ehlobene ne-NoSQL, ngibona ukuthi indima yolwazi lwamagrafu iya ngokuya iba ngaphezulu. Lapho usebenza ngedatha ye-hierarchical eyinkimbinkimbi, hhayi kuphela imininingwane yolwazi yendabuko, kodwa futhi i-NoSQL ayisebenzi. Ngokuvamile, ngokunyuka kwenani lamazinga esixhumanisi kanye nobukhulu besizindalwazi, kuba nokuncipha kokusebenza. Futhi njengoba ubudlelwano buba nzima kakhulu, inani LOKUJOYINA nalo liyakhula.

Yiqiniso, kumodeli yobudlelwane kunezixazululo zokusebenza ngezigaba (isibonelo, ukusebenzisa ama-CTE aphindaphindiwe), kodwa lezi kuseyizindlela zokulungisa. Ngesikhathi esifanayo, ukusebenza kwezizindalwazi zegrafu ye-SQL Server kwenza kube lula ukuphatha amaleveli amaningi okulandelana. Kokubili imodeli yedatha nemibuzo yenziwe lula, ngakho-ke ukusebenza kahle kwayo kuyanda. Yehlisa ngokuphawulekayo inani lekhodi.

Imininingo egciniwe yamagrafu iwulimi oluchazayo lokumelela amasistimu ayinkimbinkimbi. Lobu buchwepheshe sebuvele busetshenziswa kabanzi embonini ye-IT ezindaweni ezifana nezinkundla zokuxhumana, izinhlelo zokulwa nokukhwabanisa, ukuhlaziya inethiwekhi ye-IT, izincomo zomphakathi, izincomo zomkhiqizo kanye nokuqukethwe.

Ukusebenza kwesizindalwazi segrafu ku-SQL Server kulungele izimo lapho idatha ixhumeke kakhulu futhi inobudlelwano obuchazwe kahle.

Imodeli yedatha yegrafu

Igrafu iqoqo lama-vertices (ama-node, nodi) kanye nemiphetho (ubudlelwano, umphetho). Ama-Vertices amelela izinhlangano, kanti imiphetho imelela izixhumanisi izimfanelo zazo ezingaqukatha ulwazi.

Amamodeli esizindalwazi segrafu amabhizinisi njengegrafu njengoba kuchazwe kuthiyori yegrafu. Izakhiwo zedatha zingama mpo namaphethelo. Izimfanelo ziyizakhiwo zama-vertices namaphethelo. Ukuxhumana kuwukuxhunywa kwama-vertices.

Ngokungafani namanye amamodeli edatha, isizindalwazi segrafu sibeka phambili ubudlelwano phakathi kwamabhizinisi. Ngakho-ke, asikho isidingo sokubala ubudlelwano usebenzisa okhiye bangaphandle noma ngenye indlela. Kungenzeka ukudala amamodeli wedatha ayinkimbinkimbi usebenzisa kuphela i-vertex kanye ne-edge abstractions.

Ezweni lanamuhla, ubudlelwano bokumodela budinga amasu ayinkimbinkimbi kakhulu. Ukumodela ubudlelwano, i-SQL Server 2017 inikezela ngamakhono esizindalwazi segrafu. Ama-vertices namaphethelo egrafu amelelwa njengezinhlobo ezintsha zamathebula: I-NODE kanye ne-EDGE. Imibuzo yegrafu isebenzisa umsebenzi omusha we-T-SQL obizwa ngokuthi MATCH(). Njengoba lokhu kusebenza kwakhelwe ku-SQL Server 2017, kungasetshenziswa kuzinqolobane zakho ezikhona ngaphandle kwesidingo sokuguqulwa kwanoma yikuphi ukugcinwa kwesizindalwazi.

Izinzuzo Zemodeli Yegrafu

Namuhla, amabhizinisi nabasebenzisi bafuna izinhlelo zokusebenza ezisebenza ngedatha eyengeziwe, kuyilapho belindele ukusebenza okuphezulu nokuthembeka. Ukumelwa kwegrafu yedatha kunikeza izindlela ezilula zokuphatha ubudlelwano obuyinkimbinkimbi. Le ndlela ixazulula izinkinga eziningi futhi isiza ukuthola imiphumela ngaphakathi komongo othile.

Kubukeka sengathi izinhlelo zokusebenza eziningi zizozuza kusizindalwazi segrafu ngokuzayo.

Ukumodela Kwedatha: Kusuka Kwezobudlelwane kuye Ekufaniseni Igrafu

Isibonelo:

Ake sibheke isibonelo sesakhiwo senhlangano esinohlu lwabasebenzi: isisebenzi sibika kumphathi, umphathi ubika kumphathi omkhulu, njalo njalo. Kuye ngenkampani ethile, kungaba nenani lamazinga kulesi sigaba. Kodwa njengoba inani lamazinga likhula, ukubala ubudlelwano kusizindalwazi esihlobene kuba nzima nakakhulu. Kunzima kakhulu ukucabanga ngezigaba zabasebenzi, isigaba sezokukhangisa noma ukuxhumana kwezokuxhumana. Ake sibone ukuthi i-SQL Graph ingayixazulula kanjani inkinga yokuphatha amazinga ahlukene obuholi.

Kulesi sibonelo, masenze imodeli yedatha elula. Dala itafula labasebenzi I-EMP nge id I-EMPNO kanye nekholomu M.G.R.A ekhomba umazisi wenhloko (umphathi) womsebenzi. Lonke ulwazi olumayelana nezikhundla lugcinwe kuleli thebula futhi lungabuzwa kusetshenziswa amakholomu I-EMPNO и M.G.R..

Umdwebo olandelayo futhi ubonisa imodeli yeshadi le-org efanayo namazinga amane okuzalela ngendlela ejwayeleke kakhudlwana. Izisebenzi zingama mpo egrafu asuka etafuleni I-EMP. Ibhizinisi "isisebenzi" lixhunywe kulo ngokwalo ngobudlelwane "abathumelayo" (ReportsTo). Ngokwegrafu, isixhumanisi siwunqenqema (EDGE) oluxhuma ama-node (NODE) abasebenzi.

Ake sakhe itafula elivamile I-EMP bese wengeza amanani lapho ngokomdwebo ongenhla.

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)

Isithombe esingezansi sibonisa abasebenzi:

isisebenzi nge I-EMPNO 7369 ingaphansi kuka-7902;
isisebenzi nge I-EMPNO 7902 ngaphansi kuka-7566
isisebenzi nge I-EMPNO 7566 ngaphansi kuka-7839

Manje ake sibheke ukumelwa kwedatha efanayo ngendlela yegrafu. Inodi YOMSEBENZI inezimfanelo ezimbalwa futhi ixhunywe kuyo ngokwayo ngobudlelwano "be-submits" (EmplReportsTo). I-EmplReportsTo igama lobudlelwano.

Ithebula lomphetho (EDGE) lingase libe nezibaluli.

Dala ithebula le-node EmpNode

I-syntax yokwakha i-node ilula kakhulu: kunkulumo DALA ITHEBULA kwengezwe ekugcineni NJENGOBA 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;

Manje ake siguqule idatha isuke kuthebula elivamile iye kwigrafu. Olandelayo BHALA ifaka idatha kusuka kuthebula lobudlelwano I-EMP.

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

Kuthebula le-node kukholamu ekhethekile $node_id_* i-ID yomsingathi igcinwa njenge-JSON. Amakholomu asele aleli thebula aqukethe izibaluli zenodi.

Dala imiphetho (EDGE)

Ukudala ithebula elinqenqemeni kufana kakhulu nokwakha ithebula le-node, ngaphandle kokuthi igama elingukhiye NJENGOMPHAKATHI.

CREATE TABLE empReportsTo(Deptno int) AS EDGE

Manje ake sichaze ubudlelwano phakathi kwabasebenzi sisebenzisa amakholomu I-EMPNO и M.G.R.. Ishadi lenhlangano libonisa ngokusobala ukuthi kubhalwa kanjani 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);

Ithebula elisemaphethelweni linamakholomu amathathu ngokuzenzakalelayo. Okokuqala, $edge_id — isihlonzi sonqenqema ngendlela ye-JSON. Abanye ababili ($from_id и $to_id) kumelela ubudlelwano phakathi kwamanodi. Ngaphezu kwalokho, imiphetho ingaba nezakhiwo ezengeziwe. Esimweni sethu, lokhu I-Deptno.

Ukubuka Kwesistimu

Ekubukeni kwesistimu sys.tables Kunamakholomu amasha amabili:

is_edge
iyi-node

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

I-SMS

Izinto ezihlobene namagrafu zitholakala kufolda ye-Graph Tables. Isithonjana sethebula le-node simakwe ngechashazi, futhi isithonjana setafula elisemaphethelweni simakwe ngemibuthano emibili exhunyiwe (ebukeka kancane njengezibuko).

OKUFANELEKILEYO

Isimo MATCH ithathwe ku-CQL (Cypher Query Language). Lena indlela ephumelelayo yokubuza izici zegrafu. I-CQL iqala ngesisho MATCH.

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

Izibonelo

Ake sibheke izibonelo ezimbalwa.

Umbuzo ongezansi ubonisa abasebenzi uSmith nomphathi wakhe ababika kubo.

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'

Umbuzo olandelayo owokutholela abasebenzi kanye nabaphathi bezinga lesibili likaSmith. Uma ususa okunikezwayo LAPHO, bese bonke abasebenzi bazovezwa ngenxa yalokho.

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'

Futhi ekugcineni, isicelo sabasebenzi nabaphathi bezinga lesithathu.

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'

Manje ake sishintshe indlela ukuze sithole 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)

isiphetho

I-SQL Server 2017 izimise njengesixazululo sebhizinisi esiphelele sezidingo ze-IT zebhizinisi ezahlukahlukene. Inguqulo yokuqala ye-SQL Graph iyathembisa kakhulu. Naphezu kwemikhawulo ethile, sekuvele kukhona ukusebenza okwanele ukuhlola amathuba amagrafu.

Ukusebenza kwe-SQL Graph kuhlanganiswe ngokugcwele ku-SQL Engine. Kodwa-ke, njengoba sekushiwo kakade, i-SQL Server 2017 inemikhawulo elandelayo:

Akukho ukusekelwa kwe-polymorphism.

Izixhumanisi ezingaqondile kuphela ezisekelwayo.
Ama-Edges awakwazi ukubuyekeza amakholomu awo angu-$from_id kanye ne-$to_id nge-UPDATE.
Ukuvalwa kwesikhashana akusekelwe, kodwa kungatholwa kusetshenziswa ama-CTE.
Ukusekelwa okunomkhawulo wezinto ze-In-Memory OLTP.
Amathebula esikhashana (Ithebula Lesikhashana Le-System-Versioned), amathebula esikhashana endawo nawembulunga yonke awasekelwe.
Izinhlobo zethebula neziguquguqukayo zethebula azikwazi ukumenyezelwa njenge-NODE noma i-EDGE.
Imibuzo ye-Cross-database ayisekelwe.
Ayikho indlela eqondile noma uhlobo oluthile lwewizadi (iwizadi) lokuguqula amatafula avamile abe amatafula egrafu.
Ayikho i-GUI yokubonisa amagrafu, kodwa i-Power BI ingasetshenziswa.

Funda kabanzi:

I-SQL Server isebenzisa kanjani izihlungi ze-bitmap

Source: www.habr.com

Isingeniso ku-SQL Server 2017 Graph Databases