Integrating R and SQL Server prior to 2016 version

Introduction

It was announced about year ago that upcoming SQL Server 2016 version would have integration with R. Now with the latest CTP candidates released by Microsoft it becomes more clear how this solution will look like.

I believe that it may be really great feature, especially for the people who extensively used both R and SQL. But what if one is not able to upgrade to 2016 easily and in the same time is eager to use ability to call R scripts from SQL Sever?

I found out that it is surprisingly easy to implement such integration by using standard means.

In this article I would like to demonstrate such proof-of-concept implementation of R integration with SQL Server versions prior to 2016.

My idea is to implement SQL Server CLR stored procedure which will use R.NET CodePlex in-process interoperability bridge to execute arbitrary R scripts from T-SQL.

The suggested stored procedure interface looks as following:

EXEC dbo.RunRScript @Script = N'
 # put your R code here
'

This interface looks similar to the new stored procedure sp_execute_external_script in SQL Server 2016 CTP, but can be deployed on any prior version which supports CLR integration (2012, 2008, etc).

Building Minimum Spanning Trees for Import / Export flows with R

Introduction

Inspired by this Complex Networks and Minimal Spanning Trees in International Trade Network paper, I decided to build such MST's by using R for 2010's decade and to see what are the changes since 2000 year (the paper provides MST for this year).