Criar um banco de dados de grafo e executar algumas consultas de correspondência de padrões usando T-SQL

Artigo
06/29/2023

Aplica-se a: SQL Server 2017 (14.x) e versões posteriores Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

Este exemplo fornece um script Transact-SQL para criar um banco de dados de grafo com nós e bordas e, em seguida, usar a nova cláusula MATCH para corresponder a alguns padrões e percorrer o grafo. Este script de exemplo funciona em SQL do Azure Banco de Dados e SQL Server 2017 (14.x) e versões posteriores.

Esquema de exemplo

Este exemplo cria um esquema de grafo para uma rede social hipotética que tem PeopleRestaurant nós e City . Esses nós são conectados uns aos outros usando Friendsbordas , Likese LocatedInLivesIn . O diagrama a seguir mostra um esquema de exemplo com restaurantbordas , city, person nós e LivesIn, LocatedIn. Likes

Diagrama mostrando um esquema de exemplo com bordas de restaurante, cidade, nós de pessoa e LivesIn, LocatedIn, Likes.

Exemplo de Script

O script de exemplo a seguir usa a nova sintaxe T-SQL para criar tabelas de nó e borda. Saiba como inserir dados em tabelas de nó e borda usando INSERT a instrução e também mostra como usar MATCH a cláusula para correspondência e navegação de padrões.

Esse script executa as seguintes etapas:

Crie um banco de dados chamado GraphDemo.
Criar tabelas de nós.
Criar tabelas de borda.

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

Agora, inseriremos dados para representar as relações.

Insira dados em tabelas de nó.
1. Inserir em uma tabela de nó é o mesmo que inserir em uma tabela regular.
Insira dados em tabelas de borda, nesse caso, para quais restaurantes cada pessoa gosta na likes borda.
1. Ao inserir em uma tabela de borda, forneça as $node_id colunas de $from_id e $to_id .
Insira dados na livesIn borda para associar as pessoas à cidade onde elas residem.
Insira dados na locatedIn borda para associar restaurantes à cidade onde eles estão localizados.
Insira dados na friendOf borda para amigos associados.

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

Em seguida, consultaremos os dados para encontrar insights dos dados.

Use a função MATCH do grafo para encontrar quais restaurantes John gosta.
Encontra os restaurantes que os amigos de John gostam.
Encontre pessoas que gostam de um restaurante na mesma cidade em que vivem.

-- 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 fim, uma consulta mais avançada encontra os amigos de amigos. Essa consulta exclui os casos em que a relação "retorna o loop". Por exemplo, Alice é amiga de João; João é amigo de Maria; E Maria, por sua vez, é amiga de Alice. Isso faz com que um "loop" volte para Alice. Em muitos casos, é necessário marcar explicitamente para esses loops e excluir os 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;

Limpar

Limpe o esquema e o banco de dados criados para o exemplo em 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

Limpe o esquema e o banco de dados criados para o exemplo no Banco de Dados SQL do Azure.

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

Compartilhar via

Criar um banco de dados de grafo e executar algumas consultas de correspondência de padrões usando T-SQL

Esquema de exemplo

Exemplo de Script

Limpar

Próximas etapas

Comentários

Recursos adicionais