次の方法で共有


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

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

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

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

この記事の主な目的は、ストアド プロシージャの 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 回でトレーニングして保存したモデルを運用化する方法について説明します。

データを確認する

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

元のパブリック データセットでは、タクシーの識別情報と乗車レコードが別々のファイルに入力されていました。 サンプル データの利用を簡単にするために、 medallionhack_licensepickup_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 でラップすることができます。

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

  2. 次のスクリプトに貼り付けて、ヒストグラムをプロットするストアド プロシージャを作成します。 この例には、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 データを取得し、クライアント コンピューターに画像ファイルとして保存できます。

  1. Management Studioで、次のステートメントを実行します。

    EXEC [dbo].[RPlotHistogram]
    

    結果

    plot0xFFD8FFE000104A4649...

  2. PowerShell コマンド プロンプトを開き、次のコマンドを実行します。引数として適切なインスタンス名、データベース名、ユーザー名、資格情報を指定します。 Windows ID を使用する場合は、 -U-PTに置き換えることができます。

    bcp "exec RPlotHistogram" queryout "plot.jpg" -S <SQL Server instance name> -d  NYCTaxi_Sample  -U <user name> -P <password> -T
    

    Note

    bcp のコマンド スイッチでは大文字と小文字が区別されます。

  3. 接続に成功した場合は、グラフィック ファイル形式に関する詳細情報を入力するように求められます。

    プロンプトのたびに 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 をコマンド行の終わりに追加します。パスワード引数の後です。

  4. 出力ファイルは、PowerShell コマンドを実行した同じディレクトリに作成されます。 プロットを表示するには、ファイル plot.jpg を開きます。

    チップありとチップなしのタクシー乗車

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

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

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

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

  2. 次のスクリプトに貼り付けて、ヒストグラムをプロットするストアド プロシージャを作成します。 この例には、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 スクリプトのほとんどがそのようなグラフィックス関数のオプションを表します。plothistなどです。

  • このコマンドを 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 コードにより作成されたファイルを確認します。

  1. STDOUT メッセージで示されているフォルダーに進みます (この例では、これは C:\temp\plots)

  2. rHistogram_Tipped.jpg を開いて、チップをもらった乗車の数とチップをもらわなかった乗車の数を比較して表示します (このヒストグラムは、前の手順で生成したヒストグラムと似ています)。

  3. rHistograms_Tip_and_Fare_Amount.pdf を開き、料金に対してプロットされた、チップの金額の分布を表示します。

    tip_amount と fare_amount を示すヒストグラム

  4. rXYPlots_Tip_vs_Fare_Amount.pdf を開き、x 軸を料金、y 軸をチップ額とする散布図を表示します。

    料金に対してプロットされたチップの金額

次のステップ

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

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