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