R チュートリアル:データの調査と視覚化

[アーティクル]
05/30/2023

適用対象: SQL Server 2016 (13.x) 以降 Azure SQL Managed Instance

全 5 回からなるこのチュートリアルシリーズの第 2 回では、サンプルデータを探索し、いくつかのプロットを生成します。後で、Python でグラフィックスオブジェクトをシリアル化し、それらのオブジェクトを逆シリアル化してプロットする方法を学習します。

全 5 回から成るこのチュートリアルシリーズの第 2 回では、サンプルデータを確認し、base R の汎用 barplot と hist 関数を使用して、プロットをいくつか生成します。

この記事の主な目的は、ストアドプロシージャの Transact-SQL から R 関数を呼び出し、結果をアプリケーションファイル形式で保存する方法を示すことです。

barplot を使用してストアドプロシージャを作成し、varbinary データとして R プロットを生成します。 bcp を使用して、バイナリストリームをイメージファイルにエクスポートします。
hist を使用してストアドプロシージャを作成し、結果を JPG および PDF 出力として保存します。

Note

視覚化はデータの形状と分布を理解するための強力なツールであるため、R には、ヒストグラム、散布図、ボックスプロット、および他のデータ探索グラフを生成するための幅広い関数とパッケージが用意されています。 R は、通常、グラフィカル出力用の R デバイスを使用してイメージを作成します。これをキャプチャし、アプリケーションでレンダリングするために varbinary データ型として保存することができます。また、任意のサポートファイル形式 (.JPG、.PDF など) に画像を保存することもできます。

この記事では、次のことを行います。

サンプルデータを確認する
T-SQL で R を使用してプロットを作成する
複数のファイル形式でプロットを出力する

パート 1 では、前提条件をインストールしてサンプルデータベースを復元しました。

第 3 回では、Transact-SQL 関数を使用して生データから特徴を作成する方法について説明します。その後、その関数をストアドプロシージャから呼び出し、機能の値を含むテーブルを作成します。

第 4 回では、モジュールを読み込み、必要な関数を呼び出して、SQL Server ストアドプロシージャを使用してモデルを作成およびトレーニングします。

第 5 回では、第 4 回でトレーニングして保存したモデルを運用化する方法について説明します。

データを確認する

データ科学ソリューションの開発には、通常、集中的なデータの探索とデータの視覚化が伴います。最初に、サンプルデータを確認してください (まだ確認していない場合)。

元のパブリックデータセットでは、タクシーの識別情報と乗車レコードが別々のファイルに入力されていました。サンプルデータの利用を簡単にするために、 medallion、 hack_license、 pickup_datetime列で元のデータセットを結合しました。レコードもサンプリングされており、元のレコード数の 1% だけが取得されています。結果的にダウンサンプリングされたデータセットは 1,703,957 行と 23 列です。

タクシーの識別子

medallion 列は、タクシーの一意の ID を表します。
hack_license 列には、タクシー運転手のライセンス番号が含まれます (匿名にしてあります)。

乗車と料金の記録

各乗車記録には、乗車場所、降車場所、走行距離が含まれています。
各料金記録には、支払の種類、支払の合計額、チップ額など、支払情報が含まれています。
最後の 3 つの列はさまざまな機械学習タスクに利用できます。 tip_amount 列には連続する数値が含まれ、回帰分析用の label 列として利用できます。 tipped 列には yes/no 値のみが含まれ、二項分類に利用されます。 tip_class 列には複数の クラスラベル が含まれ、複数クラスの分類タスク用のラベルとして利用できます。

このチュートリアルでは、二項分類タスクのみを紹介します。他の 2 つの機械学習タスク (回帰と複数クラスの分類) でもモデルの構築をお試しください。

ラベル列に使用される値はすべて tip_amount 列に基づき、次のようなビジネスルールが適用されます。

派生列名	ルール
tipped	tip_amount > 0 の場合、tipped = 1、それ以外では tipped = 0
tip_class	クラス 0: tip_amount = $0 クラス 1: tip_amount > $5 および tip_amount <= $10 クラス 2: tip_amount > $5 および tip_amount <= $10 クラス 3: tip_amount > $10 および tip_amount <= $20 クラス 4: tip_amount > $20

T-SQL で R を使用してプロットを作成する

重要

SQL Server 2019 以降、この分離メカニズムを使用して、プロットファイルが保存されるディレクトリに適切なアクセス許可を与える必要があります。これらのアクセス許可の設定方法の詳細については、「Windows 上の SQL Server 2019:Machine Learning Services」の「ファイルのアクセス許可」セクションを参照してください。

プロットを作成するには、R 関数 barplot を使用します。この手順では、Transact-SQL クエリのデータに基づいてヒストグラムをプロットします。この関数は、ストアドプロシージャ RPlotHistogram でラップすることができます。

SQL Server Management Studio では、オブジェクトエクスプローラーで、NYCTaxi_Sample データベースを右クリックし、 [新しいクエリ] を選択します。または、Azure Data Studio の [ファイル] メニューで、 [新しいノートブック] を選択して、データベースに接続します。

次のスクリプトに貼り付けて、ヒストグラムをプロットするストアドプロシージャを作成します。この例には、RPlotHistogram という名前が付けられています。

CREATE PROCEDURE [dbo].[RPlotHistogram]
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @query nvarchar(max) =  
  N'SELECT tipped FROM [dbo].[nyctaxi_sample]'  
  EXECUTE sp_execute_external_script @language = N'R',  
                                     @script = N'  
   image_file = tempfile();  
   jpeg(filename = image_file);  
   #Plot histogram  
   barplot(table(InputDataSet$tipped), main = "Tip Histogram", col="lightgreen", xlab="Tipped or not", ylab = "Counts", space=0)
   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 によりクエリテキスト ('SELECT tipped FROM nyctaxi_sample') が定義され、スクリプト入力変数 @input_data_1の引数として R スクリプトに渡されます。外部プロセスとして実行する R スクリプトの場合、スクリプトへの入力と、SQL Server で R セッションを開始する sp_execute_external_script システムストアドプロシージャへの入力の間に 1 対 1 のマッピングが必要です。
R スクリプト内では、イメージを格納するための変数 (image_file) が定義されています。
barplot 関数が呼び出されて、プロットが生成されます。
このコマンドを SQL Server で外部スクリプトとして実行しているため、R デバイスはオフに設定されています。 R では通常、高レベルのプロットコマンドを送るとき、R は device という名前のグラフィックスウィンドウを開きます。ファイルに書き込む場合や、出力を他の方法で処理する場合は、デバイスの電源をオフにすることができます。
R グラフィックスオブジェクトは、出力のために R data.frame にシリアル化されます。

ストアドプロシージャを実行し、bcp を使用してバイナリデータをイメージファイルにエクスポートする

ストアドプロシージャは varbinary データのストリームとして画像を返し、直接見ることはできません。ただし、 bcp ユーティリティを利用すれば、varbinary データを取得し、クライアントコンピューターに画像ファイルとして保存できます。

Management Studioで、次のステートメントを実行します。
```
EXEC [dbo].[RPlotHistogram]
```
結果

plot0xFFD8FFE000104A4649...
PowerShell コマンドプロンプトを開き、次のコマンドを実行します。引数として適切なインスタンス名、データベース名、ユーザー名、資格情報を指定します。 Windows ID を使用する場合は、 -U や -P を Tに置き換えることができます。
```
bcp "exec RPlotHistogram" queryout "plot.jpg" -S <SQL Server instance name> -d  NYCTaxi_Sample  -U <user name> -P <password> -T
```
Note

bcp のコマンドスイッチでは大文字と小文字が区別されます。
接続に成功した場合は、グラフィックファイル形式に関する詳細情報を入力するように求められます。

プロンプトのたびに ENTER キーを押し、初期設定を適用します。ただし、次については変更します。
- フィールドプロットのプレフィックス長に「0」を入力します。
- 後で再利用するために出力パラメーターを保存する場合、「 Y 」を入力します。
```
Enter the file storage type of field plot [varbinary(max)]: 
Enter prefix-length of field plot [8]: 0
Enter length of field plot [0]:
Enter field terminator [none]:

Do you want to save this format information in a file? [Y/n]
Host filename [bcp.fmt]:
```
結果
```
Starting copy...
1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 3922   Average : (0.25 rows per sec.)
```
ヒント

フォーマット情報をファイル (bcp.fmt) に保存する場合、 bcp ユーティリティによりフォーマット定義が生成されます。それを今後、同様のコマンドに適用できます。グラフィックファイルフォーマットの選択を求められません。フォーマットファイルを使用するには、 -f bcp.fmt をコマンド行の終わりに追加します。パスワード引数の後です。
出力ファイルは、PowerShell コマンドを実行した同じディレクトリに作成されます。プロットを表示するには、ファイル plot.jpg を開きます。

`hist` を使用してストアドプロシージャを作成する

一般的に、データサイエンティストはデータ視覚化を複数行い、さまざまな観点からデータを考察します。この例では、RPlotHist というストアドプロシージャを作成し、ヒストグラム、その他の R グラフィックスを .JPG および .PDF 形式で書き込みます。

このストアドプロシージャは、hist 関数を使用してヒストグラムを作成し、.JPG、.PDF、および .PNG などの一般的な形式でバイナリデータをエクスポートします。

SQL Server Management Studio では、オブジェクトエクスプローラーで、NYCTaxi_Sample データベースを右クリックし、 [新しいクエリ] を選択します。

次のスクリプトに貼り付けて、ヒストグラムをプロットするストアドプロシージャを作成します。この例には、RPlotHist という名前が付けられています。

CREATE PROCEDURE [dbo].[RPlotHist]  
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 プロット関数を呼び出し、実際のグラフィックスファイルを生成できます。埋め込み R スクリプトのほとんどがそのようなグラフィックス関数のオプションを表します。plot や histなどです。
このコマンドを SQL Server で外部スクリプトとして実行しているため、R デバイスはオフに設定されています。 R では通常、高レベルのプロットコマンドを送るとき、R は device という名前のグラフィックスウィンドウを開きます。ファイルに書き込む場合や、出力を他の方法で処理する場合は、デバイスの電源をオフにすることができます。
すべてのフォルダーがローカルフォルダー C:\temp\Plots に保存されます。宛先フォルダーはストアドプロシージャの一部として R スクリプトに渡された引数により定義されます。ファイルを別のフォルダーに出力するには、ストアドプロシージャに埋め込まれている R スクリプトの mainDir 変数の値を変更します。スクリプトを変更し、さまざまなフォーマットを出力したり、出力するファイルを増やしたりできます。

ストアドプロシージャを実行する

次のステートメントを実行して、バイナリプロットデータを JPEG および PDF ファイル形式にエクスポートします。

EXEC RPlotHist

結果

STDOUT message(s) from external script:
[1] Creating output plot files:[1] C:\temp\plots\rHistogram_Tipped_18887f6265d4.jpg[1] 

C:\temp\plots\rHistograms_Tip_and_Fare_Amount_1888441e542c.pdf[1]

C:\temp\plots\rXYPlots_Tip_vs_Fare_Amount_18887c9d517b.pdf

ファイル名の数値はランダムに生成され、既存のファイルに書き込もうとしたときに、エラーが発生しないようにします。

出力の表示

プロットを表示するには、宛先フォルダーを開き、ストアドプロシージャの R コードにより作成されたファイルを確認します。

STDOUT メッセージで示されているフォルダーに進みます (この例では、これは C:\temp\plots)
rHistogram_Tipped.jpg を開いて、チップをもらった乗車の数とチップをもらわなかった乗車の数を比較して表示します (このヒストグラムは、前の手順で生成したヒストグラムと似ています)。
rHistograms_Tip_and_Fare_Amount.pdf を開き、料金に対してプロットされた、チップの金額の分布を表示します。
rXYPlots_Tip_vs_Fare_Amount.pdf を開き、x 軸を料金、y 軸をチップ額とする散布図を表示します。

次のステップ

この記事では、次の内容について説明します。

サンプルデータを確認しました
T-SQL で R を使用してプロットを作成しました
複数のファイル形式でプロットを出力する

R チュートリアル:データの特徴の作成

次の方法で共有

R チュートリアル:データの調査と視覚化

データを確認する

T-SQL で R を使用してプロットを作成する

ストアドプロシージャを実行し、bcp を使用してバイナリデータをイメージファイルにエクスポートする

`hist` を使用してストアドプロシージャを作成する

ストアドプロシージャを実行する

出力の表示

次のステップ

フィードバック

その他のリソース

次の方法で共有

R チュートリアル:データの調査と視覚化

データを確認する

T-SQL で R を使用してプロットを作成する

ストアド プロシージャを実行し、bcp を使用してバイナリ データをイメージ ファイルにエクスポートする

hist を使用してストアド プロシージャを作成する

ストアド プロシージャを実行する

出力の表示

次のステップ

フィードバック

その他のリソース

ストアドプロシージャを実行し、bcp を使用してバイナリデータをイメージファイルにエクスポートする

`hist` を使用してストアドプロシージャを作成する

ストアドプロシージャを実行する