🥇Kenyelletso ho SQL Server 2017 Graph Databases

Pele thupelo e qala "MS SQL Server Developer" Re u lokiselitse phetolelo e 'ngoe e molemo.

Li-database tsa kerafo ke theknoloji ea bohlokoa bakeng sa litsebi tsa database. Ke leka ho latela mekhoa e mecha le theknoloji e ncha sebakeng sena 'me ka mor'a ho sebetsa le li-database tsa likamano le tsa NoSQL, ke bona hore karolo ea li-database tsa graph e ntse e eketseha le ho feta. Ha o sebetsa ka lintlha tse rarahaneng tsa maemo a holimo, eseng feela li-database tsa setso, empa le NoSQL ha li sebetse. Hangata, ka keketseho ea palo ea litekanyetso tsa li-link le boholo ba database, ho na le ho fokotseha ha tshebetso. 'Me ha likamano li ntse li rarahana le ho feta, palo ea JOIN le eona ea eketseha.

Ha e le hantle, ka mohlala oa likamano ho na le tharollo ea ho sebetsa le lihlopha tsa maemo (mohlala, ho sebelisa li-CTE tse phetoang), empa tsena e ntse e le li-workaround. Ka nako e ts'oanang, ts'ebetso ea li-database tsa graph tsa SQL Server e etsa hore ho be bonolo ho sebetsana le maemo a mangata a bolaoli. Ka bobeli mofuta oa data le lipotso li nolofalitsoe, ka hona ts'ebetso ea tsona ea eketseha. E fokotsa haholo palo ea khoutu.

Li-database tsa kerafo ke puo e hlalosang maikutlo bakeng sa ho emela litsamaiso tse rarahaneng. Theknoloji ena e se e sebelisoa haholo indastering ea IT libakeng tse kang marang-rang a sechaba, litsamaiso tse khahlanong le bomenemene, tlhahlobo ea marang-rang ea IT, likhothaletso tsa sechaba, lihlahisoa le likhothaletso tsa litaba.

Ts'ebetso ea database ea graph ho SQL Server e loketse maemo ao data e hokahaneng haholo 'me e na le likamano tse hlalositsoeng hantle.

Mohlala oa data oa kerafo

Kerafo ke sehlopha sa li-vertices (li-node, node) le mathōko (likamano, moeli). Li-Vertices li emela mekhatlo, 'me lithako li emela lihokelo tseo litšoaneleho tsa tsona li ka bang le tlhahisoleseling.

Mekhatlo ea meralo ea bobolokelo ba kerafo e le kerafo joalo ka ha e hlalositsoe ka khopolo ea kerafo. Mehaho ea data ke li-vertices le mathōko. Litšoaneleho ke thepa ea li-vertices le lithako. Khokahano ke khokahano ea li-vertices.

Ho fapana le mefuta e meng ea data, li-database tsa graph li etelletsa likamano lipakeng tsa mekhatlo pele. Ka hona, ha ho hlokahale ho bala likamano ho sebelisa linotlolo tsa kantle ho naha kapa ka tsela e 'ngoe. Hoa khoneha ho theha mefuta e rarahaneng ea data ho sebelisa li-vertex feela le li-abstractions tse ka thōko.

Lefatšeng la kajeno, likamano tsa mohlala li hloka mekhoa e rarahaneng le ho feta. Ho etsa mohlala oa likamano, SQL Server 2017 e fana ka bokhoni ba database ea graph. Lithako le mathōko a graph li emeloa e le mefuta e mecha ea litafole: NODE le EDGE. Lipotso tsa kerafo li sebelisa ts'ebetso e ncha ea T-SQL e bitsoang MATCH(). Kaha ts'ebetso ena e hahiloe ho SQL Server 2017, e ka sebelisoa lits'ebetsong tsa hau tse teng ntle le tlhoko ea phetoho efe kapa efe ea database.

Melemo ea Mohlala oa Kerafo

Matsatsing ana, likhoebo le basebelisi ba batla lits'ebetso tse sebetsang ka data e ngata, ha ba ntse ba lebelletse ts'ebetso e phahameng le ts'epahalo. Kemiso ea graph ea data e fana ka mekhoa e bonolo ea ho sebetsana le likamano tse rarahaneng. Mokhoa ona o rarolla mathata a mangata mme o thusa ho fumana sephetho ka har'a moelelo o itseng.

Ho bonahala eka lits'ebetso tse ngata li tla rua molemo ho li-database tsa graph nakong e tlang.

Mokhoa oa ho Etsa Boitsebiso: Ho tloha ho Relational ho ea ho Mohlala oa Kerafo

Mohlala:

Ha re shebeng mohlala oa sebopeho sa mokhatlo o nang le sehlopha sa basebetsi: mosebeletsi o tlaleha ho mookameli, mookameli o tlaleha ho mookameli e moholo, joalo-joalo. Ho ipapisitsoe le k'hamphani e ikhethileng, ho ka ba le maemo a mangata sehlopheng sena sa maemo. Empa ha palo ea maemo e ntse e eketseha, ho bala likamano sebakeng sa polokelo ea likamano ho ba thata le ho feta. Ho thata haholo ho nahana ka maemo a phahameng a basebetsi, maemo a phahameng a ho bapatsa kapa likhokahano tsa litaba tsa sechaba. Ha re boneng hore na SQL Graph e ka rarolla bothata joang ba ho sebetsana le maemo a fapaneng a bolaoli.

Bakeng sa mohlala ona, ha re etseng mohlala o bonolo oa data. Etsa tafole ea basebetsi PMS ka id EMPNO le kholomo M.G.R.A e supang ID ea hlooho (mookameli) oa mosebetsi. Lintlha tsohle tse mabapi le maemo a phahameng li bolokiloe tafoleng ena 'me li ka botsoa ho sebelisoa likholomo EMPNO и M.G.R..

Sets'oants'o se latelang se boetse se bonts'a mofuta o tšoanang oa chate ea org o nang le maemo a mane a ho haha sehlaha ka mokhoa o tloaelehileng. Basebeletsi ke li-vertices tsa kerafo e tsoang tafoleng PMS. Setsi "mosebetsi" se hokahane le sona ka kamano ea "submits" (ReportsTo). Ka mantsoe a graph, sehokelo ke moeli (EDGE) o kopanyang li-node (NODE) tsa basebetsi.

Ha re theheng tafole e tloaelehileng PMS 'me u kenye boleng moo ho latela setšoantšo se kaholimo.

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)

Setšoantšo se ka tlase se bontša basebetsi:

mosebeletsi ka EMPNO 7369 e ka tlase ho 7902;
mosebeletsi ka EMPNO 7902 tlasa 7566
mosebeletsi ka EMPNO 7566 tlasa 7839

Joale a re shebeng boemeli ba data e tšoanang ka mokhoa oa kerafo. Node ea MOSEBETSI e na le litšobotsi tse 'maloa' me e hokahane le eona ka kamano ea "submits" (EmplReportsTo). EmplReportsTo ke lebitso la kamano.

Tafole ea moeli (EDGE) le eona e ka ba le litšobotsi.

Theha tafole ea node EmpNode

Syntax ea ho theha node e bonolo haholo: ho polelo QALANG LETLAPA ekeletsoa qetellong JOALOKA NOKO.

CREATE TABLE dbo.EmpNode(
ID Int Identity(1,1),
EMPNO NUMERIC(4) NOT NULL,
ENAME VARCHAR(10),
MGR NUMERIC(4),
DNO INT
) AS NODE;

Joale ha re fetoleng data ho tloha tafoleng e tloaelehileng ho ea ho kerafo. E 'ngoe SELLOANE e kenya data ho tsoa tafoleng ea likamano PMS.

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

Ka tafole ea nodes ka kholomo e khethehileng $node_id_* ID ea moamoheli e bolokiloe joalo ka JSON. Litšiea tse setseng tsa tafole ena li na le litšobotsi tsa node.

Create Edges (EDGE)

Ho theha tafole e ka thōko ho tšoana haholo le ho theha tafole ea node, ntle le hore lentsoe la sehlooho JOALOKA MATLHE.

CREATE TABLE empReportsTo(Deptno int) AS EDGE

Joale ha re hlaloseng likamano lipakeng tsa basebetsi ba sebelisang likholomo EMPNO и M.G.R.. Chate ea mokhatlo e bontša ka ho hlaka mokhoa oa ho ngola SELLOANE.

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

Tafole ea moeli e na le litšiea tse tharo ka ho sa feleng. Pele, $edge_id - sekhetho sa moeli ka mokhoa oa JSON. Tse ling tse peli ($from_id и $to_id) emela kamano e teng pakeng tsa nodes. Ho phaella moo, lithako li ka ba le thepa e eketsehileng. Tabeng ea rona, sena Deptno.

Maikutlo a Sistimi

Ka pono ea tsamaiso sys.tables Ho na le litšiea tse peli tse ncha:

e_e bohale
ke_node

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

ssms

Lintho tse amanang le li-graph li fumaneha ka har'a foldara ea Graph Tables. Letšoao la tafole ea node le tšoailoe ka letheba, 'me lets'oao la tafole le tšoauoa ka li-circles tse peli tse hokahaneng (tse shebahalang joalo ka likhalase).

MATCH polelo

Tlhaloso TŠOANELO e nkiloe ho CQL (Cypher Query Language). Ena ke mokhoa o sebetsang oa ho botsisisa litšobotsi tsa kerafo. CQL e qala ka polelo TŠOANELO.

hokahanngoang

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

mehlala

A re hlahlobeng mehlala e seng mekae.

Potso e ka tlase e bonts'a basebetsi bao Smith le mookameli oa hae ba tlalehang ho bona.

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'

Potso e latelang ke ea ho fumana basebetsi le batsamaisi ba boemo ba bobeli ba Smith. Haeba u tlosa tlhahiso HOBANE'NG, joale basebetsi bohle ba tla bontšoa ka lebaka leo.

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'

'Me qetellong, kopo ea basebetsi le batsamaisi ba boemo ba boraro.

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'

Joale ha re fetoleng tsela ho fumana baokameli ba 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)

fihlela qeto e

SQL Server 2017 e itlhomme e le tharollo e felletseng ea khoebo bakeng sa litlhoko tse fapaneng tsa IT tsa khoebo. Mofuta oa pele oa SQL Graph o ts'episa haholo. Leha ho na le mefokolo e itseng, ho se ho ntse ho na le ts'ebetso e lekaneng ea ho hlahloba menyetla ea li-graph.

Ts'ebetso ea SQL Graph e kopantsoe ka botlalo ho SQL Engine. Leha ho le joalo, joalo ka ha ho se ho boletsoe, SQL Server 2017 e na le meeli e latelang:

Ha ho na tšehetso bakeng sa polymorphism.

Ke lihokelo tsa unidirectional feela tse tšehetsoang.
Meeli ha e khone ho ntlafatsa likholomo tsa bona tsa $from_id le $to_id ka UPDATE.
Ho koaloa ha transitive ha ho tšehetsoe, empa ho ka fumanoa ho sebelisoa CTEs.
Tšehetso e fokolang bakeng sa lintho tse In-Memory OLTP.
Litafole tsa nakoana (System-Versioned Temporal Table), litafole tsa nakoana tsa lehae le tsa lefatše ha li tšehetsoe.
Mefuta ea litafole le mefuta ea litafole e ke ke ea phatlalatsoa e le NODE kapa EDGE.
Lipotso tse fapaneng tsa database ha li tšehetsoe.
Ha ho na tsela e tobileng kapa mofuta o itseng oa wizate (wizate) ho fetolela litafole tse tloaelehileng ho litafole tsa graph.
Ha ho na GUI ea ho bonts'a li-graph, empa Power BI e ka sebelisoa.

Bala haholoanyane:

Kamoo SQL Server e sebelisang li-filters tsa bitmap kateng

Source: www.habr.com

Kenyelletso ho SQL Server 2017 Graph Databases