sp_execute_external_script (Transact-SQL)

適用於：SQL Server 2016 (13.x) 和更新版本 Azure SQL 受控執行個體

預sp_execute_external_script存程式會執行提供做為程式輸入自變數的腳本，並搭配 機器學習 Services 和 Language Extensions 使用。

針對 機器學習 Services，Python 和 R 是支持的語言。 針對語言延伸模組，支援 Java，但必須使用 CREATE EXTERNAL LANGUAGE 來定義。

若要執行 sp_execute_external_script，您必須先安裝 機器學習 Services 或 Language Extensions。 如需詳細資訊，請參閱在 Windows 和 Linux 上安裝 SQL Server 機器學習 Services （Python 和 R），或在 Windows 和 Linux 上安裝 SQL Server 語言延伸模組。

Transact-SQL 語法慣例

Syntax

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'

指定為常值或變數輸入的外部語言腳本。 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 （Transact-SQL）。 您可以從資源管理員目錄檢視、DMV 和計數器取得工作負載的相關信息。 如需詳細資訊，請參閱 Resource Governor 目錄檢視 （Transact-SQL）、 資源管理員相關的動態管理檢視 （Transact-SQL）和 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 讓外部腳本針對每個分割區執行一次。 如需詳細資訊和範例，請參閱 教學課程：建立分割區型模型。

您可以藉由指定 @parallel = 1來平行執行文稿。 如果輸入查詢可以平行處理，您應該將 做為 自變數的一部分設定 @parallel = 1 為 sp_execute_external_script。 根據預設，查詢優化器會在超過 256 個數據列的數據表上 @parallel = 1 運作，但如果您想要明確地處理，此腳本會包含 參數作為示範。

提示

針對定型工作負載，您可以使用 @parallel 搭配任何任意的定型指令碼，甚至是使用非 Microsoft-rx 演算法的指令碼。 一般來說，只有 RevoScaleR 演算法 (具有 rx 前置詞) 在 SQL Server 的定型案例中提供平行處理原則。 但是，使用 SQL Server 2019 （15.x） 和更新版本中的新參數，您可以平行處理會呼叫未特別設計該功能的函式的腳本。

Python 和 R 腳本的串流執行

串流可讓 Python 或 R 腳本使用比記憶體中容納更多的數據。 若要控制在串流期間傳遞的數據列數目，請在集合中指定 參數 @r_rowsPerRead 的 @params 整數值。 例如，如果您要定型使用非常寬數據的模型，您可以調整值來讀取較少的數據列，以確保所有數據列都可以以一個區塊傳送。 您也可以使用此參數來管理一次讀取和處理的數據列數目，以減輕伺服器效能問題。

@r_rowsPerRead串流和@parallel自變數的參數都應該被視為提示。 若要套用提示，必須能夠產生包含平行處理的 SQL 查詢計劃。 如果無法這樣做，就無法啟用平行處理。

注意

只有 Enterprise Edition 才支援串流和平行處理。 您可以在 Standard Edition 的查詢中包含參數，而不會引發錯誤，但參數沒有任何作用，且 R 腳本會在單一進程中執行。

限制

資料類型

在輸入查詢或程序參數 sp_execute_external_script 中使用時，不支援下列數據類型，並傳回不支援的類型錯誤。

因應措施是 Transact-SQL CAST 中支援類型的數據行或值，再將它傳送至外部腳本。

• cursor
• timestamp
• datetime2、 datetimeoffset、 time
• sql_variant
• text、 image
• xml
• hierarchyid， geometry， geography
• CLR 使用者定義型別

一般而言，無法對應至 Transact-SQL 數據類型的任何結果集都會輸出為 NULL。

R 特有的限制

如果輸入包含 不符合 R 中允許值範圍的日期時間 值，則會將值轉換成 NA。 這是必要的，因為 SQL 機器學習允許比 R 語言支援更大的值範圍。

即使這兩種語言都使用 IEEE 754，SQL 機器學習仍不支援浮點數值（例如 、 +Inf-InfNaN、 ）。 目前的行為只會將值直接傳送至 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 套件。 如需詳細資訊，請參閱 在 SQL Server 上安裝其他 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