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
Msdn link here
After, you have a new step “Consent to install Microsoft R Open” and click Accept.
Like all new programming toy, I test to have a “Hello World”. 😀
The command is simple:
I push my test with a simple addition:
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:
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)
At the end, you need 9 packages for RSQLServer and the JRE…
Use the SQL Server connector
I can read a table with 2 functions:
Be careful, you need to go at the line with command (see the “+“ symbol in the picture)
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