R Services In SQL Server 2017

Hello Everyone. In this article I will be demonstrating how to use R Services in SQL Server 2017. Thisarticle is intended for those who have a basic idea of R language. If you don't, still no worries. You can still go through and grab it easily. I will go into details of R language in some other article.

First of all, to get started you need to have SQL Server 2016 or SQL Server 2017 installed. SQL Server 2016 supports only R language. SQL Server 2017 supports R and Python.

Next, you need to install R Services(In-Database) manually.

Open up SQL Server Installation Center.
Click on "Installation" on the left pane and then on "New SQL Server stand-alone installation or add features to an existing installation".
It will ask for the SQL Server Installation Media folder. Generally, you will get it in this path "C:\SQLServer2017Media". I have SQL Server 2017 Developer edition. That's why I select "C:\SQLServer2017Media\Developer_ENU".
Then keep clicking Next until you reach "Installtion Type" and select "Add featurs to an existing instance of SQL Server 2017". Choose your instance.
Select R and then click next and follow the steps to complete the installation.

This installation creates a Launchpad service for R.

Once the installation is complete, now have to configure R Services in SQL Server. So open up SQL Server Management Studio and execute the following statement ,

EXEC sp_configure 'external scripts enabled'

It will display that the run value for the "external scripts enabled" is 0. Now you will have to change it to 1. For that, execute this statement,

EXEC sp_configure 'external scripts enabled', 1

It will ask for another statement to execute to reconfigure,

RECONFIGURE WITH OVERRIDE

Once it is done, open up SQL Server 2017 Configuration Manager. Restart the "SQL Server Launchpad".

Now come back to SSMS and execute the following statement again,

EXEC sp_configure 'external scripts enabled'

Now if you see the run value as 1, then you are ready to use R in SQL Server. To check whether it is working or not, execute this statement.

EXEC sp_execute_external _script
@language =N'R',
@script=N'print(R.version)'

You should get the output something like this,

STDOUT message(s) from external script:
platform x86_64-w64-mingw32
arch x86_64
os mingw32
system x86_64, mingw32
status
major 3
minor 3.3
year 2017
month 03
day 06
svn rev 72310
language R
version.string R version 3.3.3 (2017-03-06)
nickname Another Canoe

Now what is happening here? There is a stored procedure name "sp_execute_external_script" which has the capability to execute R and Python scripts. It take two parameters language and script. There are other parameters as well. For example,

EXEC sp_execute_external_script
@language =N'R',
@script=N'
OutputDataSet <- InputDataSet;
print(OutputDataSet)',
@input_data_1 = N'SELECT Tip from TaxiData'

Once you have a proper hands-on, you can wrap this "sp_execute_external_script" in another stored procedure and then use it from your application. Now for the plots, they are saved as pictures or in varbinary data type.

EXECUTE sp_execute_external_script
@language = N'R'
, @script = N'
# Define the cars vector with 5 values
cars <- c(1, 3, 6, 4, 9)
imageDir <- ''C:\\<directory name>'';
image_filename = tempfile(pattern = "plot_", tmpdir = imageDir, fileext = ".jpg")
print(image_filename);
jpeg(filename=image_filename, width=600, height = 800);
print(plot(cars));
dev.off();
OutputDataSet <- data.frame(data=readBin(file(image_filename, "rb"), what=raw(), n=1e6));'

That's all. I hope you find it useful. Stay tuned.

3 Comments

Join the conversation! Your thoughts help the community grow.

Suraj Vissa7y ago
Hi Sourav, my run value is not getting updated for some reason. I followed all your steps, and it seemed to be executing fine. Also, I restarted the Launchpad. Would you happen to know why it is not updating to 1?
Hadshana Kamalanathan8y ago
Useful one
Sagar Pandurang Kap8y ago
Great kind of work....