R を使用した SQL Server データの表示と集計 (チュートリアル)

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

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

このレッスンでは、RevoScaleR パッケージの関数について説明し、次のタスクについて説明します。

SQL Server への接続
必要なデータを含むクエリを定義するか、テーブルまたはビューを指定します。
R コードの実行時に使用する計算コンテキストを 1 つまたは複数定義します。
必要に応じて、ソースからの読み込み時に、データソースに適用される変換を定義します。

SQL Server の計算コンテキストを定義する

クライアントワークステーションの R 環境で次の R ステートメントを実行します。このセクションでは、データサイエンスワークステーションと Microsoft R Client を想定しています。これには、すべての RevoScaleR パッケージに加えて、基本の軽量な R ツールセットが含まれているためです。たとえば、Rgui.exe を使用して、このセクションで R スクリプトを実行できます。

RevoScaleR パッケージがまだ読み込まれていない場合、次の R コード行を実行します。
```
library("RevoScaleR")
```
引用符は省略可能ですが、この場合は推奨されます。

エラーが発生した場合は、ご使用の R 開発環境で RevoScaleR パッケージを含むライブラリが使用されていることを確認します。現在のライブラリパスを表示するには、.libPaths() のようなコマンドを使用します。
SQL Server の接続文字列を作成し、R 変数 connStr に保存します。

プレースホルダー "your_server_name" を有効な SQL Server インスタンス名に変更する必要があります。サーバー名については、インスタンス名のみを使用できます。または、ご使用のネットワークによっては、名前を完全修飾する必要がある場合もあります。

SQL Server 認証の場合、接続構文は次のようになります。
```
connStr <- "Driver=SQL Server;Server=your_server_name;Database=nyctaxi_sample;Uid=your-sql-login;Pwd=your-login-password"
```
Windows 認証の場合、構文は少し異なります。
```
connStr <- "Driver=SQL Server;Server=your_server_name;Database=nyctaxi_sample;Trusted_Connection=True"
```
通常は、R コードにパスワードが保存されないように、可能であれば、Windows 認証の使用をお勧めします。
新しい計算コンテキストの作成で使用する変数を定義します。計算コンテキストオブジェクトを作成したら、それを使用して SQL Server インスタンスで R コードを実行できます。
```
sqlShareDir <- paste("C:\\AllShare\\",Sys.getenv("USERNAME"),sep="")
sqlWait <- TRUE
sqlConsoleOutput <- FALSE
```
- ワークステーションと SQL Server コンピューター間で双方向に R オブジェクトをシリアル化する際、R では、一時ディレクトリが使用されます。 sqlShareDirとして使用されるローカルディレクトリを指定するか、既定のものをそのまま利用できます。
- sqlWait を利用し、R にサーバーからの結果を待機させるかどうかを指示します。待機するジョブと待機しないジョブの詳細については、Microsoft R で RevoScaleR を使用した分散コンピューティングと並列コンピューティングに関するページを参照してください。
- 引数 sqlConsoleOutput を使用し、R コンソールから出力を表示しないことを指示します。
RxInSqlServer コンストラクターを呼び出して、既に定義されている変数と接続文字列で計算コンテキストオブジェクトを作成し、新しいオブジェクトを R 変数 sqlcc に保存します。
```
sqlcc <- RxInSqlServer(connectionString = connStr, shareDir = sqlShareDir, wait = sqlWait, consoleOutput = sqlConsoleOutput)
```
既定では、計算コンテキストはローカルです。つまり、アクティブな計算コンテキストを明示的に設定する必要があります。
```
rxSetComputeContext(sqlcc)
```
- rxSetComputeContext では以前のアクティブな計算コンテキストが非表示で返されるので、それを利用できます
- rxGetComputeContext ではアクティブな計算コンテキストが返されます
計算コンテキストの設定は、RevoScaleR パッケージ内の関数を使用する操作にのみ影響します。計算コンテキストは、オープンソース R 操作を実行する方法には影響しません。

RxSqlServer を使用してデータソースを作成する

RevoScaleR や RevoScaleR などの Microsoft R ライブラリを使用する場合、データソースは、RevoScaleR 関数を使用して作成するオブジェクトです。データソースオブジェクトでは、モデルのトレーニングや特徴の抽出などのタスクに使用するデータのセットを指定します。 SQL Server を含むさまざまなソースからデータを取得できます。現在サポートされているソースの一覧については、「RxDataSource」を参照してください。

以前に、接続文字列を定義し、その情報を R 変数に保存しました。この接続情報を再利用して、取得するデータを指定できます。

SQL クエリを文字列変数として保存します。このクエリでは、モデルをトレーニングするためのデータが定義されます。
```
sampleDataQuery <- "SELECT TOP 1000 tipped, fare_amount, passenger_count,trip_time_in_secs,trip_distance, pickup_datetime, dropoff_datetime, pickup_longitude, pickup_latitude, dropoff_longitude, dropoff_latitude FROM nyctaxi_sample"
```
ここでは、実行速度を向上させるために TOP 句を使用していましたが、クエリによって返される実際の行は、順序によって異なる場合があります。そのため、概要の結果も以下に示すものと異なる場合があります。 TOP 句は削除してもかまいません。
RxSqlServerData 関数にクエリの定義を引数として渡します。
```
inDataSource <- RxSqlServerData(
  sqlQuery = sampleDataQuery,
  connectionString = connStr,
  colClasses = c(pickup_longitude = "numeric", pickup_latitude = "numeric",
  dropoff_longitude = "numeric", dropoff_latitude = "numeric"),
  rowsPerRead=500
  )
```
- 引数 colClasses により、 SQL Server と R の間でデータを移動するときに使用する列の型が指定されます。これが重要なのは、SQL Server が R と異なるデータ型とその他のデータ型を使用するためです。詳しくは、R のライブラリとデータ型に関する記事をご覧ください。
- 引数 rowsPerRead はメモリ使用量を管理し効率的な計算を行うために重要な役割を果たします。 R Services (データベース内) 内の拡張分析関数の大部分は、データをチャンク単位で処理し、中間結果を蓄積していきます。すべてのデータを読み取ったら、最終的な計算結果を返します。 rowsPerRead パラメーターを追加して、各チャンクに読み取られる処理対象のデータ行の数を制御できます。このパラメーターの値が大きすぎる場合、そのような大規模なデータチャンクを効率的に処理するのに十分なメモリは備えていないため、データアクセスは遅くなる可能性があります。一部のシステムでは、rowsPerRead を極端に小さい値に設定しても、パフォーマンスが低下することがあります。
この時点で、inDataSource オブジェクトが作成されましたが、これにはデータは何も含まれていません。 rxImport や rxSummary のような関数を実行するまで、データは SQL クエリからローカル環境にプルされません。

ただし、データオブジェクトを定義したので、それを他の関数の引数として使用することができます。

R の概要で SQL Server データを使用する

このセクションでは、リモート計算コンテキストをサポートする R Services (データベース内) で提供されている機能のいくつかを試してみましょう。データソースに R 関数を適用すると、SQL Server データの探索、集計、グラフの作成ができます。

関数 rxGetVarInfo を呼び出して、データソース内の変数とそのデータ型の一覧を取得します。

rxGetVarInfo は便利な機能です。任意のデータフレームまたはリモートデータオブジェクト内のデータセットでこれを呼び出して、最大値や最小値、データ型、因子列内のレベル数などの情報を取得します。

あらゆる種類のデータ入力、機能変換、または機能エンジニアリングの後にこの関数を実行することを検討してください。そうすることで、モデルで使用するすべての機能が予期したデータ型を持つことを確認し、エラーを回避できます。
```
rxGetVarInfo(data = inDataSource)
```
結果
```
Var 1: tipped, Type: integer
Var 2: fare_amount, Type: numeric
Var 3: passenger_count, Type: integer
Var 4: trip_time_in_secs, Type: numeric, Storage: int64
Var 5: trip_distance, Type: numeric
Var 6: pickup_datetime, Type: character
Var 7: dropoff_datetime, Type: character
Var 8: pickup_longitude, Type: numeric
Var 9: pickup_latitude, Type: numeric
Var 10: dropoff_longitude, Type: numeric
```
次に、RevoScaleR 関数 rxSummary を呼び出して、個々の変数に関する詳細な統計情報を取得します。

rxSummary は R summary 関数に基づいていますが、いくつかの追加機能と利点があります。 rxSummary は複数の計算コンテキストで機能し、チャンキングをサポートしています。 rxSummary を使用して値を変換したり、係数レベルに基づいて集計したりすることもできます。

この例では、乗客の数に基づいて料金を集計します。
```
start.time <- proc.time()
rxSummary(~fare_amount:F(passenger_count,1,6), data = inDataSource)
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 summarize the inDataSource.", sep=""))
```
- rxSummary への最初の引数は、集計する数式または用語を指定します。ここでは、集計する前に、F() 関数を使用して、passenger_count 内の値を因子に変換します。また、passenger_count 因子変数に最小値 (1) および最大値 (6) を指定する必要があります。
- 出力する統計情報を指定しない場合、rxSummary は平均、StDev、最小、最大、有効な結果と不足している結果の数を出力します。
- この例には、パフォーマンスを比較できるように、関数の開始時間と終了時間を追跡するコードも含まれています。
結果

RxSummary 関数が正常に実行された場合は、次のような結果が表示され、その後にカテゴリ別の統計情報の一覧が表示されます。
```
rxSummary(formula = ~fare_amount:F(passenger_count, 1,6), data = inDataSource)
Data: inDataSource (RxSqlServerData Data Source)
Number of valid observations: 1000
```

ビッグデータに関するボーナス演習

すべての行を含む新しいクエリ文字列を定義してみます。この実験用に新しいデータソースオブジェクトを設定することをお勧めします。 rowsToRead パラメーターを変更して、スループットにどのように影響するかを確認することもできます。

bigDataQuery  <- "SELECT tipped, fare_amount, passenger_count,trip_time_in_secs,trip_distance, pickup_datetime, dropoff_datetime, pickup_longitude, pickup_latitude, dropoff_longitude, dropoff_latitude FROM nyctaxi_sample"

bigDataSource <- RxSqlServerData(
      sqlQuery = bigDataQuery,
      connectionString = connStr,
      colClasses = c(pickup_longitude = "numeric", pickup_latitude = "numeric",
      dropoff_longitude = "numeric", dropoff_latitude = "numeric"),
      rowsPerRead=500
      )

start.time <- proc.time()
rxSummary(~fare_amount:F(passenger_count,1,6), data = bigDataSource)
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 summarize the inDataSource.", sep=""))

ヒント

これが実行されている間、プロセスエクスプローラーや SQL Profiler などのツールを使用して、接続がどのように行われ、R コードが SQL Server サービスを使用して実行されているかを確認できます。

次のステップ

R を利用してグラフやプロットを作成する

次の方法で共有

R を使用した SQL Server データの表示と集計 (チュートリアル)

SQL Server の計算コンテキストを定義する

RxSqlServer を使用してデータソースを作成する

R の概要で SQL Server データを使用する

ビッグデータに関するボーナス演習

次のステップ

フィードバック

その他のリソース

次の方法で共有

R を使用した SQL Server データの表示と集計 (チュートリアル)

SQL Server の計算コンテキストを定義する

RxSqlServer を使用してデータ ソースを作成する

R の概要で SQL Server データを使用する

ビッグ データに関するボーナス演習

次のステップ

フィードバック

その他のリソース

RxSqlServer を使用してデータソースを作成する

ビッグデータに関するボーナス演習