Step by step introduction to execute R Script in SQL Server 2016

Introduction

R is an open source programming language and environment originally written by Ross Ihaka and Robert Gentleman and is an ideal fit for statistical analysis, graphics representation,reporting, predictive analytics and machine learning.

Since SQL Server 2016, Microsoft has incorporated R as a component into their database engine after acquiring the same with a vision of solving data analytics problem within

1. On-Premise
2. MS Azure
3. Hybrid Cloud Platform

The primary focus of the article is to get ourself acquainted with how to execute R-Script into SQL Server 2016 Environment.

Environment Setup for R-Services

As part of the installation process we only need to enable R-Services while installing SQL Server 2016.

Once done, let us open SSMS and execute the below scripts

	execute sp_execute_external_script 
	@language = N'R',
	@script = N'print(R.version)'
 

and we encountered the below error

sp_execute_external_script is as stored procedure that is use to execute a script (R i this case) provided as argument at an external location.In order to do so, let us execute the below script

	EXECUTE sp_configure 'external scripts enabled', 1;
	GO
	RECONFIGURE;
	GO
  

Now, if we run again the below command

	execute sp_execute_external_script 
	@language = N'R',
	@script = N'print(R.version)'
  

we still encounter the same error message

At this point let us see what is the current status of the property external scripts enabled

  EXEC SP_CONFIGURE 'external scripts enabled';  
	GO 
  

We can figure out that the run_value is 0 and not 1.

To fix this, let us go to the Services window [ services.msc ] and Restart SQL Server (SQL2016)

We will encounter a prompt screen

and click Yes to proceed.

At this point let us see what is the current status of the property external scripts enabled

  EXEC SP_CONFIGURE 'external scripts enabled';  
	GO 
  

We can figure out that the run_value is now 1.

Now run again the below command

	execute sp_execute_external_script 
	@language = N'R',
	@script = N'print(R.version)'
  

and we get the below response from the external script

Let us do some basic R Program

We will basically run some basic R-Scripting here using TSQL only to make us familiar like String operation, Arithmetic operation, Vector, Arrays etc.

  execute sp_execute_external_script 
@language = N'R',
@script = N'
			sayHello <- "Hello, World!"				# String example.
			addResult <- 30 + 5					# Integer Addition example.
			subResult <- 30 - 5					# Integer Subtraction example.
			multiplyResult <- 30 * 5				# Integer Multiplication example.
			divisionResult <- 30 / 5				# Integer Division example.
			shirtColor <- c("pink","white","blue")  		# Vector example.
			myColorArray <- array(c("Pink","Green"),dim=c(3,3,2))	 # Array example.

			print(sayHello)
			print(addResult)
			print(subResult)
			print(multiplyResult)
			print(divisionResult)
			print(shirtColor)	
			print(myColorArray)	'
  

The result after execution

A brief explanation is provided below

Reference

1. SQL Server R tutorials
2. R Introduction

Conclusion

Hope this article will help to start with R-Scripting inside SQL Server premise. There are lot more to explore and we will cover some of them in the up coming articles.Happy reading.