共用方式為


使用 sqlrutils 將 R 程式碼轉換為預存程序

本文描述使用 sqlrutils 封裝,將 R 程式碼轉換成 T-SQL 預存程序的步驟。 為能獲致最佳結果,可能需要部分修改您的程式碼,以確保所有輸入皆能參數化。

步驟 1: 重寫 R 指令碼

為了獲得最佳結果,您應該重寫 R 程式碼,將它封裝為單一函式。

函式所使用的所有變數皆應該定義在函式內,或定義為輸入參數。 請參閱本文的 範例程式碼

此外,由於 R 函式的輸入參數會成為 SQL 預存程序的輸入參數,因此您必須確定您的輸入和輸出符合下列類型需求:

輸入

輸入參數之間最多只能有一個資料框架。

資料框架內的物件與函式的其他所有輸入參數,皆必須是下列 R 資料類型:

  • POSIXct
  • NUMERIC
  • character
  • integer
  • 邏輯
  • raw

如果輸入類型不是上述類型之一,就必須將其序列化並以 未經處理的形式傳遞給函式。 在此情況下,函式也必須包含可還原序列化輸入項目的程式碼。

輸出

函式可以輸出下列其中一個項目:

  • 資料框架,其中包含支援的資料類型。 資料框架中的所有物件都必須使用其中一種支援的資料類型。
  • 具名清單,其中包含最多一個資料框架。 清單的所有成員都應該使用其中一種支援的資料類型。
  • NULL (如果您的函式未傳回任何結果)

步驟 2: 產生必要的物件

在您的 R 程式碼已清除,而且可以作為單一函式來呼叫之後,您將使用 sqlrutils 套件中的函式,以可傳遞至實際建置預存程序的建構函式形式,來準備輸入和輸出。

sqlrutils 提供可定義輸入資料結構描述和類型,以及定義輸出資料結構描述和類型的函式。 它也包含可將 R 物件轉換成所需輸出類型的函式。 根據您的程式碼所使用的資料類型而定,您可能會進行多個函式呼叫來建立必要的物件。

輸入

如果您的函式接受輸入,則針對每個輸入,呼叫下列函式:

  • 如果輸入是資料框架,則為 setInputData
  • setInputParameter 適用於所有其他輸入類型

當您進行每個函式呼叫時,會建立一個 R 物件,稍後您會將它作為引數傳遞給 StoredProcedure,以建立完整的預存程序。

輸出

sqlrutils 提供多個函式,可將 R 物件 (例如清單) 轉換成 SQL Server 所需的 data.frame。 如果您的函式會直接輸出資料框架,而不需先將其包裝到清單中,您即可略過這個步驟。 如果您的函數傳回 NULL,您也可以略過轉換此步驟。

轉換清單或從清單中取得特定項目時,請從下列函式中選擇:

  • 如果清單中的變數為資料框架,則為 setOutputData
  • setOutputParameter 適用於清單的其他所有成員

當您進行每個函式呼叫時,會建立一個 R 物件,稍後您會將它作為引數傳遞給 StoredProcedure,以建立完整的預存程序。

步驟 3: 產生預存程序

當所有輸入和輸出參數都準備就緒時,請呼叫 StoredProcedure 建構函式。

使用量

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

為了說明,假設您想要使用這些參數來建立名為 sp_rsample 的預存程序:

  • 使用現有的函式 foosql。 此函數的基礎是 R 函數 foo 中現有的程式碼,但您重寫了此函數,以滿足本節所述之需求,並將更新的函數命名為 foosql
  • 使用資料框架 queryinput 作為輸入
  • 使用 R 變數名稱 (sqloutput) 以輸出形式產生資料框架
  • 您想要建立 T-SQL 程式碼作為 C:\Temp 資料夾中的檔案,以便您可以在稍後使用 SQL Server Management Studio 執行它
StoredProcedure (foosql, sp_rsample, queryinput, sqloutput, filePath = "C:\\Temp")

注意

由於您是將檔案寫入檔案系統,因此可以省略定義資料庫連線的引數。

函式的輸出是 T-SQL 預存程序,可以在 SQL Server 2016 (需要 R 服務) 或 SQL Server 2017 (需要機器學習服務搭配 R) 的執行個體上執行。

如需其他範例,請參閱套件說明,方法是從 R 環境呼叫 help(StoredProcedure)

步驟 4: 註冊並執行預存程序

有兩種方式可讓您執行預存程序:

  • 從支援 SQL Server 2016 或 SQL Server 2017 執行個體連線的任何用戶端使用 T-SQL
  • 從 R 環境

這兩種方法都需要在您要使用預存程序的資料庫中註冊預存程序。

註冊預存程序

您可以使用 R 來註冊預存程序,也可以在 T-SQL 中執行 CREATE PROCEDURE 陳述式。

  • 使用 T-SQL。 如果您更熟悉 T-SQL,請開啟 SQL Server Management Studio (或任何其他可執行 SQL DDL 命令的用戶端),然後使用 StoredProcedure 函式準備的程式碼來執行 CREATE PROCEDURE 陳述式。

  • 使用 R。在您仍在 R 環境中時,可以使用 registerStoredProceduresqlrutils中的 函式向資料庫註冊預存程序。

    例如,您可以藉由進行此 R 呼叫,在 sqlConnStr 中定義的執行個體和資料庫中註冊預存程序 sp_rsample

    registerStoredProcedure(sp_rsample, sqlConnStr)
    

重要

無論您使用的是 R 或 SQL,都必須使用有權限建立新資料庫物件的帳戶來執行陳述式。

使用 SQL 執行

建立預存程序之後,請使用支援 T-SQL 的任何用戶端開啟 SQL 資料庫的連線,並傳遞預存程序所需之任何參數的值。

使用 R 執行

如果您想要從 R 程式碼執行預存程序,而不是從 SQL Server,則需要一些額外的準備工作。 例如,如果預存程序需要輸入值,則必須在執行函式之前設定那些輸入參數,然後將那些物件傳遞到 R 程式碼中的預存程序。

呼叫已備妥之 SQL 預存程序的整體程序如下:

  1. 呼叫 getInputParameters 以取得輸入參數物件的清單。
  2. 針對每個輸入參數,定義 $query 或設定 $value
  3. 使用 executeStoredProcedure 以從 R 開發環境執行預存程序,同時傳遞您所設定的輸入參數物件清單。

範例

此範例會顯示 R 指令碼的前後版本,該指令碼從 SQL Server 資料庫取得資料、對資料執行一些轉換,然後將其保存到不同的資料庫中。

這個簡單的範例僅用來說明您可以如何重新排列 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)

注意

當您使用 ODBC 連線,而不是叫用 RxSqlServerData 函式的話,則必須使用 rxOpen 開啟連接,才能在資料庫上執行作業。

程式碼準備之後

在更新的版本中,第一行會定義函式名稱。 所有來自原始 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)
}

注意

雖然您不需要明確地在程式碼中開啟 ODBC 連接,但使用 sqlrutils時仍需要 ODBC 連接。

另請參閱

sqlrutils 參考