🥇Introduction aux bases de données graphiques SQL Server 2017

À l'approche du lancement du cours «Développeur MS SQL Server» nous avons préparé pour vous une autre traduction utile.

Les bases de données graphiques constituent une technologie importante pour les spécialistes des bases de données. Je m'efforce de suivre les innovations et les nouvelles technologies dans ce domaine et, après avoir travaillé avec des bases de données relationnelles et NoSQL, je constate que le rôle des bases de données graphiques devient de plus en plus essentiel. Dans le traitement de données hiérarchiques complexes, les bases de données traditionnelles, ainsi que NoSQL, se révèlent peu efficaces. Souvent, avec l'augmentation du nombre de niveaux de relations et de la taille de la base, il y a une baisse de performance. De plus, avec la complexification des relations, le nombre de JOIN augmente.

Bien sûr, dans le modèle relationnel, il existe des solutions pour travailler avec des hiérarchies (par exemple, à l'aide de CTE récursifs), mais cela reste des solutions de contournement. Cependant, la fonctionnalité des bases de données graphiques dans SQL Server permet de gérer facilement plusieurs niveaux de hiérarchie. Cela simplifie à la fois le modèle de données et les requêtes, ce qui améliore leur efficacité. Le volume de code est considérablement réduit.

Les bases de données graphiques sont un langage expressif pour représenter des systèmes complexes. Cette technologie est déjà largement utilisée dans l'industrie informatique dans des domaines tels que les réseaux sociaux, les systèmes anti-fraude, l'analyse des réseaux informatiques, les recommandations sociales, ainsi que pour les recommandations de produits et de contenu.

La fonctionnalité des bases de données graphiques dans SQL Server est adaptée aux scénarios où les données sont fortement interconnectées et ont des relations clairement définies.

Modèle de données graphiques

Un graphique est un ensemble de sommets (nœuds, node) et d'arêtes (relations, edge). Les sommets représentent des entités, tandis que les arêtes représentent les relations, dont les attributs peuvent contenir des informations.

Une base de données graphique modélise les entités sous forme de graphique tel que défini en théorie des graphes. Les structures de données sont les sommets et les arêtes. Les attributs sont les propriétés des sommets et des arêtes. Une relation est une connexion entre sommets.

Contrairement à d'autres modèles de données, les bases de données graphiques privilégient les relations entre entités. Ainsi, il n'est pas nécessaire de calculer les liaisons via des clés étrangères ou d'autres méthodes. Il est possible de créer des modèles de données complexes en utilisant uniquement les abstractions de sommets et d'arêtes.

Dans le monde moderne, la modélisation des relations nécessite des méthodologies de plus en plus complexes. Pour cela, SQL Server 2017 propose des fonctionnalités de bases de données graphiques. Les nœuds et les arêtes du graphique sont représentés via de nouveaux types de tables : NODE et EDGE. Une nouvelle fonction T-SQL nommée MATCH() est utilisée pour les requêtes graphiques. Étant donné que cette fonctionnalité est intégrée dans SQL Server 2017, elle peut être utilisée dans vos bases de données existantes sans besoin de les convertir.

Avantages du modèle graphique

Aujourd'hui, les entreprises et les utilisateurs exigent des applications capables de gérer des volumes de données de plus en plus importants, tout en s'attendant à une performance et une fiabilité élevées. La représentation des données sous forme de graphique propose des outils pratiques pour traiter des relations complexes. Cette approche permet de résoudre de nombreux problèmes et aide à obtenir des résultats dans un contexte donné.

Il semble qu'à l'avenir, de nombreuses applications pourront tirer parti des bases de données graphiques.

Modélisation des données : du modèle relationnel au modèle graphique

Exemple

Prenons un exemple de structure organisationnelle avec une hiérarchie d'employés : un employé rend compte à un manager, le manager à un senior manager, et ainsi de suite. Selon l'entreprise, il peut y avoir n'importe quel nombre de niveaux dans cette hiérarchie. Mais à mesure que le nombre de niveaux augmente, le calcul des relations dans une base de données relationnelle devient de plus en plus complexe. Il est assez difficile de représenter la hiérarchie des employés, l'organisation marketing ou les liens dans les réseaux sociaux. Voyons comment SQL Graph peut aider à résoudre le problème de la gestion de différents niveaux hiérarchiques.

Pour cet exemple, nous allons créer un modèle de données simple. Créons une table des employés EMP avec un identifiant EMPNO et une colonne MGR, indiquant l'identifiant du supérieur hiérarchique (manager) de l'employé. Toutes les informations sur la hiérarchie sont stockées dans cette table et peuvent être interrogées à l'aide des colonnes EMPNO et MGR.

Le diagramme suivant montre également le même modèle d'organisation avec quatre niveaux d'imbrication de manière plus familière. Les employés sont les nœuds du graphique à partir de la table EMP. L'entité « employé » est liée à elle-même par la relation « rend compte à » (ReportsTo). En termes de graphique, cette relation est une arête (EDGE) qui relie les nœuds (NODE) des employés.

Créons une table ordinaire EMP et ajoutons-y des valeurs conformément au diagramme ci-dessus.

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)

Les employés figurant dans l'illustration ci-dessous sont :

l'employé avec EMPNO 7369 relève de 7902 ;
l'employé avec EMPNO 7902 relève de 7566
l'employé avec EMPNO 7566 relève de 7839

Voyons maintenant la représentation des mêmes données sous forme de graphe. Le sommet EMPLOYEE a plusieurs attributs et est lié à lui-même par la relation « rapporte à » (EmplReportsTo). EmplReportsTo est le nom de la relation.

La table des arêtes (EDGE) peut également contenir des attributs.

Créons la table des nœuds EmpNode

La syntaxe de création d'un nœud est assez simple : on ajoute à l'expression CREATE TABLE à la fin « 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;

Nous allons maintenant convertir les données de la table ordinaire en graphe. La requête suivante INSERT insère les données de la table relationnelle EMP.

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

Dans la table des nœuds, dans une colonne spéciale $node_id_* est stocké l'identifiant du nœud sous forme de JSON. Les autres colonnes de cette table contiennent les attributs du nœud.

Créons des arêtes (EDGE)

La création de la table des arêtes est très similaire à celle de la table des nœuds, à l'exception du fait qu'on utilise le mot-clé « AS EDGE ».

CREATE TABLE empReportsTo(Deptno int) AS EDGE

Définissons maintenant les relations entre les employés, en utilisant les colonnes EMPNO et MGR. Le diagramme de l'organisation montre clairement comment écrire 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 table des arêtes par défaut se compose de trois colonnes. La première, $edge_id — l'identifiant de l'arête au format JSON. Les deux autres ($from_id et $to_id) représentent la relation entre les nœuds. De plus, les arêtes peuvent avoir des propriétés supplémentaires. Dans notre cas, c'est Deptno.

Vues système

Dans la vue système sys.tables deux nouvelles colonnes ont été ajoutées :

is_edge
is_node

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

SSMS

Les objets liés aux graphes se trouvent dans le dossier Graph Tables. L'icône de la table des nœuds est marquée d'un point, tandis que les tables des arêtes sont représentées par deux cercles liés (ce qui ressemble un peu à des lunettes).

L'expression MATCH

Expression MATCH est tirée de CQL (Cypher Query Language). C'est un moyen efficace de requêter les propriétés du graphe. CQL commence par l'expression MATCH.

Syntaxe

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

Exemples

Voyons quelques exemples.

La requête ci-dessous affiche les employés qui relèvent de Smith et de son 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 requête suivante est destinée à rechercher des employés et des managers de niveau deux pour Smith. Si l'on retire la phrase OÙ, alors tous les employés s'afficheront dans le résultat.

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'

Et enfin, la requête pour les employés et les managers de niveau trois.

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'

Maintenant, changeons de direction pour obtenir les chefs de 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)

Conclusion

SQL Server 2017 s'est imposé comme une solution d'entreprise complète pour divers défis informatiques. La première version de SQL Graph est très prometteuse. Même avec certaines limitations, il existe déjà suffisamment de fonctionnalités pour explorer les possibilités des graphes.

Les fonctionnalités de SQL Graph sont complètement intégrées dans le moteur SQL. Cependant, comme déjà mentionné, SQL Server 2017 présente les limitations suivantes :

Pas de support pour le polymorphisme.

Seules les relations unidirectionnelles sont prises en charge.
Les colonnes $from_id et $to_id des arêtes ne peuvent pas être mises à jour via UPDATE.
Les fermetures transitives ne sont pas prises en charge, mais elles peuvent être obtenues à l'aide d'un CTE.
Support limité pour les objets In-Memory OLTP.
Les tables temporelles (System-Versioned Temporal Table), les tables locales et globales temporaires ne sont pas prises en charge.
Les types de tables et les variables de table ne peuvent pas être déclarés comme NODE ou EDGE.
Les requêtes entre bases de données (cross-database queries) ne sont pas prises en charge.
Il n'existe pas de moyen direct ni d'assistant (wizard) pour convertir des tables ordinaires en graphes.
Il n'y a pas d'interface graphique pour afficher les graphes, mais vous pouvez utiliser Power BI.

Lire aussi :

Comment SQL Server utilise les filtres bitmap

Source : habr.com