🥇Sissejuhatus SQL Server 2017 graafide andmebaasidesse

Kursuse alguse eelõhtul „MS SQL Server Developer“ oleme valmis seadnud teile veel ühe kasuliku tõlke.

Graafikandmed on andmebaasispetsialistide jaoks oluline tehnoloogia. Ma püüan hoida silma peal uuendustel ja uutest tehnoloogiatel selles valdkonnas ning pärast tööd relatsiooniliste ja NoSQL andmebaasidega näen, et graafikandmete roll muutub üha olulisemaks. Komplekse hierarhiliste andmete töötlemisel ei ole mitte ainult traditsioonilised andmebaasid, vaid ka NoSQL väga tõhusad. Tihti, kui seoste arv ja andmebaasi suurus kasvavad, märgatakse jõudluse vähenemist. Ja seoste keerukuse suurenedes kasvab ka JOINide arv.

Muidugi on relatsioonilises mudelis lahendusi hierarhiate töötlemiseks (näiteks rekursiivsete CTE abil), kuid need jäävad siiski ringtee lahendusteks. Samal ajal võimaldab SQL Serveri graafikandmete funktsionaalsus hõlpsasti töödelda mitut hierarhia taset. Niiviisi lihtsustuvad nii andmemudel kui ka päringud, mis omakorda suurendab nende tõhusust. Koodimaht väheneb oluliselt.

Graafikandmed on väljendusrikas keel keerukate süsteemide esitlemiseks. Seda tehnoloogiat kasutatakse juba üsna laialdaselt IT-sektoris valdkondades nagu sotsiaalmeedia, pettuste ennetamise süsteemid, IT-võrkude analüüs, sotsiaalsed soovitused, toote- ja sisusoovitused.

SQL Serveri graafikandmete funktsionaalsus sobib stsenaariumiteks, kus andmed on omavahel tugevalt seotud ja neil on selgelt määratletud seosed.

Graafikandmete mudel

Graafik – see on hulga tippe (sõlmi, node) ja servi (seoseid, edge). Tippude kaudu esindavad üksused ja servade kaudu seosed, mille atribuutides võib sisalduda teave.

Graafikandmebaas mudeldab üksusi graafina, nagu see on määratletud graafiteoorias. Andmestruktuurid on tippud ja servad. Atribuudid on tippude ja servade omadused. Seos on tippude vaheline ühendus.

Erinevalt muudest andmemudelitest on graafikandmetes prioriteet suhete vahel üksuste. Seetõttu ei ole vaja seoseid arvutada, kasutades välist võtmeid või muid meetodeid. Saame luua keerulisi andmemudeleid, kasutades ainult tippude ja servade abstraktsioone.

Kaasaegses maailmas nõuab suhete modelleerimine üha keerukamaid meetodeid. SQL Server 2017 pakub sidemete modelleerimiseks graafide andmebaasi võimalusi. Graafi tipud ja servad esitatakse kui uued tabelite tüübid: NODE ja EDGE. Graafi päringute jaoks kasutatakse uut T-SQL funktsiooni nimega MATCH(). Kuna see funktsionaalsus on integreeritud SQL Server 2017-sse, saab seda kasutada teie olemasolevates andmebaasides ilma mingisuguste konversioonideta.

Graafimudeli kasu

Praegu nõuavad äri ja kasutajad rakendusi, mis töötavad järjest suuremate andmemahtudega, oodates samal ajal kõrget jõudlust ja usaldusväärsust. Andmete esitamine graafina pakub mugavaid vahendeid keeruliste suhete käsitlemiseks. See lähenemine aitab lahendada paljusid probleeme ja aitab saavutada tulemusi antud konteksti raames.

Tundub, et tulevikus saavad paljud rakendused graafide andmebaaside kasutamisest kasu.

Andmete modelleerimine: relatsioonilisest mudelist graafimudeliks

Näide

Vaatame näidet organisatsioonilisest struktuurist töötajate hierarhiaga: töötaja on alluvuses juhile, juht — vanemjuhile ja nii edasi. Olenevalt konkreetsetest ettevõtetest võib selles hierarhias olla mis tahes arv tasandeid. Kuid tasandite arvu suurenedes muutub suhete arvutamine relatsioonilises andmebaasis üha keerulisemaks. Töötajate, turunduse hierarhia või sotsiaalsete suhete esitlemine on selles üsna keeruline. Vaatame, kuidas SQL Graph saab lahendada erinevate hierarhia tasemete töötlemise probleemi.

Selle näite jaoks loome lihtsa andmemudeli. Loome töötajate tabeli EMP identifikaatoriga EMPNO ja veeruga MGR, mis näitab töötaja juhendi (juhi) identifikaatorit. Kogu teave hierarhia kohta salvestatakse selles tabelis ja seda saab pärida veergude abil. EMPNO ja MGR.

Järgmises joonises on kujutatud sama organisatsioonistruktuuri mudelit, kus on neli taset, tuttavamal kujul. Töötajad on graafi tipud tabelist EMP. Entiteet „töötaja“ on seotud iseenda ka „alluva“ (ReportsTo) suhte kaudu. Graafi mõistetes on suhe serv (EDGE), mis ühendab töötajate sõlme (NODE).

Lähme loome tavalise tabeli EMP ja lisame sinna väärtused vastavalt ülaltoodud diagrammile.

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)

Allpool on näidatud töötajad:

töötaja number EMPNO 7369 allub 7902;
töötaja number EMPNO 7902 allub 7566
töötaja number EMPNO 7566 allub 7839

Nüüd vaatame samu andmeid graafina. Tipp EMPLOYEE on mitmete atribuutidega ja on seotud ennastpidi suhtega "allub" (EmplReportsTo). EmplReportsTo on suhte nimi.

Servade (EDGE) tabelis võivad samuti olla atribuudid.

Loome sõlme tabeli EmpNode

Sõlme loomise süntaks on üsna lihtne: väljendile CREATE TABLE lisatakse lõppu "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;

Nüüd teisendame andmed tavalisest tabelist graafiks. Järgmine INSERT lisab andmed suhetes tabelist EMP.

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

Sõlme tabelis on eraldi veerus $node_id_* salvestatakse sõlme identifikaator JSON vormis. Ülejäänud veergudes on sõlme atribuudid.

Loome servad (EDGE)

Servade tabeli loomine on väga sarnane sõlme tabeli loomisega, välja arvatud see, et kasutatakse võtmesõna "AS EDGE".

CREATE TABLE empReportsTo(Deptno int) AS EDGE

Nüüd määratleme ühendused töötajate vahel, kasutades veerge EMPNO ja MGR. Orgstruktuuri diagrammil on selgelt näha, kuidas kirjutada 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);

Vaikimisi on servade tabelis kolm veergu. Esimene, $edge_id — serva identifikaator JSON-vormingus. Kaks teist ($from_id ja $to_id) näitavad seost sõlmede vahel. Lisaks võivad servadel olla ka täiendavad omadused. Meie juhul on see Deptno.

Süsteemivaated

Süsteemivaates sys.tables ilmuvad kaks uut veergu:

is_edge
is_node

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

SSMS

Graafikuga seotud objektid asuvad Graph Tables kaustas. Sõlmede tabeli ikoon on tähistatud punktiga ja servade tabelid — kahe omavahel seotud ringiga (mis sarnaneb veidi prillidele).

MÄRKUS

Märkuse MÄRKUS on CQL-ist (Cypher Query Language) laenatud. See on efektiivne viis graafi omaduste pärimiseks. CQL algab väljendiga MÄRKUS.

Süntaks

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

Näited

Vaadakem mõningaid näiteid.

Alltoodud päring kuvab töötajad, kellele Smith allub, ja tema juhataja.

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'

Järgmine päring on mõeldud Smithi teise taseme töötajate ja juhtide leidmiseks. Kui eemaldada lause KUS, siis kuvatakse kõik töötajad.

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'

Ja lõpuks, päring kolmanda taseme töötajate ja juhtide jaoks.

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'

Nüüd muutkem suunda, et saada Smithi ülemusi.

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)

Kokkuvõte

SQL Server 2017 on tõestanud end tervikliku ärilahendusena mitmesuguste IT-ülesannete jaoks. SQL Graphi esimene versioon on väga paljutõotav. Isegi mõnedest piirangutest hoolimata on juba praegu piisavalt funktsionaalsust, et uurida graafide võimalusi.

SQL Graphi funktsionaalsus on täielikult integreeritud SQL Engine'iga. Kuid nagu juba mainitud, on SQL Server 2017-l järgmised piirangud:

Polümorfismi tugi puudub.

Toetatakse ainult ühesuunalisi suhteid.
Sõlmedes ei saa $from_id ja $to_id veerge UPDATE kaudu uuendada.
Transitiivseid sulge ei toetata, kuid neid saab CTE abil saada.
In-Memory OLTP objektide tugi on piiratud.
Ajutised tabelid (System-Versioned Temporal Table), ajutised kohalike ja globaalsete tabelite tugi puudub.
Tabeli tüüpe ja tabeli muutujad ei saa deklareerida NODE või EDGE-na.
Kandmine teistes andmebaasides (cross-database queries) ei ole toetatud.
Tavaliste tabelite muutmine graafikuteks pole otse võimalik ega mingeid meistreid (wizard) ei ole.
Graafide kuvamiseks puudub GUI, kuid Power BI-d on võimalik kasutada.