Creación de una base de datos de grafos y ejecución de algunas consultas de coincidencia de patrones mediante T-SQL

Se aplica a: SQL Server 2017 (14.x) y versiones posteriores Azure SQL DatabaseAzure SQL Managed Instance

En este ejemplo se proporciona un script transact-SQL para crear una base de datos de grafos con nodos y bordes y, a continuación, usar la nueva cláusula MATCH para que coincida con algunos patrones y recorrer el gráfico. Este script de ejemplo funciona tanto en Azure SQL Database como en SQL Server 2017 (14.x) y versiones posteriores.

Esquema de ejemplo

En este ejemplo se crea un esquema de gráfico para una red social hipotética que tiene Peoplenodos y RestaurantCity . Estos nodos se conectan entre sí mediante Friendsbordes , LivesInLikesy LocatedIn . En el diagrama siguiente se muestra un esquema de ejemplo con restaurantbordes , city, person nodos y LivesIn, LocatedIn. Likes

Script de ejemplo

El siguiente script de ejemplo usa la nueva sintaxis de T-SQL para crear tablas perimetrales y de nodo. Obtenga información sobre cómo insertar datos en tablas perimetrales y de nodo mediante INSERT la instrucción y también muestra cómo usar MATCH la cláusula para la coincidencia de patrones y la navegación.

Este script realiza los pasos siguientes:

1. Cree una base de datos denominada GraphDemo.
2. Crear tablas de nodo.
3. Crear tablas perimetrales.

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

Ahora, insertaremos datos para representar las relaciones.

1. Insertar datos en tablas de nodo.
   1. La inserción en una tabla de nodos es la misma que la inserción en una tabla normal.
2. Inserte datos en tablas perimetrales, en este caso, para los que cada persona quiera en el likes perímetro.
   1. Al insertar en una tabla perimetral, proporcione las $node_id columnas from $from_id y $to_id .
3. Inserte datos en el livesIn perímetro para asociar personas a la ciudad donde viven.
4. Inserte datos en el locatedIn perímetro para asociar restaurantes a la ciudad donde se encuentran.
5. Inserte datos en el friendOf borde a los amigos asociados.

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

A continuación, consultaremos los datos para buscar información de los datos.

1. Use la función MATCH del grafo para buscar qué restaurantes le gusta John.
2. Encuentra los restaurantes a los que le gustan los amigos de John.
3. Encuentra gente que le gusta un restaurante en la misma ciudad en la que viven.

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

Por último, una consulta más avanzada busca a los amigos de amigos. Esta consulta excluye los casos en los que la relación "vuelve a bucles". Por ejemplo, Alice es amiga de John; Juan es amigo de María; y Mary a su vez es amiga de Alice. Esto hace que un "bucle" vuelva a Alice. En muchos casos, es necesario comprobar explícitamente estos bucles y excluir los resultados.

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

Limpieza

Limpie el esquema y la base de datos creados para el ejemplo en 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

Limpie el esquema y la base de datos creados para el ejemplo en 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

Pasos siguientes

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