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:
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