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 Machine Learning Services (Python 和 R) ，或在 Windows 和Linux上安裝 SQL Server 語言延伸模組。

sp_execute_external_script預存程式會執行提供作為程式輸入引數的腳本，並與 SQL Server 2017 上的Machine Learning Services搭配使用。

針對機器學習服務， Python 和 R 是支援的語言。

若要執行 sp_execute_external_script，您必須先安裝機器學習服務。 如需詳細資訊，請參閱在 Windows 上安裝 SQL Server Machine Learning Services (Python 和 R) 。

sp_execute_external_script預存程式會執行提供作為程式輸入引數的腳本，並與 SQL Server 2016 上的R 服務搭配使用。

針對 R 服務， R 是支援的語言。

若要執行 sp_execute_external_script，您必須先安裝 R 服務。 如需詳細資訊，請參閱在 Windows 上安裝 SQL Server Machine Learning Services (Python 和 R) 。

sp_execute_external_script預存程式會執行提供作為程式輸入引數的腳本，並與Azure SQL 受控執行個體 中的機器學習服務搭配使用。

針對機器學習服務， Python 和 R 是支援的語言。

若要執行 sp_execute_external_script，您必須先啟用機器學習服務。 如需詳細資訊，請參閱Azure SQL 受控執行個體 檔中的機器學習服務。

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

SQL Server 2017 和更早版本的語法

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 中，有效值為R和Python。

表示指令碼語言。 語言 為 sysname。 在 SQL Server 2016 中，唯一的有效值為R。

表示指令碼語言。 語言 為 sysname。 在Azure SQL 受控執行個體中，有效值為R和Python。

@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 ，以及使用 CREATE EXTERNAL LANGUAGE (定義的任何語言，例如，搭配語言延伸模組使用的 JAVA) 。

使用 sp_execute_external_script 來執行以支援的語言撰寫的腳本。 支援的語言是 SQL Server 2017 機器學習服務中的Python和R。

使用 sp_execute_external_script 來執行以支援的語言撰寫的腳本。 唯一支援的語言是 SQL Server 2016 R Services 中的R。

使用 sp_execute_external_script 來執行以支援的語言撰寫的腳本。 支援的語言是Azure SQL 受控執行個體機器學習服務中的Python和R。

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

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

您可以藉由設定外部資源集區來控制外部腳本所使用的資源。 如需詳細資訊，請參閱 CREATE EXTERNAL RESOURCE POOL (Transact-SQL) 。 您可以從資源管理員目錄檢視、DMV 和計數器取得工作負載的相關資訊。 如需詳細資訊，請參閱Resource Governor目錄檢視 (Transact-SQL) 、Resource Governor相關的動態管理檢視 (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 和更新版本中的新參數，您可以平行處理呼叫未使用該功能特別設計之函式的腳本。

Python 和 R 腳本的串流執行

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

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

注意

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

限制

資料類型

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

因應措施是，將資料行或值 轉換成 Transact-SQL 中支援的類型，然後再將它傳送至外部腳本。

• cursor

• timestamp

• datetime2， datetimeoffset， time

• sql_variant

• text， image

• xml

• hierarchyid， geometry， geography

• CLR 使用者定義型別

一般而言，無法對應到 Transact-SQL 資料類型的任何結果集都會輸出為 Null。

R 特有的限制

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

即使這兩種語言都使用 IEEE 754， +Inf-InfNaN SQL 機器學習中仍不支援浮點數 (、) 。 目前的行為只會直接將值傳送至 SQL;因此，SQL 用戶端會擲回錯誤。 因此，這些值會轉換成 Null。

權限

需要 EXECUTE ANY EXTERNAL SCRIPT 資料庫許可權。

範例

本節包含如何使用此預存程式，以使用 Transact-SQL 執行 R 或 Python 腳本的範例。

A. 傳回 R 資料集以SQL Server

下列範例會建立預存程式，此預存程式會使用 sp_execute_external_script 傳回 R 隨附的鳶尾花資料集。

DROP PROC IF EXISTS get_iris_dataset;  
go  
CREATE PROC 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 PROC IF EXISTS generate_iris_model;
GO
CREATE PROC 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