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 语言扩展。

sp_execute_external_script存储过程执行作为该过程的输入参数提供的脚本，并与 SQL Server 2017 上的 机器学习 Services （14.x） 一起使用。

对于 机器学习 服务，支持 Python 和 R 语言。

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

sp_execute_external_script存储过程执行作为过程的输入参数提供的脚本，并与 SQL Server 2016 （13.x）上的 R Services 一起使用。

对于 R 服务， R 是受支持的语言。

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

sp_execute_external_script存储过程执行作为过程的输入参数提供的脚本，并将其与 Azure SQL 托管实例 中的 机器学习 Services 一起使用。

对于 机器学习 服务，支持 Python 和 R 语言。

若要执行sp_execute_external_script，必须先启用机器学习服务。 有关详细信息，请参阅 Azure SQL 托管实例 中的机器学习服务。

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 ] ]
[ ; ]

SQL Server 2017 和早期版本的语法

EXEC 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' ]
    [ , [ @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）。

指示脚本语言。 语言 为 sysname。 在 SQL Server 2017（14.x）中，有效值为 R 和 Python。

指示脚本语言。 语言 为 sysname。 在 SQL Server 2016（13.x）中，唯一的有效值为 R。

指示脚本语言。 语言 为 sysname。 在Azure SQL 托管实例中，有效值为 R 和 Python。

[ @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）定义的与语言扩展一起使用的任何语言。

用于 sp_execute_external_script 执行以支持的语言编写的脚本。 支持的语言是 SQL Server 2017 （14.x） 机器学习服务中的 Python 和 R。

用于 sp_execute_external_script 执行以支持的语言编写的脚本。 唯一支持的语言是 SQL Server 2016 （13.x） R 服务中的 R 。

用于 sp_execute_external_script 执行以支持的语言编写的脚本。 支持的语言是 Azure SQL 托管实例 机器学习 Services 中的 Python 和 R。

默认情况下，此存储过程返回的结果集是带有未命名列的输出。 脚本中使用的列名是脚本环境的本地名称，不会反映在输出的结果集中。 若要命名结果集列，请使用 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 运行时，因此更加快速。

相关内容