As SQL Server 2016 will be released soon and to follow up on the talk I gave at SQL Saturday Atlanta, this post will guide you through the steps needed to make R work. Like many features of SQL Server 2016, R is optionally installed. To install R make sure that when you install R, the Option for R Services (In-Database) is checked, as shown below.
Microsoft is incorporating the version of R sold by Revolution Analytics, which they called R Server. This version, while fully compatible with Open Source R, has some additional features which allow the R code to be run not only in memory, but use of a chunking technology to swap the data to disk so that the R code will not run out of memory. The commands to use this additional functionality all start with rx and are part of the proprietary ScaleR feature set. To use the R Server as a separate stand along product, instead of selecting the R Server in database option, select the R Server Standalone shared features. A R server could be useful if you want to perform large scale data analysis on a Hadoop Cluster, or other Non-SQL database like Teradata.
SQL Server Steps to Enable R
SQL Server 2016 is installed, especially if it was installed by others, you may be wondering if the R service really is installed. Take a look at the services running on the machine with SQL Server. If the SQL Server Launchpad service is running, as shown below, the R services are installed and running.
The last thing needed to run R is to configure and restart the SQL Server Services. In a new query type the following command
sp_configure 'external scripts enabled', 1
GO
Reconfigure
GO
After restarting the SQL Server Service, SQL Server will now run R code. The following text can be run on any SQL Server 2016 instance to see if R has been configured correctly
EXEC sp_execute_external_script @language =N'R',
@script=N'OutputDataSet <-InputDataSet',
@input_data_1 =N'SELECT 1 as CheckToSeeIfRIsWorking'
WITH result sets (([CheckToSeeIfRIsWorking] int not null));
GO
The code is executed as an external script, specifying that the language used should be R. @script contains the R code, which is a simple command to take the mean of the data coming from the InputDataSet. @Input_Data_1 contains the location of the data to be processed. Of course the R code could of course be more complicated, but this code example is generic enough to test for everyone to ensure R is set up properly on SQL Server 2016.
Yours Always
Ginger Grant
Data aficionado et SQL Raconteur
Pingback: R In SQL Server 2016 – Curated SQL
Nice post, I have found another helpful post for installation guide of SQL Server R Services. See here: http://www.sqlmvp.org/install-r-services-in-sql-server-2016/
Johnson —
Thanks for sharing another post. Hopefully other people will also find it helpful.
Regards,
Ginger
Hi Ginger,
Novice to R here, trying to setwd() from within SQL Server 2016 SP1 — and not getting anywhere. Is this even possible in the given environment and, if so, what setup is required?
Cheers,
–Mario
Mario —
Great to hear that you are starting to play with R. In answer to your question about setwd(), which used to set the working directory in R, SQL Server 2016 uses a specific directory structure to support the R functions contained within it, and does not support changing it. If you are running locally, this is of course supported, but if you are using R Server, you probably want to set the shareDir in the RXInSsqlServer command instead. Let me know if you have any more questions or you would like me to provide further clarification.
Regards,
Ginger
Thank you for the prompt reply, Ginger. Must admit, it’s worse than Greek to me (know about 20 Greek words, and about 1.5 R commands). I am trying to run the code within SQL Server which, I presume, “talks” directly to the R server.
Could you let me know what do you mean by “locally” vs. … what else…? Also, a few pointers about shareDir, whether the rx commands can be executed from within SQL Server and a few words around how to think about shareDir would go a long way.
Sorry to be a bother, there is desperately little literature on these idiosyncrasies — hopefully vNext might deal with some of them a bit more elegantly.
Cheers,
–Mario
Mario —
You asked a lot of very good questions which require detailed answers. To answer one of your questions, yes, rx commands can be run from within SQL Server. Rx commands are the only way to use some of the advanced memory management features of R Server.
To answer your question regarding context switching, I wrote a post which explains it better here http://wp.me/p4beUo-rb . ShareDir is a property of RxInSQLServer nad is used by R Server in processing. You can still set your working directory in your R UI, which is where your R code is developed prior to loading it to SQL Server.
Let me know if you have any other questions.
Regards,
Ginger
Wow! Thank you so much, Ginger. Your explanations and posts are an oasis in the Desert of SQL R Knowledge Base (pun intended).
Time to roll up the sleeves.
Cheers,
–Mario