Bevezetés a gráf alapú adatbázisokba SQL Server 2017

A kurzus indítása előtt „MS SQL Server Fejlesztő” még egy hasznos fordítást készítettünk önök számára.

A gráf alapú adatbázisok fontos technológia az adatbázis szakemberek számára. Igyekszem nyomon követni az újításokat és új technológiákat ebben a területen, és a relációs és NoSQL adatbázisokkal végzett munka után látom, hogy a gráf alapú adatbázisok szerepe folyamatosan növekszik. Összetett hierarchikus adatokkal való munkavégzéskor nemcsak a hagyományos adatbázisok, hanem a NoSQL is hatékonytalan. Gyakran, ahogy a kapcsolatok szintjei és az adatbázis mérete nő, csökken a teljesítmény. A kapcsolatok bonyolultságának növekedésével a JOIN-ok száma is növekszik.

Természetesen a relációs modellben léteznek megoldások a hierarchiák kezelésére (például rekurzív CTE-k segítségével), de ez mindenképpen csak kerülőutak marad. Eközben az SQL Server gráf alapú adatbázisainak funkcionalitása lehetővé teszi, hogy könnyedén kezeljünk több szint hierarchiát. Egyszerűsödik mind az adatmodellek, mind a lekérdezések, ami növeli a hatékonyságukat. Jelentősen csökken a kód mennyisége.

A gráf alapú adatbázisok kifejező nyelvet biztosítanak összetett rendszerek megjelenítésére. Ezt a technológiát már széles körben használják az IT-iparban olyan területeken, mint a közösségi média, a csalásmegelőző rendszerek, az IT-hálózatok elemzése, a közösségi ajánlások, valamint a termék- és tartalomajánlások.

Az SQL Server gráf alapú adatbázisainak funkcionalitása jól alkalmazható olyan forgatókönyvekben, ahol az adatok erősen kapcsolódnak egymáshoz és pontosan meghatározott kapcsolatokkal rendelkeznek.

Gráf alapú adatmodell

A gráf egy csúcsok (nódok) és élek (kapcsolatok) halmaza. A csúcsok entitásokat képviselnek, az élek pedig kapcsolatokat, amelyek attribútumaiban információ található.

A gráf alapú adatbázisok az entitásokat gráf formájában modellezik, ahogyan azt a gráfelmélet meghatározza. Az adatszerkezetek csúcsok és élek. Az attribútumok a csúcsok és élek tulajdonságai. A kapcsolat a csúcsok összekapcsolása.

Más adatmodellekhez képest a gráf alapú adatbázisokban a kölcsönhatások előnyben részesülnek az entitások között. Ezért nem szükséges a kapcsolatok számításához külső kulcsok vagy más módszerek használata. Bonyolult adatmodellek hozhatók létre, csupán a csúcsok és élek absztrakcióival.

A modern világban a kapcsolatok modellezése egyre bonyolultabb módszereket igényel. Az SQL Server 2017 grafikus adatbázisainak lehetőségeket kínál a kapcsolatok modellezésére. A gráf csúcsait és éleit új táblatípusokként reprezentálják: NODE és EDGE. A gráfkérdések megválaszolásához új T-SQL függvényt használunk, amelyet MATCH() néven ismerünk. Mivel ez a funkció beépített az SQL Server 2017-be, így meglévő adatbázisaiban is használható átalakítás nélkül.

A gráfmodellek előnyei

Ma a vállalkozások és felhasználók olyan alkalmazásokat várnak el, amelyek egyre nagyobb adathalmazon képesek működni, miközben magas teljesítményt és megbízhatóságot biztosítanak. Az adatok gráf formájában történő ábrázolása kényelmes eszközöket kínál a bonyolult kapcsolatok feldolgozásához. Ez a megközelítés számos problémát megoldhat, és segít az eredmények elérésében a megadott kontextus keretein belül.

Úgy tűnik, hogy a jövőben sok alkalmazás profitálhat a grafikus adatbázisok használatából.

Adatok modellezése: relációs modellből gráfmodellbe

támadásra

Nézzünk meg egy példát egy szervezeti struktúrára munkavállalói hierarchiával: egy munkavállaló alárendelt egy menedzsernek, a menedzser pedig egy idősebb menedzsernek, és így tovább. A konkrét vállalat függvényében ennek a hierarchiának tetszőleges számú szintje lehet. Azonban ahogy növekszik a szintek száma, a relációs adatbázisban a kapcsolatok számítása egyre összetettebbé válik. Nehezebbé válik a munkavállalók hierarchiájának, a marketinghierarchiának vagy a közösségi kapcsolatoknak a megjelenítése. Nézzük meg, hogyan oldható meg a különböző szintű hierarchia kezelése SQL Graph segítségével.

Ehhez a példához készítsünk egy egyszerű adatmodellt. Hozzunk létre egy munkavállalói táblát EMP azonosítóval EMPNO és egy oszloppal MGR, amely a munkavállaló megfelelő menedzsere (felügyelője) azonosítójára utal. Az összes hierarchiai információ ebben a táblában tárolódik, és a oszlopok segítségével lekérdezhető. EMPNO és MGR.

A következő diagram ugyanazt az org struktúrát mutatja be négy szintű beágyazottsággal egy megszokottabb formában. A munkavállalók a gráf csúcsait képviselik az EMPtáblából. A "munkavállaló" entitás a "jelent" (ReportsTo) kapcsolaton keresztül kapcsolódik saját magához. A gráf kifejezéseivel a kapcsolat élt (EDGE) jelent, amely a munkavállalói csúcsokat (NODE) köti össze.

Hozzuk létre a szokásos táblázatot EMP és adjunk hozzá értékeket az előző ábra szerint.

CREATE TABLE EMP
(EMPNO INT NOT NULL,
ENAME VARCHAR(20),
JOB VARCHAR(10),
MGR INT,
JOINDATE DATETIME,
SALARY DECIMAL(7, 2),
COMMISION 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)

Az alábbi ábrán láthatóak a munkavállalók:

• az alkalmazott EMPNO 7369 a 7902-nek van alárendelve;
• az alkalmazott EMPNO a 7902 a 7566-nak van alárendelve
• az alkalmazott EMPNO a 7566 a 7839-nek van alárendelve

Most nézzük meg az adatokat gráf formájában. Az EMPLOYEE csúcs több attribútummal rendelkezik és önmagához az 'alárendelés' (EmplReportsTo) kapcsolattal van kapcsolva. Az EmplReportsTo a kapcsolat neve.

A él diagram (EDGE) táblában is lehetnek attribútumok.

Hozzunk létre egy csomópont táblát EmpNode

A csomópont létrehozásának szintaxisa meglehetősen egyszerű: a kifejezéshez CREATE TABLE a végére hozzáadjuk a 'AS NODE'-ot.

CREATE TABLE dbo.EmpNode(
ID Int Identity(1,1),
EMPNO NUMERIC(4) NOT NULL,
ENAME VARCHAR(10),
MGR NUMERIC(4),
DNO INT
) AS NODE;

Most alakítsuk át az adatokat a hagyományos táblázatból gráf struktúrába. A következő INSERT beszúrja az adatokat a relációs táblából EMP.

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

A csomópontok táblájában egy külön oszlopban $node_id_* tárolja a csomópont azonosítóját JSON formátumban. A táblázat többi oszlopában a csomópont attribútumai találhatók.

Hozzunk létre él (EDGE) kapcsolatokat

Az él táblázat létrehozása nagyon hasonló a csomópont táblázat létrehozásához, kivéve, hogy a kulcsszó 'AS EDGE'-et.

CREATE TABLE empReportsTo(Deptno int) AS EDGE

Most határozzuk meg a kapcsolatok közötti kapcsolatot az alkalmazottak között, a következő oszlopok felhasználásával EMPNO és MGR. Az org struktúra diagram jól láthatóan mutatja, hogyan kell megírni 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);

Az alapértelmezett élek táblája három oszlopból áll. Az első, $edge_id — az él azonosítója JSON formátumban. A másik kettő ($from_id és $to_id) a csomópontok közötti kapcsolatot képviseli. Ezen kívül az élek további tulajdonságokat is hordozhatnak. Az esetünkben ez Deptno.

Rendszerszemléletek

A rendszerszemléletben sys.tables két új oszlop jelent meg:

1. is_edge
2. is_node

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

SSMS

A grafikonokkal kapcsolatos objektumok a Grafikonok mappában találhatók. A csomóponttáblák ikonját pont jelzi, míg az éltáblák ikonját két összekapcsolt kör (ami kicsit hasonlít a szemüvegre) jelzi.

A MATCH kifejezés

Kifejezés MATCH a CQL-ból (Cypher Query Language) van átvéve. Ez egy hatékony módja a gráf tulajdonságainak lekérdezésére. A CQL egy MATCH kifejezéssel kezdődik MATCH.

Szintaxis

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

Példák

Nézzünk meg néhány példát.

Az alábbi lekérdezés a Smith által irányított alkalmazottakat és managerét társítja.

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'

A következő lekérdezés célja a második szintű alkalmazottak és menedzserek keresése Smith számára. Ha eltávolítjuk az ajánlatot AHOL, akkor az összes alkalmazott megjelenik az eredmények között.

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'

Végül, az alkalmazottak és menedzserek harmadik szintjének lekérdezése.

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'

Most változtassuk meg az irányt, hogy megszerezzük Smith főnökeit.

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)

Zárás

Az SQL Server 2017 megbízható vállalati megoldásként bizonyította magát a különböző IT-üzleti feladatokhoz. Az SQL Graph első verziója ígéretes. Még néhány korlátozás ellenére is elegendő funkcionalitás áll rendelkezésre a gráfok lehetőségeinek felfedezéséhez.

Az SQL Graph funkcionalitása teljes mértékben integrálva van az SQL Engine-be. Azonban, ahogy említettem, az SQL Server 2017-ben a következő korlátozások vannak:

Nincs támogatás a polimorfizmus számára.

• Csak egyirányú kapcsolatok támogatottak.
• A élek oszlopait ($from_id és $to_id) nem lehet frissíteni az UPDATE használatával.
• Nincsenek támogatott transzitiv zárások (transitive closure), de ezek CTE segítségével megszerezhetők.
• Korlátozott az In-Memory OLTP objektumok támogatása.
• Nem támogatottak a temporális táblák (System-Versioned Temporal Table), ideiglenes helyi és globális táblák.
• A táblázatos típusokat és táblázatos változókat nem lehet NODE vagy EDGE-ként nyilvántartani.
• Nem támogatottak az adatbázisok közötti lekérdezések (cross-database queries).
• Nincs közvetlen mód vagy varázsló (wizard) a normál táblák gráfokká alakítására.
• A gráfok megjelenítésére nincs GUI, de használható a Power BI.

Tovább olvasva:

• Hogyan használja a SQL Server a bitmap szűrőket

Forrás: habr.com