More than 5 years have passed since last update.

【SQL Server 2017 による In-Database R 分析チュートリアル】Lesson 5: T-SQLを使用したモデルのトレーニングと保存

Posted at 2017-09-06

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

このレッスンでは、Rを使用して機械学習モデルをトレーニングする方法を学習します。作成したデータ特徴を使用してモデルをトレーニングし、訓練されたモデルをSQL Serverのテーブルに保存します。

モデルのトレーニングのためのストアドプロシージャを作成する

T-SQLからRを呼び出すときは、システムストアドプロシージャsp_execute_external_scriptを使用します。ここではモデルトレーニングを繰り返すことを念頭に、sp_execute_exernal_scriptをカプセル化した別のストアド・プロシージャTrainTipPredictionModelを作成します。

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

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

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

T-SQL

CREATE PROCEDURE [dbo].[TrainTipPredictionModel]

AS
BEGIN
  DECLARE @inquery nvarchar(max) = N'
    select tipped, fare_amount, passenger_count,trip_time_in_secs,trip_distance,
    pickup_datetime, dropoff_datetime,
    dbo.fnCalculateDistance(pickup_latitude, pickup_longitude,  dropoff_latitude, dropoff_longitude) as direct_distance
    from nyctaxi_sample
    --tablesample (70 percent) repeatable (98052)
'
  -- Insert the trained model into a database table
  INSERT INTO nyc_taxi_models
  EXEC sp_execute_external_script @language = N'R',
                                  @script = N'

## Create model
logitObj <- rxLogit(tipped ~ passenger_count + trip_distance + trip_time_in_secs + direct_distance, data = InputDataSet)
summary(logitObj)

## Serialize model and put it in data frame
trained_model <- data.frame(model=as.raw(serialize(logitObj, NULL)));
',
  @input_data_1 = @inquery,
  @output_data_1_name = N'trained_model'
  ;

END
GO

SELECTクエリはカスタムスカラ関数fnCalculateDistanceを使用して、乗車位置と降車位置の間の直接距離を計算します。クエリの結果はデフォルトのR入力変数InputDatasetに格納されます。
Rスクリプトは、R Services (In-Database)に含まれるRevoScaleRライブラリのrxLogit関数を呼び出して、ロジスティック回帰モデルを作成します。tippedをラベル（目的変数）に、passenger_count、trip_distance、trip_time_in_secs、およびdirect_distanceを特徴値列（説明変数）としてモデルを作成します。
R変数logitObjで示される訓練済みモデルはシリアライズされ出力パラメータとして返ります。この出力をnyc_taxi_modelsテーブルに登録することで、将来の予測に繰り返し使用することができます。

ストアドプロシージャ`TrainTipPredictionModel`を使用してRモデルを生成する

ストアドプロシージャTrainTipPredictionModelを呼び出します。
```
EXEC [dbo].[TrainTipPredictionModel]
```
Management Studio のメッセージウィンドウでRの標準出力メッセージを確認してください。

"STDOUT message(s) from external script: Rows Read: 1703957, Total Rows Processed: 1703957, Total Chunk Time: 0.049 seconds "

個々の関数に固有のメッセージ rxLogitが表示され、モデル作成の一部として生成された変数とテストメトリックが表示されます。
ステートメントが完了したら、nyc_taxi_modelsテーブルを開きます。

テーブルに新しいレコードが1つ追加され、シリアライズされたモデルが登録されていることを確認します。