修改 R/Python 程式碼以在 SQL Server (資料庫內) 執行個體中執行

適用於： SQL Server 2016 (13.x) 和更新版本 Azure SQL 受控執行個體

本文提供高階指導方針，說明如何修改 R 或 Python 程式碼以作為 SQL Server 預存程序執行，進而改善存取 SQL 資料時的效能。

當您將 R/Python 程式碼從本機 IDE 或其他環境移至 SQL Server 時，程式碼通常會正常運作，而不需要進一步修改。 這特別適用於簡單的程式碼，例如接受一些輸入並傳回值的函數。 您也可以更輕鬆地移植使用 RevoScaleR/revoscalepy 套件的解決方案，以支援在進行最少量變更的情況下，於不同的執行內容中執行。 請注意，MicrosoftML 適用於 SQL Server 2016 (13.x)、SQL Server 2017 (14.x) 和 SQL Server 2019 (15.x)，不會出現在 SQL Server 2022 (16.x) 中。

不過，如果發生下列任何一種情況，您的程式碼可能會需要大量變更：

• 您可以使用存取網路或無法在 SQL Server 上安裝的程式庫。
• 此程式碼會對 SQL Server 外部的資料來源 (例如 Excel 工作表、共用上的檔案，以及其他資料庫) 進行個別呼叫。
• 您想要將預存程序參數化，並在 sp_execute_external_script 的 @script 參數中執行程式碼。
• 您的原始解決方案包含在生產環境中獨立執行時可能會更有效率的多個步驟，例如資料準備或特徵工程與模型定型、評分或報告。
• 您希望透過變更程式庫、使用平行執行，或將部分處理作業卸載至 SQL Server，藉以最佳化效能。

步驟 1： 計劃資源和需求

Packages

• 決定需要哪些套件，並確保它們可在 SQL Server 上運作。

• 請在機器學習服務使用的預設封裝程式庫中預先安裝套件。 不支援子程式庫。

資料來源

• 如果您想要在 sp_execute_external_script 中內嵌程式碼，請識別主要和次要資料來源。

  • 主要 資料來源是大型資料集 (例如模型定型資料)，或用於預測的輸入資料。 請計劃將您最大的資料集對應到 sp_execute_external_script 的輸入參數。

  • 次要 資料來源通常是較小的資料集，例如因素清單或其他群組變數。

  目前，sp_execute_external_script 僅支援使用單一資料集做為預存程序的輸入。 但是，您可以新增多個純量或二進位輸入。

  預存程序呼叫如果前面有 EXECUTE，就無法用來作為 sp_execute_external_script 的輸入。 您可以使用查詢、檢視或任何其他有效的 SELECT 陳述式。

• 判斷您需要的輸出。 如果您使用 sp_execute_external_script 執行程式碼，則預存程序只能輸出一個資料框架作為結果。 不過，您也可以輸出多個純量輸出，包括二進位格式的繪圖與模型，以及衍生自程式碼或 SQL 參數的其他純量值。

資料類型

若要詳細了解 R/Python 與 SQL Server 之間的資料類型對應，請參閱下列文章：

• R 與 SQL Server 之間的資料類型對應
• Python 與 SQL Server 之間的資料類型對應

請查看 R/Python 程式碼中使用的資料類型，然後執行下列動作：

• 建立可能資料類型問題的檢查清單。

  SQL Server 機器學習服務支援所有 R/Python 資料類型。 不過，SQL Server 支援比 R 或 Python 更多種的資料類型。 因此，將 SQL Server 資料移進和移出程式碼時，會執行一些隱含資料類型轉換。 您可能需要明確地將某些資料轉型或轉換。

  支援 NULL 值。 但是，R 使用 na 資料建構來代表遺漏值，這類似於 Null。

• 請考慮消除 R 所無法使用資料的相依性：例如，R 無法使用來自 SQL Server 的 rowid 和 GUID 資料類型並且會產生錯誤。

步驟 2： 轉換或重新封裝程式碼

程式碼的變更程度取決於您想要從遠端用戶端提交程式碼，以便在 SQL Server 計算內容中執行，還是想要在預存程序中部署程式碼。 後者可以提供更好的效能和資料安全性，但有一些其他需求。

• 請盡可能將您的主要輸入資料定義為 SQL 查詢，以避免資料移動。

• 在預存程序中執行程式碼時，您可以傳遞多個純量輸入。 針對您想要在輸出中使用的任何參數，請加入 OUTPUT 關鍵字。

  例如，下列純量輸入 @model_name 包含模型名稱 (R 指令碼稍後也會修改此名稱)，並在結果中包含各自資料行的輸出：

  -- declare a local scalar variable which will be passed into the R script
  DECLARE @local_model_name AS NVARCHAR (50) = 'DefaultModel';
  
  -- The below defines an OUTPUT variable in the scope of the R script, called model_name
  -- Syntactically, it is defined by using the @model_name name. Be aware that the sequence
  -- of these parameters is very important. Mandatory parameters to sp_execute_external_script
  -- must appear first, followed by the additional parameter definitions like @params, etc.
  EXECUTE sp_execute_external_script @language = N'R', @script = N'
    model_name <- "Model name from R script"
    OutputDataSet <- data.frame(InputDataSet$c1, model_name)'
    , @input_data_1 = N'SELECT 1 AS c1'
    , @params = N'@model_name nvarchar(50) OUTPUT'
    , @model_name = @local_model_name OUTPUT;
  
  -- optionally, examine the new value for the local variable:
  SELECT @local_model_name;
  

• 您以 sp_execute_external_script 預存程序參數形式傳入的任何變數，都必須對應至程式碼中的變數。 變數預設會依名稱對應。 輸入資料集中的所有資料行也都必須對應至指令碼中的變數。

  例如，假設您的 R 指令碼包含如以下的公式：

  formula <- ArrDelay ~ CRSDepTime + DayOfWeek + CRSDepHour:DayOfWeek
  

  如果輸入資料集並未包含與 ArrDelay、CRSDepTime、DayOfWeek、CRSDepHour 及 DayOfWeek 名稱相符的資料行，就會引發錯誤。

• 在某些情況下，必須事先定義結果的輸出架構。

  例如，若要將資料插入資料表中，您必須使用 WITH RESULT SET 子句來指定架構。

  如果指令碼使用引數 @parallel=1，則還需要輸出結構描述。 這是因為 SQL Server 可能建立多個處理序來平行執行查詢，並在最後收集結果。 因此，必須先準備好輸出結構描述，才能建立平行處理序。

  在其他情況下，您可以使用 WITH RESULT SETS UNDEFINED 選項來省略結果架構。 此陳述式會從指令碼傳回資料集，而不需要命名資料行或指定 SQL 資料類型。

• 請考慮使用 T-SQL (而非 R/Python) 來產生計時或追蹤資料。

  例如，您可以透過新增傳遞至結果的 T-SQL 呼叫 (而不是在指令碼中產生類似資料)，來傳遞用於稽核與儲存的系統時間或其他資訊。

改善效能和安全性

• 避免將預測或中繼結果寫入檔案。 請改為將預測寫入資料表，以避免資料移動。

• 先執行所有查詢，然後檢閱 SQL Server 查詢計劃來識別可平行執行的工作。

  如果可以平行處理輸入查詢，請設定 @parallel=1 作為您 sp_execute_external_script 引數的一部分。

  只要 SQL Server 可以與資料分割資料表搭配運作，或在多個處理序之間分散查詢並在最後彙總結果，通常就可以使用此旗標來進行平行處理。 如果您使用要求讀取所有資料的演算法來定型模型，或是您需要建立彙總，則通常無法使用此旗標來進行平行處理。

• 檢閱您的程式碼，以判斷是否有步驟可藉由使用個別的預存程序呼叫，來獨立執行或以更有效率的方式執行。 例如，您可以透過將特徵工程或特徵擷取分開執行，並將值儲存至資料表，來獲得更好的效能。

• 尋找使用 T-SQL 而不使用 R/Python 程式碼來進行集合型計算的方式。

  例如，這個 R 解決方案會顯示使用者定義的 T-SQL 函式和 R 如何執行相同的特徵工程工作：資料科學端對端逐步解說。

• 請洽詢資料庫開發人員，以判斷要使用經記憶體最佳化的資料表等 SQL Server 功能，還是 Resource Governor (如果有 Enterprise Edition) 來改善效能。

• 如果使用 R，請盡可能以支援分散式執行的 RevoScaleR 函數取代傳統 R 函數。 如需詳細資訊，請參閱基底 R 與 RevoScaleR 函數的比較。

步驟 3： 準備開始部署

• 通知系統管理員，以便在部署程式碼之前，先安裝和測試套件。

  在開發環境中，在安裝您的程式碼時一併安裝套件或許是可行的方式，但在生產環境中則是一個不好的做法。

  無論您使用的是預存程序，或是在 SQL Server 計算內容中執行 R/Python 程式碼，都不支援使用者程式庫。

在預存程序中封裝 R/Python 程式碼

• 建立 T-SQL 使用者定義函數，以使用 sp-execute-external-script 陳述式內嵌您的程式碼。

• 如果有複雜的 R 程式碼，請使用 R 套件 sqlrutils 來轉換程式碼。 此套件的設計目的是要協助有經驗的 R 使用者撰寫良好的預存程序程式碼。 您可以使用明確定義的輸入和輸出，將 R 程式碼重寫為單一函數，然後使用 sqlrutils 套件以正確的格式產生輸入和輸出。 sqlrutils 封裝會為您產生完整的預存程序程式碼，而且也可以在資料庫中註冊預存程序。

  如需詳細資訊和範例，請參閱 sqlrutils (SQL)。

與其他工作流程整合

• 利用 T-SQL 工具和 ETL 程序。 在資料工作流程中預先執行特徵工程、特徵擷取，以及資料清理。

  當您在專用開發環境中工作時，您可以將資料提取到您的電腦、反覆分析該資料，然後寫出或顯示結果。 不過，將獨立程式碼移轉到 SQL Server 時，此程序大部分都可簡化或委派給其他 SQL Server 工具來執行。

• 使用安全、非同步視覺化策略。

  SQL Server 的使用者通常無法存取伺服器上的檔案，而且 SQL 用戶端工具通常不支援 R/Python 圖形裝置。 如果您在解決方案中產生繪圖或其他圖形，請考慮將繪圖匯出為二進位資料，並儲存至資料表或寫入。

• 將預測和評分函式包裝在預存程序中以供應用程式直接存取。

下一步

若要檢視如何在 SQL Server 中部署 R 和 Python 解決方案的範例，請參閱下列教學課程：

R 教學課程

• 使用 SQL 機器學習在 R 中開發預測模型

• 使用二元分類預測紐約市計程車車資

• 適用 R 資料科學家的 SQL 開發

Python 教學課程

• 使用 SQL 機器學習搭配線性迴歸來預測滑雪工具租用

• 使用二元分類預測紐約市計程車車資