修改要在 SQL Server（数据库内）实例中运行的 R/Python 代码

适用于： SQL Server 2016 (13.x) 及更高版本 Azure SQL 托管实例

本文提供有关如何修改 R 或 Python 代码以将它们作为 SQL Server 存储过程运行，从而提高访问 SQL 数据时的性能的概述指南。

将 R/Python 代码从本地 IDE 或其他环境移动到 SQL Server 时，代码通常无需进一步修改即可正常工作。 对于简单的代码（例如采用某些输入并返回值的函数）尤其如此。 此外，移植使用 RevoScaleR / revoscalepy 包的解决方案也更容易，这些包支持在不同的执行上下文中运行（只需稍加修改）。 请注意，MicrosoftML 适用于 SQL Server 2016 (13.x)、SQL Server 2017 (14.x) 和 SQL Server 2019 (15.x)，不会出现在 SQL Server 2022 (16.x) 中。

但是，如果以下任一条件成立，则你可能需要对代码进行大量更改：

• 使用访问网络或无法在 SQL Server 上安装的库。
• 代码对 SQL Server 之外的数据源进行单独调用，如 Excel 工作表、共享位置上的文件和其他数据库。
• 你希望参数化存储过程，并运行 sp_execute_external_script 的 @script 参数中的代码。
• 最初的解决方案包含多个在生产环境单独执行时效率可能更高的步骤，如数据准备、特征工程与模型定型、评分或报告。
• 想要通过更改库、使用并行执行或将某些处理任务卸载到 SQL Server 来优化性能。

步骤 1。 规划要求和资源

包

• 确定所需的包，并确保它们可用于 SQL Server 上。

• 预先在机器学习服务使用的默认包库中安装包。 用户库不受支持。

数据源

• 如果要在 sp_execute_external_script 中嵌入代码，请标识主数据源和辅助数据源。

  • 主数据源是大型数据集，例如模型定型数据或用于预测的输入数据 。 计划将最大的数据集映射到 sp_execute_external_script 的输入参数。

  • 辅助数据源通常是较小的数据集，例如系数列表或其他分组变量 。

  目前，sp_execute_external_script 仅支持单个数据集作为存储过程的输入。 但是，可以添加多个标量或二进制输入。

  前面带有 EXECUTE 的存储过程调用不能用作 sp_execute_external_script 的输入。 你可以使用查询、视图或任何其他有效的 SELECT 语句。

• 确定所需的输出。 如果使用 sp_execute_external_script 运行代码，存储过程只能输出一个数据帧作为结果。 但是，也可以输出多个标量输出，包括二进制格式的绘图和模型，以及从代码或 SQL 参数得到的其他标量值。

数据类型

有关 R/Python 与 SQL Server 之间的数据类型映射的详细信息，请参阅以下文章：

• R 与 SQL Server 之间的数据类型映射
• Python 与 SQL Server 之间的数据类型映射

查看 R/Python 代码中使用的数据类型，然后执行以下操作：

• 针对可能的数据类型问题创建一份查检表。

  SQL Server 机器学习服务支持所有 R/Python 数据类型。 但是，SQL Server 支持的数据类型比 R 或 Python 更多。 因此，将 SQL Server 数据移入和移出代码时，会执行某些隐式数据类型转换。 可能需要显式强制转换或转换某些数据。

  支持 NULL 值。 但是，R 使用 na 数据构造来表示缺失值（类似于 null 值）。

• 考虑消除对 R 不能使用的数据的依赖：例如，SQL Server 的 rowid 和 GUID 数据类型不能由 R 使用，会生成错误。

步骤 2. 转换或重新打包代码

更改代码的程度取决于你是要从远程客户端提交代码以在 SQL Server 计算上下文中运行，还是要将代码部署为存储过程的一部分。 后者可以提供更好的性能和数据安全性，但会带来一些额外的要求。

• 为避免数据移动，应尽可能将主输入数据定义为 SQL 查询。

• 在存储过程中运行代码时，可以传递多个标量输入。 对于要在输出中使用的任何参数，请添加 OUTPUT 关键字 。

  例如，下面的标量输入 @model_name 包含模型名称，该模型名称稍后也会被 R 脚本修改，并在结果中输出到其自身的列中：

  -- declare a local scalar variable which will be passed into the R script
  DECLARE @local_model_name AS NVARCHAR (50) = 'DefaultModel';
  
  -- The below defines an OUTPUT variable in the scope of the R script, called model_name
  -- Syntactically, it is defined by using the @model_name name. Be aware that the sequence
  -- of these parameters is very important. Mandatory parameters to sp_execute_external_script
  -- must appear first, followed by the additional parameter definitions like @params, etc.
  EXECUTE sp_execute_external_script @language = N'R', @script = N'
    model_name <- "Model name from R script"
    OutputDataSet <- data.frame(InputDataSet$c1, model_name)'
    , @input_data_1 = N'SELECT 1 AS c1'
    , @params = N'@model_name nvarchar(50) OUTPUT'
    , @model_name = @local_model_name OUTPUT;
  
  -- optionally, examine the new value for the local variable:
  SELECT @local_model_name;
  

• 作为 sp_execute_external_script 存储过程的参数传入的任何变量必须映射到代码中的变量。 默认情况下，变量按名称映射。 输入数据集中的所有列必须也映射到脚本中的变量。

  例如，假设 R 脚本包含如下所示的公式：

  formula <- ArrDelay ~ CRSDepTime + DayOfWeek + CRSDepHour:DayOfWeek
  

  如果输入数据集不包含具有匹配名称 ArrDelay、CRSDepTime、DayOfWeek、CRSDepHour 和 DayOfWeek 的列，则会引发错误。

• 在某些情况下，必须提前为结果定义输出架构。

  例如，若要将数据插入到表中，则必须使用 WITH RESULT SET 子句来指定架构 。

  如果脚本使用 @parallel=1 参数，则还需要输出架构。 原因是 SQL Server 可能会创建多个进程来并行运行查询，最后收集结果。 因此，必须先准备输出架构，然后才能创建并行进程。

  在其他情况下，你可以通过使用“WITH RESULT SETS UNDEFINED”选项来省略结果架构 。 此语句从脚本返回数据集，不需命名列或指定 SQL 数据类型。

• 考虑使用 T-SQL（而不是 R/Python）生成计时或跟踪数据。

  例如，你可以通过添加传递给结果的 T-SQL 调用来传递用于审核和存储的系统时间或其他信息，而不是在脚本中生成类似的数据。

提高性能和安全性

• 避免将预测或中间结果写入文件。 改为将预测写入表，以避免数据移动。

• 提前运行所有查询，并检查 SQL Server 查询计划以确定可以并行执行的任务。

  如果输入查询可并行化，请将 @parallel=1 作为参数的一部分设置为 sp_execute_external_script。

  只要 SQL Server 可以处理分区表或者在多个进程之间分布查询并最终聚合结果，就通常可以使用此标志进行并行处理。 如果用于训练模型的算法需要读取所有数据，或者你需要创建聚合，则通常无法使用此标志进行并行处理。

• 检查代码，确定是否可以独立执行某些步骤，或者是否可以使用单独的存储过程调用来更有效地执行某些步骤。 例如，通过分别执行特征工程或特征提取，并将值保存到表中，可以获得更好的性能。

• 尝试使用 T-SQL（而不是 R/Python 代码）进行基于集的计算的方法。

  例如，该 R 解决方案显示用户定义的 T-SQL 函数和 R 如何执行相同的特征工程任务：数据科学端到端演练。

• 咨询数据库开发人员，确定通过 SQL Server 功能提高性能的方式，例如内存优化表 或 Resource Governor（如果你拥有 Enterprise Edition 的话）。

• 如果使用的是 R，如有可能，请将传统的 R 函数替换为支持分布式执行的 RevoScaleR 函数。 有关详细信息，请参阅比较 Base R 和 RevoScaleR 函数。

步骤 3. 准备部署

• 通知管理员，以便在部署代码之前可以安装和测试包。

  在开发环境中，也许可以将包安装为代码的一部分，但在生产环境中，这是一种不好的做法。

  无论是使用存储过程，还是在 SQL Server 计算上下文中运行 R/Python 代码，都不支持用户库。

将 R/Python 代码打包到存储过程中

• 创建 T-SQL 用户定义函数，使用 sp-execute-external-script 语句嵌入代码。

• 如果 R 代码较为复杂，请使用 R 包 sqlrutils 来转换代码。 该包旨在帮助有经验的 R 用户编写优秀的存储过程代码。 使用明确定义的输入和输出，将 R 代码重写为单一函数，然后使用 sqlrutils 包生成格式正确的输入和输出。 sqlrutils 包会生成完整的存储过程代码，还可以在数据库中注册该存储过程 。

  有关详细信息和示例，请参阅 sqlrutils (SQL)。

与其他工作流集成

• 利用 T-SQL 工具和 ETL 进程。 作为数据工作流的一部分，请预先执行特征工程、特征提取和数据清理。

  使用专用的开发环境时，你可能会将数据提取到计算机，以迭代方式分析数据，然后写出或显示结果。 但是，如果将独立的 R 代码迁移到 SQL Server，则此过程的大部分步骤可以简化或委派给其他 SQL Server 工具。

• 使用安全的异步可视化策略。

  SQL Server 用户通常无法访问服务器上的文件，且 SQL 客户端工具通常不支持 R/Python 图形设备。 如果解决方案的一部分是生成绘图或其他图形，则考虑将绘图导出为二进制数据并将其保存到表或写入。

• 在存储过程中包装预测和评分函数，供应用程序直接访问。

后续步骤

若要查看如何在 SQL Server 中部署 R 和 Python 解决方案的示例，请参阅以下教程：

R 教程

• 通过 SQL 机器学习在 R 中开发预测模型

• 使用二元分类来预测纽约市出租车费用

• 面向 R 数据科学家的 SQL 开发

Python 教程

• 通过 SQL 机器学习使用线性回归来预测雪橇租赁

• 使用二元分类来预测纽约市出租车费用