SQL Server R multiple result sets

Question

Is it possible to get more than one table as a result from executing R script with SQL Server 2016+? Let's take a random simple example from the internet (no need to post mine to not overcomplicate the issue):

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 posted in here.

Here the result is returned as a single table. Let's say I do various calculations with the data and now I want to return multiple tables as a result.

For example:

a<-InputDataSet

b<-InputDataSet + 5

These would return two different tables as results. Now I cannot figure any nice way to return the data in two separate tables as it only returns one table. Obviously, I can return it like this:

OutputDataSet<-data.frame(a, b)

But dealing with different functions and different data it soon becomes quite a hassle. For example I use a function lm. Now one dataset would be calculated estimated values and another would be the coefficients of each column participating in the equation. Again, of course I can join these two datatables and deal with them later, but the output result becomes colossal in many cases.

The parameters to the procedure look like: ..., @output_data_1_name, but there is no @output_data_2_name, etc. thus I do not see a way. Maybe it is possible to create the OutputDataSet so it holds multiple tables? If so - I am not aware of such way in R due to my lack of experience with it.

tldr; is it possible to return multiple result sets or my only solution is to manually construct the output in R code so I would always get one?

user_1886985 · Answer

Multiple tables (data.frame or data.table) can be returned in the form of a block matrix. data.table::rbindlist(L, fill = T) will create a block matrix out of a list of L of data.frame or data.table

DROP TABLE IF EXISTS temp153462
CREATE TABLE temp153462
              ([speed] int NULL,
               [dist] int NULL,
               [sepal_length] float NULL,
               [sepal_width] float NULL,
               [petal_length] float NULL,
               [petal_width] float NULL,
               [species] nvarchar(20) NULL)
insert into temp153462

exec sp_execute_external_script 
@language = N'R', 
@script = N'library(data.table)            
            library(datasets)
            print(cars)
            print(iris)
            OutputDataSet <- rbindlist(list(cars, iris), fill = T)
            print(OutputDataSet)'
SELECT speed, dist FROM temp153462 WHERE speed is not NULL
SELECT sepal_length, sepal_width, petal_length, petal_width FROM temp153462 WHERE sepal_width is not NULL

This does require the data.table package, which I recommend for other reasons.

There is also plyr::rbind.fill() which does something similar.

SQL Server R multiple result sets

2 Answers