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:
1 | 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
1 2 3 4 | 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:
1 2 3 4 5 | 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:
1 2 3 4 5 6 7 | 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.