A preview of using Revolution R Enterprise inside SQL Server

by Andrie de Vries

The second week of SQLRelay (#SQLRelay) kicked off in London earlier this week. SQLRelay is a series of conferences, spanning 10 cities in the United Kingdom over two weeks. The London agenda included 4 different streams, with tracks for the DBA, BI and Analytics users, as well as a workshop track with two separate tutorials.

My speaking slot was in the afternoon, with the title "In-database analytics using Revolution R and SQL".

In my talk I covered:

• A high level overview of R
• Data science in the cloud
• Connecting R to SQL
• Scalable R
• R in SQL Server
• Moving your workflow to the cloud

Although the functionality of using R directly inside SQL Server will only be part of SQL Server 2016, Microsoft announced earlier this year that SQL Server 2016 will include Revolution Analytics. I expect that more information will be released during the PASS 2015 summit in Seattle at the end of this month.

 

In my talk I included 5 simple demonstrations.  The first 3 demonstrations appeal to the data scientist coding in R:

• Connecting R to SQL Server using an RODBC connector
• Using Revolution R Enterprise (RRE) in a local parallel compute context, reading data from a local file
• Changing the compute context to SQL Server, and running the R code directly inside the SQL Server machine

The last two demonstrations demonstrate how to run some R code embedded in a SQL stored procedure:

• Creating a very simple script that calls out to R
• Using R to generate some data, in this case simply bringing some data in from the famous iris data set that is built into R.

 

Presentation

The presentation is available on SlideShare:

Taking R Analytics to SQL and the Cloud from Revolution Analytics

 

Sample code

Here are the code samples I used in the demonstration:

 

 

Comments

[csm]

Very interesting post Andrie, thank you.

I saw a similar demo of executing R inside SQL Server months ago, but AFAIK this funcionality is still not available in current CTP (2.4) of SQL Server, isn't it? Did you use a different (special) version of SQL 2016 or do we have to change some configuration in order to activate this functionality?

Thanks in advance

[Matthias]

Thanks Andrie for sharing! I am very much looking forward to try out myself.
Any idea where can I find more information on licencing RRE in combination with existing SQL Server licenses?

[John Ryle]

Andrie,

Great article. Thanks for sharing!
Can't wait to see R inside SQL Server. Exciting times!

[Robert Young]

If one wants to see the "proper" way to execute R from inside a database engine, read-up Joe Conway's notes on PL/R with Postgres. You needn't install either to see the semantics; basically, register R and create User Defined Functions. Here: http://www.joeconway.com/plr/doc/index.html

[Ramdas]

Thanks for sharing, excellent article.