Introduzione ai database grafici di SQL Server 2017

In attesa dell'avvio del corso «MS SQL Server Developer» abbiamo preparato per voi un'altra traduzione utile.

I database grafici sono una tecnologia importante per i professionisti dei database. Cerco di rimanere aggiornato sulle innovazioni e le nuove tecnologie in questo campo e, dopo aver lavorato con database relazionali e NoSQL, vedo che il ruolo dei database grafici sta crescendo sempre di più. Nella gestione di dati gerarchici complessi, non solo i database tradizionali, ma anche i NoSQL si rivelano poco efficaci. Spesso, con l'aumento dei livelli di collegamento e delle dimensioni del database, si osserva una diminuzione delle prestazioni. E all'aumentare della complessità delle relazioni, cresce anche il numero di JOIN.

Certo, nel modello relazionale ci sono soluzioni per lavorare con le gerarchie (ad esempio, utilizzando CTE ricorsivi), ma rimangono comunque delle soluzioni alternative. Tuttavia, le funzionalità dei database grafici di SQL Server permettono di gestire più facilmente diversi livelli di gerarchia. Sia il modello dati che le query vengono semplificati, aumentando così la loro efficienza. Viene notevolmente ridotto il volume 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 ambiti come i social media, i sistemi antifrode, l'analisi delle reti IT, le raccomandazioni sociali e le raccomandazioni su prodotti e contenuti.

Le funzionalità delle basi di dati a grafo in SQL Server sono adatte a scenari in cui i dati sono fortemente correlati e presentano relazioni ben definite.

Modello di dati a grafo

Un grafo è un insieme di vertici (nodi, node) e archi (relazioni, edge). I vertici rappresentano entità e gli archi rappresentano collegamenti, le cui caratteristiche possono contenere informazioni.

Una base di dati a grafo modella le entità come un grafo, come definito nella teoria dei grafi. Le strutture dati sono i vertici e gli archi. Gli attributi sono le proprietà dei vertici e degli archi. La connessione è l'unione dei vertici.

A differenza di altri modelli di dati, nei database a grafo le relazioni tra le entità sono prioritarie. Pertanto, non è necessario calcolare le relazioni tramite chiavi esterne o altri metodi. È possibile creare modelli di dati complessi utilizzando solo astrazioni di nodi e archi.

Nel mondo moderno, la modellazione delle relazioni richiede metodiche sempre più complesse. Per la modellazione delle relazioni, SQL Server 2017 offre funzionalità dei database a grafo. I nodi e gli archi del grafo sono rappresentati come nuovi tipi di tabelle: NODE e EDGE. Per le query al 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.

I vantaggi del modello a grafo

Attualmente, le aziende e gli utenti richiedono applicazioni in grado di gestire volumi sempre maggiori di dati, mentre si aspettano alta performance e affidabilità. Rappresentare i dati come un grafo offre strumenti utili per gestire relazioni complesse. Questo approccio consente di risolvere numerosi problemi e aiuta a ottenere risultati all'interno di un contesto definito.

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

Modellazione dei dati: dalla modalità relazionale a quella grafica

Esempio

Consideriamo un esempio di struttura organizzativa con una gerarchia di dipendenti: un dipendente risponde a un manager, il manager a un senior manager e così via. A seconda dell'azienda specifica, in questa gerarchia possono esserci diversi livelli. Tuttavia, con l'aumentare del numero di livelli, il calcolo delle relazioni in un database relazionale diventa sempre più complesso. È piuttosto difficile rappresentare la gerarchia dei dipendenti, la gerarchia nel marketing o le connessioni nei social network. Vediamo come 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 dei dipendenti EMP con l'identificativo 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 richieste tramite le colonne. EMPNO e MGR.

Nella seguente diagramma è rappresentato anche 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. La sostanza "dipendente" è connessa a se stessa tramite la 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 i valori in base al 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)

Nella figura riportata sotto sono mostrati i dipendenti:

• il dipendente con EMPNO 7369 riporta a 7902;
• il dipendente con EMPNO 7902 riporta a 7566
• il dipendente con EMPNO 7566 riporta a 7839

Ora diamo un'occhiata alla rappresentazione degli stessi dati sotto forma di grafo. Il nodo EMPLOYEE ha diversi attributi e è collegato a se stesso tramite la relazione "riporta 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 creare 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 convertiamo i dati da una tabella normale a un grafo. Il seguente INSERT 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 colonne di questa tabella si trovano gli attributi del nodo.

Creiamo i bordi (EDGE).

La creazione della tabella dei bordi è molto simile a quella della tabella dei nodi, ad eccezione del 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 MGRLa diagramma organizzativa mostra chiaramente come scrivere. 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);

La tabella dei bordi predefinita consiste in tre colonne. La prima, $edge_id — identificatore dell'arco in formato JSON. Gli altri 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.

Vedi di sistema

Nella vista di sistema sys.tables sono comparsi 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 legati ai grafi si trovano nella cartella Graph Tables. L'icona della tabella dei nodi è contrassegnata da un punto, mentre le tabelle degli archi mostrano due cerchi collegati (che assomigliano un po' a occhiali).

L'espressione MATCH

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

Sintassi

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

Esempi

Diamo un'occhiata ad alcuni esempi.

La query qui sotto mostra i dipendenti che riportano 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 a cercare dipendenti e manager di secondo livello per Smith. Se si rimuove la frase DOVE, il risultato mostrerà 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)
e 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)
e e.ENAME='SMITH'

Adesso cambiamo direzione per ottenere i capi 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 aziendale completa per diverse esigenze IT. La prima versione di SQL Graph è molto promettente. Anche se con alcune limitazioni, già ora vi è un sufficiente numero di funzionalità per esplorare le potenzialità dei grafi.

La funzionalità SQL Graph è completamente integrata nel SQL Engine. Tuttavia, come già detto, SQL Server 2017 presenta i seguenti limiti:

Nessun supporto per il polimorfismo.

• Sono supportate solo le 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 di tabella e le variabili di tabella non possono essere dichiarati come NODE o EDGE.
• Non sono supportate le query tra database (cross-database queries).
• Non esiste un modo diretto né un wizard per convertire tabelle normali in tabelle grafiche.
• Non c'è un GUI per visualizzare i grafi, ma si può utilizzare Power BI.

Leggi di più:

• Come SQL Server utilizza i bitmap filter.

Fonte: habr.com