🥇Mau oyamba a Graph Databases SQL Server 2017

Asanayambe maphunziro "MS SQL Server Developer" Takukonzerani kumasulira kwina kothandiza.

Ma graph database ndiukadaulo wofunikira kwa akatswiri a database. Ndimayesetsa kukhala ndi zatsopano komanso matekinoloje atsopano m'derali ndipo, nditagwira ntchito ndi maubwenzi ndi NoSQL databases, ndikuwona udindo wa ma graph databases kukhala wofunikira kwambiri. Mukamagwira ntchito ndi zovuta zotsogola, osati zolemba zakale zokha zomwe sizigwira ntchito, komanso NoSQL. Nthawi zambiri, kuchuluka kwa milingo yolumikizirana komanso kukula kwa database kumawonjezeka, magwiridwe antchito amachepa. Ndipo pamene maubwenzi akukhala ovuta, chiwerengero cha JOIN chimawonjezekanso.

Zachidziwikire, mtundu waubale uli ndi mayankho ogwirira ntchito ndi ma hierarchies (mwachitsanzo, kugwiritsa ntchito ma CTE obwereza), koma awa akadali njira zogwirira ntchito. Nthawi yomweyo, magwiridwe antchito a SQL Server graph databases amakulolani kuti muzitha kuthana ndi magawo angapo aulamuliro. Zonse zamtundu wa data ndi mafunso zimasavuta, chifukwa chake magwiridwe ake amawonjezeka. Kuchuluka kwa code kumachepetsedwa kwambiri.

Ma graph database ndi chilankhulo chofotokozera choyimira machitidwe ovuta. Ukadaulo uwu wagwiritsidwa kale ntchito kwambiri mumakampani a IT m'malo monga malo ochezera a pa Intaneti, machitidwe odana ndi chinyengo, kusanthula kwa maukonde a IT, malingaliro ochezera, zopangira ndi zomwe zili.

Magwiridwe a database ya ma graph mu SQL Server ndi oyenera zochitika zomwe deta imalumikizana kwambiri ndipo imakhala ndi maubale odziwika bwino.

Graph Data Model

Grafu ndi gulu la vertices (mfundo) ndi m'mphepete (maubwenzi). Ma Vertices amayimira mabungwe, ndipo m'mphepete mwake mumayimira maubwenzi omwe mawonekedwe ake amatha kukhala ndi chidziwitso.

Ma graph database amawonetsa mabungwe ngati ma graph monga amafotokozera mu chiphunzitso cha graph. Mapangidwe a data ndi ma vertices ndi m'mphepete. Makhalidwe ndi mawonekedwe a vertices ndi m'mphepete. Mgwirizano ndi mgwirizano wa vertices.

Mosiyana ndi mitundu ina ya data, nkhokwe zama graph zimayika patsogolo ubale pakati pa mabungwe. Chifukwa chake, palibe chifukwa chowerengera maubwenzi pogwiritsa ntchito makiyi akunja kapena njira zina zilizonse. N'zotheka kupanga zitsanzo zovuta za deta pogwiritsa ntchito vertex ndi m'mphepete mwake.

M'dziko lamakono, maubwenzi achitsanzo amafunikira njira zovuta kwambiri. Kuti muwonetse maubwenzi, SQL Server 2017 imapereka luso la database la graph. Ma vertices ndi m'mphepete mwa graph amaimiridwa ngati mitundu yatsopano ya matebulo: NODE ndi EDGE. Mafunso azithunzi amagwiritsa ntchito ntchito yatsopano ya T-SQL yotchedwa MATCH(). Popeza magwiridwe antchitowa amapangidwa mu SQL Server 2017, atha kugwiritsidwa ntchito pazosunga zomwe muli nazo popanda kufunikira kwa kutembenuka kulikonse.

Ubwino wa chitsanzo cha graph

Masiku ano, mabizinesi ndi ogwiritsa ntchito amafuna mapulogalamu omwe amagwiritsa ntchito deta yochulukirapo, pomwe amayembekezera magwiridwe antchito komanso kudalirika. Kuyimira deta ngati graph kumapereka njira yabwino yothanirana ndi maubwenzi ovuta. Njira imeneyi imathetsa mavuto ambiri ndipo imathandizira kupeza zotsatira mkati mwa nkhani inayake.

Zikuwoneka kuti mapulogalamu ambiri adzapindula pogwiritsa ntchito ma graph database m'tsogolomu.

Kujambula kwa data: kuchokera ku chiyanjano kupita ku chitsanzo cha graph

Chitsanzo:

Tiyeni tiwone chitsanzo cha dongosolo la bungwe lomwe lili ndi utsogoleri wa antchito: wogwira ntchito amachitira malipoti kwa manijala, manijala amapereka malipoti kwa manijala wamkulu, ndi zina zotero. Kutengera ndi kampani inayake, pakhoza kukhala magawo angapo muulamulirowu. Koma kuchuluka kwa milingo kumachulukirachulukira, kuwerengera maubwenzi mu database yaubale kumakhala kovuta kwambiri. Ndizovuta kulingalira zaulamuliro wa ogwira ntchito, utsogoleri pazamalonda kapena kulumikizana ndi malo ochezera a pa Intaneti. Tiyeni tiwone momwe SQL Graph ingathetsere vuto la kusamalira magawo osiyanasiyana a utsogoleri.

Kwa chitsanzo ichi tidzapanga chitsanzo chosavuta cha deta. Tiyeni tipange tebulo la antchito EMP ndi ID EMPNO ndi column M.G.R., kusonyeza chizindikiritso cha woyang'anira wantchito (woyang'anira). Zonse zokhudza utsogoleri wasungidwa mu tebulo ili ndipo mukhoza kufunsidwa pogwiritsa ntchito mizati EMPNO и M.G.R..

Chithunzi chotsatirachi chikuwonetsa chitsanzo cha bungwe lomwe lili ndi magawo anayi a zisa mu mawonekedwe odziwika bwino. Ogwira ntchito ndi ma vertices a graph kuchokera patebulo EMP. Bungwe la "wogwira ntchito" limalumikizidwa kwa lokha ndi kulumikizana "submits" (ReportsTo). M'mawu a graph, ulalo ndi m'mphepete (EDGE) yomwe imalumikiza node (NODE) ya antchito.

Tiyeni tipange tebulo lokhazikika EMP ndikuwonjezera zomwe zili pamenepo molingana ndi chithunzi pamwambapa.

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)

Chithunzi chili m'munsichi chikuwonetsa antchito:

wogwira ntchito ndi EMPNO 7369 amamvera 7902;
wogwira ntchito ndi EMPNO 7902 imamvera 7566
wogwira ntchito ndi EMPNO 7566 imamvera 7839

Tsopano tiyeni tiwone chithunzithunzi cha ma graph a data yomweyi. The EMPLOYEE vertex ili ndi zikhumbo zingapo ndipo imalumikizidwa yokha ndi ubale wa "subordinates" (EmplReportsTo). EmplReportsTo ndi dzina laubwenzi.

Gome la m'mphepete (EDGE) litha kukhalanso ndi mawonekedwe.

Tiyeni tipange node tebulo EmpNode

Mawu opangira node ndi osavuta: ku mawu Pangani MITU amawonjezeredwa mpaka kumapeto "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;

Tsopano tiyeni titembenuzire deta kuchokera patebulo lokhazikika kukhala tebulo la graph. Ena LANDANI imayika data kuchokera pa tebulo logwirizana EMP.

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

Mu tebulo la mfundo mu ndime yapadera $node_id_* chizindikiritso cha node chimasungidwa ngati JSON. Mizati yotsala ya tebulo ili ndi mawonekedwe a nodi.

Kupanga m'mphepete (EDGE)

Kupanga tebulo la m'mphepete kumakhala kofanana ndi kupanga tebulo la node, kupatula kuti mawu ofunika amagwiritsidwa ntchito "AS EDGE".

CREATE TABLE empReportsTo(Deptno int) AS EDGE

Tsopano tiyeni tifotokoze kugwirizana pakati pa antchito pogwiritsa ntchito mizati EMPNO и M.G.R.. Chithunzi chojambula cha bungwe chikuwonetsa bwino momwe angalembe LANDANI.

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

Gome la m'mphepete mwachisawawa lili ndi mizati itatu. Choyamba, $edge_id - chizindikiritso cha m'mphepete mwa mawonekedwe a JSON. Ena awiri ($from_id и $to_id) amaimira mgwirizano pakati pa mfundo. Kuonjezera apo, m'mphepete mwake mukhoza kukhala ndi katundu wowonjezera. Kwa ife zili choncho Deptno.

Mawonedwe a System

Mu mawonekedwe a system sys.tables mizati iwiri yatsopano yawoneka:

ndi_m'mphepete
ndi_node

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

Zamgululi

Zinthu zokhudzana ndi ma graph zili mufoda ya Graph Tables. Chizindikiro cha tebulo la node chimakhala ndi kadontho, ndipo chithunzi cham'mphepete mwa tebulo chimakhala ndi mabwalo awiri olumikizana (monga magalasi).

MATCH mawu

Kulongosola GANIZANI zotengedwa kuchokera ku CQL (Cypher Query Language). Iyi ndi njira yabwino yofufuzira mawonekedwe a graph. CQL imayamba ndi mawu GANIZANI.

malembedwe

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

zitsanzo

Tiyeni tione zitsanzo zingapo.

Funso ili pansipa likuwonetsa antchito omwe Smith ndi manejala wake amawafotokozera.

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'

Funso lotsatirali ndikupeza antchito achiwiri ndi mamanenjala a Smith. Ngati muchotsa chiganizocho KUMENE, ndiye zotsatira zake zidzawonetsa antchito onse.

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'

Ndipo potsiriza, pempho la antchito achitatu ndi oyang'anira.

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'

Tsopano tiyeni tisinthe njira kuti titenge mabwana a 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)

Pomaliza

SQL Server 2017 yadzikhazikitsa yokha ngati yankho lathunthu labizinesi pantchito zosiyanasiyana zamabizinesi a IT. Mtundu woyamba wa SQL Graph ndiwothandiza kwambiri. Ngakhale pali zolephera zina, pali kale magwiridwe antchito okwanira kuti mufufuze kuthekera kwa ma graph.

SQL Graph magwiridwe antchito amaphatikizidwa kwathunthu mu SQL Engine. Komabe, monga tanenera kale, SQL Server 2017 ili ndi malire awa:

Palibe chithandizo cha polymorphism.

Malumikizidwe a unidirectional okha ndi omwe amathandizidwa.
M'mphepete sangathe kusintha $from_id ndi $to_id mizati kudzera UPDATE.
Kutseka kwapakati sikuthandizidwa, koma kumatha kupezeka pogwiritsa ntchito CTE.
Thandizo la zinthu za In-Memory OLTP ndizochepa.
Matebulo osakhalitsa (System-Versioned Temporal Table), matebulo osakhalitsa am'deralo ndi apadziko lonse lapansi samathandizidwa.
Mitundu yamatebulo ndi zosintha zamatebulo sizingatchulidwe ngati NODE kapena EDGE.
Mafunso amitundu yonse samathandizidwa.
Palibe njira yachindunji kapena wizard yosinthira matebulo okhazikika kukhala ma graph.
Palibe GUI yowonetsera ma graph, koma mutha kugwiritsa ntchito Power BI.

Werengani zambiri:

Momwe SQL Server imagwiritsira ntchito zosefera za bitmap

Source: www.habr.com

Chidziwitso cha Graph Databases SQL Server 2017