SQL Server 2016: R Services

I was during a long time captivated by the R programming language.
I’m happy to see that SQL Server 2016 provides now a SQL Server R Services.

This service enables SQL Server to execute R scripts.
The first step is to install the R Services during the installation of SQL Server.

As you can see on the picture, you have 2 selections in the feature Selection Menu:

• R Services (In-Database) is the service that enable the usage of the R Script in SQL Server. MSDN link here
• R Server (Standalone) is a server environment to run R jobs based on the enhanced R packages developed by Revolution Analytics. MSDN link here

Tips: to install by script the R service, you need to use ADVANCEDANALYTICS keyword and for the R Server SQL_SHARED_MR keyword.

You can check the configurationfile.ini generated.

To start my test, I create a simple database with one table in my environment.

Now, you have to configure the instance to be able to use R script.
In the instance configuration, you need to set the ‘external script enabled’ to 1.
Script to search the configuration:

SELECT * FROM sys.configurations WHERE name='external scripts enabled'

As you can see in the result, this configuration is not dynamic and need a restart of the engine.

After a restart, the configuration is enabled.

To start a R script, you will use the stored procedure sp_execute_external_script

The first parameter is the language (@language) and the only valid value is ‘R’ 😀
The second parameter is the R script (@script) as a literal or variable input and is a nvarchar(max).
The next parameter is the input data (@input_data_1_name) as a T-SQL query and is also a nvarchar(max)
The fourth parameter is the output data name (@output_data_1_name) and the default value is “OutputDataSet”
I thing these are the main parameters to a first usage.

The other thing to add is the <execution_option>  like “WITH RESULT SETS …”

For my first test, I use the easy sample from the MSDN to verify that R is running

exec sp_execute_external_script  @language =N'R',
@script=N'OutputDataSet<-InputDataSet',  
@input_data_1 =N'select 1 as hello'
with result sets (([hello] int not null));

As you can see I have this Error message:
Msg 39011, Level 16, State 1, Line 23
SQL Server was unable to communicate with the LaunchPad service. Please verify the configuration of the service. 

I google it and find that I need to start the “SQL Server LaunchPad” service.

Et Voilà! I have run my first R script into SQL Server.

I come back to my table named R_Table that I created before and I will use now the R language with this table.
The first test is to select my first column C_1:

exec sp_execute_external_script
  @language = N'R'
, @script = N' OutputDataSet <- InputDataSet;'
, @input_data_1 = N' SELECT [C_1] FROM R_Table;'
WITH RESULT SETS (([C_1] int NOT NULL));

The second test is to select my second column C_2 and as you can see, it works!

I also test an easy case without T_SQL script in input:

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) ));

It is just the beginning of the R language script into SQL Server.
I let you discovered by yourself this powerful new feature in SQL Server 2016.