Créer une base de données de graphe et exécuter des requêtes de correspondance de modèle à l’aide de T-SQL

S’applique à : SQL Server 2017 (14.x) et versions ultérieures Azure SQL DatabaseAzure SQL Managed Instance

Cet exemple fournit un script Transact-SQL pour créer une base de données de graphe avec des nœuds et des arêtes, puis utiliser la nouvelle clause MATCH pour faire correspondre certains modèles et parcourir le graphique. Cet exemple de script fonctionne sur Azure SQL Database et SQL Server 2017 (14.x) et versions ultérieures.

Exemple de schéma

Cet exemple crée un schéma de graphe pour un réseau social hypothétique qui a Peopledes nœuds et RestaurantCity . Ces nœuds sont connectés les uns aux autres à l’aide Friendsdes arêtes , LikesLivesIn et LocatedIn . Le diagramme suivant montre un exemple de schéma avec restaurant, city, person nœuds et LivesIn, LikesLocatedIn, arêtes.

Exemple de script

L’exemple de script suivant utilise la nouvelle syntaxe T-SQL pour créer des tables de nœud et de périphérie. Découvrez comment insérer des données dans des tables de nœud et de périphérie à l’aide INSERT de l’instruction et montre également comment utiliser MATCH une clause pour la correspondance et la navigation des modèles.

Ce script effectue les étapes suivantes :

1. Créez une base de données nommée GraphDemo.
2. Créez des tables de nœuds.
3. Créez des tables de périphérie.

-- Create a GraphDemo database
IF NOT EXISTS (SELECT * FROM sys.databases WHERE NAME = 'graphdemo')
    CREATE DATABASE GraphDemo;
GO

USE GraphDemo;
GO

-- Create NODE tables
CREATE TABLE Person (
  ID INTEGER PRIMARY KEY,
  name VARCHAR(100)
) AS NODE;

CREATE TABLE Restaurant (
  ID INTEGER NOT NULL,
  name VARCHAR(100),
  city VARCHAR(100)
) AS NODE;

CREATE TABLE City (
  ID INTEGER PRIMARY KEY,
  name VARCHAR(100),
  stateName VARCHAR(100)
) AS NODE;

-- Create EDGE tables.
CREATE TABLE likes (rating INTEGER) AS EDGE;
CREATE TABLE friendOf AS EDGE;
CREATE TABLE livesIn AS EDGE;
CREATE TABLE locatedIn AS EDGE;

À présent, nous allons insérer des données pour représenter les relations.

1. Insérez des données dans des tables de nœuds.
   1. L’insertion dans une table de nœuds est identique à l’insertion dans une table normale.
2. Insérez des données dans des tables d’arête, dans ce cas, pour les restaurants que chaque personne aime dans le likes bord.
   1. Lors de l’insertion dans une table d’arêtes, fournissez les $node_id colonnes de et $to_id .$from_id
3. Insérez des données dans la livesIn périphérie pour associer les personnes à la ville où elles vivent.
4. Insérez des données dans la locatedIn périphérie pour associer les restaurants à la ville où ils se trouvent.
5. Insérez des données dans la friendOf périphérie pour les amis associés.

-- Insert data into node tables. Inserting into a node table is same as inserting into a regular table
INSERT INTO Person (ID, name)
    VALUES (1, 'John')
         , (2, 'Mary')
         , (3, 'Alice')
         , (4, 'Jacob')
         , (5, 'Julie');

INSERT INTO Restaurant (ID, name, city)
    VALUES (1, 'Taco Dell','Bellevue')
         , (2, 'Ginger and Spice','Seattle')
         , (3, 'Noodle Land', 'Redmond');

INSERT INTO City (ID, name, stateName)
    VALUES (1,'Bellevue','WA')
         , (2,'Seattle','WA')
         , (3,'Redmond','WA');

-- Insert into edge table. While inserting into an edge table,
-- you need to provide the $node_id from $from_id and $to_id columns.
/* Insert which restaurants each person likes */
INSERT INTO likes
    VALUES ((SELECT $node_id FROM Person WHERE ID = 1), (SELECT $node_id FROM Restaurant WHERE ID = 1), 9)
         , ((SELECT $node_id FROM Person WHERE ID = 2), (SELECT $node_id FROM Restaurant WHERE ID = 2), 9)
         , ((SELECT $node_id FROM Person WHERE ID = 3), (SELECT $node_id FROM Restaurant WHERE ID = 3), 9)
         , ((SELECT $node_id FROM Person WHERE ID = 4), (SELECT $node_id FROM Restaurant WHERE ID = 3), 9)
         , ((SELECT $node_id FROM Person WHERE ID = 5), (SELECT $node_id FROM Restaurant WHERE ID = 3), 9);

/* Associate in which city live each person*/
INSERT INTO livesIn
    VALUES ((SELECT $node_id FROM Person WHERE ID = 1), (SELECT $node_id FROM City WHERE ID = 1))
         , ((SELECT $node_id FROM Person WHERE ID = 2), (SELECT $node_id FROM City WHERE ID = 2))
         , ((SELECT $node_id FROM Person WHERE ID = 3), (SELECT $node_id FROM City WHERE ID = 3))
         , ((SELECT $node_id FROM Person WHERE ID = 4), (SELECT $node_id FROM City WHERE ID = 3))
         , ((SELECT $node_id FROM Person WHERE ID = 5), (SELECT $node_id FROM City WHERE ID = 1));

/* Insert data where the restaurants are located */
INSERT INTO locatedIn
    VALUES ((SELECT $node_id FROM Restaurant WHERE ID = 1), (SELECT $node_id FROM City WHERE ID =1))
         , ((SELECT $node_id FROM Restaurant WHERE ID = 2), (SELECT $node_id FROM City WHERE ID =2))
         , ((SELECT $node_id FROM Restaurant WHERE ID = 3), (SELECT $node_id FROM City WHERE ID =3));

/* Insert data into the friendOf edge */
INSERT INTO friendOf
    VALUES ((SELECT $NODE_ID FROM Person WHERE ID = 1), (SELECT $NODE_ID FROM Person WHERE ID = 2))
         , ((SELECT $NODE_ID FROM Person WHERE ID = 2), (SELECT $NODE_ID FROM Person WHERE ID = 3))
         , ((SELECT $NODE_ID FROM Person WHERE ID = 3), (SELECT $NODE_ID FROM Person WHERE ID = 1))
         , ((SELECT $NODE_ID FROM Person WHERE ID = 4), (SELECT $NODE_ID FROM Person WHERE ID = 2))
         , ((SELECT $NODE_ID FROM Person WHERE ID = 5), (SELECT $NODE_ID FROM Person WHERE ID = 4));

Ensuite, nous allons interroger les données pour trouver des insights à partir des données.

1. Utilisez la fonction MATCH de graphe pour trouver les restaurants que John aime.
2. Trouve les restaurants que les amis de John aiment.
3. Trouvez des gens qui aiment un restaurant dans la même ville où ils vivent.

-- Find Restaurants that John likes
SELECT Restaurant.name
FROM Person, likes, Restaurant
WHERE MATCH (Person-(likes)->Restaurant)
AND Person.name = 'John';

-- Find Restaurants that John's friends like
SELECT Restaurant.name
FROM Person person1, Person person2, likes, friendOf, Restaurant
WHERE MATCH(person1-(friendOf)->person2-(likes)->Restaurant)
AND person1.name='John';

-- Find people who like a restaurant in the same city they live in
SELECT Person.name
FROM Person, likes, Restaurant, livesIn, City, locatedIn
WHERE MATCH (Person-(likes)->Restaurant-(locatedIn)->City AND Person-(livesIn)->City);

Enfin, une requête plus avancée recherche les amis d’amis d’amis. Cette requête exclut les cas où la relation « revient en boucle ». Par exemple, Alice est une amie de John ; John est un ami de Marie; et Mary à son tour est une amie d’Alice. Cela provoque une « boucle » vers Alice. Dans de nombreux cas, il est nécessaire de case activée explicitement pour ces boucles et d’exclure les résultats.

-- Find friends-of-friends-of-friends, excluding those cases where the relationship "loops back".
-- For example, Alice is a friend of John; John is a friend of Mary; and Mary in turn is a friend of Alice.
-- This causes a "loop" back to Alice. In many cases, it is necessary to explicitly check for such loops and exclude the results.
SELECT CONCAT(Person.name, '->', Person2.name, '->', Person3.name, '->', Person4.name)
FROM Person, friendOf, Person as Person2, friendOf as friendOffriend, Person as Person3, friendOf as friendOffriendOfFriend, Person as Person4
WHERE MATCH (Person-(friendOf)->Person2-(friendOffriend)->Person3-(friendOffriendOfFriend)->Person4)
AND Person2.name != Person.name
AND Person3.name != Person2.name
AND Person4.name != Person3.name
AND Person.name != Person4.name;

Nettoyer

Nettoyez le schéma et la base de données créés pour l’exemple dans SQL Server.

USE graphdemo;
go

DROP TABLE IF EXISTS likes;
DROP TABLE IF EXISTS Person;
DROP TABLE IF EXISTS Restaurant;
DROP TABLE IF EXISTS City;
DROP TABLE IF EXISTS friendOf;
DROP TABLE IF EXISTS livesIn;
DROP TABLE IF EXISTS locatedIn;

USE master;
go
DROP DATABASE graphdemo;
go

Nettoyez le schéma et la base de données créés pour l’exemple dans Azure SQL Database.

--Connect to the graphdemo database
DROP TABLE IF EXISTS likes;
DROP TABLE IF EXISTS Person;
DROP TABLE IF EXISTS Restaurant;
DROP TABLE IF EXISTS City;
DROP TABLE IF EXISTS friendOf;
DROP TABLE IF EXISTS livesIn;
DROP TABLE IF EXISTS locatedIn;

--Connect to the master database
DROP DATABASE graphdemo;
go

Étapes suivantes

• Traitement de graphe
• CREATE TABLE (SQL Graph)
• INSERT (SQL Graph)
• MATCH (Transact-SQL)