【SQL Server 2017 による In-Database R 分析 チュートリアル】Lesson 3: データの探索と可視化

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

#### 前のステップ
[Lesson 2: PowerShellを使用したSQL Serverへのデータインポート](http://qiita.com/qio9o9/items/c16ec818afd80fb7f232)

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

# Lesson 3: データの探索と可視化

このレッスンでは、サンプルデータを探索しR関数を使用していくつかのプロットを生成します。これらのR関数は、すでにRサービス（データベース内）に含まれています。 Transact-SQLからR関数を呼び出すことができます。

## データの確認

データサイエンスソリューション開発には、通常、集中的なデータの探索とデータの可視化が含まれます。まずサンプルデータを確認してください。

元のデータセットにはタクシー識別子と運転記録が別々のファイルで提供されていますが、サンプルデータを使いやすくするためにmedallion、hack_license、pickup_datetimeをキーにジョインしています。また使用するレコードは、元のレコード数の1％でサンプリングしています。サンプリングされたデータセットは1,703,957の行と23の列を持っています。

**タクシー識別子**

- medallion列はタクシーの一意なID番号を示します。
- hack_license列は運転手の匿名化された運転免許証番号を示します。

**運転記録および運賃記録**

- 各運転記録には、乗車と降車の場所と時間、および運転距離が含まれます。
- 各運賃記録には、支払タイプ、合計支払い額、チップ金額などの支払情報が含まれます。
- 最後の3つの列は、さまざまな機械学習タスクに使用できます。
    - tip_amount列は連続した数値が含まれ、回帰分析のためのラベル列（目的変数）として使用できます。
    - tipped列は yes / no 値のみがあり、二項分類のためのラベル列（目的変数）として使用できます。
    - tip_class列は複数の**クラスラベル**があり、多項分類のためのラベル列（目的変数）として使用できます。

- ラベル列（目的変数）として使用される値はtip_amount列に基づいています。
  
    |列|ルール|
    |:--|:--|
    |tipped|If tip_amount > 0, tipped = 1, otherwise tipped = 0|
    |tip_class|Class 0: tip_amount = 0ドル <br/> Class 1: tip_amount > 0ドル and tip_amount <= 5ドル <br/> Class 2: tip_amount > 5ドル and tip_amount <= 10ドル <br/> Class 3: tip_amount > 10ドル and tip_amount <= 20ドル <br/> Class 4: tip_amount > 20ドル|

## T-SQL内のRでプロットを作成する

可視化はデータと異常値の分布を理解するために重要で、Rにはデータ可視化のための多くのパッケージが提供されています。Rの標準ディストリビューションは、ヒストグラム、散布図、箱ひげ図、およびその他のデータ探索グラフを作成するための多くの機能を含みます。

Rは画像出力にR deviceを用います。このデバイスの出力をvarbinaryデータ型でキャプチャしアプリ内でレンダリングするもしくはファイル（.JPG、.PDFなど）に保存します。

このセクションでは、ストアドプロシージャを使用して各タイプの出力を操作する方法を学習します。全体の流れは次のとおりです。

- varbinaryデータとしてRプロットを生成するストアドプロシージャを作成する

- プロットを生成して画像ファイルに保存する

- ストアドプロシージャを使用してバイナリプロットデータをJPGまたはPDFファイルに変換する

## ストアドプロシージャPlotHistogramを作成する

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

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

2. `PlotHistogram`を右クリックし、[変更] を選択して新しいクエリウィンドウでTransact-SQLスクリプトを開きます。
    
    ```SQL:T-SQL
    CREATE PROCEDURE [dbo].[PlotHistogram]
    AS
    BEGIN
      SET NOCOUNT ON;
      DECLARE @query nvarchar(max) =  
      N'SELECT tipped FROM nyctaxi_sample'  
      EXECUTE sp_execute_external_script @language = N'R',  
                                         @script = N'  
       image_file = tempfile();  
       jpeg(filename = image_file);  
       #Plot histogram  
       rxHistogram(~tipped, data=InputDataSet, col=''lightgreen'',   
       title = ''Tip Histogram'', xlab =''Tipped or not'', ylab =''Counts'');  
       dev.off();  
       OutputDataSet <- data.frame(data=readBin(file(image_file, "rb"), what=raw(), n=1e6));  
       ',  
       @input_data_1 = @query  
       WITH RESULT SETS ((plot varbinary(max)));  
    END
    GO
    ```
    - 変数 `@query`は、スクリプト入力変数`@input_data_1`への引数としてRスクリプトに渡されるクエリテキスト(`'SELECT tipped FROM nyctaxi_sample'`)を定義    します。
    - R変数`image_file`はイメージを格納します。`rxHistogram`関数はプロットを生成します。
    - R deviceは**off**に設定します。
      Rではプロットコマンドを発行するとグラフィックスウィンドウが開きます。もしファイルへの書き込みや別の方法で出力したい場合はdeviceをオフにします。
    - Rグラフィックスオブジェクトは、出力のためにR data.frameにシリアル化されます。

## 画像ファイルを生成しファイルに保存します

ストアドプロシージャ`PlotHistogram`は画像をvarbinaryデータのストリームとして返します。これをbcpユーティリティで使用しイメージファイルとして保存します。

1. Management Studioで以下のクエリを実行します。
  
    ```SQL:T-SQL
    EXEC [dbo].[PlotHistogram]
    ```
  
    ![sqldev-r-step3-1-gho9o9.png](https://qiita-image-store.s3.amazonaws.com/0/195839/6046eb80-43e9-e2b8-9ae3-2b0ecb32fce7.png)

2. コマンドプロンプトを使用し、適切なインスタンス名、データベース名、ユーザー名、資格情報を指定して次のコマンドを実行します。
  
     ```CMD
     bcp "exec PlotHistogram" queryout "plot.jpg" -S <SQL Server instance name> -d <database name> -U <user name> -P <password>
     ```

3. 接続に成功すると、画像ファイルに関する詳細情報の入力が求められます。次の変更を除きENTERで進みます。
    - prefix-length of field plotに0を入力します。
    - フォーマットファイルを保存する場合は、yを入力します。
    ![sqldev-r-step3-2-gho9o9.png](https://qiita-image-store.s3.amazonaws.com/0/195839/b294ba4e-d566-6e7e-33a5-6be42612be31.png)
    
    > [!TIP]
    > フォーマットファイル（bcp.fmt）の保存を指定するとbcpユーティリティはフォーマット定義を生成します。このフォーマット定義は、将来画像ファイルフォーマットオプションを要求されることなく同様のコマンドに適用できます。 書式ファイルを使用するには、コマンドラインに `-f bcp.fmt`を追加します。

4. 出力ファイル（plot.jpg）は、コマンドを実行したディレクトリに作成されます。
  
    ![sqldev-r-step3-3-gho9o9.jpg](https://qiita-image-store.s3.amazonaws.com/0/195839/92aa0dfe-8c3e-dc5f-19d5-329c90dca5f4.jpeg)
  
## 表示可能なファイルにプロットデータをエクスポートします。

Rプロットをバイナリデータ型に出力することは、アプリケーションでの使用には便利ですが、データ探索段階でレンダリングされたプロットを必要とするデータ分析者にとっては不便です。一般的にデータ分析者は様々な視点からデータへの洞察を得るために、複数のデータ視覚化を生成します。

ここでは、ヒストグラム、散布図、およびその他のR画像ファイルを.JPGおよび.PDF形式に書き込むストアドプロシージャ`PlotInOutputFiles`を使用します。

`PlotInOutputFiles`は[Lesson 2: PowerShellを使用したSQL Serverへのデータインポート](http://qiita.com/qio9o9/items/c16ec818afd80fb7f232)を通じてSQL Serverに定義されています。
    
1. Management Studioのオブジェクトエクスプローラで、[プログラミング]、[ストアドプロシージャ]の順に展開します。

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

    ```SQL:T-SQL
    CREATE PROCEDURE [dbo].[PlotInOutputFiles]  
    AS  
    BEGIN  
      SET NOCOUNT ON;  
      DECLARE @query nvarchar(max) =  
      N'SELECT cast(tipped as int) as tipped, tip_amount, fare_amount FROM [dbo].[nyctaxi_sample]'  
      EXECUTE sp_execute_external_script @language = N'R',  
      @script = N'  
       # Set output directory for files and check for existing files with same names   
        mainDir <- ''C:\\temp\\plots''  
        dir.create(mainDir, recursive = TRUE, showWarnings = FALSE)  
        setwd(mainDir);  
        print("Creating output plot files:", quote=FALSE)
        
        # Open a jpeg file and output histogram of tipped variable in that file.  
        dest_filename = tempfile(pattern = ''rHistogram_Tipped_'', tmpdir = mainDir)  
        dest_filename = paste(dest_filename, ''.jpg'',sep="")  
        print(dest_filename, quote=FALSE);  
        jpeg(filename=dest_filename);  
        hist(InputDataSet$tipped, col = ''lightgreen'', xlab=''Tipped'',   
            ylab = ''Counts'', main = ''Histogram, Tipped'');  
         dev.off();  

        # Open a pdf file and output histograms of tip amount and fare amount.   
        # Outputs two plots in one row  
        dest_filename = tempfile(pattern = ''rHistograms_Tip_and_Fare_Amount_'', tmpdir = mainDir)  
        dest_filename = paste(dest_filename, ''.pdf'',sep="")  
        print(dest_filename, quote=FALSE);  
        pdf(file=dest_filename, height=4, width=7);  
        par(mfrow=c(1,2));  
        hist(InputDataSet$tip_amount, col = ''lightgreen'',   
            xlab=''Tip amount ($)'',   
            ylab = ''Counts'',   
            main = ''Histogram, Tip amount'', xlim = c(0,40), 100);  
        hist(InputDataSet$fare_amount, col = ''lightgreen'',   
            xlab=''Fare amount ($)'',   
            ylab = ''Counts'',   
            main = ''Histogram,   
            Fare amount'',   
            xlim = c(0,100), 100);  
       dev.off();  
  
        # Open a pdf file and output an xyplot of tip amount vs. fare amount using lattice;  
        # Only 10,000 sampled observations are plotted here, otherwise file is large.  
        dest_filename = tempfile(pattern = ''rXYPlots_Tip_vs_Fare_Amount_'', tmpdir = mainDir)  
        dest_filename = paste(dest_filename, ''.pdf'',sep="")  
        print(dest_filename, quote=FALSE);  
        pdf(file=dest_filename, height=4, width=4);  
        plot(tip_amount ~ fare_amount,   
            data = InputDataSet[sample(nrow(InputDataSet), 10000), ],   
            ylim = c(0,50),   
            xlim = c(0,150),   
            cex=.5,   
            pch=19,   
            col=''darkgreen'',    
            main = ''Tip amount by Fare amount'',   
            xlab=''Fare Amount ($)'',   
            ylab = ''Tip Amount ($)'');   
        dev.off();',  
     @input_data_1 = @query  
     END
    ```
    - ストアドプロシージャ内のSELECTクエリの出力はRデータフレーム`InputDataSet`に格納され、このデータを様々なRプロット関数を呼び出し画像ファイルを生成します。
    - ファイルはローカルフォルダ`C:\temp\Plots\\`に保存しています。例えばコピー先フォルダをストアドプロシージャの引数に定義し、R変数`mainDir`に渡すことで、保存先フォルダを変更することができます。

3. Management Studio で以下のクエリを実行します。

    ```SQL:T-SQL
    EXEC [dbo].[PlotInOutputFiles]
    ```

    ![sqldev-r-step3-3-gho9o9.png](https://qiita-image-store.s3.amazonaws.com/0/195839/5aef6f10-476e-f5a4-f569-e6329f41e7bb.png)
    
    ファイル名の数字はランダムに生成され、既存のファイルに書き込むときにエラーが発生しないようにしています。

4. `C:\temp\Plots\\`に作成されたファイルを確認します。
    - `rHistogram_Tipped.jpg`ファイルは、チップが得られた数と得られなかった数を示します。
    ![sqldev-r-step3-4-gho9o9.jpg](https://qiita-image-store.s3.amazonaws.com/0/195839/ddbc1099-872e-0d09-d239-a5980f87fa83.jpeg)
    - `rHistograms_Tip_and_Fare_Amount.pdf`ファイルは、運賃とチップ金額の分布を示します。
    ![sqldev-r-step3-5-gho9o9.png](https://qiita-image-store.s3.amazonaws.com/0/195839/89447220-cddc-9f37-0170-01870dd74f58.png)
    - `rXYPlots_Tip_vs_Fare_Amount.pdf`ファイルは、x軸上に運賃、y軸上にチップ金額とした散布図です。
    ![sqldev-r-step3-6-gho9o9.png](https://qiita-image-store.s3.amazonaws.com/0/195839/a833a70d-a4bb-a369-8883-9cdc76f26ef7.png)

## リンク

#### 次のステップ

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

#### 前のステップ

[Lesson 2: PowerShellを使用したSQL Serverへのデータインポート](http://qiita.com/qio9o9/items/c16ec818afd80fb7f232)

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

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

#### 出典

[Lesson 3: Explore and visualize the data](https://docs.microsoft.com/en-us/sql/advanced-analytics/tutorials/sqldev-explore-and-visualize-the-data)

#### 関連項目

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