sp_execute_external_script (Transact-SQL)

適用於：SQL Server 2016 (13.x) 和更新版本 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 ] ]
[ ; ]

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'

[ @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。

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

外部文本所使用之輸入參數的值清單。

備註

根據預設，這個預存程式所傳回的結果集是具有未命名數據行的輸出。 腳本中使用的數據行名稱是腳本環境的本機名稱，而且不會反映在輸出的結果集中。 若要命名結果集數據行，請使用 WITH RESULT SET EXECUTE的子句。

除了傳回結果集之外，您還可以使用 OUTPUT 參數將純量值傳回。

監視腳本執行

使用 sys.dm_external_script_requests 和 sys.dm_external_script_execution_stats監視腳本執行。

Python 和 R 腳本的串流執行

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

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

限制

資料類型

在輸入查詢或程序參數 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要使用的資料行名稱和資料類型。

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

為了評分，您也可以使用原生 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