Data

13 abr, 2018

Falando um pouco sobre [SQL].[Graph]

Publicidade

Os negócios atuais requerem o armazenamento de suas informações e relacionamentos de forma mais complexa e ainda esperam o alto desempenho, integridade e confiabilidade das informações. Temos exemplos das redes sociais; sistemas medidores de tendências; sistemas antifraudes, os quais requerem um banco de dados que interconecte cada vez mais dados dos mais diversos tipos e fontes de forma que crie um relacionamento mais próximo do seu objetivo.

O SQL Server 2017 trás algumas novas funcionalidades e recursos para o desenvolvimento de um banco de dados gráfico, onde agora temos dois novos tipos de tabela, sendo o nó (NODE) e a borda (EDGE) e ainda uma nova função T-SQL chamada MATCH, para conseguirmos realizar um relacionamento de muitos-para-muitos. Dessa forma podemos criar banco de dados com representações de relacionamentos complexos como uma estrutura hierárquica, por exemplo, ou uma estrutura de entidades e relacionamentos (usuários, curtidas, postagem).

Demonstração prática

Neste artigo, estou utilizando o SQL Server 2017 – Express. Você pode fazer o download do mesmo no link: https://www.microsoft.com/en-us/sql-server/sql-server-2017.

Vamos criar um modelo de dados simples para simular uma rede social.

Não vamos nos preocupar agora no relacionamento entre as entidades, visto que vamos utilizar do relacionamento entre nós (NODE) através das bordas (EDGE) para uso do SQL Graph. Vamos agora elaborar o script de criação das tabelas seguindo os novos tipos de tabela.

USE Treinamento;
GO

CREATE SCHEMA RedeSocial
GO

CREATE TABLE RedeSocial.Usuarios (
	IdUsuarios INT IDENTITY(1,1),
	Nome varchar(45)

	CONSTRAINT PK_IDUSUARIO_USUARIOS PRIMARY KEY(IdUsuarios)
) AS NODE
GO

CREATE TABLE RedeSocial.Pagina (
	IdPagina INT IDENTITY(1,1),
	NomePagina VARCHAR(45),
	Categoria VARCHAR(20)

	CONSTRAINT PK_IDPAGINA_PAGINA PRIMARY KEY(IdPagina)
) AS NODE
GO

CREATE TABLE RedeSocial.EstadoCidade (
	IdEstadoCidade INT IDENTITY(1,1),
	Estado VARCHAR(45),
	Cidade VARCHAR(45)

	CONSTRAINT PK_IdEstadoCidade_EstadoCidade PRIMARY KEY (IdEstadoCidade)
) AS NODE
GO

  1. Estou utilizando minha base de treinamento para elaboração desse artigo.
  2. Criação de um schema para melhor identificar o relacionamento entre as entidades.
  3. Criação da tabela de usuários como um nó (AS NODE ).
  4. Criação da tabela de pagina como um nó (AS NODE ) onde ficaram paginas referente a filmes,lugares,marcas, etc.
  5. Criação da tabela de EstadoCidade como um nó (AS NODE ), eu poderia dividir em duas tabelas, mas se trata de um modelo simples apenas para ilustrar o funcionamento.

Vamos popular nossas tabelas!

SET NOCOUNT ON

-- Populando dados de usuários
INSERT INTO RedeSocial.Usuarios (Nome) VALUES ('João Pedro')
INSERT INTO RedeSocial.Usuarios (Nome) VALUES ('Marcos Vinicius')
INSERT INTO RedeSocial.Usuarios (Nome) VALUES ('Gabriela Santos')
INSERT INTO RedeSocial.Usuarios (Nome) VALUES ('Rogerio Silva')
INSERT INTO RedeSocial.Usuarios (Nome) VALUES ('Bruna Araujo')
INSERT INTO RedeSocial.Usuarios (Nome) VALUES ('Airton Lira')
INSERT INTO RedeSocial.Usuarios (Nome) VALUES ('Fernando silveira')
INSERT INTO RedeSocial.Usuarios (Nome) VALUES ('Andressa Santos')
INSERT INTO RedeSocial.Usuarios (Nome) VALUES ('Roberto Carlos')

-- Populando Paginas
INSERT INTO RedeSocial.Pagina (NomePagina,Categoria) VALUES ('Restaurante do seu zé','Estabelecimento')
INSERT INTO RedeSocial.Pagina (NomePagina,Categoria) VALUES ('Lanchonete Hamburgão','Estabelecimento')
INSERT INTO RedeSocial.Pagina (NomePagina,Categoria) VALUES ('Mc Donalds Oficial','Estabelecimento')
INSERT INTO RedeSocial.Pagina (NomePagina,Categoria) VALUES ('Outback SteakHouse','Estabelecimento')
INSERT INTO RedeSocial.Pagina (NomePagina,Categoria) VALUES ('Sal Gastronomia ','Estabelecimento')
INSERT INTO RedeSocial.Pagina (NomePagina,Categoria) VALUES ('O Exercista','Filmes')
INSERT INTO RedeSocial.Pagina (NomePagina,Categoria) VALUES ('Nada a Perder','Filmes')
INSERT INTO RedeSocial.Pagina (NomePagina,Categoria) VALUES ('Jogador N°1','Filmes')
INSERT INTO RedeSocial.Pagina (NomePagina,Categoria) VALUES ('Uma Dobra no Tempo','Filmes')

-- Populando EstadoCidade
INSERT INTO RedeSocial.EstadoCidade (Estado,Cidade) VALUES ('Paraiba','João Pessoa')
INSERT INTO RedeSocial.EstadoCidade (Estado,Cidade) VALUES ('São Paulo','São Bernardo do campo')
INSERT INTO RedeSocial.EstadoCidade (Estado,Cidade) VALUES ('Minas Gerais','Mariana')
INSERT INTO RedeSocial.EstadoCidade (Estado,Cidade) VALUES ('Rio de Janeiro','Cabo Frio')

SET NOCOUNT OFF

GO

Visualizando uma das tabelas populadas, observamos que existe uma coluna chamada “$node_id” essa coluna é um metadado que armazena os valores do NodeID como um JSON:

Vamos agora criar o relacionamento (EDGE) entre as entidades de acordo com a nossa necessidade. Neste caso vamos criar os seguintes relacionamentos:

  • Seguidores da página (Usuários > seguem > Página).
  • Localização do usuário (Usuários > estão > EstadoCidade).
  • Conhecimento entre os usuários (Usuário > conhece > Usuário).
CREATE TABLE RedeSocial.SeguePagina AS EDGE
GO

CREATE TABLE RedeSocial.LocalizacaoUsuarios AS EDGE
GO

CREATE TABLE RedeSocial.RelacaoUsuarios AS EDGE
GO

Não precisamos especificar nenhuma coluna, apenas atribuir o “AS EDGE” para identificar que se trata de uma borda que irá conectar duas entidades. A tabela EDGE terá as colunas de $node_id, $from_id e $to_id , e com isso teremos que popular o relacionamento (simulando uma rede social em operação) para verificarmos o uso do MATCH e sua eficácia. Toda vez que é criado uma tabela do tipo EDGE ou NODE e não é criado um índice clustered, por padrão é criado um índice nonclustered para o $node_id:

--Populando usuários que Seguem as Paginas
INSERT INTO RedeSocial.SeguePagina ($FROM_ID, $TO_ID) VALUES 
 ((SELECT $NODE_ID FROM RedeSocial.Usuarios WHERE IdUsuarios = 1), (SELECT $NODE_ID FROM RedeSocial.Pagina WHERE IdPagina = 3))

INSERT INTO RedeSocial.SeguePagina ($FROM_ID, $TO_ID) VALUES 
 ((SELECT $NODE_ID FROM RedeSocial.Usuarios WHERE IdUsuarios = 3), (SELECT $NODE_ID FROM RedeSocial.Pagina WHERE IdPagina = 2))

 INSERT INTO RedeSocial.SeguePagina ($FROM_ID, $TO_ID) VALUES 
 ((SELECT $NODE_ID FROM RedeSocial.Usuarios WHERE IdUsuarios = 4), (SELECT $NODE_ID FROM RedeSocial.Pagina WHERE IdPagina = 1))

 INSERT INTO RedeSocial.SeguePagina ($FROM_ID, $TO_ID) VALUES 
 ((SELECT $NODE_ID FROM RedeSocial.Usuarios WHERE IdUsuarios = 5), (SELECT $NODE_ID FROM RedeSocial.Pagina WHERE IdPagina = 2))
 GO

 --Populando usuários que estiveram em determinada cidade

 INSERT INTO RedeSocial.LocalizacaoUsuarios ($FROM_ID, $TO_ID) VALUES 
 ((SELECT $NODE_ID FROM RedeSocial.Usuarios WHERE IdUsuarios = 7), (SELECT $NODE_ID FROM RedeSocial.EstadoCidade WHERE IdEstadoCidade = 2))

  INSERT INTO RedeSocial.LocalizacaoUsuarios ($FROM_ID, $TO_ID) VALUES 
 ((SELECT $NODE_ID FROM RedeSocial.Usuarios WHERE IdUsuarios = 4), (SELECT $NODE_ID FROM RedeSocial.EstadoCidade WHERE IdEstadoCidade = 2))

  INSERT INTO RedeSocial.LocalizacaoUsuarios ($FROM_ID, $TO_ID) VALUES 
 ((SELECT $NODE_ID FROM RedeSocial.Usuarios WHERE IdUsuarios = 5), (SELECT $NODE_ID FROM RedeSocial.EstadoCidade WHERE IdEstadoCidade = 4))

  INSERT INTO RedeSocial.LocalizacaoUsuarios ($FROM_ID, $TO_ID) VALUES 
 ((SELECT $NODE_ID FROM RedeSocial.Usuarios WHERE IdUsuarios = 7), (SELECT $NODE_ID FROM RedeSocial.EstadoCidade WHERE IdEstadoCidade = 4))
GO

 -- Populando usuários que se conhecem

  INSERT INTO RedeSocial.RelacaoUsuarios ($FROM_ID, $TO_ID) VALUES 
 ((SELECT $NODE_ID FROM RedeSocial.Usuarios WHERE IdUsuarios = 3), (SELECT $NODE_ID FROM RedeSocial.Usuarios WHERE IdUsuarios = 2))

  INSERT INTO RedeSocial.RelacaoUsuarios ($FROM_ID, $TO_ID) VALUES 
 ((SELECT $NODE_ID FROM RedeSocial.Usuarios WHERE IdUsuarios = 2), (SELECT $NODE_ID FROM RedeSocial.Usuarios WHERE IdUsuarios = 4))

  INSERT INTO RedeSocial.RelacaoUsuarios ($FROM_ID, $TO_ID) VALUES 
 ((SELECT $NODE_ID FROM RedeSocial.Usuarios WHERE IdUsuarios = 1), (SELECT $NODE_ID FROM RedeSocial.Usuarios WHERE IdUsuarios = 2))
 
  INSERT INTO RedeSocial.RelacaoUsuarios ($FROM_ID, $TO_ID) VALUES 
 ((SELECT $NODE_ID FROM RedeSocial.Usuarios WHERE IdUsuarios = 4), (SELECT $NODE_ID FROM RedeSocial.Usuarios WHERE IdUsuarios = 3))
GO

Nesse script estamos elaborando um suposto relacionamento entre: usuários e pagina, usuários e estadocidade, usuários e usuários, obtendo o $node_id de ambas as tabelas e passando para seus supostos “de > para”. Vamos realizar um select na borda de RelacaoUsuarios e verificar como estão os dados:

Podemos observar que foi obtido todo o schema das entidades envolvidas em formato JSON com seus respectivos Node_Ids, e na parte mais interna verificamos que é utilizado pelo plano de execução um table Scan, visto que só foi criado automaticamente pelo SQL Server um índice nonclustered no edge_id. Vamos realizar as simulações com o Match para responder as seguintes questões:

1. Quais são as paginas que os usuários que estiveram no Rio de Janeiro seguem:

SELECT Usuario.Nome,Estado.Estado,Pagina.NomePagina
FROM   RedeSocial.Usuarios  Usuario,
RedeSocial.LocalizacaoUsuarios  Esteve, RedeSocial.EstadoCidade  Estado,RedeSocial.SeguePagina Segue,
RedeSocial.Pagina Pagina
WHERE   
Estado.Estado = 'Rio de Janeiro' AND
MATCH (Usuario-(esteve)->Estado) AND
MATCH (Usuario-(Segue)->Pagina)

2. Quantos usuários estiveram no Rio de Janeiro:

SELECT COUNT(Usuario.Nome) As Quantidade, Estado.Estado
FROM RedeSocial.Usuarios  Usuario,
RedeSocial.LocalizacaoUsuarios  Esteve,RedeSocial.EstadoCidade  Estado
WHERE
Estado.Estado = 'Rio de Janeiro' AND
MATCH (Usuario-(esteve)->Estado)
GROUP BY Estado.Estado

3. Quem conhece quem:

SELECT Usuario.Nome,'CONHECE' AS Situacao,Conhecido.Nome
FROM RedeSocial.Usuarios  Usuario,RedeSocial.RelacaoUsuarios Conhece,
RedeSocial.Usuarios  Conhecido
WHERE
MATCH(Usuario-(Conhece)->Conhecido)

-- Limpando nossa tabela EDGE de localização dos usuarios
DELETE FROM RedeSocial.LocalizacaoUsuarios
GO


--Alimentado 1 milhão de registros na parte de localização
SET NOCOUNT ON
DECLARE @COUNT INT = 0
DECLARE @IDUSUARIO INT = 9
DECLARE @IDESTADO INT = 4

WHILE(@COUNT < 100000)
BEGIN
      if(@IDUSUARIO = 0)
		    SET @IDUSUARIO = 9
	  If(@IDESTADO = 0)
			SET @IDESTADO = 4
	  INSERT INTO RedeSocial.LocalizacaoUsuarios ($FROM_ID, $TO_ID) VALUES 
	  ((SELECT $NODE_ID FROM RedeSocial.Usuarios WHERE IdUsuarios = @IDUSUARIO), (SELECT $NODE_ID FROM RedeSocial.EstadoCidade WHERE IdEstadoCidade = @IDESTADO))
	  SET @IDUSUARIO -= 1
	  SET @IDESTADO -= 1
	  SET @COUNT += 1
END

SET NOCOUNT OFF
GO

Agora vamos realizar a seguinte consulta:

SELECT COUNT(Usuario.Nome) As Quantidade, Estado.Estado
FROM RedeSocial.Usuarios  Usuario,
RedeSocial.LocalizacaoUsuarios  Esteve,RedeSocial.EstadoCidade  Estado
WHERE
(Estado.Estado = 'Rio de Janeiro' OR Estado.Estado = 'São Paulo') AND
MATCH (Usuario-(esteve)->Estado)
GROUP BY Estado.Estado
GO

Vamos habilitar as estatísticas de IO e analisar:

SET STATISTICS IO ON
GO

SELECT COUNT(Usuario.Nome) As Quantidade, Estado.Estado
FROM RedeSocial.Usuarios  Usuario,
RedeSocial.LocalizacaoUsuarios  Esteve,RedeSocial.EstadoCidade  Estado
WHERE
(Estado.Estado = 'Rio de Janeiro' OR Estado.Estado = 'São Paulo') AND
MATCH (Usuario-(esteve)->Estado)
GROUP BY Estado.Estado
GO

Reparem que tivemos 508 leituras lógicas na tabela de LocalizacaoUsuarios. Agora vamos realizar a criação de um índice “Columstore” no qual é permitido em tabelas do tipo EDGE e NODE. Com isso, reduziremos o custo de I/O visto que com esse índice ocorrerá o processamento em “lote” e não um valor por vez.

SET STATISTICS IO OFF
GO

CREATE CLUSTERED columnstore INDEX IDX_Usuarios 
ON RedeSocial.LocalizacaoUsuarios;
GO

Vamos executar novamente a consulta e verificar as estatísticas de I/O:

Reduzimos de forma significativa o custo de I/O!

Portanto, o novo recurso do SQL Server a suporte de dados gráficos é interessante e abre portas para novos sistemas complexo, onde todo o relacionamento precisa ser medido e o que se antes não seria possível com SQL Server, hoje já podemos ver essa aproximação.

Fontes