sp_execute_external_script (Transact-SQL)

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

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

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

sp_execute_external_scriptを実行するには、まず Machine Learning Services または言語拡張機能をインストールする必要があります。 詳細については、「Windows での SQL Server Machine Learning Services (Python と R)のインストールLinux、または 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'

スクリプト言語を示します。 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 スクリプトの場合、並列処理は自動的に処理されるため、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を使用して、サポートされている言語で記述されたスクリプトを実行します。 サポートされている言語は、 Python と R Machine Learning Services で使用され、言語拡張で使用される CREATE EXTERNAL LANGUAGE (Java など) で定義されている言語です。

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

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

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

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

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 への入力として渡されます。 詳細と例については、「 Tutorial: パーティション ベースのモデルの作成」を参照してください。

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

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

Note

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

制限事項

データ型

次のデータ型は、 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 に固有の制限事項

入力に datetime R の値の許容範囲に適合しない値が含まれている場合、値は NAに変換されます。 これは、SQL 機械学習で R 言語でサポートされている値よりも大きな範囲の値が許可されるために必要です。

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

アクセス許可

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

例

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

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

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

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 を使用してあやめのモデルを生成し、モデルを返すストアド プロシージャを作成します。

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