🥇 Okwu Mmalite nke Graph Databases SQL Server 2017

Tupu mmalite nke usoro "MS SQL Onye Mmepụta Server" Anyị akwadola ntụgharị asụsụ ọzọ bara uru maka gị.

Ebe nchekwa data eserese bụ teknụzụ dị mkpa maka ndị ọkachamara nchekwa data. M na-agbalị ịnọgide na-enwe ihe ọhụrụ na nkà na ụzụ ọhụrụ na mpaghara a na, mgbe m na-arụ ọrụ na mmekọrịta na NoSQL ọdụ data, m na-ahụ ọrụ nke graph ọdụ data na-aghọwanye mkpa. Mgbe ị na-arụ ọrụ na data dị mgbagwoju anya, ọ bụghị naanị ọdụ data ọdịnala adịghị arụ ọrụ, kamakwa NoSQL. Ọtụtụ mgbe, ka ọnụ ọgụgụ nke njikọ njikọ na nha nke nchekwa data na-abawanye, arụmọrụ na-ebelata. Ma ka mmekọrịta ahụ na-adịwanye mgbagwoju anya, ọnụọgụ JOIN na-abawanye.

N'ezie, ụdị mmekọrịta ahụ nwere ihe ngwọta maka ịrụ ọrụ na ndị isi (dịka ọmụmaatụ, iji CTE na-emegharị), ma ndị a ka bụ ihe mgbagwoju anya. N'otu oge ahụ, arụmọrụ nke ọdụ data eserese SQL Server na-enye gị ohere ijikwa ọtụtụ ọkwa nke ọkwa n'ụzọ dị mfe. Ma ụdị data na ajụjụ dị mfe, ya mere arụmọrụ ha na-abawanye. A na-ebelata ọnụọgụ koodu nke ukwuu.

Ebe nchekwa data eserese bụ asusu na-egosipụta maka usoro mgbagwoju anya. A na-eji teknụzụ a eme ihe n'ọtụtụ ebe na ụlọ ọrụ IT na mpaghara dị ka netwọk mmekọrịta, usoro mgbochi aghụghọ, nyocha netwọk IT, ndụmọdụ mmekọrịta, ngwaahịa na ọdịnaya.

Ọrụ nchekwa data eserese dị na SQL Server dabara adaba maka ọnọdụ ebe data jikọtara nke ọma yana nwee mmekọrịta akọwapụtara nke ọma.

Ihe ngosi data eserese

Eserese bụ ntọala nke vertices (ọnụ) na ọnụ (mmekọrịta, ọnụ). Vertices na-anọchi anya ụlọ ọrụ, na akụkụ na-anọchi anya mmekọrịta nke njirimara nwere ike ịnwe ozi.

Ihe nchekwa data eserese eserese na-egosipụta ihe dị ka eserese ka akọwara na tiori eserese. Ọdịdị data bụ vertices na nsọtụ. Àgwà ọma bụ njirimara nke vertices na akụkụ. Njikọ bụ njikọ nke vertices.

N'adịghị ka ụdị data ndị ọzọ, ọdụ data eserese na-ebute mmekọrịta dị n'etiti ụlọ ọrụ ụzọ. Ya mere, ọ dịghị mkpa ịgbakọ mmekọrịta site na iji igodo mba ọzọ ma ọ bụ ụzọ ọ bụla ọzọ. Ọ ga-ekwe omume ịmepụta ụdị data mgbagwoju anya site na iji naanị vertex na abstractions ihu.

N'ime ụwa ọgbara ọhụrụ, mmekọrịta ịmebe na-achọ usoro dị mgbagwoju anya. Iji mepụta mmekọrịta, SQL Server 2017 na-enye ike nchekwa data eserese. A na-anọchi anya akụkụ na nsọtụ nke eserese dị ka ụdị tebụl ọhụrụ: NODE na EDGE. Ajuju eserese na-eji ọrụ T-SQL ọhụrụ akpọrọ MATCH(). Ebe ọ bụ na arụrụ ọrụ a n'ime SQL Server 2017, enwere ike iji ya na ọdụ data dị adị na-enweghị mkpa ntụgharị ọ bụla.

Uru nke ụdị eserese

Taa, azụmahịa na ndị ọrụ na-achọ ngwa na-ejikwa data ndị ọzọ, ebe ha na-atụ anya ịrụ ọrụ dị elu na ntụkwasị obi. Nnọchite anya data dị ka eserese na-enye ụzọ dabara adaba iji jikwa mmekọrịta siri ike. Usoro a na-edozi ọtụtụ nsogbu ma na-enyere aka nweta nsonaazụ n'ime ọnọdụ enyere.

Ọ dị ka ọtụtụ ngwa ga-erite uru site na iji ọdụ data eserese n'ọdịnihu.

Nlereanya data: site na njikọ ruo ụdị eserese

Ihe nlele:

Ka anyị leba anya n'ihe atụ nke usoro nhazi nke nwere usoro ndị ọrụ: onye ọrụ na-akọ onye njikwa, onye njikwa na-akọ onye isi njikwa, na ihe ndị ọzọ. Dabere na ụlọ ọrụ akọwapụtara, enwere ike ịnwe ọnụọgụ ọkwa ọ bụla na ọkwa a. Mana ka ọnụ ọgụgụ nke ọkwa na-abawanye, ịgbakọ mmekọrịta na nchekwa data mmekọrịta na-aghọwanye mgbagwoju anya. Ọ bụ ihe siri ike iche n'echiche nhazi ọkwa nke ndị ọrụ, ndị isi na ahịa ma ọ bụ njikọ na netwọk mmekọrịta. Ka anyị hụ ka SQL Graph nwere ike isi dozie nsogbu nke ijikwa ọkwa ọkwa dị iche iche.

Maka ihe atụ a, anyị ga-eme ka ụdị data dị mfe. Ka anyị mepụta tebụl ndị ọrụ PMS na ID EMPNO na kọlụm M.G.R., na-egosi njirimara nke onye nlekọta ọrụ (onye njikwa). A na-echekwa ozi niile gbasara ndị isi na tebụl a, enwere ike jụọ ya site na iji kọlụm EMPNO и M.G.R..

Eserese na-esonụ na-egosi otu ụdị nhazi nhazi nwere ọkwa anọ nke akwụ n'ụdị a maara nke ọma. Ndị ọrụ bụ akụkụ nke eserese si na tebụl PMS. Ejikọtala ụlọ ọrụ "onye ọrụ" n'onwe ya site na njikọ "na-enyefe" (ReportsTo). Na usoro eserese, njikọ bụ ọnụ (EDGE) nke jikọtara ọnụ (NODE) nke ndị ọrụ.

Ka anyị mepụta tebụl mgbe niile PMS ma tinye ụkpụrụ ebe ahụ dịka eserese dị n'elu.

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)

Foto dị n'okpuru na-egosi ndị ọrụ:

onye ọrụ na EMPNO 7369 na-erubere 7902;
onye ọrụ na EMPNO 7902 na-erubere 7566
onye ọrụ na EMPNO 7566 na-erubere 7839

Ugbu a, ka anyị lelee eserese eserese nke otu data ahụ. Vertex nke onye ọrụ nwere ọtụtụ njirimara yana njikọ "ndị nọ n'okpuru" (EmplReportsTo). EmplReportsTo bụ aha mmekọrịta ahụ.

Tebụlụ nsọtụ (EDGE) nwekwara ike ịnwe njirimara.

Ka anyị mepụta tebụl ọnụ EmpNode

Okwu syntax maka ịmepụta ọnụ dị nnọọ mfe: na okwu Mepụta Isiokwu agbakwunyere na njedebe "Dị ka 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;

Ugbu a, ka anyị tụgharịa data ahụ site na tebụl mgbe niile ka ọ bụrụ eserese. Osote INSERT tinye data site na tebụl mmekọrịta PMS.

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

Na tebụl ọnụ na kọlụm pụrụ iche $node_id_* A na-echekwa ihe nchọpụta ọnụ ka JSON. Ogidi ndị fọdụrụ na tebụl a nwere njirimara ọnụ.

Ịmepụta ọnụ (EDGE)

Ịmepụta tebụl nsọtụ yiri ịmepụta tebụl ọnụ, belụsọ na ejiri isiokwu "Dị ka EDGE".

CREATE TABLE empReportsTo(Deptno int) AS EDGE

Ugbu a, ka anyị kọwaa njikọ dị n'etiti ndị ọrụ na-eji ogidi EMPNO и M.G.R.. Eserese nhazi nhazi na-egosi n'ụzọ doro anya ka esi ede ihe INSERT.

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

Tebụlụ nsọtụ nke ndabara nwere ogidi atọ. Nke mbụ, $edge_id - ihe nchọpụta ihu n'ụdị JSON. Abụọ ndị ọzọ ($from_id и $to_id) na-anọchi anya njikọ dị n'etiti ọnụ. Na mgbakwunye, ọnụ nwere ike inwe ihe ndị ọzọ. N'ọnọdụ anyị ọ bụ Deptno.

Echiche Sistemu

N'ihe nlele sistemu sys.tables ogidi abụọ ọhụrụ apụtala:

bụ_ọnụ
bụ_ọnụ

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

SMS

Ihe ndị metụtara eserese dị na folda eserese eserese. Akara ngosi tebụl ọnụ bụ ntụpọ, na akara tebụl nsọtụ ka ejiri okirikiri abụọ jikọtara (nke dị ka enyo).

MATCH okwu

Okwu MATCH ewepụtara na CQL (Asụsụ ajụjụ Cypher). Nke a bụ ụzọ dị mma iji jụọ njirimara nke eserese. CQL na-amalite site na nkwupụta MATCH.

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

atụ

Ka anyị leba anya n’ihe atụ ole na ole.

Ajụjụ dị n'okpuru na-egosi ndị ọrụ Smith na onye njikwa ya kọọrọ.

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'

Ajụjụ na-esonụ bụ maka ịchọta ndị ọrụ na ndị njikwa ọkwa nke abụọ maka Smith. Ọ bụrụ na i wepụ ahịrịokwu ahụ EBE, mgbe ahụ, nsonaazụ ga-egosipụta ndị ọrụ niile.

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'

Na n'ikpeazụ, arịrịọ maka ndị ọrụ na ndị njikwa ọkwa nke atọ.

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'

Ugbu a, ka anyị gbanwee ụzọ iji nweta ndị isi 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)

nkwubi

SQL Server 2017 eguzobewo onwe ya dị ka ngwọta ụlọ ọrụ zuru oke maka ọrụ azụmahịa IT dị iche iche. Ụdị nke mbụ nke SQL Graph na-ekwe nkwa nke ukwuu. N'agbanyeghị oke ụfọdụ, enweelarị ọrụ zuru oke iji nyochaa ike nke eserese.

Arụ ọrụ eserese SQL abanyela nke ọma n'ime Injin SQL. Agbanyeghị, dịka ekwurula, SQL Server 2017 nwere oke ndị a:

Enweghị nkwado polymorphism.

Naanị njikọ otu ụzọ ka akwadoro.
Edge enweghị ike imelite kọlụm $from_id na $to_id site na Mmelite.
Anaghị akwado mmechi ntụgharị, mana enwere ike nweta ya site na iji CTE.
Nkwado maka ihe OLTP ebe nchekwa nwere oke.
Tebụl nwa oge (Tebụlụ nwa oge nke sistemụ arụrụ arụ), anaghị akwado tebụl mpaghara nwa oge na nke zuru ụwa ọnụ.
Enweghị ike ịkpọ ụdị tebụl na mgbanwe tebụl dị ka NODE ma ọ bụ EDGE.
Akwadoghi ajuju-obe data.
Enweghị ụzọ kpọmkwem ma ọ bụ ọkachamara iji tọghata tebụl oge na tebụl eserese.
Enweghị GUI iji gosipụta eserese, mana ị nwere ike iji Ike BI.

GỤKWUO:

Kedu ka SQL Server si eji ihe nzacha bitmap

isi: www.habr.com

Okwu mmalite na ọdụ data eserese SQL Server 2017