快速入門：搭配 SQL 機器學習使用 R 的資料類型和物件

發行項
05/04/2023

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

在此快速入門中，您將會了解當您在 SQL Server 機器學習服務中或在巨量資料叢集上使用 R 時，如何使用資料結構與資料類型。在此快速入門中，您將會了解如何在 R 與 SQL Server 之間移動資料，以及可能發生的常見問題。

在此快速入門中，您將會了解當您在 SQL Server 機器學習服務中使用 R 時，如何使用資料結構與資料類型。在此快速入門中，您將會了解如何在 R 與 SQL Server 之間移動資料，以及可能發生的常見問題。

在此快速入門中，您將會了解當您在 SQL Server R Services 中使用 R 時，如何使用資料結構與資料類型。在此快速入門中，您將會了解如何在 R 與 SQL Server 之間移動資料，以及可能發生的常見問題。

在此快速入門中，您將了解在 Azure SQL 受控執行個體機器學習服務中使用 R 時，如何使用資料結構與資料類型。您將了解在 R 與 SQL 受控執行個體之間移動資料，以及可能發生的常見問題。

首先要瞭解的常見問題包括：

資料類型有時不相符
可能會發生隱含轉換
有時需要轉型和轉換作業
R 和 SQL 會使用不同的資料物件

Prerequisites

您需要符合下列必要條件，才能執行此快速入門。

SQL Server 機器學習服務。若要安裝機器學習服務，請參閱 Windows 安裝指南或 Linux 安裝指南。您也可以啟用 SQL Server 巨量資料叢集上的機器學習服務。

SQL Server 機器學習服務。若要安裝機器學習服務，請參閱 Windows 安裝指南。

SQL Server 2016 R Services。若要安裝 R Services，請參閱 Windows 安裝指南。

Azure SQL 受控執行個體機器學習服務。如需詳細資訊，請參閱 Azure SQL 受控執行個體機器學習服務概觀。

執行 SQL 查詢的工具，這些查詢包含 R 指令碼。本快速入門使用 Azure Data Studio。

一律傳回資料框架

當您的指令碼將結果從 R 傳回到 SQL Server 時，它必須傳回資料做為 data.frame。您在指令碼中產生的任何其他類型物件 (不論是清單、因數、向量或二進位資料)，如果您想要輸出它以做為預存程序結果的一部分，就必須將它轉換為資料框架。幸好有多個 R 函數可用來支援將其他物件變更為資料框架。您甚至可將二進位模型序列化，並在資料框架中傳回它；稍後您將在本快速入門中執行此動作。

首先，讓我們使用一些 R 的基本 R 物件 (向量、矩陣及清單) 進行實驗，並查看轉換為資料框架如何變更傳遞到 SQL Server 的輸出。

在 R 中比較這兩個 "Hello World" 指令碼。這兩個指令碼看起來幾乎完全相同，但第一個會傳回三個值的單一資料行，而第二個會傳回三個資料行，每個均含單一值。

範例 1

EXECUTE sp_execute_external_script
       @language = N'R'
     , @script = N' mytextvariable <- c("hello", " ", "world");
       OutputDataSet <- as.data.frame(mytextvariable);'
     , @input_data_1 = N' ';

範例 2

EXECUTE sp_execute_external_script
        @language = N'R'
      , @script = N' OutputDataSet<- data.frame(c("hello"), " ", c("world"));'
      , @input_data_1 = N'  ';

識別結構描述和資料類型

為何結果如此不同？

您通常可以使用 R str() 命令來找到答案。在 R 指令碼中的任一處加入函數 str(object_name)，以傳回指定 R 物件的資料結構描述做為資訊訊息。

若要了解為什麼範例 1 和範例 2 會有如此不同的結果，請在每個陳述式中 @script 變數定義的結尾插入一行 str(OutputDataSet)，如下：

已加入 str 函數的範例 1

EXECUTE sp_execute_external_script
        @language = N'R'
      , @script = N' mytextvariable <- c("hello", " ", "world");
      OutputDataSet <- as.data.frame(mytextvariable);
      str(OutputDataSet);'
      , @input_data_1 = N'  '
;

已加入 str 函數的範例 2

EXECUTE sp_execute_external_script
  @language = N'R', 
  @script = N' OutputDataSet <- data.frame(c("hello"), " ", c("world"));
    str(OutputDataSet);' , 
  @input_data_1 = N'  ';

現在，檢視 [訊息] 中的文字，以查看為何會有不同的輸出。

結果 - 範例 1

STDOUT message(s) from external script:
'data.frame':	3 obs. of  1 variable:
$ mytextvariable: Factor w/ 3 levels " ","hello","world": 2 1 3

結果 - 範例 2

STDOUT message(s) from external script:
'data.frame':	1 obs. of  3 variables:
$ c..hello..: Factor w/ 1 level "hello": 1
$ X...      : Factor w/ 1 level " ": 1
$ c..world..: Factor w/ 1 level "world": 1

如您所見，稍微變化一下 R 語法，就會對結果的結構描述產生極大的影響。我們不會詳述原因，但 R 資料類型中的差異會在 Hadley Wickham 所著 "Advanced R" 的資料結構 (英文) 一節中詳細說明。

現在只需注意，您需要在將 R 物件強制轉型為資料框架時檢查預期的結果。

提示

您也可以使用 R 身分識別函數 (例如 is.matrix、is.vector) 來傳回內部資料結構的相關資訊。

資料物件的隱含轉換

每個 R 資料物件都有自己的規則，如果在結合其他資料物件時，兩個資料物件具有相同數目的維度，或者如果任一個資料類型包含異質資料類型，就會按照規則處理其值。

首先，建立測試資料的小型資料表。

CREATE TABLE RTestData (col1 INT NOT NULL)

INSERT INTO RTestData
VALUES (1);

INSERT INTO RTestData
VALUES (10);

INSERT INTO RTestData
VALUES (100);
GO

例如，假設您執行下列陳述式，以使用 R 執行矩陣相乘。您將含有三個值的單一資料行矩陣乘以含有四個值的陣列，並預期會得到一個 4x3 矩陣。

EXECUTE sp_execute_external_script
    @language = N'R'
    , @script = N'
        x <- as.matrix(InputDataSet);
        y <- array(12:15);
    OutputDataSet <- as.data.frame(x %*% y);'
    , @input_data_1 = N' SELECT [Col1]  from RTestData;'
    WITH RESULT SETS (([Col1] int, [Col2] int, [Col3] int, Col4 int));

其中，具有三個值的資料行會轉換成單一資料行矩陣。因為矩陣只是 R 中的特殊陣列案例，所以，陣列 y 會隱含地強制轉型為單一資料行的矩陣，以使這兩個引數相符。

結果

Col1	Col2	Col3	Col4
12	13	14	15
120	130	140	150
1200	1300	1400	1500

不過，請注意您變更陣列 y 大小時會發生的情況。

execute sp_execute_external_script
   @language = N'R'
   , @script = N'
        x <- as.matrix(InputDataSet);
        y <- array(12:14);
   OutputDataSet <- as.data.frame(y %*% x);'
   , @input_data_1 = N' SELECT [Col1]  from RTestData;'
   WITH RESULT SETS (([Col1] int ));

R 現在會傳回單一值做為結果。

結果

Col1
1542

原因為何？在此案例中，因為這兩個引數可以當做長度相同的向量來處理，所以 R 會傳回內積做為矩陣。根據線性代數的規則，這是預期的行為；不過，如果下游應用程式預期輸出結構描述永遠不會變更，則它可能會造成問題！

提示

收到錯誤？請確定您是在包含資料表的資料庫內容中執行預存程序，而不是在 master 或其他資料庫中執行。

此外，建議您避免在這些範例中使用暫存資料表。有些 R 用戶端會終止批次之間的連線，並刪除暫存資料表。

合併或相乘不同長度的資料行

R 提供絕佳彈性來使用不同大小的向量，以及將這些類似資料行的結構結合至資料框架中。向量的清單看起來像是一個資料表，但它們不遵循用以管理資料庫資料表的所有規則。

例如，下列指令碼定義長度為 6 的數值陣列，並將它儲存在 R 變數 df1 中。此數值陣列接著會與 RTestData 資料表 (含有三 (3) 個值) 的整數結合，以建立新的資料框架 df2。

EXECUTE sp_execute_external_script
    @language = N'R'
    , @script = N'
               df1 <- as.data.frame( array(1:6) );
               df2 <- as.data.frame( c( InputDataSet , df1 ));
               OutputDataSet <- df2'
    , @input_data_1 = N' SELECT [Col1]  from RTestData;'
    WITH RESULT SETS (( [Col2] int not null, [Col3] int not null ));

為了填滿資料框架，R 會根據比對陣列 df1 中元素數目所需的次數，多次重複使用擷取自 RTestData 的元素。

結果

Col2	Col3
1	1
10	2
100	3
1	4
10	5
100	6

請記住，資料框架只是看起來像資料表，其實是一個向量清單。

轉型或轉換資料

R 和 SQL Server 不會使用相同的資料類型，因此，當您在 SQL Server 中執行查詢來取得資料，然後將該資料傳遞至 R 執行階段時，通常會發生某些類型的隱含轉換。當您將資料從 R 傳回至 SQL Server 時，將發生另一組轉換。

SQL Server 將資料從查詢發送至 Launchpad 服務所管理的 R 處理序，並將其轉換為內部表示法以提升效率。
R 執行階段會將資料載入 data.frame 變數，並對資料執行自己的作業。
資料庫引擎會使用受保護的內部連線來將資料傳回至 SQL server，並根據 SQL Server 資料類型顯示資料。
藉由使用可發出 SQL 查詢並處理表格式資料集的用戶端或網路程式庫來連接到 SQL Server，您就能取得資料。此用戶端應用程式可能會以其他方式影響資料。

若要查看其運作方式，請在 AdventureWorksDW 資料倉儲上執行這類查詢。此檢視會傳回用來建立預測的銷售資料。

USE AdventureWorksDW
GO

SELECT ReportingDate
         , CAST(ModelRegion as varchar(50)) as ProductSeries
         , Amount
           FROM [AdventureWorksDW].[dbo].[vTimeSeries]
           WHERE [ModelRegion] = 'M200 Europe'
           ORDER BY ReportingDate ASC

注意

您可以使用任何版本的 AdventureWorks，或使用您自己的資料庫建立不同查詢。重點是，嘗試處理一些包含文字、日期時間和數值的資料。

現在，請嘗試將此查詢做為輸入貼到預存程序。

EXECUTE sp_execute_external_script
       @language = N'R'
      , @script = N' str(InputDataSet);
      OutputDataSet <- InputDataSet;'
      , @input_data_1 = N'
           SELECT ReportingDate
         , CAST(ModelRegion as varchar(50)) as ProductSeries
         , Amount
           FROM [AdventureWorksDW].[dbo].[vTimeSeries]
           WHERE [ModelRegion] = ''M200 Europe''
           ORDER BY ReportingDate ASC ;'
WITH RESULT SETS undefined;

如果您收到錯誤，您可能需要對查詢文字進行一些編輯。例如，WHERE 子句中的字串述詞必須以兩組單引號括住。

讓查詢能夠正常運作之後，檢閱 str 函數的結果，以查看 R 如何處理輸入資料。

結果

STDOUT message(s) from external script: 'data.frame':    37 obs. of  3 variables:
STDOUT message(s) from external script: $ ReportingDate: POSIXct, format: "2010-12-24 23:00:00" "2010-12-24 23:00:00"
STDOUT message(s) from external script: $ ProductSeries: Factor w/ 1 levels "M200 Europe",..: 1 1 1 1 1 1 1 1 1 1
STDOUT message(s) from external script: $ Amount       : num  3400 16925 20350 16950 16950

已使用 R 資料類型 POSIXct 來處理 datetime 資料行。
已將文字資料行 "ProductSeries" 識別為一個因數，這表示類別變數。預設會處理字串值以做為因數。如果您將字串傳遞至 R，則會將它轉換為整數，以供內部使用，接著對應回輸出上的字串。

摘要

您可以從這些簡短的範例中了解到，將 SQL 查詢做為輸入進行傳遞時，需檢查資料轉換的效果。由於 R 不支援部分 SQL Server 資料類型，因此請考慮以下方式，以避免發生錯誤：

事先測試您的資料，並確認結構描述中可能會在傳遞至 R 程式碼發生問題的資料行或值。
個別指定輸入資料來源中的資料行，而不是使用 SELECT *，並了解將如何處理每個資料行。
在準備輸入資料時視需要執行明確的轉型，以避免出現意外狀況。
避免傳遞會造成錯誤且不適用模型化的資料行 (例如 GUID 或 rowguid)。

如需可支援與不支援資料類型的詳細資訊，請參閱 R 程式庫和資料類型。

後續步驟

若要了解如何使用 SQL 機器學習撰寫進階 R 函式，請遵循此快速入門：

使用 SQL 機器學習撰寫進階 R 函式

共用方式為