dbi Blog

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