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.