Introduzione ai database grafici SQL Server 2017

Prima dell'inizio del corso «Sviluppatore MS SQL Server» abbiamo preparato per voi un'altra traduzione utile.

Le basi di dati a grafo rappresentano una tecnologia importante per gli specialisti delle basi di dati. Cerco di seguire le innovazioni e le nuove tecnologie in questo settore e, dopo aver lavorato con basi di dati relazionali e NoSQL, vedo che il ruolo delle basi di dati a grafo sta crescendo sempre di più. Quando si lavora con dati gerarchici complessi, non solo le basi di dati tradizionali risolvono poco, ma anche le NoSQL. Spesso, con l'aumento del numero di livelli di connessione e della dimensione del database, si osserva una diminuzione delle prestazioni. E con il complicarsi delle interrelazioni cresce anche il numero di JOIN.

Certo, nel modello relazionale ci sono soluzioni per lavorare con gerarchie (ad esempio, utilizzando CTE ricorsivi), ma rimane comunque un modo indiretto. Tuttavia, la funzionalità delle basi di dati a grafo di SQL Server consente di gestire facilmente più livelli di gerarchia. Sia il modello di dati che le query vengono semplificati, aumentando quindi la loro efficienza. Si riduce significativamente la quantità di codice.

Le basi di dati a grafo sono un linguaggio espressivo per rappresentare sistemi complessi. Questa tecnologia è già ampiamente utilizzata nell'industria IT in settori come i social network, i sistemi antifrode, l'analisi delle reti IT, le raccomandazioni sociali, e le raccomandazioni sui prodotti e contenuti.

La funzionalità delle basi di dati a grafo in SQL Server è adatta per scenari in cui i dati sono fortemente interconnessi e hanno relazioni ben definite.

Modello di dati a grafo

Un grafo è un insieme di vertici (nodi, node) ed edge (relazioni, edge). I vertici rappresentano entità e gli edge rappresentano relazioni, i cui attributi possono contenere informazioni.

Un database a grafo modella le entità sotto forma di grafo come definito nella teoria dei grafi. Le strutture dati sono i vertici e gli edge. Gli attributi rappresentano le proprietà dei vertici e degli edge. La relazione è la connessione tra i vertici.

A differenza di altri modelli di dati, nelle basi di dati a grafo le relazioni tra entità sono prioritarie. Pertanto, non è necessario calcolare le relazioni attraverso chiavi esterne o altri metodi. È possibile creare modelli di dati complessi utilizzando solo le astrazioni di vertici e edge.

Nel mondo moderno, la modellizzazione delle relazioni richiede metodologie sempre più complesse. Per la modellizzazione delle relazioni, SQL Server 2017 offre le possibilità dei database a grafo. I vertici e gli archi del grafo sono rappresentati come nuovi tipi di tabelle: NODE e EDGE. Per le query sul grafo si utilizza una nuova funzione T-SQL chiamata MATCH(). Poiché questa funzionalità è integrata in SQL Server 2017, può essere utilizzata nei vostri database esistenti senza la necessità di alcuna conversione.

Vantaggi del modello a grafo

Attualmente, le aziende e gli utenti richiedono applicazioni che operano con volumi di dati sempre più grandi, aspettandosi nel contempo elevate prestazioni e affidabilità. La rappresentazione dei dati come grafo offre mezzi comodi per gestire relazioni complesse. Questo approccio consente di risolvere molti problemi e aiuta a ottenere risultati all'interno di un contesto specifico.

A quanto pare, in futuro molte applicazioni potranno trarre vantaggio dall'uso dei database a grafo.

Modellizzazione dei dati: dal modello relazionale al modello a grafo

Esempio

Consideriamo un esempio di struttura organizzativa con una gerarchia di dipendenti: un dipendente riporta a un manager, il manager a un senior manager e così via. A seconda dell'azienda specifica, ci possono essere diversi livelli in questa gerarchia. Tuttavia, man mano che il numero dei livelli aumenta, il calcolo delle relazioni in un database relazionale diventa sempre più complesso. Non è facile rappresentare una gerarchia di dipendenti, una gerarchia nel marketing o le connessioni nei social network. Vediamo come l'SQL Graph può risolvere il problema della gestione di vari livelli di gerarchia.

Per questo esempio, creiamo un semplice modello di dati. Creiamo una tabella per i dipendenti EMP con un identificatore EMPNO e una colonna MGR, che indica l'identificativo del supervisore (manager) del dipendente. Tutte le informazioni sulla gerarchia sono memorizzate in questa tabella e possono essere interrogate tramite le colonne EMPNO e MGR.

Nel diagramma seguente è rappresentato lo stesso modello di struttura organizzativa con quattro livelli di nidificazione in una forma più familiare. I dipendenti sono i vertici del grafo della tabella EMP. L'entità "dipendente" è collegata a se stessa attraverso una relazione "riporta a" (ReportsTo). In termini di grafo, la relazione è un arco (EDGE) che collega i nodi (NODE) dei dipendenti.

Creiamo una normale tabella EMP e aggiungiamo valori secondo il diagramma sopra.

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)

Nell'immagine sottostante sono mostrati i dipendenti:

• l'impiegato con EMPNO 7369 è subordinato a 7902;
• l'impiegato con EMPNO 7902 è subordinato a 7566
• l'impiegato con EMPNO 7566 è subordinato a 7839

Ora diamo un'occhiata alla rappresentazione degli stessi dati come un grafo. Il nodo EMPLOYEE ha diversi attributi ed è collegato a se stesso tramite una relazione 'si riferisce a' (EmplReportsTo). EmplReportsTo è il nome della relazione.

Nella tabella dei bordi (EDGE) possono esserci anche attributi.

Creiamo la tabella dei nodi EmpNode

La sintassi per la creazione di un nodo è piuttosto semplice: all'espressione CREATE TABLE si aggiunge alla fine "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;

Ora trasformiamo i dati dalla normale tabella in un grafo. Il seguente INSERISCI inserisce i dati dalla tabella relazionale EMP.

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

Nella tabella dei nodi in una colonna speciale $node_id_* viene memorizzato l'identificatore del nodo in formato JSON. Negli altri campi di questa tabella si trovano gli attributi del nodo.

Creiamo bordi (EDGE)

La creazione della tabella dei bordi è molto simile alla creazione della tabella dei nodi, eccetto per il fatto che si usa la parola chiave "AS EDGE".

CREATE TABLE empReportsTo(Deptno int) AS EDGE

Ora definiamo le relazioni tra i dipendenti utilizzando le colonne EMPNO e MGR. Dal diagramma dell'organigramma è chiaro come scrivere INSERISCI.

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

La tabella degli archi predefiniti è composta da tre colonne. La prima, $edge_id — è l'identificatore dell'arco in formato JSON. Le altre due ($from_id e $to_id) rappresentano la connessione tra i nodi. Inoltre, gli archi possono avere proprietà aggiuntive. Nel nostro caso, si tratta di Deptno.

Viste di sistema

Nella vista di sistema sys.tables sono state aggiunte due nuove colonne:

1. is_edge
2. is_node

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

SSMS

Gli oggetti correlati ai grafi si trovano nella cartella Tabelle Grafico. L'icona della tabella dei nodi è contrassegnata da un punto, mentre le tabelle degli archi — da due cerchi collegati (simile a occhiali).

L'espressione MATCH

Espressione MATCH proviene dal CQL (Cypher Query Language). È un modo efficace per interrogare le proprietà del grafo. CQL inizia con l'espressione MATCH.

Sintassi

MATCH ()

::=
    { {
                 	{ <-(  )- }
               	| { -(  )-> }
             	
             	}
 	}
 	[ { AND } { (  ) } ]
 	[ ,...n ]

::=
    node_table_name | node_alias

::=
    edge_table_name | edge_alias

Esempi

Vediamo alcuni esempi.

La query sottostante mostra i dipendenti che riferiscono a Smith e il suo manager.

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'

La seguente query è destinata alla ricerca di dipendenti e manager di secondo livello per Smith. Se si rimuove la frase DOVE, nel risultato verranno visualizzati tutti i dipendenti.

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'

E, infine, la query per i dipendenti e manager di terzo livello.

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'

Ora modifichiamo la direzione per ottenere i superiori di 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)

Conclusione

SQL Server 2017 si è affermato come una soluzione enterprise completa per diverse sfide IT aziendali. La prima versione di SQL Graph è molto promettente. Anche se ci sono alcune limitazioni, c'è già abbastanza funzionalità per esplorare le potenzialità dei grafi.

La funzionalità SQL Graph è completamente integrata nel SQL Engine. Tuttavia, come già accennato, in SQL Server 2017 ci sono le seguenti limitazioni:

Nessun supporto per il polimorfismo.

• Sono supportate solo relazioni unidirezionali.
• Non è possibile aggiornare le colonne $from_id e $to_id degli archi tramite UPDATE.
• Non sono supportate le chiusure transitivi (transitive closure), ma possono essere ottenute tramite CTE.
• Supporto limitato per gli oggetti In-Memory OLTP.
• Non sono supportate le tabelle temporali (System-Versioned Temporal Table), tabelle temporanee locali e globali.
• I tipi tabellari e le variabili tabellari non possono essere dichiarati come NODE o EDGE.
• Non sono supportate le query tra database (cross-database queries).
• Non esiste un modo diretto o un wizard per convertire tabelle normali in grafiche.
• Non c'è un'interfaccia grafica per la visualizzazione dei grafi, ma si può utilizzare Power BI.

Leggi anche:

• Come SQL Server utilizza i bitmap filter

Fonte: habr.com