快速入門:搭配 SQL 機器學習使用 R 的資料類型和物件
適用於: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 函式,請遵循此快速入門: