SQL Server 2016: Microsoft R Server

In my last article, I wrote about the R Services into SQL Server.
Now, Microsoft provides an open source R platform to complete this new SQL Server integrated feature in 2016.
On the Microsoft Website, you can download directly R Server 2016 for different operating systems:

• Windows of course
• SUSE Linux
• Red Hat Linux
• Version for Hadoop on Red Hat
• Version for Teradata DB

Here is the link

Installation

In the SQL Server Installation Center on the Installation Tab, you will find a new installation menu: New R Server (Standalone) installation

This will install the R Server and automatically select the R Server (Standalone) in the shared Feature.

Msdn link here
After, you have a new step “Consent to install Microsoft R Open” and click Accept.

At the end, you need to restart your server

After the restart, you can see that all files for the R Server are installed on <SQL Server root path>\130\R_Server

If you want to run the R interface, you need to go to <SQL Server root path>\130\R_Server\bin\x64 and run the Rgui.exe

In order to run R without an interface just like command line, you need to run R.exe or Rterm.exe from the same folder.

Like all new programming toy, I test to have a “Hello World”. 😀
The command is simple:

print(“Hello World”)

I push my test with a simple addition:

 print (32.7+10.24)

After my easy test, I try to connect my R Server with a SQL Server…

Configure to use SQL Server

To use SQL Server, I search the RSQLServer package on CRAN website
I copy all files in the folder libraries.

After, I load the library with the command:

library(RSQLServer)

I obtain an error and see that the package need the package DBI.
It is not dbi services package, it is DataBase Interface, but the sameness was cool! 😉
Before I continue, I give you just a little tips that I discovered during my tests:

Yes, as you can see it is case sensitive.

I search now the DBI package on CRAN website
I reload the library RSQLServer and I have a new error for a missing package pylyr…

I continue with a lot of missing packages and a give you the list of these packages:

• RSQLServer (link)
• DBI(link)
• dplyr(link)
• assertthat(link)
• R6(link)
• Rcpp(link)
• Magrittr(link)
• RJDBC(link)
• rJava(link)

Don’t forget, it is just a copy in the library folder:

I finished with a Java problem in the package rJava

I install the JRE like in the picture and now, my RSQLServer library is loaded… 😎

At the end, you need 9 packages for RSQLServer and the JRE…

Use the SQL Server connector

After that, I will connect the SQL Server database and I use the command dbConnect:

I can read a table with 2 functions:

•  dbReadTable with just the table name to read (read all the table)
  

• dbSendQuery with the select query (is more flexible) and fetch to see the result
  

For the fun, I have executed an R script into the SQL command through the R Server used for my precedent blog on R Services:

Be careful, you need to go at the line with command (see the “+“ symbol in the picture)

Code used:

library(RSQLServer)
conn <- dbConnect(RSQLServer::SQLServer(),server="169.254.248.110", port=1433,database="R_TEST",properties=list(user="R_user",password="R_user"))
dbReadTable(conn,name='R_Table')
res = dbSendQuery(conn,'SELECT * FROM R_Table')
fetch(res,n=-1)
res = dbSendQuery(conn,'execute sp_execute_external_script @language=N\'R\', @script= N\' n = c(1, 2, 3, 4, 5)
s = c("titi", "toto", "tata", "tete", "tutu")
OutputDataSet<- data.frame(n,s);\',@input_data_1 = N\' \' WITH RESULT SETS (([C_1] int, [C_2] varchar(10) ));')
fetch(res,n=-1)
dbDisconnect(conn)

Finally, it is a good test to install and configure the R Server to be connected to a SQL Server instance.
You have seen that the installation is very simple but you have a lot of steps in the configuration of the RSQLServer package before you can use it.
Now, you can play with it and have nice statistics before….
Now, to develop with R language, you have a plugin in Visual Studio here