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。 您可以從資源管理員目錄檢視、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。 根據預設，查詢優化器會在超過 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 套件。 如需詳細資訊，請參閱 使用 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