sp_execute_external_script (Transact-SQL)

適用対象: SQL Server 2016 (13.x) 以降 Azure SQL Managed Instance

ストアド プロシージャはsp_execute_external_script、プロシージャの入力引数として指定されたスクリプトを実行し、Machine ラーニング Services および Language Extensions で使用されます。

Machine ラーニング Services では、Python と R がサポートされている言語です。 言語拡張の場合、Java はサポートされていますが、CREATE EXTERNAL LANGUAGE で定義する必要があります。

実行sp_execute_external_scriptするには、まず Machine ラーニング Services または言語拡張機能をインストールする必要があります。 詳細については、「Windows および Linux に SQL Server Machine ラーニング Services (Python と R) をインストールする」または「Windows および Linux に SQL Server 言語拡張機能をインストールする」を参照してください。

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

引数

[ @language = ] N'language'

スクリプト言語を示します。 言語 は sysname です。 有効な値は、R、Python、CREATE EXTERNAL LANGUAGE (Java など) で定義されている任意の言語です。

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

パラメーターを に設定して、R スクリプトの並列実行を@parallel1有効にします。 このパラメーターの既定値は 0 (並列処理なし) です。 出力がクライアント コンピューターに直接ストリーミングされる場合 @parallel = 1 は、句が必要であり、 WITH RESULT SETS 出力スキーマを指定する必要があります。

• RevoScaleR 関数を使用しない R スクリプトの場合、このパラメーターを @parallel 使用すると、スクリプトを簡単に並列化できる場合に、大規模なデータセットを処理する場合に役立ちます。 たとえば、モデルで R predict 関数を使用して新しい予測を生成する場合は、クエリ エンジンへのヒントとして設定 @parallel = 1 します。 クエリを並列化できる場合、MAXDOP 設定に従って行が分散されます。

• RevoScaleR 関数を使用する R スクリプトの場合、並列処理は自動的に処理されるため、呼び出しにはsp_execute_external_script指定@parallel = 1しないでください。

[ @params = ] N'@parameter_name data_type' [ OUT |OUTPUT ] [ ,...n ]

外部スクリプトで使用される入力パラメーター宣言の一覧。

[ @parameter1 = ] 'value1' [ OUT |OUTPUT ] [ ,...n ]

外部スクリプトで使用される入力パラメーターの値の一覧。

解説

重要

クエリ ツリーは SQL 機械学習によって制御され、ユーザーはクエリに対して任意の操作を実行できません。

サポートされている言語で記述されたスクリプトを実行するために使用 sp_execute_external_script します。 サポートされている言語は、Machine ラーニング Services で使用される Python と R、および言語拡張機能で使用される CREATE EXTERNAL LANGUAGE (Java など) で定義されている言語です。

既定では、このストアド プロシージャによって返される結果セットは、名前のない列を含む出力です。 スクリプト内で使用される列名はスクリプト環境に対してローカルであり、出力された結果セットには反映されません。 結果セットの列に名前を付けるには、次のEXECUTE句をWITH RESULT SET使用します。

結果セットを返すだけでなく、OUTPUT パラメーターを使用してスカラー値を返すことができます。

外部リソース プールを構成することで、外部スクリプトで使用されるリソースを制御できます。 詳細については、「CREATE EXTERNAL RESOURCE POOL (Transact-SQL)」を参照してください。 ワークロードに関する情報は、リソース ガバナー カタログ ビュー、DMV、カウンターから取得できます。 詳細については、 リソース ガバナー カタログ ビュー (Transact-SQL)、 リソース ガバナー関連の動的管理ビュー (Transact-SQL)、および SQL Server の外部スクリプト オブジェクトに関するページを参照してください。

スクリプトの実行を監視する

sys.dm_external_script_requestsとsys.dm_external_script_execution_statsを使用してスクリプトの実行を監視します。

パーティション モデリングのパラメーター

パーティション分割されたデータのモデリングを有効にする 2 つの追加パラメーターを設定できます。パーティションは、データ セットを論理的なパーティションに自然に分割する 1 つ以上の列に基づいており、スクリプトの実行中にのみ作成および使用されます。 年齢、性別、地理的地域、日付または時刻の繰り返し値を含む列は、パーティション分割されたデータ セットに適した例です。

2 つのパラメーターは input_data_1_partition_by_columns と input_data_1_order_by_columnsで、2 番目のパラメーターを使用して結果セットを並べ替えます。 パラメーターは、パーティションごとに 1 回実行される外部スクリプトとの入力 sp_execute_external_script として渡されます。 詳細と例については、「チュートリアル: パーティション ベースのモデルを作成する」を参照してください。

を指定することで、スクリプトを @parallel = 1並列で実行できます。 入力クエリを並列化できる場合は、 @parallel = 1 引数 sp_execute_external_scriptの一部として . 既定では、クエリ オプティマイザーは 256 行を超えるテーブルで動作 @parallel = 1 しますが、これを明示的に処理する場合、このスクリプトにはデモとしてパラメーターが含まれます。

ヒント

トレーニング ワークロードの場合、Microsoft-rx 以外のアルゴリズムを使用している場合でも、任意のトレーニング スクリプトで @parallel を使用できます。 通常、SQL Server のトレーニング シナリオで並列処理が提供されるのは、RevoScaleR アルゴリズム (rx プレフィックスが付いたもの) だけです。 ただし、SQL Server 2019 (15.x) 以降のバージョンの新しいパラメーターを使用すると、その機能で特別に設計されていない関数を呼び出すスクリプトを並列化できます。

Python および R スクリプトのストリーミング実行

ストリーミングを使用すると、Python または R スクリプトでメモリに収まるよりも多くのデータを操作できます。 ストリーミング中に渡される行数を制御するには、コレクション内のパラメーター @r_rowsPerRead に整数値を @params 指定します。 たとえば、非常に広いデータを使用するモデルをトレーニングする場合は、すべての行を 1 つのデータ チャンクで送信できるように、読み取る行数を減らすように値を調整できます。 このパラメーターを使用して、サーバーのパフォーマンスの問題を軽減するために、一度に読み取りおよび処理される行の数を管理することもできます。

ストリーミングの @r_rowsPerRead パラメーターと引数の両方を @parallel ヒントと見なす必要があります。 ヒントを適用するには、並列処理を含む SQL クエリ プランを生成できる必要があります。 これが不可能な場合は、並列処理を有効にできません。

Note

ストリーミングと並列処理は、Enterprise Edition でのみサポートされます。 エラーを発生させずに Standard Edition のクエリにパラメーターを含めることができますが、パラメーターに影響はなく、R スクリプトは 1 つのプロセスで実行されます。

制限事項

データ型

次のデータ型は、入力クエリまたはプロシージャの sp_execute_external_script パラメーターで使用する場合はサポートされず、サポートされていない型エラーが返されます。

回避策として、外部スクリプトに送信する前に、 CAST 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 言語でサポートされている値よりも大きな範囲の値が許可されるために必要です。

両方の言語で I Enterprise Edition E 754 が使用されている場合でも、+InfFLOAT 値 (例: , -Inf, NaN) は SQL 機械学習ではサポートされていません。 現在の動作では、値が SQL に直接送信されます。その結果、SQL クライアントはエラーをスローします。 したがって、これらの値は NULL.

アクセス許可

EXECUTE ANY EXTERNAL SCRIPT データベース権限が必要です。

例

このセクションでは、このストアド プロシージャを使用して Transact-SQL を使用して R または Python スクリプトを実行する方法の例を示します。

A. R データ セットを SQL Server に返す

次の例では、R に含まれる Iris データセットを返すために使用 sp_execute_external_script するストアド プロシージャを作成します。

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 モデルを作成し、それからスコアを生成する

この例では、単純な Python モデルでスコアを生成するために使用 sp_execute_external_script する方法を示します。

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 するストアド プロシージャを作成します。

Note

この例では、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)
• external scripts enabled サーバー構成オプション
• SERVERPROPERTY (Transact-SQL)
• SQL Server のExternal Scripts オブジェクト
• sys.dm_external_script_requests
• sys.dm_external_script_execution_stats