🥇 Ifihan si SQL Server 2017 Awọn aaye data ayaworan

Ṣaaju ibẹrẹ ti ẹkọ naa "MS SQL Olùgbéejáde Server" A ti pese itumọ miiran ti o wulo fun ọ.

Awọn apoti isura infomesonu ayaworan jẹ imọ-ẹrọ pataki fun awọn akosemose data data. Mo gbiyanju lati tẹle awọn imotuntun ati awọn imọ-ẹrọ tuntun ni agbegbe yii ati lẹhin ṣiṣe pẹlu ibatan ati awọn apoti isura infomesonu NoSQL, Mo rii pe ipa ti awọn apoti isura infomesonu ayaworan ti n di siwaju ati siwaju sii. Nigbati o ba n ṣiṣẹ pẹlu data akosoagbasomode eka, kii ṣe awọn apoti isura infomesonu ibile nikan, ṣugbọn NoSQL tun jẹ doko. Nigbagbogbo, pẹlu ilosoke ninu nọmba awọn ipele ọna asopọ ati iwọn data data, idinku ninu iṣẹ. Ati pe bi awọn ibatan ṣe di idiju, nọmba awọn JOIN tun pọ si.

Nitoribẹẹ, ninu awoṣe ibatan awọn solusan wa fun ṣiṣẹ pẹlu awọn ipo giga (fun apẹẹrẹ, lilo awọn CTE recursive), ṣugbọn iwọnyi tun jẹ awọn adaṣe. Ni akoko kanna, iṣẹ ṣiṣe ti awọn apoti isura infomesonu ayaworan SQL Server jẹ ki o rọrun lati mu awọn ipele pupọ ti awọn ipo giga. Mejeeji awoṣe data ati awọn ibeere jẹ irọrun, ati nitorinaa ṣiṣe wọn pọ si. Ti o ṣe pataki dinku iye koodu.

Awọn apoti isura infomesonu ayaworan jẹ ede asọye fun aṣoju awọn ọna ṣiṣe ti eka. Imọ-ẹrọ yii ti ni lilo pupọ ni ile-iṣẹ IT ni awọn agbegbe bii awọn nẹtiwọọki awujọ, awọn ọna ṣiṣe arekereke, itupalẹ nẹtiwọọki IT, awọn iṣeduro awujọ, ọja ati awọn iṣeduro akoonu.

Iṣẹ-ṣiṣe data ayaworan ni SQL Server jẹ o dara fun awọn oju iṣẹlẹ nibiti data naa ti ni asopọ pupọ ati pe o ni awọn ibatan asọye daradara.

Awoṣe data aworan

Aworan kan jẹ eto awọn inaro (awọn apa, ipade) ati awọn egbegbe (awọn ibatan, eti). Vertices ṣe aṣoju awọn nkan, ati awọn egbegbe duro fun awọn ọna asopọ ti awọn abuda le ni alaye ninu.

Aworan data data awọn awoṣe awọn nkan bi iyaya kan bi a ti ṣalaye ninu ilana iyaworan. Awọn ẹya data jẹ awọn igun ati awọn egbegbe. Awọn abuda jẹ awọn ohun-ini ti awọn inaro ati awọn egbegbe. Asopọ jẹ asopọ ti awọn inaro.

Ko dabi awọn awoṣe data miiran, awọn apoti isura infomesonu ayaworan ṣe pataki awọn ibatan laarin awọn nkan. Nitorinaa, ko si iwulo lati ṣe iṣiro awọn ibatan nipa lilo awọn bọtini ajeji tabi ni ọna miiran. O ṣee ṣe lati ṣẹda awọn awoṣe data idiju nipa lilo fatesi nikan ati awọn abstractions eti.

Ni agbaye ode oni, awọn ibatan awoṣe nilo diẹ sii ati siwaju sii awọn ilana imudara. Lati ṣe apẹẹrẹ awọn ibatan, SQL Server 2017 nfunni ni awọn agbara data iwọn aworan. Awọn inaro ati awọn egbegbe ti aworan naa jẹ aṣoju bi awọn oriṣi tabili tuntun: NODE ati EDGE. Awọn ibeere aworan lo iṣẹ T-SQL tuntun ti a pe ni MATCH (). Niwọn igba ti a ti kọ iṣẹ yii sinu SQL Server 2017, o le ṣee lo ninu awọn apoti isura infomesonu ti o wa laisi iwulo fun eyikeyi iyipada data.

Awọn anfani ti Awoṣe Aworan

Ni ode oni, awọn iṣowo ati awọn olumulo n beere awọn ohun elo ti o ṣiṣẹ pẹlu data diẹ sii ati siwaju sii, lakoko ti o nreti iṣẹ giga ati igbẹkẹle. Aṣoju ayaworan ti data nfunni ni ọna irọrun fun mimu awọn ibatan idiju mu. Ọna yii yanju ọpọlọpọ awọn iṣoro ati iranlọwọ lati gba awọn abajade laarin ipo ti a fun.

O dabi pe ọpọlọpọ awọn ohun elo yoo ni anfani lati awọn apoti isura infomesonu ayaworan ni ọjọ iwaju.

Awoṣe Data: Lati Ibasepo si Awoṣe Aworan

Apeere:

Jẹ ki a wo apẹẹrẹ ti eto igbekalẹ pẹlu awọn ilana ti awọn oṣiṣẹ: oṣiṣẹ ṣe ijabọ si oluṣakoso, oluṣakoso kan ṣe ijabọ si oluṣakoso agba, ati bẹbẹ lọ. Ti o da lori ile-iṣẹ kan pato, nọmba eyikeyi ti awọn ipele le wa ni ipo-iṣe yii. Ṣugbọn bi nọmba awọn ipele ti n pọ si, ṣiṣe iṣiro awọn ibatan ni ibi ipamọ data ibatan di iṣoro ati siwaju sii. O jẹ ohun soro lati fojuinu awọn logalomomoise ti awọn abáni, awọn logalomomoise ni tita tabi awujo media awọn isopọ. Jẹ ki a wo bii SQL Graph ṣe le yanju iṣoro ti mimu awọn ipele oriṣiriṣi ti awọn ipo ipo.

Fun apẹẹrẹ yii, jẹ ki a ṣe awoṣe data ti o rọrun. Ṣẹda tabili awọn oṣiṣẹ EMP pẹlu id EMPNO ati ọwọn M.G.R.A ti o tọka si ID ti ori (oluṣakoso) ti oṣiṣẹ. Gbogbo alaye nipa awọn logalomomoise ti wa ni fipamọ ni yi tabili ati ki o le wa ni ibeere nipa lilo awọn ọwọn EMPNO и M.G.R..

Aworan atọka atẹle naa tun fihan awoṣe aworan apẹrẹ org kanna pẹlu awọn ipele itẹ-ẹiyẹ mẹrin ni fọọmu ti o faramọ diẹ sii. Abáni ni awọn vertices ti awonya lati tabili EMP. Ohun kan “oṣiṣẹ” ti sopọ mọ ararẹ nipasẹ ibatan “fi silẹ” (ReportsTo). Ni awọn ofin ayaworan, ọna asopọ jẹ eti (EDGE) ti o so awọn apa (NODEs) ti awọn oṣiṣẹ.

Jẹ ki a ṣẹda tabili deede EMP ati ṣafikun awọn iye nibẹ ni ibamu si aworan atọka loke.

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)

Nọmba ti o wa ni isalẹ fihan awọn oṣiṣẹ:

abáni pẹlu EMPNO 7369 wa labẹ 7902;
abáni pẹlu EMPNO 7902 labẹ 7566
abáni pẹlu EMPNO 7566 labẹ 7839

Bayi jẹ ki a wo aṣoju ti data kanna ni irisi iyaya kan. Ipade Oṣiṣẹ ni awọn abuda pupọ ati pe o ni asopọ si ararẹ nipasẹ ibatan “fisilẹ” (EmplReportsTo). EmplReportsTo ni orukọ ibatan naa.

Tabili eti (EDGE) le tun ni awọn abuda.

Ṣẹda tabili ipade EmpNode

Sintasi fun ṣiṣẹda ipade jẹ ohun rọrun: si ikosile Ṣẹda tabili kun si opin 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;

Bayi jẹ ki a yi data pada lati tabili deede si aworan kan. Itele FI SII ifibọ data lati kan ibatan tabili EMP.

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

Ni awọn ipade tabili ni pataki kan iwe $node_id_* ID ogun ti wa ni ipamọ bi JSON. Awọn ọwọn ti o ku ti tabili yii ni awọn abuda ti ipade naa.

Ṣẹda Edges (EDGE)

Ṣiṣẹda tabili eti kan jọra si ṣiṣẹda tabili ipade, ayafi ti Koko naa BI EDGE.

CREATE TABLE empReportsTo(Deptno int) AS EDGE

Bayi jẹ ki a ṣalaye awọn ibatan laarin awọn oṣiṣẹ nipa lilo awọn ọwọn EMPNO и M.G.R.. Aworan org fihan kedere bi o ṣe le kọ FI SII.

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

Tabili eti ni awọn ọwọn mẹta nipasẹ aiyipada. Akoko, $edge_id - idamo eti ni irisi JSON. Awọn meji miiran ($from_id и $to_id) ṣe aṣoju ibasepọ laarin awọn apa. Ni afikun, awọn egbegbe le ni awọn ohun-ini afikun. Ninu ọran wa, eyi Deptno.

Awọn iwo eto

Ni wiwo eto sys.tables Awọn ọwọn tuntun meji wa:

jẹ_eti
is_node

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

SMS

Awọn nkan ti o ni ibatan si awọn aworan wa ninu folda Awọn tabili Awọn aworan. Aami tabili ipade ti samisi pẹlu aami kan, ati aami tabili eti ti samisi pẹlu awọn iyika asopọ meji (eyiti o dabi awọn gilaasi).

MATCH ikosile

Ikosile BARAMU ti a gba lati CQL (Ede ibeere Cypher). Eyi jẹ ọna ti o munadoko lati beere awọn ohun-ini ti aworan kan. CQL bẹrẹ pẹlu ikosile BARAMU.

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

Awọn apẹẹrẹ

Jẹ ki a wo awọn apẹẹrẹ diẹ.

Ibeere ti o wa ni isalẹ ṣafihan awọn oṣiṣẹ ti Smith ati oluṣakoso rẹ ṣe ijabọ si.

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'

Ibeere atẹle jẹ fun wiwa awọn oṣiṣẹ ati awọn alakoso ipele keji fun Smith. Ti o ba yọ ipese naa kuro Nibo, lẹhinna gbogbo awọn oṣiṣẹ yoo han bi abajade.

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'

Ati nikẹhin, ibeere fun awọn oṣiṣẹ ipele-kẹta ati awọn alakoso.

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'

Bayi jẹ ki a yipada itọsọna lati gba awọn ọga 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)

ipari

SQL Server 2017 ti fi idi ara rẹ mulẹ bi ojutu ile-iṣẹ pipe fun ọpọlọpọ awọn iwulo IT iṣowo. Ẹya akọkọ ti SQL Graph jẹ ileri pupọ. Paapaa laibikita awọn idiwọn, iṣẹ ṣiṣe ti to tẹlẹ wa lati ṣawari awọn aye ti awọn aworan.

Iṣẹ-ṣiṣe aworan SQL ti wa ni kikun sinu SQL Engine. Sibẹsibẹ, bi a ti sọ tẹlẹ, SQL Server 2017 ni awọn idiwọn wọnyi:

Ko si atilẹyin fun polymorphism.

Awọn ọna asopọ unidirectional nikan ni atilẹyin.
Awọn egbegbe ko le ṣe imudojuiwọn awọn ọwọn $from_id ati $to_id nipasẹ Imudojuiwọn.
Awọn pipade iyipada ko ni atilẹyin, ṣugbọn wọn le gba ni lilo awọn CTE.
Atilẹyin to lopin fun awọn nkan OLTP In-Memory.
Awọn tabili igba diẹ (Tabili Iṣe-iṣafihan Eto), agbegbe igba diẹ ati awọn tabili agbaye ko ni atilẹyin.
Awọn oriṣi tabili ati awọn oniyipada tabili ko le sọ bi NODE tabi EDGE.
Awọn ibeere aaye data agbelebu ko ni atilẹyin.
Ko si ọna taara tabi diẹ ninu iru oluṣeto (oluṣeto) lati yi awọn tabili deede pada si awọn tabili awọn aworan.
Ko si GUI lati ṣafihan awọn aworan, ṣugbọn agbara BI le ṣee lo.

Ka siwaju:

Bawo ni SQL Server nlo awọn asẹ bitmap

orisun: www.habr.com

Ifihan si SQL Server 2017 Awọn aaye data ayaworan