{"id":71626,"date":"2017-07-06T03:46:25","date_gmt":"2017-07-06T03:46:25","guid":{"rendered":"https:\/\/www.simple-talk.com\/?p=71626"},"modified":"2021-09-29T16:21:11","modified_gmt":"2021-09-29T16:21:11","slug":"using-r-illustrate-relationships-graph-objects","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/using-r-illustrate-relationships-graph-objects\/","title":{"rendered":"Using R to illustrate relationships from graph objects"},"content":{"rendered":"

My new article about Graph Database Objects<\/a> was just published, it’s really a very good new feature.<\/p>\n

Another new feature we have since SQL Server 2016<\/strong> is R language<\/strong>. We can use R language inside the database to analyze our data.<\/p>\n

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.<\/p>\n

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.<\/p>\n

\n
use<\/span>\u00a0master<\/span>
\n drop<\/span>\u00a0database<\/span>\u00a0if<\/span>\u00a0exists<\/span>\u00a0RGraph<\/span>
\n go<\/span>
\n create<\/span>\u00a0database<\/span>\u00a0RGraph<\/span>
\n go<\/span> <\/p>\n

use<\/span>\u00a0RGraph<\/span>
\n go<\/span> <\/p>\n

drop<\/span>\u00a0table<\/span>\u00a0if<\/span>\u00a0exists<\/span>\u00a0dbo<\/span>.<\/span>Likes<\/span>
\n drop<\/span>\u00a0table<\/span>\u00a0if<\/span>\u00a0exists<\/span>\u00a0dbo<\/span>.<\/span>ForumMembers<\/span> <\/p>\n

\n CREATE<\/span>\u00a0TABLE<\/span>\u00a0[dbo]<\/span>.<\/span>[ForumMembers]<\/span>(<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[MemberID]<\/span>\u00a0[int]<\/span>\u00a0IDENTITY<\/span>(<\/span>1<\/span>,<\/span>1<\/span>)<\/span>\u00a0NOT<\/span>\u00a0NULL<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[MemberName]<\/span>\u00a0[varchar]<\/span>(<\/span>100<\/span>)<\/span>\u00a0NULL<\/span>
\n )<\/span>
\n AS<\/span>\u00a0NODE<\/span>
\n GO<\/span> <\/p>\n

CREATE<\/span>\u00a0TABLE<\/span>\u00a0[dbo]<\/span>.<\/span>[Likes]<\/span>
\n AS<\/span>\u00a0EDGE<\/span> <\/p>\n

INSERT<\/span>\u00a0ForumMembers<\/span>\u00a0values<\/span>\u00a0(<\/span>‘Mike’<\/span>)<\/span>,<\/span>(<\/span>‘Carl’<\/span>)<\/span>,<\/span>(<\/span>‘Paul’<\/span>)<\/span>,<\/span>(<\/span>‘Christy’<\/span>)<\/span>,<\/span>(<\/span>‘Jennifer’<\/span>)<\/span>,<\/span>(<\/span>‘Charlie’<\/span>)<\/span> <\/p>\n

INSERT<\/span>\u00a0Likes<\/span>\u00a0(<\/span>$to_id<\/span>,<\/span>$from_id<\/span>)<\/span>
\n \u00a0\u00a0\u00a0\u00a0VALUES<\/span>
\n \u00a0\u00a0\u00a0\u00a0(<\/span>(<\/span>SELECT<\/span>\u00a0$node_id<\/span>\u00a0FROM<\/span>\u00a0dbo<\/span>.<\/span>ForumMembers<\/span>\u00a0WHERE<\/span>\u00a0MemberID<\/span>\u00a0=<\/span>\u00a01<\/span>)<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0(<\/span>SELECT<\/span>\u00a0$node_id<\/span>\u00a0FROM<\/span>\u00a0dbo<\/span>.<\/span>ForumMembers<\/span>\u00a0WHERE<\/span>\u00a0MemberID<\/span>\u00a0=<\/span>\u00a02<\/span>)<\/span>)<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0(<\/span>(<\/span>SELECT<\/span>\u00a0$node_id<\/span>\u00a0FROM<\/span>\u00a0dbo<\/span>.<\/span>ForumMembers<\/span>\u00a0WHERE<\/span>\u00a0MemberID<\/span>\u00a0=<\/span>\u00a03<\/span>)<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0(<\/span>SELECT<\/span>\u00a0$node_id<\/span>\u00a0FROM<\/span>\u00a0dbo<\/span>.<\/span>ForumMembers<\/span>\u00a0WHERE<\/span>\u00a0MemberID<\/span>\u00a0=<\/span>\u00a02<\/span>)<\/span>)<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0(<\/span>(<\/span>SELECT<\/span>\u00a0$node_id<\/span>\u00a0FROM<\/span>\u00a0dbo<\/span>.<\/span>ForumMembers<\/span>\u00a0WHERE<\/span>\u00a0MemberID<\/span>\u00a0=<\/span>\u00a01<\/span>)<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0(<\/span>SELECT<\/span>\u00a0$node_id<\/span>\u00a0FROM<\/span>\u00a0dbo<\/span>.<\/span>ForumMembers<\/span>\u00a0WHERE<\/span>\u00a0MemberID<\/span>\u00a0=<\/span>\u00a04<\/span>)<\/span>)<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0(<\/span>(<\/span>SELECT<\/span>\u00a0$node_id<\/span>\u00a0FROM<\/span>\u00a0dbo<\/span>.<\/span>ForumMembers<\/span>\u00a0WHERE<\/span>\u00a0MemberID<\/span>\u00a0=<\/span>\u00a05<\/span>)<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0(<\/span>SELECT<\/span>\u00a0$node_id<\/span>\u00a0FROM<\/span>\u00a0dbo<\/span>.<\/span>ForumMembers<\/span>\u00a0WHERE<\/span>\u00a0MemberID<\/span>\u00a0=<\/span>\u00a04<\/span>)<\/span>)<\/span> <\/p>\n

insert<\/span>\u00a0into<\/span>\u00a0likes<\/span>\u00a0(<\/span>$from_id<\/span>,<\/span>$to_id<\/span>)<\/span>\u00a0values<\/span>
\n \u00a0\u00a0(<\/span>(<\/span>select<\/span>\u00a0$node_id<\/span>\u00a0from<\/span>\u00a0dbo<\/span>.<\/span>forummembers<\/span>\u00a0where<\/span>\u00a0MemberName<\/span>=<\/span>‘Mike’<\/span>)<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0(<\/span>select<\/span>\u00a0$node_id<\/span>\u00a0from<\/span>\u00a0dbo<\/span>.<\/span>forummembers<\/span>\u00a0where<\/span>\u00a0MemberName<\/span>=<\/span>‘Paul’<\/span>)<\/span>)<\/span>,<\/span>
\n \u00a0\u00a0(<\/span>(<\/span>select<\/span>\u00a0$node_id<\/span>\u00a0from<\/span>\u00a0dbo<\/span>.<\/span>forummembers<\/span>\u00a0where<\/span>\u00a0MemberName<\/span>=<\/span>‘Paul’<\/span>)<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0(<\/span>select<\/span>\u00a0$node_id<\/span>\u00a0from<\/span>\u00a0dbo<\/span>.<\/span>forummembers<\/span>\u00a0where<\/span>\u00a0MemberName<\/span>=<\/span>‘Christy’<\/span>)<\/span>)<\/span>,<\/span>
\n \u00a0\u00a0(<\/span>(<\/span>select<\/span>\u00a0$node_id<\/span>\u00a0from<\/span>\u00a0dbo<\/span>.<\/span>forummembers<\/span>\u00a0where<\/span>\u00a0MemberName<\/span>=<\/span>‘Christy’<\/span>)<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0(<\/span>select<\/span>\u00a0$node_id<\/span>\u00a0from<\/span>\u00a0dbo<\/span>.<\/span>forummembers<\/span>\u00a0where<\/span>\u00a0MemberName<\/span>=<\/span>‘Carl’<\/span>)<\/span>)<\/span>,<\/span>
\n \u00a0\u00a0(<\/span>(<\/span>select<\/span>\u00a0$node_id<\/span>\u00a0from<\/span>\u00a0dbo<\/span>.<\/span>forummembers<\/span>\u00a0where<\/span>\u00a0MemberName<\/span>=<\/span>‘Paul’<\/span>)<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0(<\/span>select<\/span>\u00a0$node_id<\/span>\u00a0from<\/span>\u00a0dbo<\/span>.<\/span>forummembers<\/span>\u00a0where<\/span>\u00a0MemberName<\/span>=<\/span>‘Jennifer’<\/span>)<\/span>)<\/span>,<\/span>
\n \u00a0\u00a0(<\/span>(<\/span>select<\/span>\u00a0$node_id<\/span>\u00a0from<\/span>\u00a0dbo<\/span>.<\/span>forummembers<\/span>\u00a0where<\/span>\u00a0MemberName<\/span>=<\/span>‘Jennifer’<\/span>)<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0(<\/span>select<\/span>\u00a0$node_id<\/span>\u00a0from<\/span>\u00a0dbo<\/span>.<\/span>forummembers<\/span>\u00a0where<\/span>\u00a0MemberName<\/span>=<\/span>‘Carl’<\/span>)<\/span>)<\/span> <\/span><\/div>\n<\/blockquote>\n

\n You need to follow a few steps to enable R scripts:<\/p>\n

1) Install R in SQL Server<\/p>\n

\n

You can follow the instructions in this link to install R in SQL Server<\/a><\/p>\n<\/blockquote>\n

2) Install the package iGraph in R<\/p>\n

\n

You can follow the instructions in this link to install iGraph package<\/a><\/p>\n<\/blockquote>\n

3) Enable the use of external scripts. You can execute the following t-sql code:<\/p>\n

\n
exec<\/span>\u00a0sp_configure<\/b><\/span>\u00a0‘external\u00a0scripts\u00a0enabled’<\/span>,<\/span>\u00a01<\/span>;<\/span><\/span><\/div>\n
RECONFIGURE;<\/span> <\/span><\/div>\n<\/blockquote>\n

4) Execute the script<\/p>\n

\n

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.<\/p>\n

exec<\/span>\u00a0sp_execute_external_script<\/b><\/span>\u00a0@language<\/span>\u00a0=<\/span>\u00a0N’R’<\/span>,<\/span>
\n @script<\/span>\u00a0=<\/span>\u00a0N’ <\/span><\/span><\/div>\n
require(igraph) <\/span><\/span><\/div>\n
g\u00a0<-\u00a0graph.data.frame(graphdf) <\/span><\/span><\/div>\n
V(g)$label.cex\u00a0<-\u00a02 <\/span><\/span><\/div>\n
png(filename\u00a0=\u00a0“c:\\\\R\\\\plot1.png”,\u00a0height\u00a0=\u00a01200,\u00a0width\u00a0=\u00a01200,\u00a0res\u00a0=\u00a0100); plot(g,\u00a0vertex.label.family\u00a0=\u00a0“sans”,\u00a0vertex.size\u00a0=\u00a040) <\/span><\/span><\/div>\n
dev.off() ‘<\/span>,<\/span>
\n @input_data_1<\/span>\u00a0=<\/span>\u00a0N’select\u00a0LikeMember.MemberName\u00a0as\u00a0LikeMember, \u00a0\u00a0\u00a0LikedMember.MemberName\u00a0as\u00a0LikedMember \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0from\u00a0dbo.ForumMembers\u00a0as\u00a0LikeMember, \u00a0dbo.ForumMembers\u00a0as\u00a0LikedMember, \u00a0Likes <\/span><\/span><\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 where\u00a0Match(LikeMember-(Likes)->LikedMember) ‘<\/span>,<\/span>
\n @input_data_1_name<\/span>\u00a0=<\/span>\u00a0N’graphdf’<\/span><\/span><\/div>\n
GO<\/span> <\/span><\/div>\n<\/blockquote>\n

\n The image below shows the resulting graph with the relations between forum members:<\/p>\n

\"Cluster<\/p>\n

It’s interesting to remember about the new feature in SQL Server 2017: We can also build the same script using Python.<\/p>\n","protected":false},"excerpt":{"rendered":"

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……<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2,143531],"tags":[5134],"coauthors":[6810],"class_list":["post-71626","post","type-post","status-publish","format-standard","hentry","category-blogs","category-t-sql-programming-sql-server","tag-sql-prompt"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/71626","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=71626"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/71626\/revisions"}],"predecessor-version":[{"id":83068,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/71626\/revisions\/83068"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=71626"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=71626"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=71626"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=71626"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}