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サービスの場合、PythonRはサポートされている言語です。 言語拡張機能の場合、Java はサポートされますが 、CREATE EXTERNAL LANGUAGE を使用して定義する必要があります

を実行 するにはsp_execute_external_script サービスまたは言語拡張機能Machine Learningインストールする必要があります。 詳細については、「 install SQL Server Machine Learning Services (Python and R) on Windows and Linux」または「install SQL Server Language Extensions on Windows and Linux」を参照してください。

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

Machine Learningサービスの場合、PythonRはサポートされている言語です。

を実行 するにはsp_execute_external_script サービスをインストールするMachine Learningがあります。 詳細については、「SQL Server Machine Learningに SQL Server Machine Learning Services (Python と R) をインストールする」を参照Windows。

この sp_execute_external_script プロシージャは、プロシージャの入力引数として指定されたスクリプトを実行し、2016 年 1 月 2016 日に R Services SQL Serverされます。

R Services の場合 、R はサポートされている言語です。

を実行 するにはsp_execute_external_script R Services をインストールする必要があります。 詳細については、「SQL Server Machine Learningに SQL Server Machine Learning Services (Python と R) をインストールする」を参照Windows。

この sp_execute_external_script ストアド プロシージャは、プロシージャの入力引数として指定されたスクリプトを実行し、Azure Machine Learning Services で使用SQL Managed Instance。

Machine Learningサービスの場合、PythonRはサポートされている言語です。

を実行 するにはsp_execute_external_script サービスを有効にするMachine Learningがあります。 詳細については、Azure のMachine Learning Services に関するドキュメントをSQL Managed Instanceしてください

トピック リンク アイコン 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 ] ]

2017 SQL Server以前の構文

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 です。 2017 SQL Server、有効な値は R と Python です

スクリプト言語を示します。 言語は sysname です。 2016 SQL Server、有効な値は R のみです

スクリプト言語を示します。 言語は sysname です。 Azure SQL Managed Instance、有効な値は R と Python です

@ script = N'script' リテラルまたは変数入力として指定された外部言語スクリプト。 scriptnvarchar(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 ] パラメーターを 1 に設定して、R スクリプトの @parallel 並列実行を有効にします。 このパラメーターの既定値は 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 Learning Services で使用される PythonR と、言語拡張機能で使用される CREATE EXTERNAL LANGUAGE (Java など) で定義されている任意の言語です。

サポート sp_execute_external_script 言語で記述されたスクリプトを実行するには、次のコマンドを使用します。 サポートされている言語は 、2017 SQL Server Machine Learning Services の Python と R です。

サポート sp_execute_external_script 言語で記述されたスクリプトを実行するには、次のコマンドを使用します。 サポートされている言語は、2016 R Services SQL Server R のみです。

サポート sp_execute_external_script 言語で記述されたスクリプトを実行するには、次のコマンドを使用します。 サポートされている言語は 、Azure SQL Managed Instance Machine Learning Services の Python と R です。

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

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

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

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

を使用してスクリプトの実行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 以降の新しいパラメーターを使用すると、その機能で特に設計されていない関数を呼び出すスクリプトを並列化できます。

Python スクリプトと R スクリプトのストリーミング実行

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

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

注意

ストリーミングと並列処理は、1 つのアプリケーションでのみEnterprise Edition。 エラーを発生することなく Standard Edition 内のクエリにパラメーターを含めできますが、パラメーターは効果を持たず、R スクリプトは 1 つのプロセスで実行されます。

制限

データ型

次のデータ型は、 プロシージャの入力クエリまたはパラメーターで使用する場合 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 に変換 されます。 これは、R 言語でSQLより大きな範囲の値を許可する機械学習の場合に必要です。

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

アクセス許可

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

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

A. R データ セットを次の値にSQL Server

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

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. データからデータに基づいて R モデルを生成SQL Server

次の例では、オブジェクトを使用してア sp_execute_external_script モデルを生成し、モデルを返すストアド プロシージャを作成します。

注意

この例では、e1071 パッケージを事前にインストールする必要があります。 詳細については、「 に追加のR パッケージをインストールする」をSQL Server。

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 のランタイムを呼び出さないので高速です。

関連項目