SQL と R を使用してグラフとプロットを作成する (チュートリアル)

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

適用対象: SQL Server 2016 (13.x) 以降のバージョン

チュートリアルのこのパートでは、R を使用して SQL Server データでプロットとマップを生成する方法について説明します。単純なヒストグラムを作成してから、より複雑なマッププロットを作成します。

前提条件

この手順では、進行中の R セッションは、このチュートリアルの前の手順に基づいていることを前提としています。ここでは、これらの手順で作成した接続文字列とデータソースオブジェクトを使用します。スクリプトの実行には、次のツールとパッケージが使用されます。

R コマンドを実行する Rgui.exe
T-SQL を実行する Management Studio
googMap
ggmap パッケージ
mapproj パッケージ

ヒストグラムの作成

rxHistogram 関数を利用し、最初のプロットを生成します。 rxHistogram 関数は、オープンソース R パッケージのそれと同様の機能を提供しますが、リモート実行コンテキストで実行できます。

# Plot fare amount on SQL Server and return the plot
start.time <- proc.time()
rxHistogram(~fare_amount, data = inDataSource, title = "Fare Amount Histogram")
used.time <- proc.time() - start.time
print(paste("It takes CPU Time=", round(used.time[1]+used.time[2],2), " seconds, Elapsed Time=", round(used.time[3],2), " seconds to generate plot.", sep=""))

この画像はお使いの開発環境の R グラフィックスデバイスで返されたものです。たとえば、RStudio で、 プロット ウィンドウをクリックします。 R Tools for Visual Studioで、別個のグラフィックスウィンドウが開きます。

Note

グラフの外観が異なっていますか?

それは、inDataSource では上位の 1000 行のみが使用されるからです。 TOP を利用した行の順序付けは ORDER BY 句なしでは非決定性となるため、データと結果のグラフが異なる可能性があります。この画像は約 10,000 行のデータを利用して生成されました。行数を変えて、実験してみることをお勧めします。お使いの環境で結果を得るためにかかった時間をメモしてください。

マッププロットの作成

通常、データベースサーバーによってインターネットアクセスがブロックされます。これは、マップやその他の画像をダウンロードしてプロットを生成する必要がある R パッケージを使用する場合に、不便な場合があります。しかし、独自のアプリケーションを開発する場合に役立つ回避策があります。基本的に、クライアントでマップ表現を生成してから、SQL Server テーブルに属性として格納されているポイントをマップ上でオーバーレイします。

R プロットオブジェクトを作成する関数を定義します。カスタム関数 mapPlot は、タクシーが客を乗せた場所を利用し、それぞれの場所から開始した乗車の数をプロットする散布図を作成します。これは ggplot2 パッケージと ggmap パッケージを利用します。これらは既にインストールされ、読み込まれているはずです。
```
mapPlot <- function(inDataSource, googMap){
    library(ggmap)
    library(mapproj)
    ds <- rxImport(inDataSource)
    p <- ggmap(googMap)+
    geom_point(aes(x = pickup_longitude, y =pickup_latitude ), data=ds, alpha =.5,
color="darkred", size = 1.5)
    return(list(myplot=p))
}
```
- 関数 mapPlot は 2 つの引数を受け取ります。RxSqlServerData を利用して先に定義した既存のデータオブジェクトとクライアントから渡されたマップ表示です。
- ds 変数で始まる行では、以前に作成したデータソース inDataSource からメモリデータを読み込むために、rxImport が使用されます (このデータソースには 1000 行しか含まれていません。より多くのデータポイントを含むマップを作成する場合は、別のデータソースに置き換えることができます)。
- オープンソース R 関数を使用するときは、データをローカルメモリのデータフレームに読み込む必要があります。ただし、rxImport 関数を呼び出すことによって、リモート計算コンテキストのメモリ内で実行できます。
計算コンテキストをローカルに変更し、マップの作成に必要なライブラリを読み込みます。
```
rxSetComputeContext("local")
library(ggmap)
library(mapproj)
gc <- geocode("Times Square", source = "google")
googMap <- get_googlemap(center = as.numeric(gc), zoom = 12, maptype = 'roadmap', color = 'color');
```
- gc 変数は、ニューヨークにあるタイムズスクウェアの一組の座標を保存します。
- googmap で始まる行により、指定した座標を中心に据えるマップが生成されます。
次に示すように、rxExec でプロット関数をラップすることで、SQL Server 計算コンテキストに切り替え、結果を表示します。 rxExec 関数は RevoScaleR パッケージに含まれ、リモートの計算コンテキストで任意の R 関数の実行をサポートします。
```
rxSetComputeContext(sqlcc)
myplots <- rxExec(mapPlot, inDataSource, googMap, timesToRun = 1)
plot(myplots[[1]][["myplot"]]);
```
- googMap のマップデータは、リモートで実行される関数 mapPlot に引数として渡されます。マップはローカル環境で生成されたため、SQL Server のコンテキストでプロットを作成するためには、マップを関数に渡す必要があります。
- plot で始まる行を実行すると、レンダリングされたデータがローカル R 環境にシリアル化され、R クライアントで表示できるようになります。
Note

Azure 仮想マシンで SQL Server を使用している場合は、この時点でエラーが発生する可能性があります。 Azure の既定のファイアウォール規則によって R コードによるネットワークアクセスがブロックされると、エラーが発生します。このエラーを修正する方法の詳細については、Azure VM で Machine Learning (R) Services をインストールするに関するページを参照してください。
出力プロットは次の図のようになります。乗車場所が赤い点でマップ上に追加されます。使用したデータソースに格納されている場所の数によっては、画像が異なる場合があります。

次のステップ

R と SQL を使用してデータ機能を作成する

SQL と R を使用してグラフとプロットを作成する (チュートリアル)

前提条件

ヒストグラムの作成

マッププロットの作成

次のステップ

フィードバック

フィードバック

その他のリソース

SQL と R を使用してグラフとプロットを作成する (チュートリアル)

前提条件

ヒストグラムの作成

マップ プロットの作成

次のステップ

フィードバック

フィードバック

その他のリソース

マッププロットの作成