sqlrutils を使用して R コードをストアド プロシージャに変換する

この記事では、 sqlrutils パッケージを使用して、T-SQL ストアド プロシージャとして実行するように R コードを変換する手順について説明します。 可能な限り最良の結果を得るには、すべての入力をパラメーター化できるようにコードを多少変更する必要がある場合があります。

ステップ 1. R スクリプトの書き換え

最良の結果を得るには、R コードを 1 つの関数としてカプセル化するように書き直す必要があります。

関数で使用されるすべての変数は、関数内で定義するか、入力パラメーターとして定義する必要があります。 この記事の サンプル コード を参照してください。

また、R 関数の入力パラメーターは SQL ストアド プロシージャの入力パラメーターになるため、入力と出力が次の型要件に準拠していることを確認する必要があります。

入力

入力パラメーターの中には、最大で 1 つのデータ フレームがあります。

データ フレーム内のオブジェクト、および関数の他のすべての入力パラメーターは、次の R データ型である必要があります。

• POSIXct
• numeric
• 文字
• 整数
• 論理的
• raw

入力型が上記の型の 1 つでない場合は、シリアル化して 生として関数に渡す必要があります。 この場合、関数には、入力を逆シリアル化するコードも含める必要があります。

出力

この関数は、次のいずれかを出力できます。

• サポートされているデータ型を含むデータ フレーム。 データ フレーム内のすべてのオブジェクトは、サポートされているデータ型のいずれかを使用する必要があります。
• 最大 1 つのデータ フレームを含む名前付きリスト。 リストのすべてのメンバーは、サポートされているデータ型のいずれかを使用する必要があります。
• 関数が結果を返さない場合は NULL

ステップ 2. 必要なオブジェクトを生成する

R コードがクリーンアップされ、単一の関数として呼び出すことができる場合は、 sqlrutils パッケージ内の関数を使用して、ストアド プロシージャを実際にビルドするコンストラクターに渡すことができる形式で入力と出力を準備します。

sqlrutils には、入力データ スキーマと型を定義し、出力データ スキーマと型を定義する関数が用意されています。 また、R オブジェクトを必要な出力の種類に変換できる関数も含まれています。 コードで使用するデータ型に応じて、必要なオブジェクトを作成するために複数の関数呼び出しを行う場合があります。

入力

関数が入力を受け取る場合は、入力ごとに次の関数を呼び出します。

• setInputData 入力がデータ フレームの場合
• setInputParameter 他のすべての入力タイプの場合

各関数呼び出しを行うと、後で StoredProcedure に引数として渡して完全なストアド プロシージャを作成する R オブジェクトが作成されます。

出力

sqlrutils には、リストなどの R オブジェクトを、SQL Serverに必要な data.frame に変換するための複数の関数が用意されています。 関数がデータ フレームを直接出力する場合は、最初にリストにラップせずに、この手順をスキップできます。 関数が NULL を返す場合は、この手順の変換をスキップすることもできます。

リストを変換するとき、またはリストから特定の項目を取得する場合は、次の関数から選択します。

• setOutputData リストから取得する変数がデータ フレームの場合
• setOutputParameter リスト内の他のすべての項目に対して

各関数呼び出しを行うと、後で StoredProcedure に引数として渡して完全なストアド プロシージャを作成する R オブジェクトが作成されます。

手順 3. ストアド プロシージャを生成する

すべての入力パラメーターと出力パラメーターの準備ができたら、 StoredProcedure コンストラクターを呼び出します。

使用方法

StoredProcedure (func, spName, ..., filePath = NULL ,dbName = NULL, connectionString = NULL, batchSeparator = "GO")

たとえば、次のパラメーターを使用して sp_rsample という名前のストアド プロシージャを作成するとします。

• 既存の関数 foosql を使用します。 この関数は R 関数 foo の既存のコードに基づいていましたが、 このセクションで説明されているように要件に準拠するように関数を書き直し、更新された関数を foosql という名前にしました。
• データ フレーム queryinput を入力として使用します
• R 変数名 sqloutput を持つデータ フレームを出力として生成します。
• 後で SQL Server Management Studio を使用して実行できるように、T-SQL コードを C:\Temp フォルダーにファイルとして作成する

StoredProcedure (foosql, sp_rsample, queryinput, sqloutput, filePath = "C:\\Temp")

関数の出力は T-SQL ストアド プロシージャであり、SQL Server 2016 (R Services が必要) または SQL Server 2017 のインスタンスで実行できます (R を使用するサービスMachine Learning必要)。

その他の例については、R 環境から help(StoredProcedure) を呼び出すことによって、パッケージのヘルプを参照してください。

ステップ 4. ストアド プロシージャの登録と実行

ストアド プロシージャを実行する方法は 2 つあります。

• SQL Server 2016 または SQL Server 2017 インスタンスへの接続をサポートする任意のクライアントからの T-SQL の使用
• R 環境から

どちらのメソッドでも、ストアド プロシージャを使用するデータベースにストアド プロシージャを登録する必要があります。

ストアド プロシージャを登録する

ストアド プロシージャは R を使用して登録することも、T-SQL で CREATE PROCEDURE ステートメントを実行することもできます。

• T-SQL の使用。 T-SQL に慣れている場合は、SQL Server Management Studio (または SQL DDL コマンドを実行できるその他のクライアント) を開き、CREATE PROCEDURE関数によって準備されたコードを使用してStoredProcedure ステートメントを実行します。

• R の使用。まだ R 環境にいる間は、registerStoredProcedure の 関数を使用して、ストアド プロシージャをデータベースに登録できます。

  たとえば、次の R 呼び出しを行って、sqlConnStrで定義されているインスタンスとデータベースにストアド プロシージャ sp_rsampleを登録できます。

  registerStoredProcedure(sp_rsample, sqlConnStr)
  

SQL を使用して実行する

ストアド プロシージャが作成されたら、T-SQL をサポートする任意のクライアントを使用して SQL データベースへの接続を開き、ストアド プロシージャで必要なすべてのパラメーターの値を渡します。

R を使用して実行する

SQL Serverからではなく、R コードからストアド プロシージャを実行する場合は、追加の準備が必要です。 たとえば、ストアド プロシージャに入力値が必要な場合は、関数を実行する前にこれらの入力パラメーターを設定し、それらのオブジェクトを R コードのストアド プロシージャに渡す必要があります。

準備された SQL ストアド プロシージャを呼び出す全体的なプロセスは次のとおりです。

1. getInputParametersを呼び出して、入力パラメーター オブジェクトの一覧を取得します。
2. $queryを定義するか、各入力パラメーターの$valueを設定します。
3. executeStoredProcedureを使用して、R 開発環境からストアド プロシージャを実行し、設定した入力パラメーター オブジェクトの一覧を渡します。

例

この例では、SQL Server データベースからデータを取得し、データに対していくつかの変換を実行し、別のデータベースに保存する R スクリプトの前と後のバージョンを示します。

この簡単な例は、ストアド プロシージャへの変換を容易にするために R コードを再配置する方法を示すためにのみ使用されます。

コードの準備前

sqlConnFrom <- "Driver={ODBC Driver 13 for SQL Server};Server=MyServer01;Database=AirlineSrc;Trusted_Connection=Yes;"
  
sqlConnTo <- "Driver={ODBC Driver 13 for SQL Server};Server=MyServer01;Database=AirlineTest;Trusted_Connection=Yes;"
  
sqlQueryAirline <- "SELECT TOP 10000 ArrDelay, CRSDepTime, DayOfWeek FROM [AirlineDemoSmall]"
  
dsSqlFrom <- RxSqlServerData(sqlQuery = sqlQueryAirline, connectionString = sqlConnFrom)
  
dsSqlTo <- RxSqlServerData(table = "cleanData", connectionString = sqlConnTo)
  
xFunc <- function(data) {
    data$CRSDepHour <- as.integer(trunc(data$CRSDepTime))
    return(data)
    }
  
xVars <- c("CRSDepTime")
  
sqlCompute <- RxInSqlServer(numTasks = 4, connectionString = sqlConnTo)
  
rxOpen(dsSqlFrom)
rxOpen(dsSqlTo)
  
if (rxSqlServerTableExists("cleanData", connectionString = sqlConnTo))   {
    rxSqlServerDropTable("cleanData")}
  
rxDataStep(inData = dsSqlFrom, 
     outFile = dsSqlTo,
     transformFunc = xFunc,
     transformVars = xVars,
     overwrite = TRUE)

コードの準備後

更新されたバージョンでは、最初の行で関数名が定義されます。 元の R ソリューションの他のすべてのコードは、その関数の一部になります。

myetl1function <- function() { 
   sqlConnFrom <- "Driver={ODBC Driver 13 for SQL Server};Server=MyServer01;Database=Airline01;Trusted_Connection=Yes;"
   sqlConnTo <- "Driver={ODBC Driver 13 for SQL Server};Server=MyServer02;Database=Airline02;Trusted_Connection=Yes;"
    
   sqlQueryAirline <- "SELECT TOP 10000 ArrDelay, CRSDepTime, DayOfWeek FROM [AirlineDemoSmall]"

   dsSqlFrom <- RxSqlServerData(sqlQuery = sqlQueryAirline, connectionString = sqlConnFrom)
  
   dsSqlTo <- RxSqlServerData(table = "cleanData", connectionString = sqlConnTo)
  
   xFunc <- function(data) {
     data$CRSDepHour <- as.integer(trunc(data$CRSDepTime))
     return(data)}
  
   xVars <- c("CRSDepTime")
  
   sqlCompute <- RxInSqlServer(numTasks = 4, connectionString = sqlConnTo)
  
   if (rxSqlServerTableExists("cleanData", connectionString = sqlConnTo)) {rxSqlServerDropTable("cleanData")}
  
   rxDataStep(inData = dsSqlFrom, 
        outFile = dsSqlTo,
        transformFunc = xFunc,
        transformVars = xVars,
        overwrite = TRUE)
   return(NULL)
}

こちらも参照ください

sqlrutils のリファレンス