【SQL Server 2017 による In-Database R 分析 チュートリアル】Lesson 6: モデルの利用

チュートリアルのはじめから

SQL開発者のための In-Database R 分析

前のステップ

Lesson 5: T-SQLを使用したモデルのトレーニングと保存

Lesson 6: モデルの利用

このステップでは、ストアドプロシージャを使用してモデルを利用する方法を学びます。このストアドプロシージャは、他のアプリケーションから直接呼び出され、新しい観測値の予測を行うことができます。このチュートリアルでは、ストアドプロシージャのRモデルを使用してスコアリングを実行する2つの方法を示します。

• バッチスコアリングモード：SELECTクエリをストアドプロシージャの入力として使用します。ストアドプロシージャは入力に対応する観測値のテーブルを戻します

• 個々のスコアリングモード：個々のパラメータ値のセットを入力として渡します。ストアドプロシージャは単一のレコードまたは値を返します。

バッチスコアリング

1. 今回は小さな入力データセットを扱います。以下のクエリは予測を行うために必要な乗客数および他の特徴列を備えた10件の運転記録を返します。

   SELECT TOP 10 a.passenger_count AS passenger_count,
       a.trip_time_in_secs AS trip_time_in_secs, 
       a.trip_distance AS trip_distance, 
       a.dropoff_datetime AS dropoff_datetime, 
       dbo.fnCalculateDistance(pickup_latitude, pickup_longitude, dropoff_latitude,dropoff_longitude) AS direct_distance
   FROM
   (
       SELECT medallion, hack_license, pickup_datetime, passenger_count,trip_time_in_secs,trip_distance,
       dropoff_datetime, pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude FROM nyctaxi_sample) AS a
   LEFT OUTER JOIN
   (
   SELECT medallion, hack_license, pickup_datetime
   FROM nyctaxi_sample
   ) AS b
   ON a.medallion=b.medallion AND a.hack_license=b.hack_license 
   AND a.pickup_datetime=b.pickup_datetime  
   WHERE b.medallion IS NULL
   

   

   このクエリの結果をバッチスコアリング用のストアドプロシージャPredictTipへの入力に使用します。

   ストアドプロシージャPredictTipはLesson 2: PowerShellを使用したSQL Serverへのデータインポートを通じてSQL Serverに定義されています。

2. Management Studioのオブジェクトエクスプローラで、[プログラミング]、[ストアドプロシージャ]の順に展開します。

3. PredictTipを右クリックし、[変更] を選択して新しいクエリウィンドウでTransact-SQLスクリプトを開きます。

   CREATE PROCEDURE [dbo].[PredictTip] @inquery nvarchar(max)  
   AS  
   BEGIN  
     
     DECLARE @lmodel2 varbinary(max) = (SELECT TOP 1 model  
     FROM nyc_taxi_models);  
     EXEC sp_execute_external_script @language = N'R',  
                                     @script = N'  
   mod <- unserialize(as.raw(model));  
   print(summary(mod))  
   OutputDataSet<-rxPredict(modelObject = mod, data = InputDataSet, outData = NULL,   
             predVarNames = "Score", type = "response", writeModelVars = FALSE, overwrite = TRUE);  
   str(OutputDataSet)  
   print(OutputDataSet)  
   ',  
     @input_data_1 = @inquery, 
     @params = N'@model varbinary(max)',
     @model = @lmodel2  
     WITH RESULT SETS ((Score float));  
     
   END  
     
   GO
   

   • SELECT文はデータベースからシリアライズされたモデルが取り出しR変数の modに格納します。
   • スコアリングを行う新しいケースは、ストアドプロシージャの最初のパラメータである@inqueryで指定されたTransact-SQLクエリから取得されます。クエリデータが読み込まれると、行はデフォルトデータフレームInputDataSetに保存されます。このデータフレームは、スコアを生成するRのrxPredict関数に渡されます。
   • rxPredict関数によって返される値は、floatです。これは、ドライバがチップを得られる確率を表します。

4. 以下のように変数にクエリテキストを指定し、ストアドプロシージャのパラメータとして渡します。

   -- Define the input data
   DECLARE @query_string nvarchar(max)
   SET @query_string='SELECT TOP 10 a.passenger_count AS passenger_count,
       a.trip_time_in_secs AS trip_time_in_secs, 
       a.trip_distance AS trip_distance, 
       a.dropoff_datetime AS dropoff_datetime, 
       dbo.fnCalculateDistance(pickup_latitude, pickup_longitude, dropoff_latitude,dropoff_longitude) AS direct_distance
   FROM
   (
       SELECT medallion, hack_license, pickup_datetime, passenger_count,trip_time_in_secs,trip_distance,
        dropoff_datetime, pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude FROM nyctaxi_sample) as a
   LEFT OUTER JOIN
   (
   SELECT medallion, hack_license, pickup_datetime
   FROM nyctaxi_sample
   TABLESAMPLE (70 percent) REPEATABLE (98052)
   ) as b
   ON a.medallion=b.medallion AND a.hack_license=b.hack_license 
   AND a.pickup_datetime=b.pickup_datetime  
   WHERE b.medallion IS NULL'
   
   -- Call the stored procedure for scoring and pass the input data
   EXEC [dbo].[PredictTip] @inquery = @query_string;
   

   

個々のスコアリング

場合によっては、アプリケーションから個々の値を渡して、それらの値に基づいて単一の結果を得たい場合もあります。 たとえば、ストアドプロシージャを呼び出し、ユーザーが入力または選択した、Excelワークシート、Webアプリケーション、またはReporting Servicesレポートを設定できます。

このセクションでは、ストアドプロシージャPredictTipSingleModeを使用して単一の予測を作成する方法を学習します。

ストアドプロシージャPredictTipSingleModeはLesson 2: PowerShellを使用したSQL Serverへのデータインポートを通じてSQL Serverに定義されています。

1. Management Studioのオブジェクトエクスプローラで、[プログラミング]、[ストアドプロシージャ]の順に展開します。

2. PredictTipSingleModeを右クリックし、[変更] を選択して新しいクエリウィンドウでTransact-SQLスクリプトを開きます。

   CREATE PROCEDURE [dbo].[PredictTipSingleMode] @passenger_count int = 0,
   @trip_distance float = 0,
   @trip_time_in_secs int = 0,
   @pickup_latitude float = 0,
   @pickup_longitude float = 0,
   @dropoff_latitude float = 0,
   @dropoff_longitude float = 0
   AS  
   BEGIN  
     DECLARE @inquery nvarchar(max) = N'  
     SELECT * FROM [dbo].[fnEngineerFeatures](@passenger_count,  
   @trip_distance,  
   @trip_time_in_secs,  
   @pickup_latitude,  
   @pickup_longitude,  
   @dropoff_latitude,  
   @dropoff_longitude)  
       '  
     DECLARE @lmodel2 varbinary(max) = (SELECT TOP 1 model  
     FROM nyc_taxi_models);  
     EXEC sp_execute_external_script @language = N'R',  
                                     @script = N'  
   mod <- unserialize(as.raw(model));  
   print(summary(mod))  
   OutputDataSet<-rxPredict(modelObject = mod, data = InputDataSet, outData = NULL,   
             predVarNames = "Score", type = "response", writeModelVars = FALSE, overwrite = TRUE);  
   str(OutputDataSet)  
   print(OutputDataSet)  
   ',  
   @input_data_1 = @inquery,  
   @params = N'@model varbinary(max),@passenger_count int,@trip_distance float,@trip_time_in_secs int ,  
   @pickup_latitude float ,@pickup_longitude float ,@dropoff_latitude float ,@dropoff_longitude float',  
   @model = @lmodel2,  
   @passenger_count =@passenger_count ,  
   @trip_distance=@trip_distance,  
   @trip_time_in_secs=@trip_time_in_secs,    
   @pickup_latitude=@pickup_latitude,  
   @pickup_longitude=@pickup_longitude,  
   @dropoff_latitude=@dropoff_latitude,  
   @dropoff_longitude=@dropoff_longitude  
     WITH RESULT SETS ((Score float));  
   END
   

   • このストアドプロシージャは、乗客数、旅行距離などの複数の単一値を入力として受け取ります。
     ストアドプロシージャを外部アプリケーションから呼び出す場合は、データがRモデルの要件と一致していることを確認してください。これには、入力データをキャストしてRデータ型に変換したり、データ型とデータ長を検証することができます。
   • ストアドプロシージャは、格納されたRモデルに基づいてスコアを作成します。

3. 以下のように手動でパラメータをセットしストアドプロシージャPredictTipSingleModeを実行します。

   EXEC [dbo].[PredictTipSingleMode]
   @passenger_count = 1,
   @trip_distance = 2.5,
   @trip_time_in_secs = 631,
   @pickup_latitude = 40.763958,
   @pickup_longitude = -73.973373,
   @dropoff_latitude =  40.782139,
   @dropoff_longitude = -73.977303
   

   

まとめ

このチュートリアルでは、ストアドプロシージャに埋め込まれたRコードを操作する方法を学びました。Transact-SQLとの統合により、予測のためのPythonモデルのデプロイメントやエンタープライズデータワークフローの一部としてのモデル再トレーニングの組み込みがさらに簡単になることを確認しました。

リンク

前のステップ

Lesson 5: T-SQLを使用したモデルのトレーニングと保存

チュートリアルのはじめから

SQL開発者のための In-Database R 分析

出典

Lesson 6: Operationalize the R model

関連項目

Machine Learning Services with R