Using R to illustrate relationships from graph objects

Comments 0

Share to social media

My new article about Graph Database Objects was just published, it’s really a very good new feature.

Another new feature we have since SQL Server 2016 is R language. We can use R language inside the database to analyze our data.

The possibility to use both technologies together is very interesting. Using graph objects we can store relationships between elements, for example, relationships between forum members. Using R scripts we can build a cluster graph from the stored graph information, illustrating the relationships in the graph.

The script below creates a database for our example with a subset of the objects used in my article and a few more relationship records between the forum members.

use master
drop database if exists RGraph
go
create database RGraph
go

use RGraph
go

drop table if exists dbo.Likes
drop table if exists dbo.ForumMembers

CREATE TABLE [dbo].[ForumMembers](
       [MemberID] [int] IDENTITY(1,1) NOT NULL,
       [MemberName] [varchar](100) NULL
)
AS NODE
GO

CREATE TABLE [dbo].[Likes]
AS EDGE

INSERT ForumMembers values (‘Mike’),(‘Carl’),(‘Paul’),(‘Christy’),(‘Jennifer’),(‘Charlie’)

INSERT Likes ($to_id,$from_id)
    VALUES
    ((SELECT $node_id FROM dbo.ForumMembers WHERE MemberID = 1),
         (SELECT $node_id FROM dbo.ForumMembers WHERE MemberID = 2)),
    ((SELECT $node_id FROM dbo.ForumMembers WHERE MemberID = 3),
         (SELECT $node_id FROM dbo.ForumMembers WHERE MemberID = 2)),
    ((SELECT $node_id FROM dbo.ForumMembers WHERE MemberID = 1),
         (SELECT $node_id FROM dbo.ForumMembers WHERE MemberID = 4)),
    ((SELECT $node_id FROM dbo.ForumMembers WHERE MemberID = 5),
         (SELECT $node_id FROM dbo.ForumMembers WHERE MemberID = 4))

insert into likes ($from_id,$to_id) values
  ((select $node_id from dbo.forummembers where MemberName=‘Mike’),
    (select $node_id from dbo.forummembers where MemberName=‘Paul’)),
  ((select $node_id from dbo.forummembers where MemberName=‘Paul’),
    (select $node_id from dbo.forummembers where MemberName=‘Christy’)),
  ((select $node_id from dbo.forummembers where MemberName=‘Christy’),
    (select $node_id from dbo.forummembers where MemberName=‘Carl’)),
  ((select $node_id from dbo.forummembers where MemberName=‘Paul’),
    (select $node_id from dbo.forummembers where MemberName=‘Jennifer’)),
  ((select $node_id from dbo.forummembers where MemberName=‘Jennifer’),
    (select $node_id from dbo.forummembers where MemberName=‘Carl’))

You need to follow a few steps to enable R scripts:

1) Install R in SQL Server

You can follow the instructions in this link to install R in SQL Server

2) Install the package iGraph in R

You can follow the instructions in this link to install iGraph package

3) Enable the use of external scripts. You can execute the following t-sql code:

exec sp_configure ‘external scripts enabled’, 1;
RECONFIGURE;

4) Execute the script

Below is the R script to build a cluster graph using iGraph our relationships stored in SQL Server. You need to change the folder to store the graph.

exec sp_execute_external_script @language = N’R’,
@script = N’
require(igraph)
g <- graph.data.frame(graphdf)
V(g)$label.cex <- 2
png(filename = “c:\\R\\plot1.png”, height = 1200, width = 1200, res = 100); plot(g, vertex.label.family = “sans”, vertex.size = 40)
dev.off() ‘,
@input_data_1 = N’select LikeMember.MemberName as LikeMember,    LikedMember.MemberName as LikedMember         from dbo.ForumMembers as LikeMember,  dbo.ForumMembers as LikedMember,  Likes
        where Match(LikeMember-(Likes)->LikedMember) ‘,
@input_data_1_name = N’graphdf’
GO

The image below shows the resulting graph with the relations between forum members:

Cluster created with R from relationship graph information

It’s interesting to remember about the new feature in SQL Server 2017: We can also build the same script using Python.

Load comments

About the author

Dennes Torres

See Profile

Dennes Torres is a Data Platform MVP and Software Architect living in Malta who loves SQL Server and software development and has more than 20 years of experience. Dennes can improve Data Platform Architectures and transform data in knowledge. He moved to Malta after more than 10 years leading devSQL PASS Chapter in Rio de Janeiro and now is a member of the leadership team of MMDPUG PASS Chapter in Malta organizing meetings, events, and webcasts about SQL Server. He is an MCT, MCSE in Data Platforms and BI, with more titles in software development. You can get in touch on his blog https://dennestorres.com or at his work https://dtowersoftware.com