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
gouse RGraph
godrop table if exists dbo.Likes
drop table if exists dbo.ForumMembersCREATE TABLE [dbo].[ForumMembers](
[MemberID] [int] IDENTITY(1,1) NOT NULL,
[MemberName] [varchar](100) NULL
)
AS NODE
GOCREATE TABLE [dbo].[Likes]
AS EDGEINSERT 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 <- 2png(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, Likeswhere 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.
Load comments