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:

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