sql-server - 如何在 SQL Server 中传递两个 sql 表作为 r 代码的输入参数

Question

我正在 SQL Server 中运行 r 代码。我在 SQL Server 数据库中有两个表，我想将它们作为输入数据集传递到 R 代码中。我可以使用 @input_data_1 传递输入数据集之一。我怎样才能传递另一张 table ？

我读自 one MSDN help page说的是:

Only one input dataset can be passed as a parameter, and you can return only one dataset. However, you can call other datasets from inside your R code. But I did not find a way to do it yet.

这是我与问题相关的代码:

EXEC sp_execute_external_script 
@language = N'R'  
,@script = N' 
rules_set <- InputDataSet
#rules_set2 <- InputDataSet2 #need to be passed
'
,@input_data_1 = N'SELECT *
FROM [dbo].[[Rules_Set]'

有什么建议吗？

Answer 1

您可以使用 R 的内置 serialization在 SQL Server 2016 中完成此操作。这确实很冗长，但是它确实有效。

示例，设置两个单独的输入临时表 ( they cannot be table variables, unfortunately ):

 CREATE TABLE #Table1
    (
        [StrCol] NVARCHAR( 50 ),
        [IntCol] INT
    );

    INSERT INTO
        #Table1
        (
            [StrCol],
            [IntCol]
        )
        VALUES
        ( N'testing data 1', 1 ),
        ( N'testing data 2', 2 )
    ;

 CREATE TABLE #Table2
    (
        [StrCol] NVARCHAR( 50 ),
        [IntCol] INT
    );

    INSERT INTO
        #Table2
        (
            [StrCol],
            [IntCol]
        )
        VALUES
        ( N'more testing data 1', 5 ),
        ( N'more testing data 2', 6 )
    ;

从这里，您可以创建 VARBINARY类型来保存 R 中翻译的序列化结果。

DECLARE
    @Table1_Input NVARCHAR( MAX ) = 'SELECT * FROM #Table1;',
    @Table2_Input NVARCHAR( MAX ) = 'SELECT * FROM #Table2;',
    @Table1_Data VARBINARY( MAX ),
    @Table2_Data VARBINARY( MAX );

EXECUTE sp_execute_external_script
    @language = N'R',
    @script = N'

        if( nrow(InputDataSet) == 0 )
            stop("Invalid data passed in")

        # Read in the sql table, serialize it to an output string
        Output <- serialize(InputDataSet, NULL)
    ',
    @input_data_1 = @Table1_Input,
    @params = N'@Output VARBINARY( MAX ) OUTPUT',
    @Output = @Table1_Data OUTPUT;

EXECUTE sp_execute_external_script
    @language = N'R',
    @script = N'

        if( nrow(InputDataSet) == 0 )
            stop("Invalid data passed in")

        # Read in the sql table, serialize it to an output string
        Output <- serialize(InputDataSet, NULL)
    ',
    @input_data_1 = @Table2_Input,
    @params = N'@Output VARBINARY( MAX ) OUTPUT',
    @Output = @Table2_Data OUTPUT;

最后，

EXECUTE sp_execute_external_script
    @language = N'R',
    @script = N'

        table1 <- unserialize(Table1_Data)
        table2 <- unserialize(Table2_Data)

        OutputDataSet <- rbind(table1, table2)
    ',
    @params = N'@Table1_Data VARBINARY(MAX), @Table2_Data VARBINARY(MAX)',
    @Table1_Data = @Table1_Data,
    @Table2_Data = @Table2_Data
    WITH RESULT SETS (( [Col1] NVARCHAR( 50 ), [Col2] INT ));

结果:

Col1    Col2
testing data 1
testing data 2
more testing data 5
more testing data 6