Introducere în bazele de date grafice SQL Server 2017

În pragul începerii cursului «MS SQL Server Developer» am pregătit pentru tine o altă traducere utilă.

Sistemele de baze de date grafice sunt o tehnologie importantă pentru specialiștii în baze de date. Încerc să urmăresc inovațiile și noile tehnologii din acest domeniu și, după experiența cu baze de date relaționale și NoSQL, observ că rolul bazelor de date grafice devine tot mai semnificativ. În lucrul cu datele ierarhice complexe, nu doar bazele de date tradiționale sunt ineficiente, ci și bazele de date NoSQL. Adesea, pe măsură ce crește numărul nivelurilor de legături și dimensiunea bazei de date, se observă o scădere a performanței. Și pe măsură ce relațiile devin mai complexe, crește și numărul JOIN.

Desigur, în modelul relațional există soluții pentru gestionarea ierarhiilor (de exemplu, prin utilizarea CTE recursive), dar acestea rămân în continuare soluții de ocolire. Totuși, funcționalitatea bazelor de date grafice din SQL Server permite procesarea cu ușurință a mai multor niveluri de ierarhie. Atât modelul de date, cât și interogările devin mai simple, ceea ce crește eficiența acestora. Volumul de cod se reduce semnificativ.

Bazele de date grafice oferă un limbaj expresiv pentru reprezentarea sistemelor complexe. Această tehnologie este deja utilizată pe scară largă în industria IT în domenii precum rețelele sociale, sistemele antifraudă, analiza rețelelor IT, recomandările sociale, precum și recomandările de produse și conținut.

Funcționalitatea bazelor de date grafice în SQL Server se potrivește scenariilor în care datele sunt foarte interconectate și au legături clar definite.

Modelul de date grafic

Un grafic este un set de vârfuri (noduri, node) și muchii (legături, edge). Vârfurile reprezintă entități, iar muchiile reprezintă relații, care pot conține informații în atributele lor.

O bază de date grafică modelează entitățile sub forma unui grafic așa cum este definit în teoria grafurilor. Structurile de date sunt vârfuri și muchii. Atributele sunt proprietățile vârfurilor și muchiilor. O legătură reprezintă conexiunea dintre vârfuri.

Spre deosebire de alte modele de date, în bazele de date grafice, relațiile dintre entități sunt priorități. Prin urmare, nu este necesar să se calculeze relațiile prin chei externe sau prin alte metode. Este posibil să se creeze modele de date complexe folosind doar abstracții ale vârfurilor și muchiilor.

În lumea modernă, modelarea relațiilor necesită metodologii din ce în ce mai complexe. Pentru modelarea relațiilor, SQL Server 2017 oferă capabilități pentru baze de date grafice. Vârfurile și muchiile graficului sunt reprezentate sub formă de noi tipuri de tabele: NODE și EDGE. Pentru interogările la graf se folosește o nouă funcție T-SQL numită MATCH(). Deoarece această funcționalitate este integrată în SQL Server 2017, o puteți utiliza în bazele de date existente fără a fi nevoie de conversie.

Beneficiile modelului grafic

În prezent, afacerile și utilizatorii solicită aplicații care să funcționeze cu un volum din ce în ce mai mare de date, așteptând în același timp performanță ridicată și fiabilitate. Reprezentarea datelor sub formă de grafic oferă instrumente convenabile pentru gestionarea relațiilor complexe. Această abordare permite rezolvarea multor probleme și ajută la obținerea de rezultate în cadrul contextului dat.

Se pare că în viitor, multe aplicații vor putea beneficia de utilizarea bazelor de date grafice.

Modelarea datelor: de la modelul relațional la modelul grafic

Exemplu

Să luăm un exemplu de structură organizațională cu o ierarhie de angajați: un angajat subordonează unui manager, managerul unui senior manager și așa mai departe. În funcție de compania specifică, în această ierarhie pot exista un număr variat de niveluri. Însă, pe măsură ce numărul nivelurilor crește, calcularea relațiilor în baza de date relațională devine din ce în ce mai complicată. Este destul de dificil să se reprezinte ierarhia angajaților, ierarhia în marketing sau relațiile din rețelele sociale. Să vedem cum, prin SQL Graph, putem rezolva problema de gestionare a diferitelor niveluri ale ierarhiei.

Pentru acest exemplu, să creăm un model de date simplu. Vom crea o tabelă pentru angajați. EMP cu identificatorul EMPNO și o coloană MGR, care indică identificatorul managerului angajatului. Toate informațiile despre ierarhie sunt stocate în această tabelă și pot fi interogate folosind coloanele. EMPNO și MGR.

În următoarea diagramă este reprezentat același model al structurii organizaționale cu patru niveluri de adâncime într-o formă mai familiară. Angajații sunt vârfurile graficului din tabel. EMPEntitatea „angajat” este legată de ea însăși prin relația „subordonează” (ReportsTo). În termeni de grafic, relația este o muchie (EDGE) care leagă nodurile (NODE) ale angajaților.

Să creăm un tabel obișnuit EMP și să adăugăm acolo valori conform diagramului de mai sus.

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 imaginea de mai jos sunt prezentate angajații:

• angajatul cu EMPNO 7369 se supune lui 7902;
• angajatul cu EMPNO 7902 se supune lui 7566
• angajatul cu EMPNO 7566 se supune lui 7839

Acum să aruncăm o privire asupra reprezentării acestor date sub formă de graf. Vârful EMPLOYEE are mai multe atribute și este legat de sine prin relația „se supune” (EmplReportsTo). EmplReportsTo este denumirea relației.

În tabelul muchiilor (EDGE) pot fi de asemenea prezente atribute.

Să creăm tabelul nodurilor EmpNode

Sintaxa de creare a unui nod este destul de simplă: se adaugă la expresie CREATE TABLE la sfârșit se adaugă „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;

Acum să transformăm datele din tabelul obișnuit în graf. Următorul INSERT insează datele din tabelul relațional EMP.

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

În tabelul nodurilor, într-o coloană specială $node_id_* este stocat identificatorul nodului sub formă de JSON. În celelalte coloane ale acestui tabel se află atributele nodului.

Creăm muchii (EDGE)

Crearea tabelului de muchii este foarte similară cu crearea tabelului nodurilor, cu excepția faptului că se folosește cuvântul cheie „AS EDGE”.

CREATE TABLE empReportsTo(Deptno int) AS EDGE

Acum să definim relațiile între angajați, folosind coloanele EMPNO și MGR. Din diagrama structurii organizaționale se poate vedea bine cum se scrie INSERT.

INSERARE ÎN empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 1),
   	(SELECT $node_id FROM EmpNode WHERE id = 13),20);
INSERARE ÎN empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 2),
   	(SELECT $node_id FROM EmpNode WHERE id = 6),10);
INSERARE ÎN empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 3),
   	(SELECT $node_id FROM EmpNode WHERE id = 6),10)
INSERARE ÎN empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 4),
   	(SELECT $node_id FROM EmpNode WHERE id = 9),30);
INSERARE ÎN empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 5),
   	(SELECT $node_id FROM EmpNode WHERE id = 6),30);
INSERARE ÎN empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 6),
   	(SELECT $node_id FROM EmpNode WHERE id = 9),30);
INSERARE ÎN empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 7),
   	(SELECT $node_id FROM EmpNode WHERE id = 9),30);
INSERARE ÎN empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 8),
   	(SELECT $node_id FROM EmpNode WHERE id = 4),30);
INSERARE ÎN empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 9),
   	(SELECT $node_id FROM EmpNode WHERE id = 9),30);
INSERARE ÎN empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 10),
   	(SELECT $node_id FROM EmpNode WHERE id = 6),30);
INSERARE ÎN empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 11),
   	(SELECT $node_id FROM EmpNode WHERE id = 8),30);
INSERARE ÎN empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 12),
   	(SELECT $node_id FROM EmpNode WHERE id = 6),30);
INSERARE ÎN empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 13),
   	(SELECT $node_id FROM EmpNode WHERE id = 4),30);
INSERARE ÎN empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 14),
   	(SELECT $node_id FROM EmpNode WHERE id = 7),30);

Tabela de muchii implicită constă din trei coloane. Prima, $edge_id — identificatorul muchiei în format JSON. Cele două alte ($from_id și $to_id) reprezintă conexiunea între noduri. În plus, muchiile pot avea proprietăți suplimentare. În acest caz, acestea sunt Deptno.

Vizualizări sistemice

În vizualizarea sistemică sys.tables au apărut două coloane noi:

1. is_edge
2. is_node

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

SSMS

Obiectele legate de grafuri sunt situate în folderul Tabele Grafice. Pictograma tabelului nodurilor este marcată cu un punct, iar tabelele de muchii - cu două cercuri conectate (care seamănă puțin cu ochelarii).

Expresia MATCH

Expresia MATCH este preluată din CQL (Cassandra Query Language). Acesta este un mod eficient de a interoga proprietățile grafului. CQL începe cu expresia MATCH.

Sintaxa

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

Exemple

Să ne uităm la câteva exemple.

Interogarea de mai jos afișează angajații care îi raportau lui Smith și managerul său.

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'

Următorul query este destinat căutării angajaților și managerilor de nivel secundar pentru Smith. Dacă eliminăm propoziția WHERE, atunci vor fi afișați toți angajații.

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'

Și, în final, query-ul pentru angajații și managerii de nivel terț.

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'

Acum să schimbăm direcția pentru a obține șefii lui 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)

Concluzie

SQL Server 2017 s-a dovedit a fi o soluție corporate robustă pentru diverse sarcini IT de afaceri. Prima versiune SQL Graph este foarte promițătoare. Chiar și cu unele limitări, există deja suficiente funcționalități pentru a explora posibilitățile grafurilor.

Funcționalitatea SQL Graph este complet integrată în SQL Engine. Cu toate acestea, așa cum s-a menționat, SQL Server 2017 are următoarele limitări:

Nu este suport pentru polimorfism.

• Se sprijină doar relațiile unidirecționale.
• Coloanele $from_id și $to_id de pe muchii nu pot fi actualizate prin UPDATE.
• Nu se suportă închiderile tranzitive (transitive closure), dar acestea pot fi obținute prin CTE.
• Suportul pentru obiectele In-Memory OLTP este limitat.
• Tabelele temporale (System-Versioned Temporal Table), tabelele temporare locale și globale nu sunt acceptate.
• Tipurile tabelare și variabilele tabelare nu pot fi declarate ca NODE sau EDGE.
• Nu se suportă interogările între baze de date (cross-database queries).
• Nu există o modalitate directă sau un asistent (wizard) pentru a transforma tabelele obișnuite în grafuri.
• Pentru vizualizarea graficelor nu există GUI, dar se poate folosi Power BI.

Citește mai mult:

• Cum utilizează SQL Server filtrele bitmap

Sursa: habr.com