sp_execute_external_script (Transact-SQL)

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

存储过程sp_execute_external_script执行作为过程的输入参数提供的脚本，并用于机器学习服务和语言扩展。

对于 机器学习 服务，支持 Python 和 R 语言。 对于语言扩展，支持 Java，但必须使用 CREATE EXTERNAL LANGUAGE 定义。

若要执行sp_execute_external_script，必须先安装机器学习服务或语言扩展。 有关详细信息，请参阅在 Windows 和 Linux 上安装 SQL Server 机器学习 Services （Python 和 R），或在 Windows 和 Linux 上安装 SQL Server 语言扩展。

Transact-SQL 语法约定

语法

sp_execute_external_script
    [ @language = ] N'language'
    , [ @script = ] N'script'
    [ , [ @input_data_1 = ] N'input_data_1' ]
    [ , [ @input_data_1_name = ] N'input_data_1_name' ]
    [ , [ @input_data_1_order_by_columns = ] N'input_data_1_order_by_columns' ]
    [ , [ @input_data_1_partition_by_columns = ] N'input_data_1_partition_by_columns' ]
    [ , [ @output_data_1_name = ] N'output_data_1_name' ]
    [ , [ @parallel = ] { 0 | 1 } ]
    [ , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ] [ , ...n ]' ]
    [ , [ @parameter1 = ] 'value1' [ OUT | OUTPUT ] [ , ...n ] ]
[ ; ]

参数

[ @language = ] N'language'

指示脚本语言。 语言 为 sysname。 有效值为 R、 Python 和使用 CREATE EXTERNAL LANGUAGE 定义的任何语言（例如 Java）。

[ @script = ] N'script'

指定为文本或变量输入的外部语言脚本。 脚本为 nvarchar（max）。

[ @input_data_1 = ] N'input_data_1'

以 Transact-SQL 查询的形式指定外部脚本使用的输入数据。 input_data_1的数据类型为 nvarchar（max）。

[ @input_data_1_name = ] N'input_data_1_name'

指定用于表示由 @input_data_1 定义的查询的变量的名称。 外部脚本中变量的数据类型取决于语言。 对于 R，输入变量是数据帧。 对于 Python，输入必须是表格格式。 input_data_1_name为 sysname。 默认值为 InputDataSet。

[ @input_data_1_order_by_columns = ] N'input_data_1_order_by_columns'

用于生成每分区模型。 指定用于对结果集进行排序的列的名称，例如产品名称。 外部脚本中变量的数据类型取决于语言。 对于 R，输入变量是数据帧。 对于 Python，输入必须是表格格式。

[ @input_data_1_partition_by_columns = ] N'input_data_1_partition_by_columns'

用于生成每分区模型。 指定用于对数据进行分段的列的名称，例如地理区域或日期。 外部脚本中变量的数据类型取决于语言。 对于 R，输入变量是数据帧。 对于 Python，输入必须是表格格式。

[ @output_data_1_name = ] N'output_data_1_name'

指定外部脚本中变量的名称，其中包含在存储过程调用完成后要返回到 SQL Server 的数据。 外部脚本中变量的数据类型取决于语言。 对于 R，输出必须是数据帧。 对于 Python，输出必须是 pandas 数据帧。 output_data_1_name 为 sysname。 默认值为 OutputDataSet。

[ @parallel = ] { 0 | 1 }

通过将参数设置为 @parallel 1 启用 R 脚本的并行执行。 此参数的默认值为 0 （无并行度）。 如果 @parallel = 1 直接将输出流式传输到客户端计算机，则需要子 WITH RESULT SETS 句，并且必须指定输出架构。

• 对于不使用 RevoScaleR 函数的 R 脚本，使用 @parallel 参数对处理大型数据集很有用，假设脚本可以简单并行化。 例如，将 R predict 函数与模型一起使用以生成新预测时，将其设置为 @parallel = 1 查询引擎的提示。 如果查询可以并行化，则根据 MAXDOP 设置分布行。

• 对于使用 RevoScaleR 函数的 R 脚本，并行处理会自动处理，不应指定 @parallel = 1 调用 sp_execute_external_script 。

[ @params = ] N'@parameter_name data_type' [ OUT |OUTPUT ] [ ， ...n ]

外部脚本中使用的输入参数声明的列表。

[ @parameter1 = ] 'value1' [ OUT |OUTPUT ] [ ， ...n ]

外部脚本使用的输入参数的值列表。

注解

重要

查询树由 SQL 机器学习控制，用户无法对查询执行任意操作。

用于 sp_execute_external_script 执行以支持的语言编写的脚本。 支持的语言是 Python 和 R 与 机器学习 Services 一起使用，以及使用 CREATE EXTERNAL LANGUAGE（例如 Java）定义的与语言扩展一起使用的任何语言。

默认情况下，此存储过程返回的结果集是带有未命名列的输出。 脚本中使用的列名是脚本环境的本地名称，不会反映在输出的结果集中。 若要命名结果集列，请使用 WITH RESULT SET EXECUTE 的子句。

除了返回结果集外，还可以使用 OUTPUT 参数返回标量值。

可以通过配置外部资源池来控制外部脚本使用的资源。 有关详细信息，请参阅 CREATE EXTERNAL RESOURCE POOL。 可以从资源调控器目录视图、DMV 和计数器获取有关工作负荷的信息。 有关详细信息，请参阅 资源调控器目录视图、 资源调控器相关的动态管理视图和 SQL Server 外部脚本对象。

监视脚本执行

使用 sys.dm_external_script_requests 和 sys.dm_external_script_execution_stats监视脚本执行。

分区建模的参数

可以设置两个附加参数，用于对分区数据进行建模，其中分区基于一个或多个列，这些列自然地将数据集分段为逻辑分区，仅在脚本执行期间创建和使用。 包含年龄、性别、地理区域、日期或时间重复值的列是一些适合分区数据集的示例。

这两个参数是 input_data_1_partition_by_columns 和 input_data_1_order_by_columns，其中第二个参数用于对结果集进行排序。 参数作为输入 sp_execute_external_script 传递给每个分区执行一次的外部脚本。 有关详细信息和示例，请参阅 教程：在 SQL Server 上的 R 中创建基于分区的模型。

可以通过指定 @parallel = 1来并行执行脚本。 如果可以并行化输入查询，则应将其设置为 @parallel = 1 参数 sp_execute_external_script的一部分。 默认情况下，查询优化器在表上运行 @parallel = 1 的行数超过 256 行，但如果要显式处理，此脚本将参数作为演示包含。

提示

对于训练工作负载，可以将 @parallel 用于任意训练脚本，甚至是那些使用非 Microsoft-rx 算法的脚本。 通常，只有 RevoScaleR 算法（带有 rx 前缀）支持在 SQL Server 的训练方案中并行执行。 但是，使用 SQL Server 2019（15.x）及更高版本中的新参数，可以并行化调用未专门设计的功能的脚本。

Python 和 R 脚本的流式执行

流式处理允许 Python 或 R 脚本处理的数据超出内存中可以容纳的数据。 若要控制流式处理期间传递的行数，请在集合中@params指定参数@r_rowsPerRead的整数值。 例如，如果要训练使用非常宽数据的模型，则可以调整值以读取较少的行，以确保所有行都可以在一个数据区块中发送。 还可以使用此参数来管理一次读取和处理的行数，以缓解服务器性能问题。

@r_rowsPerRead流式处理参数和@parallel参数都应被视为提示。 若要应用提示，必须能够生成包含并行处理的 SQL 查询计划。 如果无法执行此操作，则无法启用并行处理。

注意

流式处理和并行处理仅在企业版受支持。 可以在标准版中包含参数，而不会引发错误，但参数不起作用，R 脚本在单个进程中运行。

限制

数据类型

在过程的输入查询或参数 sp_execute_external_script 中使用时，不支持以下数据类型，并返回不受支持的类型错误。

解决方法 CAST 是在将列或值发送到外部脚本之前，在 Transact-SQL 中支持的类型。

• cursor
• timestamp
• datetime2、 datetimeoffset、 time
• sql_variant
• 文本， 图像
• xml
• hierarchyid、 geometry、 geography
• CLR 用户定义的类型

通常，无法映射到 Transact-SQL 数据类型的任何结果集都将输出为 NULL。

特定于 R 的限制

如果输入包含 与 R 中允许的值范围不匹配的日期/时间 值，则值将 NA转换为 。 这是必需的，因为 SQL 机器学习允许的值范围大于 R 语言支持的范围。

尽管这两种语言都使用 IEEE 754，NaN但 SQL 机器学习中不支持浮点值（例如+Inf-Inf，浮点数）。 当前行为只是将值直接发送到 SQL;因此，SQL 客户端会引发错误。 因此，这些值将转换为 NULL。

权限

需要 EXECUTE ANY EXTERNAL SCRIPT 数据库权限。

示例

本部分包含有关如何使用 Transact-SQL 执行 R 或 Python 脚本的存储过程的示例。

A. 将 R 数据集返回到 SQL Server

以下示例创建一个存储过程，用于 sp_execute_external_script 返回 R 附带的鸢尾花数据集。

DROP PROCEDURE IF EXISTS get_iris_dataset;
GO
CREATE PROCEDURE get_iris_dataset
AS
BEGIN
    EXEC sp_execute_external_script @language = N'R',
        @script = N'iris_data <- iris;',
        @input_data_1 = N'',
        @output_data_1_name = N'iris_data'
    WITH RESULT SETS((
        "Sepal.Length" FLOAT NOT NULL,
        "Sepal.Width" FLOAT NOT NULL,
        "Petal.Length" FLOAT NOT NULL,
        "Petal.Width" FLOAT NOT NULL,
        "Species" VARCHAR(100)
    ));
END;
GO

B. 创建 Python 模型并通过该模型生成分数

此示例演示如何在 sp_execute_external_script 简单的 Python 模型上生成分数。

CREATE PROCEDURE [dbo].[py_generate_customer_scores]
AS
BEGIN
    -- Input query to generate the customer data
    DECLARE @input_query NVARCHAR(MAX) = N'SELECT customer, orders, items, cost FROM dbo.Sales.Orders'

    EXEC sp_execute_external_script @language = N'Python',
        @script = N'
import pandas as pd
from sklearn.cluster import KMeans

# Get data from input query
customer_data = my_input_data

# Define the model
n_clusters = 4
est = KMeans(n_clusters=n_clusters, random_state=111).fit(customer_data[["orders","items","cost"]])
clusters = est.labels_
customer_data["cluster"] = clusters

OutputDataSet = customer_data
',
        @input_data_1 = @input_query,
        @input_data_1_name = N'my_input_data'
    WITH RESULT SETS((
        "CustomerID" INT,
        "Orders" FLOAT,
        "Items" FLOAT,
        "Cost" FLOAT,
        "ClusterResult" FLOAT
    ));
END;
GO

Python 代码中使用的列标题不会输出到 SQL Server;因此，使用 WITH RESULT 语句指定 SQL 要使用的列名和数据类型。

°C 基于 SQL Server 中的数据生成 R 模型

以下示例创建一个存储过程，用于 sp_execute_external_script 生成鸢尾花模型并返回模型。

注意

此示例需要提前安装 e1071 包。 有关详细信息，请参阅 使用 sqlmlutils 安装 R 包。

DROP PROCEDURE IF EXISTS generate_iris_model;
GO
CREATE PROCEDURE generate_iris_model
AS
BEGIN
    EXEC sp_execute_external_script @language = N'R',
        @script = N'
      library(e1071);
      irismodel <-naiveBayes(iris_data[,1:4], iris_data[,5]);
      trained_model <- data.frame(payload = as.raw(serialize(irismodel, connection=NULL)));
',
        @input_data_1 = N'select "Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "Species" from iris_data',
        @input_data_1_name = N'iris_data',
        @output_data_1_name = N'trained_model'
    WITH RESULT SETS((model VARBINARY(MAX)));
END;
GO

要生成使用 Python 的类似模型，需要将语言标识符从 @language=N'R' 更改为 @language = N'Python'，并对 @script 参数进行必要的修改。 否则，所有参数均与 R 在功能上相同。

要进行评分，还可以使用本机 PREDICT 函数，此函数无需调用 Python 或 R 运行时，因此更加快速。

相关内容

• SQL 机器学习
• 什么是 SQL Server 语言扩展？
• 系统存储过程 (Transact-SQL)
• CREATE EXTERNAL LIBRARY (Transact-SQL)
• sp_prepare (Transact SQL)
• sp_configure (Transact-SQL)
• “已启用外部脚本”服务器配置选项
• SERVERPROPERTY (Transact-SQL)
• SQL Server 外部脚本对象
• sys.dm_external_script_requests
• sys.dm_external_script_execution_stats