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


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

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

#### 前のステップ

[Lesson 5: T-SQLを使用したモデルのトレーニングと保存](http://qiita.com/qio9o9/items/5ddf586094fa5751a8ec)

# Lesson 6: モデルの利用

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

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

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

## バッチスコアリング

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

    ```SQL
    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
    ```

    ![sqldev-r-step6-1-gho9o9.png](https://qiita-image-store.s3.amazonaws.com/0/195839/4fc86b44-9520-3ccb-5d13-eb00923236bc.png)

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

    ストアドプロシージャ`PredictTip`は[Lesson 2: PowerShellを使用したSQL Serverへのデータインポート](http://qiita.com/qio9o9/items/c16ec818afd80fb7f232)を通じてSQL Serverに定義されています。

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

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

    ```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. 以下のように変数にクエリテキストを指定し、ストアドプロシージャのパラメータとして渡します。

    ```SQL
    -- 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;
    ```

    ![sqldev-r-step6-2-gho9o9.png](https://qiita-image-store.s3.amazonaws.com/0/195839/b40daad2-ab63-d239-3c0e-9c69c87b3edf.png)

## 個々のスコアリング

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

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

ストアドプロシージャ`PredictTipSingleMode`は[Lesson 2: PowerShellを使用したSQL Serverへのデータインポート](http://qiita.com/qio9o9/items/c16ec818afd80fb7f232)を通じてSQL Serverに定義されています。

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

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

    ```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モデルに基づいてスコアを作成します。

2. 以下のように手動でパラメータをセットしストアドプロシージャ`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
    ```
    
    ![sqldev-r-step6-3-gho9o9.png](https://qiita-image-store.s3.amazonaws.com/0/195839/1e8e08db-3e7b-989d-fe2b-15ea1ff2301f.png)

## まとめ

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

## リンク

#### 前のステップ

[Lesson 5: T-SQLを使用したモデルのトレーニングと保存](http://qiita.com/qio9o9/items/5ddf586094fa5751a8ec)

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

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

#### 出典

[Lesson 6: Operationalize the R model](https://docs.microsoft.com/en-us/sql/advanced-analytics/tutorials/sqldev-operationalize-the-model)

#### 関連項目

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