🥇Sumo yeGrafu Databases SQL Server 2017

Kusati kwatanga kosi "MS SQL Server Developer" Takakugadzirira imwe shanduro inobatsira.

Grafu dhatabhesi ndiyo yakakosha tekinoroji yevashandisi vedatabase. Ndinoedza kufambirana nehunyanzvi uye matekinoroji matsva munzvimbo ino uye, mushure mekushanda nehukama uye NoSQL dhatabhesi, ndinoona basa remagraph database richiwedzera kukosha. Paunenge uchishanda neakaomesesa hierarchical data, kwete echinyakare dhatabhesi haashande, asiwo NoSQL. Kazhinji, sezvo nhamba yemazinga ekubatanidza uye saizi yedatabase inowedzera, kuita kunoderera. Uye sezvo hukama hunowedzera kuoma, nhamba yeJOIN inowedzerawo.

Ehe, iyo yehukama modhi ine zvigadziriso zvekushanda nemahierarchies (semuenzaniso, kushandisa inodzokorodza CTEs), asi izvi zvichiri zvekugadzirisa. Panguva imwecheteyo, kushanda kweSQL Server graph dhatabhesi inokubvumira kubata nyore mazinga akawanda ehutongi. Ose ari maviri modhi yedata uye mibvunzo inorerutswa, uye nekudaro kugona kwavo kunowedzera. Nhamba yekodhi yakaderedzwa zvakanyanya.

Girafu dhatabhesi mutauro unoratidza kumiririra yakaoma masisitimu. Iyi tekinoroji yatove kushandiswa zvakanyanya muindasitiri yeIT munzvimbo dzakaita sesocial network, anti-hutsotsi masisitimu, IT network kuongororwa, kurudziro yemagariro, chigadzirwa uye kurudziro yemukati.

Iyo graph database inoshanda muSQL Server inokodzera mamiriro apo data yakabatana zvakanyanya uye ine hukama hwakanyatsotsanangurwa.

Grafu data modhi

Girafu seti yemavertices (node) uye mipendero (hukama, mupendero). Vertices inomiririra masangano, uye mipendero inomiririra hukama hunogona kunge uine ruzivo.

A graph database modhi masangano segirafu sezvinotsanangurwa mugirafu dzidziso. Data zvimiro vertices uye mipendero. Hunhu zvimiro zve vertices nemapendero. Kubatana kunobatanidza vertices.

Kusiyana nemamwe mamodheru edhata, dhatabhesi remagraph anokoshesa hukama pakati pemasangano. Naizvozvo, hapana chikonzero chekuverenga hukama uchishandisa makiyi ekunze kana chero imwe nzira. Izvo zvinogoneka kugadzira yakaoma data mhando uchishandisa chete vertex uye edge abstractions.

Munyika yemazuva ano, hukama hwekuenzanisa hunoda maitiro anowedzera akaoma. Kuenzanisira hukama, SQL Server 2017 inopa graph database kugona. Iyo vertices uye mipendero yegirafu inomiririrwa semhando nyowani dzematafura: NODE uye EDGE. Mibvunzo yegirafu inoshandisa itsva T-SQL basa rinonzi MATCH(). Sezvo basa iri rakavakirwa muSQL Server 2017, rinogona kushandiswa mune yako iripo dhatabhesi pasina kudiwa kwechero shanduko.

Zvakanakira mufananidzo wegirafu

Nhasi, mabhizinesi nevashandisi vanoda zvikumbiro zvinobata data rakawanda, vachitarisira kuita kwepamusoro uye kuvimbika. Kumiririra data segirafu inopa nzira iri nyore yekubata hukama hwakaoma. Iyi nzira inogadzirisa matambudziko mazhinji uye inobatsira kuwana mhedzisiro mukati mechirevo chakapihwa.

Zvinotaridza sekunge akawanda maapplication achabatsirwa nekushandisa graph databases mune ramangwana.

Data modelling: kubva pahukama kusvika kune graph modhi

Muenzaniso:

Ngatitarisei muenzaniso wehurongwa hwehurongwa hune hutongi hwevashandi: mushandi anoshuma kune maneja, maneja anoshuma kune maneja mukuru, zvichingodaro. Zvichienderana nekambani chaiyo, panogona kunge paine chero nhamba yemazinga mune iyi hierarchy. Asi sezvo huwandu hwemazinga hunowedzera, kuverenga hukama mune yehukama dhatabhesi kunowedzera uye zvakanyanya kuoma. Zvakanyanya kuoma kufungidzira hutungamiri hwevashandi, hutungamiri mukushambadzira kana kubatanidza mumasocial network. Ngationei kuti SQL Graph inogona kugadzirisa sei dambudziko rekubata mazinga akasiyana ehutongi.

Kune uyu muenzaniso tichaita nyore data data. Ngatigadzirei tafura yevashandi PMS ne ID EMPNO uye column M.G.R., zvichiratidza chiziviso chemutariri wemushandi (maneja). Yese ruzivo nezve hierarchy inochengetwa mutafura ino uye inogona kubvunzwa uchishandisa makoramu EMPNO и M.G.R..

Dhiagiramu rinotevera rinoratidza chimiro chimwechete chesangano chine nhanho ina dzekuvaka matendere mune imwe nzira yakajairika. Vashandi ndivo vertices yegirafu kubva patafura PMS. Sangano "mushandi" rakabatanidzwa kune pacharo nekubatanidza "submits" (ReportsTo). Mune magirafu mazwi, chinongedzo imupendero (EDGE) inobatanidza node (NODE) yevashandi.

Ngatigadzire tafura yenguva dzose PMS uye wedzera kukosha ikoko zvinoenderana neiyo dhayagiramu iri pamusoro.

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)

Mufananidzo uri pasi apa unoratidza vashandi:

mushandi pamwe EMPNO 7369 vanoteerera 7902;
mushandi pamwe EMPNO 7902 inoteerera 7566
mushandi pamwe EMPNO 7566 inoteerera 7839

Iye zvino ngatitarisei mufananidzo wegirafu weiyo data yakafanana. Iyo EMPLOYEE vertex ine akati wandei hunhu uye yakabatana nayo pachayo ne "vakaderera" hukama (EmplReportsTo). EmplReportsTo ndiro zita rehukama.

Tafura yemupendero (EDGE) inogonawo kunge iine hunhu.

Ngatigadzire tafura yenode EmpNode

Iyo syntax yekugadzira node iri nyore: kune kutaura Gadzira tafura inowedzerwa kusvika kumagumo "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;

Zvino ngatichinjei data kubva patafura yenguva dzose kuenda kune girafu imwe. Next Isa inoisa data kubva patafura yehukama PMS.

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

Mutafura yemanodhi mune yakakosha mbiru $node_id_* iyo node identifier inochengetwa seJSON. Makoramu asara etafura iyi ane hunhu hwenodhi.

Kugadzira mipendero (EDGE)

Kugadzira tafura yemupendero kwakangofanana nekugadzira tafura yenode, kunze kwekuti iro kiyi izwi rinoshandiswa "AS EDGE".

CREATE TABLE empReportsTo(Deptno int) AS EDGE

Zvino ngatitsanangurirei kubatana pakati pevashandi vachishandisa makoramu EMPNO и M.G.R.. Dhiyagiramu yehurongwa hwesangano inoratidza zvakajeka manyorerwo Isa.

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

Iyo tafura yekumucheto yakasarudzika ine makoramu matatu. Chekutanga, $edge_id - mupendero identifier muJSON fomu. Mamwe maviri ($from_id и $to_id) inomiririra kubatana pakati pemanodhi. Mukuwedzera, mipendero inogona kuva nezvimwe zvinhu. Muchiitiko chedu ndizvo Deptno.

System Views

Mukuona kwehurongwa sys.tables makoramu maviri matsva aonekwa:

is_edge
iri_node

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

ssms

Zvinhu zvine chekuita nemagrafu zviri muGraph Tables folda. Iyo node tafura icon yakanyorwa nedoti, uye iyo yekumucheto tafura icon inomisikidzwa nemadenderedzwa maviri akabatana (zvishoma semagirazi).

MATCH kutaura

Tsanangudzo MATCH yakatorwa kubva kuCQL (Cypher Query Mutauro). Iyi inzira inoshanda yekubvunza zvimiro zvegirafu. CQL inotanga nechirevo MATCH.

nemarongerwo

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

mienzaniso

Ngatitarisei mienzaniso mishoma.

Mubvunzo uri pazasi unoratidza vashandi vanoudzwa naSmith nemaneja wake.

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'

Mubvunzo unotevera ndewekutsvaga vashandi vechikamu chechipiri nemamaneja eSmith. Kana ukabvisa mutsara WHERE, ipapo chigumisiro chicharatidza vashandi vose.

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'

Uye pakupedzisira, chikumbiro chechitatu-chikamu vashandi uye mamaneja.

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'

Zvino ngatichinje nzira kuti titore maboss aSmith.

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)

mhedziso

SQL Server 2017 yakazvimisikidza seyakakwana bhizinesi mhinduro kune akasiyana IT bhizinesi mabasa. Yekutanga vhezheni yeSQL Graph inovimbisa zvakanyanya. Kunyangwe paine zvimwe zvipimo, patova nekushanda kwakakwana kuongorora kugona kwemagirafu.

SQL Graph mashandiro akanyatsobatanidzwa muSQL Injini. Nekudaro, sezvatotaurwa, SQL Server 2017 ine zvinotevera zvinogumira:

Hapana polymorphism inotsigira.

Chete unidirectional connections inotsigirwa.
Mipendero haigone kugadzirisa $from_id uye $to_id makoramu kuburikidza neUPDATE.
Transitive kuvharwa hakutsigirwe, asi anogona kuwanikwa uchishandisa CTE.
Tsigiro yeIn-Memory OLTP zvinhu ishoma.
Temporal tables (System-Versioned Temporal Table), matafura enguva pfupi emuno neepasirese haatsigirwe.
Mhando dzematafura uye tafura dzakasiyana hazvigone kuziviswa seNODE kana EDGE.
Cross-database queries haitsigirwe.
Iko hakuna nzira yakananga kana wizard yekushandura matafura enguva dzose kuita matafura egraph.
Iko hakuna GUI yekuratidza magirafu, asi unogona kushandisa Power BI.

Verenga zvimwe:

Iyo SQL Server inoshandisa sei bitmap mafirita

Source: www.habr.com

Nhanganyaya kuSQL Server 2017 Graph Databases