如何在 SQL Server 中传递两个 sql 表作为 r 代码的输入参数 [英] How to pass two sql tables as input parameter for r codes in SQL Server

问题描述

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

I am running r codes in SQL Server. I have two tables in SQL Server database that I would like to pass as input data set into the R code. I can use @input_data_1 to pass one of the input data set. How can I pass the other table?

我从 一个 MSDN 帮助页面 中读到:

只能传递一个输入数据集作为参数，您可以只返回一个数据集.但是，您可以调用其他数据集在你的 R 代码中.但是我还没有找到方法.

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.

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

Here is my code related to the question:

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]'

有什么建议吗?

推荐答案

您可以使用 序列化 以在 SQL Server 2016 中完成此操作.这绝对是冗长的，但是，它确实有效.

You can use R's built in serialization to accomplish this in the SQL Server 2016. It's definitely verbose, however, it does work.

例如，设置两个单独的输入临时表(不幸的是，它们不能是表变量):

Example, setup two separate input temp tables (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 中翻译的序列化结果.

From here, you can create VARBINARY types to hold the serialized results translated in 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

这篇关于如何在 SQL Server 中传递两个 sql 表作为 r 代码的输入参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！