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

#### チュートリアルのはじめから
[SQL開発者のための In-Database R 分析](http://qiita.com/qio9o9/items/4f020bb93dc07567e556)

#### 前のステップ
[Lesson 4: T-SQLを使用したデータの特徴抽出](http://qiita.com/qio9o9/items/9d53c2aac86414c330ab)

#### 次のステップ
[Lesson 6: モデルの利用](http://qiita.com/qio9o9/items/0f524b9328e80c4fc928)

# 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へのデータインポート](http://qiita.com/qio9o9/items/c16ec818afd80fb7f232)を通じてSQL Serverに定義されています。

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

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

    ```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モデルを生成する

1. ストアドプロシージャ`TrainTipPredictionModel`を呼び出します。

    ```SQL
    EXEC [dbo].[TrainTipPredictionModel]
    ```
    
    ![sqldev-r-step5-1-gho9o9.png](https://qiita-image-store.s3.amazonaws.com/0/195839/93f80dfe-6a5c-75eb-acbd-d12bb6e9c2ea.png)

2. Management Studio のメッセージウィンドウでRの標準出力メッセージを確認してください。

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

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

3. ステートメントが完了したら、nyc_taxi_modelsテーブルを開きます。

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

    ![sqldev-r-step5-2-gho9o9.png](https://qiita-image-store.s3.amazonaws.com/0/195839/be074635-e4d4-443c-bdc4-1c717e9857dc.png)

次のステップでは、訓練されたモデルを使用して予測を作成します。

## リンク

#### 次のステップ

[Lesson 6: モデルの利用](http://qiita.com/qio9o9/items/0f524b9328e80c4fc928)

#### 前のステップ

[Lesson 4: T-SQLを使用したデータの特徴抽出](http://qiita.com/qio9o9/items/9d53c2aac86414c330ab)

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

[SQL開発者のための In-Database R 分析](http://qiita.com/qio9o9/items/4f020bb93dc07567e556)

#### 出典
[Lesson 5: Train and save a model using T-SQL](https://docs.microsoft.com/en-us/sql/advanced-analytics/r/sqldev-train-and-save-a-model-using-t-sql)

#### 関連項目

[Machine Learning Services with R](https://docs.microsoft.com/en-us/sql/advanced-analytics/r/sql-server-r-services)