🥇Introduction to Graph Databases SQL Server 2017

Ua ntej pib ntawm chav kawm "MS SQL Server Developer" Peb tau npaj lwm qhov kev txhais lus tseem ceeb rau koj.

Graph databases yog cov cuab yeej tseem ceeb rau cov kws tshaj lij database. Kuv sim ua kom muaj kev tsim kho tshiab thiab cov thev naus laus zis tshiab hauv cheeb tsam no thiab, tom qab ua haujlwm nrog kev sib raug zoo thiab NoSQL databases, kuv pom lub luag haujlwm ntawm cov duab databases ua qhov tseem ceeb. Thaum ua haujlwm nrog cov ntaub ntawv hierarchical complex, tsis tsuas yog cov ntaub ntawv ib txwm siv tsis tau, tab sis kuj NoSQL. Feem ntau, raws li tus naj npawb ntawm cov qib kev sib txuas thiab qhov loj ntawm cov ntaub ntawv nce, kev ua haujlwm qis. Thiab raws li kev sib raug zoo dhau los ua nyuaj, tus lej ntawm JOINs kuj nce ntxiv.

Tau kawg, tus qauv kev sib raug zoo muaj cov kev daws teeb meem rau kev ua haujlwm nrog hierarchies (piv txwv li, siv CTEs recursive), tab sis cov no tseem ua haujlwm. Nyob rau tib lub sijhawm, kev ua haujlwm ntawm SQL Server graph databases tso cai rau koj kom yooj yim lis ntau theem ntawm hierarchy. Ob qho tib si cov qauv ntaub ntawv thiab cov lus nug yog yooj yim, thiab yog li ntawd lawv cov efficiency nce. Tus lej ntawm cov lej raug txo qis heev.

Graph databases yog ib hom lus qhia rau sawv cev rau cov txheej txheem nyuaj. Cov thev naus laus zis no twb tau siv dav hauv kev lag luam IT hauv thaj chaw xws li kev sib tham, kev tiv thaiv kev dag ntxias, IT network tsom xam, kev pom zoo hauv zej zog, cov khoom thiab cov ntsiab lus pom zoo.

Lub graph database functionality nyob rau hauv SQL Server yog haum rau scenarios qhov twg cov ntaub ntawv yog heev interconnected thiab muaj zoo-txhais kev sib raug zoo.

Graph cov ntaub ntawv qauv

Daim duab yog ib txheej ntawm vertices (nodes) thiab ntug (kev sib raug zoo, ntug). Vertices sawv cev rau cov koom haum, thiab cov npoo sawv cev rau kev sib raug zoo uas nws cov cwj pwm yuav muaj cov ntaub ntawv.

Ib daim duab database qauv cov khoom ua ib daim duab raws li tau hais tseg hauv graph txoj kev xav. Cov qauv ntaub ntawv yog vertices thiab ntug. Cov cwj pwm yog cov khoom ntawm vertices thiab ntug. Ib qho kev sib txuas yog kev sib txuas ntawm cov vertices.

Tsis zoo li lwm cov qauv ntaub ntawv, graph databases tseem ceeb rau kev sib raug zoo ntawm cov chaw. Yog li ntawd, tsis tas yuav suav cov kev sib raug zoo siv cov yuam sij txawv teb chaws lossis lwm yam txhais tau tias. Nws yog ua tau los tsim cov ntaub ntawv complex qauv siv tsuas yog vertex thiab ntug abstractions.

Hauv lub ntiaj teb niaj hnub no, kev ua qauv kev sib raug zoo yuav tsum muaj cov txheej txheem nyuaj. Txhawm rau ua qauv kev sib raug zoo, SQL Server 2017 muaj peev xwm ntawm graph database. Lub vertices thiab ntug ntawm daim duab yog sawv cev raws li hom tshiab ntawm cov ntxhuav: NODE thiab EDGE. Graph queries siv T-SQL tshiab muaj nuj nqi hu ua MATCH(). Txij li qhov kev ua haujlwm no tau tsim rau hauv SQL Server 2017, nws tuaj yeem siv rau hauv koj cov ntaub ntawv uas twb muaj lawm yam tsis tas yuav muaj kev hloov pauv.

Cov txiaj ntsig ntawm tus qauv graph

Niaj hnub no, cov lag luam thiab cov neeg siv xav tau cov ntawv thov uas siv cov ntaub ntawv ntau thiab ntau dua, thaum xav tau kev ua haujlwm siab thiab kev ntseeg siab. Sawv cev cov ntaub ntawv raws li ib daim duab muab ib txoj kev yooj yim ntawm kev tuav txoj kev sib raug zoo. Qhov kev daws teeb meem no daws tau ntau yam teeb meem thiab pab kom tau txais cov txiaj ntsig hauv ib lub ntsiab lus.

Nws zoo li ntau daim ntawv thov yuav tau txais txiaj ntsig los ntawm kev siv graph databases yav tom ntej.

Data modeling: los ntawm kev sib raug zoo mus rau graph qauv

Piv Txwv:

Cia peb saib ib qho piv txwv ntawm lub koom haum nrog cov neeg ua haujlwm hierarchy: tus neeg ua haujlwm tshaj tawm rau tus thawj tswj hwm, tus thawj tswj hwm qhia rau tus thawj coj loj, thiab lwm yam. Nyob ntawm lub tuam txhab tshwj xeeb, tej zaum yuav muaj ntau qib hauv qhov hierarchy. Tab sis raws li tus naj npawb ntawm cov qib nce, xam kev sib raug zoo nyob rau hauv ib tug relational database yuav ntau thiab ntau complex. Nws yog qhov nyuaj heev los xav txog cov neeg ua haujlwm hierarchy, hierarchy hauv kev lag luam lossis kev sib txuas hauv kev tes hauj lwm. Cia peb saib yuav ua li cas SQL Graph tuaj yeem daws qhov teeb meem ntawm kev tuav ntau theem ntawm hierarchy.

Rau qhov piv txwv no peb yuav ua tus qauv cov ntaub ntawv yooj yim. Cia peb tsim ib lub rooj ntawm cov neeg ua haujlwm EMP nrog ID EMPNO thiab kem M.G.R., qhia tus ID ntawm tus neeg ua haujlwm tus saib xyuas (tus thawj coj). Tag nrho cov ntaub ntawv hais txog hierarchy yog khaws cia rau hauv lub rooj no thiab tuaj yeem nug tau siv cov kab EMPNO и M.G.R..

Daim duab hauv qab no qhia tau hais tias tib lub koom haum qauv qauv nrog plaub theem ntawm kev zes nyob rau hauv ib daim ntawv paub ntau dua. Cov neeg ua haujlwm yog cov vertices ntawm daim duab los ntawm lub rooj EMP. Lub chaw "tus neeg ua haujlwm" txuas nrog nws tus kheej los ntawm kev sib txuas "xa" (ReportsTo). Hauv cov ntsiab lus graph, ib qho txuas yog ib qho ntug (EDGE) uas txuas cov nodes (NODE) ntawm cov neeg ua haujlwm.

Cia peb tsim ib lub rooj tsis tu ncua EMP thiab ntxiv qhov tseem ceeb nyob rau hauv raws li daim duab saum toj no.

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)

Daim duab hauv qab no qhia cov neeg ua haujlwm:

neeg ua haujlwm nrog EMPNO 7369 ua 7902;
neeg ua haujlwm nrog EMPNO 7902 ua 7566
neeg ua haujlwm nrog EMPNO 7566 ua 7839

Tam sim no cia saib ib daim duab sawv cev ntawm tib cov ntaub ntawv. Lub EMPLOYEE vertex muaj ob peb tus cwj pwm thiab txuas nrog nws tus kheej los ntawm kev sib raug zoo "subordinates" (EmplReportsTo). EmplReportsTo yog lub npe ntawm kev sib raug zoo.

Lub rooj ntug (EDGE) kuj tseem muaj cov cwj pwm.

Wb tsim lub rooj node EmpNode

Lub syntax rau tsim ib lub node yog heev yooj yim: mus rau cov lus qhia TSIM LUB ROOJ yog ntxiv rau qhov kawg "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;

Tam sim no cia peb hloov cov ntaub ntawv los ntawm lub rooj tsis tu ncua mus rau lub rooj teeb duab. Tom ntej no NYIAJ ntxig cov ntaub ntawv los ntawm lub rooj sib txheeb EMP.

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

Nyob rau hauv lub rooj ntawm nodes nyob rau hauv ib kem tshwj xeeb $node_id_* tus naj npawb ntawm node yog khaws cia li JSON. Cov kab seem ntawm cov lus no muaj cov node tus cwj pwm.

Tsim cov npoo (EDGE)

Tsim ib lub rooj ntug zoo ib yam li kev tsim lub rooj ntawm lub rooj, tshwj tsis yog siv lo lus tseem ceeb "AS EDGE".

CREATE TABLE empReportsTo(Deptno int) AS EDGE

Tam sim no cia peb txhais cov kev sib txuas ntawm cov neeg ua haujlwm siv kab EMPNO и M.G.R.. Lub koom haum daim duab kos duab qhia meej tias yuav sau li cas NYIAJ.

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

Lub default edge table muaj peb kab. Ua ntej, $edge_id - tus cim ntug hauv JSON daim ntawv. Lwm ob ($from_id и $to_id) sawv cev ntawm kev sib txuas ntawm nodes. Tsis tas li ntawd, cov npoo tuaj yeem muaj cov khoom ntxiv. Hauv peb qhov xwm txheej nws yog Deptno.

System Views

Hauv qhov system saib sys.tables ob kab tshiab tau tshwm sim:

yog_edge
yog_node

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

ssms ua

Cov khoom muaj feem xyuam nrog cov duab muaj nyob hauv daim duab duab nplaub tshev. Lub rooj icon ntawm lub rooj yog cim nrog ib qho chaw, thiab lub ntug lub rooj icon cim nrog ob lub voj voog txuas (zoo li tsom iav).

MATCH qhia

Qhia TIV THAIV muab los ntawm CQL (Cypher Query Language). Qhov no yog ib txoj hauv kev zoo los nug cov khoom ntawm daim duab. CQL pib nrog kev qhia TIV THAIV.

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

piv txwv

Cia peb saib ob peb yam piv txwv.

Cov lus nug hauv qab no qhia cov neeg ua haujlwm rau leej twg Smith thiab nws tus thawj coj qhia.

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'

Cov lus nug hauv qab no yog nrhiav cov neeg ua haujlwm thib ob thiab cov thawj coj rau Smith. Yog koj tshem cov kab lus Qhov twg, ces qhov tshwm sim yuav tso saib tag nrho cov neeg ua haujlwm.

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'

Thiab thaum kawg, thov rau cov neeg ua haujlwm thib peb thiab cov thawj coj.

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'

Tam sim no cia peb hloov cov kev taw qhia kom tau Smith cov thawj coj.

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)

xaus

SQL Server 2017 tau tsim nws tus kheej ua tiav kev lag luam daws teeb meem rau ntau yam haujlwm IT lag luam. Thawj version ntawm SQL Graph yog qhov zoo heev. Txawm hais tias muaj qee qhov kev txwv, nws twb muaj peev xwm ua haujlwm los tshawb txog lub peev xwm ntawm cov duab.

SQL Graph functionality yog tag nrho rau hauv SQL Engine. Txawm li cas los xij, raws li tau hais dhau los, SQL Server 2017 muaj cov kev txwv hauv qab no:

Tsis muaj kev txhawb nqa polymorphism.

Tsuas yog kev sib txuas unidirectional nkaus xwb.
Edges tsis tuaj yeem hloov kho $from_id thiab $to_id kab ntawm UPDATE.
Kev hloov pauv tsis tau txais kev txhawb nqa, tab sis lawv tuaj yeem tau txais los ntawm CTE.
Kev them nyiaj yug rau In-Memory OLTP cov khoom raug txwv.
Cov ntxhuav nyob ib ntus (System-Versioned Temporal Table), tsis muaj kev txhawb nqa hauv zos thiab thoob ntiaj teb ib ntus.
Cov rooj hom thiab cov lus sib txawv tsis tuaj yeem tshaj tawm tias NODE lossis EDGE.
Hla-database queries tsis txaus siab.
Tsis muaj txoj hauv kev ncaj qha lossis tus kws pab tswv yim los hloov cov rooj tsis tu ncua rau cov rooj teeb duab.
Tsis muaj GUI los tso saib cov duab, tab sis koj tuaj yeem siv Power BI.

Nyeem ntxiv:

Yuav ua li cas SQL Server siv cov ntxaij lim dej bitmap

Tau qhov twg los: www.hab.com

Taw qhia rau SQL Server 2017 Graph Databases