More than 5 years have passed since last update.

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

Last updated at 2017-09-06Posted at 2017-09-06

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ドル Class 1: tip_amount > 0ドル and tip_amount <= 5ドル Class 2: tip_amount > 5ドル and tip_amount <= 10ドル Class 3: tip_amount > 10ドル and tip_amount <= 20ドル 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へのデータインポートを通じてSQL Serverに定義されています。

Management Studioのオブジェクトエクスプローラで、[プログラミング]、[ストアドプロシージャ]の順に展開します。
PlotHistogramを右クリックし、[変更] を選択して新しいクエリウィンドウでTransact-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ユーティリティで使用しイメージファイルとして保存します。

Management Studioで以下のクエリを実行します。
T-SQL
```
EXEC [dbo].[PlotHistogram]
```
コマンドプロンプトを使用し、適切なインスタンス名、データベース名、ユーザー名、資格情報を指定して次のコマンドを実行します。
```
bcp "exec PlotHistogram" queryout "plot.jpg" -S <SQL Server instance name> -d <database name> -U <user name> -P <password>
```
接続に成功すると、画像ファイルに関する詳細情報の入力が求められます。次の変更を除きENTERで進みます。
- prefix-length of field plotに0を入力します。
- フォーマットファイルを保存する場合は、yを入力します。
[!TIP]
フォーマットファイル（bcp.fmt）の保存を指定するとbcpユーティリティはフォーマット定義を生成します。このフォーマット定義は、将来画像ファイルフォーマットオプションを要求されることなく同様のコマンドに適用できます。書式ファイルを使用するには、コマンドラインに -f bcp.fmtを追加します。
出力ファイル（plot.jpg）は、コマンドを実行したディレクトリに作成されます。

表示可能なファイルにプロットデータをエクスポートします。

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

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

PlotInOutputFilesはLesson 2: PowerShellを使用したSQL Serverへのデータインポートを通じてSQL Serverに定義されています。

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

PlotInOutputFilesを右クリックし、[変更] を選択して新しいクエリウィンドウでTransact-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に渡すことで、保存先フォルダを変更することができます。

Management Studio で以下のクエリを実行します。
T-SQL
```
EXEC [dbo].[PlotInOutputFiles]
```
ファイル名の数字はランダムに生成され、既存のファイルに書き込むときにエラーが発生しないようにしています。
C:\temp\Plots\\に作成されたファイルを確認します。
- rHistogram_Tipped.jpgファイルは、チップが得られた数と得られなかった数を示します。
- rHistograms_Tip_and_Fare_Amount.pdfファイルは、運賃とチップ金額の分布を示します。
- rXYPlots_Tip_vs_Fare_Amount.pdfファイルは、x軸上に運賃、y軸上にチップ金額とした散布図です。