R 教學課程：探索及視覺化資料
適用於：
SQL Server 2016 (13.x) 和更新版本 Azure SQL 受控執行個體
在這個五部分教學課程系列的第二部分中，您已探索範例資料並產生一些繪圖。 稍後，您將了解如何在 Python 中序列化繪圖物件，然後將這些物件還原序列化並製作繪圖。
在這五集教學課程系列的第二集中，您要檢閱範例資料，然後使用基礎 R 中的泛型 barplot 和 hist 函數產生一些繪圖。
本文的主要目標是示範如何從預存程序中的 Transact-SQL 呼叫 R 函數，並將結果儲存為應用程式檔案格式：
- 使用
barplot來建立預存程序，以產生 R 繪圖做為 Varbinary 資料。 使用 bcp 將二進位資料流匯出至影像檔案。 - 使用
hist建立預存程序以產生繪圖，並將結果儲存為 JPG 和 PDF 輸出。
注意
由於可透過視覺效果這項強大工具了解資料圖形和分佈，因此 R 提供了各種函數和封裝來產生長條圖、散佈圖、盒狀圖及其他資料探索圖表。 R 通常會使用 R 裝置來建立影像以進行圖形化輸出，您可以擷取後儲存為 Varbinary 資料類型，以便在應用程式中轉譯。 您也可以將影像儲存為任何支援的檔案格式 (.JPG、.PDF 等)。
在本文中，您將：
- 檢閱範例資料
- 在 T-SQL 中使用 R 建立繪圖
- 以多種檔案格式輸出繪圖
在第一部分中，您已安裝必要條件並還原範例資料庫。
在第三部分中，您將了解如何使用 Transact-SQL 函式，從未經處理的資料建立特徵。 接著您將從預存程序呼叫該函數，以建立包含特徵值的資料表。
在第四部分中，您將載入模組，並呼叫所需的函式，以使用 SQL Server 預存程序來建立和定型模型。
在第五部分中，您將了解如何運作您在第四部分中定型並儲存的模型。
檢閱資料
開發資料科學方案通常會包含大量資料瀏覽和資料視覺化。 首先，需要一分鐘的時間來檢閱範例資料 (如果您還沒檢閱)。
在原始的公用資料集中，計程車識別碼和車程記錄會以不同檔案提供。 不過，為了更輕鬆地使用範例資料，已根據 medallion、 hack_license和 pickup_datetime資料行來聯結兩個原始資料集。 也會對記錄進行抽樣，只取得 1% 的原始記錄數目。 產生的向下取樣資料集有 1,703,957 個資料列和 23 個資料行。
計程車識別碼
medallion 資料行代表計程車的唯一識別碼。
hack_license 資料行包含計程車司機駕照號碼 (匿名)。
車程和小費記錄
每筆車程記錄都會包含上車和下車位置與時間，以及車程距離。
每筆費用記錄都會包括付款資訊，例如付款類型、總付款金額和小費金額。
最後三個資料行可以用於各種機器學習工作。 tip_amount 資料行包含連續數值，而且可以當成 label 資料行來進行迴歸分析。 tipped 資料行只有是/否值，並且用於二元分類。 tip_class 資料行有多個 類別標籤 ，因此可以當成多類別分類工作的標籤使用。
這個逐步解說只會示範二元分類工作；您可以自由地嘗試建立其他兩個機器學習工作 (迴歸和多類別分類) 的模型。
使用這些商務規則，用於 label 資料行的值都是根據 tip_amount 資料行︰
衍生的資料行名稱 規則 tipped If tip_amount > 0, tipped = 1, otherwise tipped = 0 tip_class Class 0: tip_amount = $0
Class 1: tip_amount > $0 and tip_amount <= $5
Class 2: tip_amount > $5 and tip_amount <= $10
Class 3: tip_amount > $10 and tip_amount <= $20
Class 4: tip_amount > $20
在 T-SQL 中使用 R 建立繪圖
重要
從 SQL Server 2019 開始，隔離機制會要求您授與繪圖檔案儲存所在目錄的適當權限。 如需有關如何設定這些權限的詳細資訊，請參閱 Windows 上 SQL Server 2019 中的檔案權限區段：機器學習服務的隔離變更
請使用 R 函數 barplot 建立繪圖。 此步驟會根據 Transact-SQL 查詢提供的資料繪製長條圖。 您可以將此函數包裝在預存程序 RPlotHistogram 中。
在 SQL Server Management Studio 的物件總管中，以滑鼠右鍵按一下 NYCTaxi_Sample 資料庫，然後選取 [新增查詢]。 或者，在 Azure Data Studio 中，從 [檔案] 功能表中選取 [新增筆記本]，然後連線到資料庫。
貼上下列指令碼，建立繪製長條圖的預存程序。 此範例名為 RPlotHistogram。
CREATE PROCEDURE [dbo].[RPlotHistogram] AS BEGIN SET NOCOUNT ON; DECLARE @query nvarchar(max) = N'SELECT tipped FROM [dbo].[nyctaxi_sample]' EXECUTE sp_execute_external_script @language = N'R', @script = N' image_file = tempfile(); jpeg(filename = image_file); #Plot histogram barplot(table(InputDataSet$tipped), main = "Tip Histogram", col="lightgreen", xlab="Tipped or not", ylab = "Counts", space=0) dev.off(); OutputDataSet <- data.frame(data=readBin(file(image_file, "rb"), what=raw(), n=1e6)); ', @input_data_1 = @query WITH RESULT SETS ((plot varbinary(max))); END GO
此指令碼中需了解的重點包括下列各項：
@query變數會定義查詢文字 ('SELECT tipped FROM nyctaxi_sample')，以當成指令碼輸入變數@input_data_1的引數傳遞給 R 指令碼。 若是以外部程序執行的 R 指令碼，在指令碼輸入，以及在 SQL Server 上啟動 R 工作階段的 sp_execute_external_script 系統預存程序輸入之間，應該要有一對一的對應。在 R 指令碼中，會定義變數 (
image_file) 以儲存影像。呼叫
barplot函數可產生繪圖。R 裝置設定為 [關閉] ，因為您是以 SQL Server 中的外部指令碼執行此命令。 一般來說，當您在 R 中發出高階繪製命令時，R 將會開啟稱為「裝置」 的圖形視窗。 如果您要寫入檔案或以其他方式處理輸出，可以關閉裝置。
R 圖形物件會序列化為 R data.frame 以進行輸出。
執行預存程序，並使用 bcp 將二進位資料匯出至影像檔案
預存程序會以您明顯無法直接檢視的 varbinary 資料流形式傳回影像。 不過，您可以使用 bcp 公用程式來取得 varbinary 資料，並將它儲存為用戶端電腦上的影像檔。
在 Management Studio 中，執行下列陳述式：
EXEC [dbo].[RPlotHistogram]結果
plot0xFFD8FFE000104A4649...
開啟 PowerShell 命令提示字元，並將適當的執行個體名稱、資料庫名稱、使用者名稱和認證提供為引數來執行下列命令。 若是使用 Windows 身分識別的使用者，您可以使用 -T 取代 -U 和 -P。
bcp "exec RPlotHistogram" queryout "plot.jpg" -S <SQL Server instance name> -d NYCTaxi_Sample -U <user name> -P <password> -T注意
bcp 的命令參數區分大小寫。
如果連接成功，系統會提示您輸入圖形檔格式的詳細資訊。
在每個提示字元下，按 ENTER 接受預設值，但不包括這些變更：
針對 prefix-length of field plot，輸入 0。
如果您想要儲存輸出參數以供日後重複使用，請輸入 Y 。
Enter the file storage type of field plot [varbinary(max)]: Enter prefix-length of field plot [8]: 0 Enter length of field plot [0]: Enter field terminator [none]: Do you want to save this format information in a file? [Y/n] Host filename [bcp.fmt]:結果
Starting copy... 1 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) Total : 3922 Average : (0.25 rows per sec.)提示
如果您將格式資訊儲存至檔案 (bcp.fmt)，則 bcp 公用程式會產生您未來可套用至類似命令的格式定義，而不提示您輸入圖形檔格式選項。 若要使用格式檔案，請在 password 引數之後將
-f bcp.fmt新增至任何命令列結尾。將會在執行 PowerShell 命令的相同目錄中建立輸出檔案。 若要檢視繪圖，只需要開啟檔案 plot.jpg。
使用 hist 建立預存程序
資料科學家通常會產生多個資料視覺效果，從不同的角度深入了解資料。 在此範例中，您將建立名為 RPlotHist 的預存程序，並且將長條圖、散佈圖及其他 R 圖形寫成 .JPG 和 .PDF 格式。
這個預存程序會使用 hist 函數來建立長條圖，將二進位資料匯出成常用的格式，例如 .JPG、.PDF 及 .PNG。
在 SQL Server Management Studio 的物件總管中，以滑鼠右鍵按一下 NYCTaxi_Sample 資料庫，然後選取 [新增查詢]。
貼上下列指令碼，建立繪製長條圖的預存程序。 此範例的名稱是 RPlotHist。
CREATE PROCEDURE [dbo].[RPlotHist] AS BEGIN SET NOCOUNT ON; DECLARE @query nvarchar(max) = N'SELECT cast(tipped as int) as tipped, tip_amount, fare_amount FROM [dbo].[nyctaxi_sample]' EXECUTE sp_execute_external_script @language = N'R', @script = N' # Set output directory for files and check for existing files with same names mainDir <- ''C:\\temp\\plots'' dir.create(mainDir, recursive = TRUE, showWarnings = FALSE) setwd(mainDir); print("Creating output plot files:", quote=FALSE) # Open a jpeg file and output histogram of tipped variable in that file. dest_filename = tempfile(pattern = ''rHistogram_Tipped_'', tmpdir = mainDir) dest_filename = paste(dest_filename, ''.jpg'',sep="") print(dest_filename, quote=FALSE); jpeg(filename=dest_filename); hist(InputDataSet$tipped, col = ''lightgreen'', xlab=''Tipped'', ylab = ''Counts'', main = ''Histogram, Tipped''); dev.off(); # Open a pdf file and output histograms of tip amount and fare amount. # Outputs two plots in one row dest_filename = tempfile(pattern = ''rHistograms_Tip_and_Fare_Amount_'', tmpdir = mainDir) dest_filename = paste(dest_filename, ''.pdf'',sep="") print(dest_filename, quote=FALSE); pdf(file=dest_filename, height=4, width=7); par(mfrow=c(1,2)); hist(InputDataSet$tip_amount, col = ''lightgreen'', xlab=''Tip amount ($)'', ylab = ''Counts'', main = ''Histogram, Tip amount'', xlim = c(0,40), 100); hist(InputDataSet$fare_amount, col = ''lightgreen'', xlab=''Fare amount ($)'', ylab = ''Counts'', main = ''Histogram, Fare amount'', xlim = c(0,100), 100); dev.off(); # Open a pdf file and output an xyplot of tip amount vs. fare amount using lattice; # Only 10,000 sampled observations are plotted here, otherwise file is large. dest_filename = tempfile(pattern = ''rXYPlots_Tip_vs_Fare_Amount_'', tmpdir = mainDir) dest_filename = paste(dest_filename, ''.pdf'',sep="") print(dest_filename, quote=FALSE); pdf(file=dest_filename, height=4, width=4); plot(tip_amount ~ fare_amount, data = InputDataSet[sample(nrow(InputDataSet), 10000), ], ylim = c(0,50), xlim = c(0,150), cex=.5, pch=19, col=''darkgreen'', main = ''Tip amount by Fare amount'', xlab=''Fare Amount ($)'', ylab = ''Tip Amount ($)''); dev.off();', @input_data_1 = @query END
此指令碼中需了解的重點包括下列各項：
預存程序內 SELECT 查詢的輸出會儲存在預設 R 資料框架
InputDataSet中。 接著可以呼叫各種 R 繪製函數，以產生實際圖形檔。 大部分的內嵌 R 指令碼代表這些圖形函數的選項 (例如plot或hist)。R 裝置設定為 [關閉] ，因為您是以 SQL Server 中的外部指令碼執行此命令。 一般來說，當您在 R 中發出高階繪製命令時，R 將會開啟稱為「裝置」 的圖形視窗。 如果您要寫入檔案或以其他方式處理輸出，可以關閉裝置。
所有檔案都會儲存至本機資料夾 C:\temp\Plots。 目的地資料夾是透過執行預存程序時提供給 R 指令碼的引數所定義。 若要將檔案輸出至不同的資料夾，請變更預存程序中所內嵌 R 指令碼內的
mainDir變數值。 您也可以修改指令碼來輸出不同的格式、多個檔案等。
執行預存程序
執行下列陳述式，將二進位繪圖資料匯出成 JPEG 和 PDF 檔案格式。
EXEC RPlotHist
結果
STDOUT message(s) from external script:
[1] Creating output plot files:[1] C:\temp\plots\rHistogram_Tipped_18887f6265d4.jpg[1]
C:\temp\plots\rHistograms_Tip_and_Fare_Amount_1888441e542c.pdf[1]
C:\temp\plots\rXYPlots_Tip_vs_Fare_Amount_18887c9d517b.pdf
檔案名稱中的字數會隨機產生，確保不會因嘗試寫入現有檔案而發生錯誤。
檢視輸出
若要檢視繪圖，請開啟目的地資料夾，並檢閱預存程序中 R 程式碼所建立的檔案。
前往 STDOUT 訊息中指示的資料夾 (在此範例中是 C:\temp\plots)
開啟
rHistogram_Tipped.jpg以顯示收到小費及未收到小費的車程數 (此長條圖與您在上個步驟中產生的長條圖類似)。開啟
rHistograms_Tip_and_Fare_Amount.pdf，查看根據費用金額繪製的小費金額分佈狀況。
開啟
rXYPlots_Tip_vs_Fare_Amount.pdf，檢視 x 軸為費用金額且 y 軸為小費金額的散佈圖。
後續步驟
在本文章中，您將：
- 已檢閱範例資料
- 已在 T-SQL 中使用 R 建立繪圖
- 以多種檔案格式輸出繪圖