Introduzione ai database a grafo di SQL Server 2017

Prima dell'inizio del corso "Sviluppatore MS SQL Server" Abbiamo preparato un'altra traduzione utile per te.

I database a grafo sono una tecnologia importante per i professionisti dei database. Cerco di seguire le innovazioni e le nuove tecnologie in questo settore e dopo aver lavorato con database relazionali e NoSQL, vedo che il ruolo dei database a grafo sta diventando sempre di più. Quando si lavora con dati gerarchici complessi, non solo i database tradizionali, ma anche NoSQL sono inefficaci. Spesso, con un aumento del numero di livelli di collegamento e delle dimensioni del database, si verifica una diminuzione delle prestazioni. E man mano che le relazioni diventano più complesse, aumenta anche il numero di JOIN.

Naturalmente, nel modello relazionale esistono soluzioni per lavorare con le gerarchie (ad esempio, utilizzando CTE ricorsive), ma si tratta comunque di soluzioni alternative. Allo stesso tempo, la funzionalità dei database a grafo di SQL Server semplifica la gestione di più livelli di gerarchia. Sia il modello di dati che le query sono semplificati e quindi la loro efficienza è aumentata. Riduce significativamente la quantità di codice.

I database a grafo sono un linguaggio espressivo per rappresentare sistemi complessi. Questa tecnologia è già ampiamente utilizzata nel settore IT in aree come i social network, i sistemi antifrode, l'analisi della rete informatica, le raccomandazioni sociali, i consigli sui prodotti e sui contenuti.

La funzionalità di database grafico in SQL Server è adatta per scenari in cui i dati sono altamente interconnessi e presentano relazioni ben definite.

Modello dati grafico

Un grafico è un insieme di vertici (nodi, nodo) e spigoli (relazioni, spigolo). I vertici rappresentano le entità e gli spigoli rappresentano i collegamenti i cui attributi possono contenere informazioni.

Un database grafico modella le entità come un grafico come definito nella teoria dei grafi. Le strutture dati sono vertici e spigoli. Gli attributi sono proprietà di vertici e spigoli. Una connessione è una connessione di vertici.

A differenza di altri modelli di dati, i database a grafo danno la priorità alle relazioni tra le entità. Pertanto, non è necessario calcolare le relazioni utilizzando chiavi esterne o in altro modo. È possibile creare modelli di dati complessi utilizzando solo astrazioni di vertici e bordi.

Nel mondo di oggi, modellare le relazioni richiede tecniche sempre più sofisticate. Per modellare le relazioni, SQL Server 2017 offre funzionalità di database grafico. I vertici e gli spigoli del grafico sono rappresentati come nuovi tipi di tabelle: NODE e EDGE. Le query Graph usano una nuova funzione T-SQL chiamata MATCH(). Poiché questa funzionalità è incorporata in SQL Server 2017, può essere utilizzata nei database esistenti senza la necessità di alcuna conversione del database.

Vantaggi del modello grafico

Al giorno d'oggi, le aziende e gli utenti richiedono applicazioni che funzionino con una quantità sempre maggiore di dati, aspettandosi al tempo stesso prestazioni e affidabilità elevate. La rappresentazione grafica dei dati offre un mezzo conveniente per gestire relazioni complesse. Questo approccio risolve molti problemi e aiuta a ottenere risultati all'interno di un dato contesto.

Sembra che molte applicazioni trarranno vantaggio dai database a grafo in futuro.

Modellazione dei dati: dalla modellazione relazionale a quella grafica

esempio

Diamo un'occhiata a un esempio di struttura organizzativa con una gerarchia di dipendenti: un dipendente riporta a un manager, un manager riporta a un senior manager e così via. A seconda della società specifica, può esserci un numero qualsiasi di livelli in questa gerarchia. Ma con l'aumentare del numero di livelli, il calcolo delle relazioni in un database relazionale diventa sempre più difficile. È abbastanza difficile immaginare la gerarchia dei dipendenti, la gerarchia nel marketing o le connessioni ai social media. Vediamo come SQL Graph può risolvere il problema della gestione di diversi livelli di gerarchia.

Per questo esempio, creiamo un semplice modello di dati. Crea una tabella dei dipendenti EMP con id EMPNO e colonna MGRA che indica l'ID del capo (manager) del dipendente. Tutte le informazioni sulla gerarchia sono memorizzate in questa tabella e possono essere interrogate utilizzando le colonne EMPNO и MGR.

Il diagramma seguente mostra anche lo stesso modello di organigramma con quattro livelli di nidificazione in una forma più familiare. I dipendenti sono i vertici del grafico dalla tabella EMP. L'entità "employee" è collegata a se stessa dalla relazione "submits" (ReportsTo). In termini di grafi, un collegamento è un bordo (EDGE) che collega i nodi (NODE) ​​dei dipendenti.

Creiamo una tabella regolare EMP e aggiungi valori lì 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)

La figura seguente mostra i dipendenti:

• dipendente con EMPNO 7369 è subordinato a 7902;
• dipendente con EMPNO 7902 subordinato a 7566
• dipendente con EMPNO 7566 subordinato a 7839

Vediamo ora la rappresentazione degli stessi dati sotto forma di grafico. Il nodo EMPLOYEE ha diversi attributi ed è collegato a se stesso da una relazione "submits" (EmplReportsTo). EmplReportsTo è il nome della relazione.

Anche la tabella edge (EDGE) può avere attributi.

Crea una tabella dei nodi EmpNode

La sintassi per creare un nodo è abbastanza semplice: all'espressione CREA TABELLA aggiunto alla fine COME NODO.

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 grafico. Prossimo INSERT inserisce i dati da una 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_* l'ID host viene memorizzato come JSON. Le restanti colonne di questa tabella contengono gli attributi del nodo.

Crea bordi (BORDO)

La creazione di una tabella edge è molto simile alla creazione di una tabella dei nodi, ad eccezione della parola chiave COME BORDO.

CREATE TABLE empReportsTo(Deptno int) AS EDGE

Ora definiamo le relazioni tra i dipendenti utilizzando le colonne EMPNO и MGR. L'organigramma 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 Edge ha tre colonne per impostazione predefinita. Primo, $edge_id — identificatore del bordo sotto forma di JSON. Gli altri due ($from_id и $to_id) rappresentano la relazione tra i nodi. Inoltre, i bordi possono avere proprietà aggiuntive. Nel nostro caso, questo Dipartimento.

Viste di sistema

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

1. è_bordo
2. è_nodo

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

SSSM

Gli oggetti relativi ai grafici si trovano nella cartella Graph Tables. L'icona della tabella dei nodi è contrassegnata da un punto e l'icona della tabella dei bordi è contrassegnata da due cerchi collegati (che assomigliano un po' agli occhiali).

CONFRONTA espressione

espressione PARTITA tratto da CQL (Cypher Query Language). Questo è un modo efficiente per interrogare le proprietà di un grafico. CQL inizia con un'espressione PARTITA.

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

Примеры

Diamo un'occhiata ad alcuni esempi.

La query seguente mostra i dipendenti a cui Smith e il suo manager riferiscono.

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 query seguente serve per trovare dipendenti e manager di secondo livello per Smith. Se rimuovi l'offerta DOVE, tutti i dipendenti verranno visualizzati come risultato.

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 una richiesta per impiegati e dirigenti 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 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 una varietà di esigenze IT aziendali. La prima versione di SQL Graph è molto promettente. Nonostante alcune limitazioni, sono già disponibili funzionalità sufficienti per esplorare le possibilità dei grafici.

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

Nessun supporto per il polimorfismo.

• Sono supportati solo i collegamenti unidirezionali.
• Gli edge non possono aggiornare le proprie colonne $from_id e $to_id tramite UPDATE.
• Le chiusure transitive non sono supportate, ma possono essere ottenute utilizzando CTE.
• Supporto limitato per oggetti OLTP in memoria.
• Le tabelle temporali (tabella temporale con versioni di sistema), le tabelle temporanee locali e globali non sono supportate.
• I tipi di tabella e le variabili di tabella non possono essere dichiarati come NODE o EDGE.
• Le query tra database non sono supportate.
• Non esiste un modo diretto o una sorta di procedura guidata (procedura guidata) per convertire tabelle normali in tabelle grafiche.
• Non esiste una GUI per visualizzare i grafici, ma è possibile utilizzare Power BI.

Per saperne di più:

• Modalità di utilizzo dei filtri bitmap in SQL Server

Fonte: habr.com