使用 sqlrutils 将 R 代码转换为存储过程

本文介绍使用 sqlrutils 包将 R 代码转换为作为 T-SQL 存储过程运行的步骤。 为了获得最佳结果，可能需要对代码进行一些修改，以确保可以参数化所有输入。

步骤 1。 重写 R 脚本

为了获得最佳结果，应重写 R 代码以将其封装为单个函数。

函数使用的所有变量都应在函数内定义，或应定义为输入参数。 请参阅本文中的 示例代码 。

此外，由于 R 函数的输入参数将成为 SQL 存储过程的输入参数，因此必须确保输入和输出符合以下类型要求：

输入

在输入参数中，最多可以有一个数据帧。

数据帧内的对象以及函数的所有其他输入参数必须具有以下 R 数据类型：

• POSIXct
• numeric
• 字符
• 整数
• 逻辑
• raw

如果输入类型不是上述类型之一，则需要序列化并作为 原始传入函数。 在这种情况下，该函数还必须包含用于反序列化输入的代码。

Outputs

该函数可以输出以下项之一：

• 包含支持的数据类型的数据帧。 数据帧中的所有对象都必须使用受支持的数据类型之一。
• 一个命名列表，最多包含一个数据帧。 列表的所有成员都应使用受支持的数据类型之一。
• 如果函数未返回任何结果，则为 NULL

步骤 2。 生成所需的对象

清理 R 代码并可以调用为单个函数后，将使用 sqlrutils 包中的函数准备表单中的输入和输出，该表单可以传递给实际生成存储过程的构造函数。

sqlrutils 提供定义输入数据架构和类型的函数，以及定义输出数据架构和类型。 它还包括可将 R 对象转换为所需输出类型的函数。 可以进行多个函数调用来创建所需的对象，具体取决于代码使用的数据类型。

输入

如果您的函数接受输入参数，请对每个输入调用以下函数：

• setInputData 如果输入是数据框
• setInputParameter 适用于所有其他输入类型

每次调用函数时，都会创建一个 R 对象，稍后将作为参数传递给该对象 StoredProcedure，以创建完整的存储过程。

Outputs

sqlrutils 提供了多个函数，用于将 R 对象（如列表）转换为SQL Server所需的 data.frame。 如果函数直接输出数据帧，而无需先将其包装到列表中，则可以跳过此步骤。 如果函数返回 NULL，还可以跳过此步骤。

转换列表或从列表中获取特定项时，请从以下函数中进行选择：

• setOutputData 如果要从列表中获取的变量是数据帧，则为
• 对于列表的所有其他成员，请选择 setOutputParameter

每次调用函数时，都会创建一个 R 对象，稍后将作为参数传递给该对象 StoredProcedure，以创建完整的存储过程。

步骤 3。 生成存储过程

当所有输入和输出参数都准备就绪时，请调用 StoredProcedure 构造函数。

用法

StoredProcedure (func, spName, ..., filePath = NULL ,dbName = NULL, connectionString = NULL, batchSeparator = "GO")

为了说明，假设要使用以下参数创建名为 sp_rsample 的存储过程：

• 使用现有函数 foosql。 该函数基于 R 函数 foo 中的现有代码，但将函数重新编写为符合 本节中所述的要求，并将更新后的函数命名为 foosql。
• 使用数据帧 queryinput 作为输入
• 生成一个数据框作为输出，其 R 变量名为 sqloutput
• 你想要在文件夹中将 T-SQL 代码创建为文件C:\Temp，以便以后可以使用SQL Server Management Studio运行它

StoredProcedure (foosql, sp_rsample, queryinput, sqloutput, filePath = "C:\\Temp")

函数的输出是在 SQL Server 2016（需要 R 服务）或 SQL Server 2017 实例上执行的 T-SQL 存储过程（需要使用 R 机器学习 服务）。

有关更多示例，请在 R 环境中调用 help(StoredProcedure) 以查看该程序包的帮助。

步骤 4. 注册并运行存储过程

可通过两种方式运行存储过程：

• 从支持连接到 SQL Server 2016 或 SQL Server 2017 实例的任何客户端中，使用 T-SQL 进行运行
• 从 R 环境中运行

这两种方法都要求在打算使用存储过程的数据库中注册存储过程。

注册存储过程

可以使用 R 注册存储过程，也可以在 T-SQL 中运行 CREATE PROCEDURE 该语句。

• 使用 T-SQL。 如果您更熟悉 T-SQL，请打开 SQL Server Management Studio（或任何其他可以运行 SQL DDL 命令的客户端），并使用 StoredProcedure 函数准备的代码执行 CREATE PROCEDURE 语句。

• 使用 R。在 R 环境中，可以使用 registerStoredProceduresqlrutils 中的函数向数据库注册存储过程。

  例如，可以通过进行以下 R 调用，在 sqlConnStr 中定义的实例和数据库中注册存储过程sp_rsample：

  registerStoredProcedure(sp_rsample, sqlConnStr)
  

使用 SQL 运行

创建存储过程后，使用支持 T-SQL 的任何客户端打开与 SQL 数据库的连接，并为存储过程所需的任何参数传递值。

使用 R 运行

如果要从 R 代码执行存储过程，而不是从SQL Server执行存储过程，则需要进行一些额外的准备。 例如，如果存储过程需要输入值，则必须在执行函数之前设置这些输入参数，然后将这些对象传递到 R 代码中的存储过程。

调用准备的 SQL 存储过程的整个过程如下所示：

1. 调用 getInputParameters 以获取输入参数对象的列表。
2. 为每个输入参数定义一个$query，或设置一个$value。
3. 使用 executeStoredProcedure 在 R 开发环境中执行存储过程，并传递你设置的输入参数对象列表。

例

此示例演示从SQL Server数据库获取数据的 R 脚本前后，对数据执行一些转换，并将其保存到其他数据库。

此简单示例仅用于演示如何重新排列 R 代码，以便更轻松地转换为存储过程。

代码准备前

sqlConnFrom <- "Driver={ODBC Driver 13 for SQL Server};Server=MyServer01;Database=AirlineSrc;Trusted_Connection=Yes;"
  
sqlConnTo <- "Driver={ODBC Driver 13 for SQL Server};Server=MyServer01;Database=AirlineTest;Trusted_Connection=Yes;"
  
sqlQueryAirline <- "SELECT TOP 10000 ArrDelay, CRSDepTime, DayOfWeek FROM [AirlineDemoSmall]"
  
dsSqlFrom <- RxSqlServerData(sqlQuery = sqlQueryAirline, connectionString = sqlConnFrom)
  
dsSqlTo <- RxSqlServerData(table = "cleanData", connectionString = sqlConnTo)
  
xFunc <- function(data) {
    data$CRSDepHour <- as.integer(trunc(data$CRSDepTime))
    return(data)
    }
  
xVars <- c("CRSDepTime")
  
sqlCompute <- RxInSqlServer(numTasks = 4, connectionString = sqlConnTo)
  
rxOpen(dsSqlFrom)
rxOpen(dsSqlTo)
  
if (rxSqlServerTableExists("cleanData", connectionString = sqlConnTo))   {
    rxSqlServerDropTable("cleanData")}
  
rxDataStep(inData = dsSqlFrom, 
     outFile = dsSqlTo,
     transformFunc = xFunc,
     transformVars = xVars,
     overwrite = TRUE)

完成代码准备后

在更新的版本中，第一行定义函数名称。 原始 R 解决方案中的所有其他代码都将成为该函数的一部分。

myetl1function <- function() { 
   sqlConnFrom <- "Driver={ODBC Driver 13 for SQL Server};Server=MyServer01;Database=Airline01;Trusted_Connection=Yes;"
   sqlConnTo <- "Driver={ODBC Driver 13 for SQL Server};Server=MyServer02;Database=Airline02;Trusted_Connection=Yes;"
    
   sqlQueryAirline <- "SELECT TOP 10000 ArrDelay, CRSDepTime, DayOfWeek FROM [AirlineDemoSmall]"

   dsSqlFrom <- RxSqlServerData(sqlQuery = sqlQueryAirline, connectionString = sqlConnFrom)
  
   dsSqlTo <- RxSqlServerData(table = "cleanData", connectionString = sqlConnTo)
  
   xFunc <- function(data) {
     data$CRSDepHour <- as.integer(trunc(data$CRSDepTime))
     return(data)}
  
   xVars <- c("CRSDepTime")
  
   sqlCompute <- RxInSqlServer(numTasks = 4, connectionString = sqlConnTo)
  
   if (rxSqlServerTableExists("cleanData", connectionString = sqlConnTo)) {rxSqlServerDropTable("cleanData")}
  
   rxDataStep(inData = dsSqlFrom, 
        outFile = dsSqlTo,
        transformFunc = xFunc,
        transformVars = xVars,
        overwrite = TRUE)
   return(NULL)
}

另请参阅

sqlrutils 参考手册