Microsoft SQL Server是一款強大的關系型數據庫管理系統，可以將原始數據轉化為可發送到任何設備的有意義報告。SQL Server在TPC-E OLTP工作負載、TPC-H數據倉庫工作負載和實際應用程序性能基準方面都有很好的表現。

本文將通過查看示例數據，利用RevoScaleR和R基礎Hist函數（這些R函數已經包含在R數據庫內）中的rxHistogram生成一些平面圖，來介紹如何在Microsoft SQL Server中使用R語言，以及演示如何調用R函數從Transact-SQL的存儲過程中保存應用程序文件的結果。

SQL Server免費資源下載試用>>

查看數據

開發數據科學解決方案通常包括深入的數據探索和數據可視化。在原始的公共數據集中，出租車標識符和行程記錄在單獨的文件中提供。不過為了使示例數據易于使用，兩個原始數據集和medallion列、hack_license以及pickup_datetime進行了連接。所形成的低采樣率數據集有1703957行和23列。

出租車標識符

• medallion列表示出租車的唯一id號。
• Hack_license包含出租車司機的駕駛證編號（匿名）。

行程和費用記錄

• 每條行程記錄都包括上車和下車地點、時間以及行程距離。
• 每條費用記錄都包括付費信息，如付款類型、總付款和小費金額。
• 最后三列可用于各種機器學習任務。
• 標簽列的值都基于tip_amount列，使用以下業務規則：

  派生列名稱	規則
  tipped	如果tip_amount > 0，則tipped = 1；否則tipped = 0
  tip_class	class 0:tip_amount = $0
  	class 1:tip_amount > $0 且 tip_amount <= $5
  	class 2:tip_amount > $5 且 tip_amount <= $10
  	class 3:tip_amount > $10 且 tip_amount <= $20
  	class 4:tip_amount > $20

使用rxHistogram創建存儲過程來繪制數據

使用RevoScaleR提供的增強型R函數中的rxHistogram來創建繪圖，這一步繪制的直方圖是基于Transact-SQL查詢，可以將此函數包裝在存儲過程中。

在SQL Server Management Studio中，右鍵單擊NYCTaxi_Sample數據庫并選擇New Query。粘貼以下腳本來創建一個存儲的過程，繪制直方圖。

CREATE PROCEDURE [dbo].[RxPlotHistogram]
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @query nvarchar(max) =  
  N'SELECT tipped FROM nyctaxi_sample'  
  EXECUTE sp_execute_external_script @language = N'R',  
                                     @script = N'  
   image_file = tempfile();  
   jpeg(filename = image_file);  
   #Plot histogram  
   rxHistogram(~tipped, data=InputDataSet, col=''lightgreen'',   
   title = ''Tip Histogram'', xlab =''Tipped or not'', ylab =''Counts'');  
   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

此腳本中的關鍵點包括：

• 該變量定義查詢文本，該文本作為腳本輸入變量的參數傳遞給R腳本。對于作為外部進程運行的R腳本，應具有在你的腳本輸入和在SQL Server中啟動R會話的sp_execute_external_script系統存儲過程的輸入之間一對一的映射。
• R腳本中一個用于定義存儲映像的變量。
• 調用RevoScaleR庫中的函數rxHistogram以生成繪圖。
• R設備設置為關閉，因為正在使用SQL Server中的外部腳本運行此命令。通常在R中發出高級繪圖命令時，R將打開一個圖形窗口來調用設備。如果要對文件進行寫入或處理其他某種輸出方式，您可以關閉該設備。
• R圖形序列化為R數據幀進行輸出。

執行存儲的過程，并使用bcp將二進制數據導出到圖像文件

該存儲過程返回的圖像是一個varbinary數據流，顯然無法直接查看該圖像。但是可以使用bcp實用工具獲取此varbinary數據，并將其保存為客戶端計算機上的圖像文件。

1. 在Management Studio中，運行以下語句：

   EXEC [dbo].[RxPlotHistogram]

2. 打開PowerShell命令提示符并運行以下命令，提供相應的實例名稱、數據庫名稱、用戶名和憑據作為參數。

   bcp "exec RxPlotHistogram" queryout "plot.jpg" -S <SQL Server instance name> -d  NYCTaxi_Sample  -U <user name> -P <password> -T

3. 如果連接成功，則將提示你輸入有關圖形文件格式的詳細信息。

   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.)

4. 在每個提示符下按 ENTER以接受默認設置，以下更改除外：
   • 對于prefix-length of field plot，請鍵入0。
   • 如果想要保存輸出參數供以后重復使用，則鍵入Y。
5. 輸出文件在和運行PowerShell命令相同的目錄中創建。若要查看圖表，只需打開文件plot.jpg。
   

使用Hist和多個輸出格式創建存儲過程

通常情況下，數據科學家生成多個可視化數據從不同的角度來了解數據。在此示例中，您將創建名為RPlotHist的存儲過程用于編寫直方圖、散點圖和其他R圖形到JPG和PDF格式。此存儲過程使用Hist函數來創建直方圖，為例如JPG、PDF和PNG等常用格式導出二進制數據。

在SQL Server Management Studio中，右鍵單擊NYCTaxi_Sample數據庫并選擇New Query。粘貼以下腳本來創建一個存儲的過程，繪制直方圖。此示例中名為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繪圖函數來生成實際的圖形文件。

所有文件將都保存到本地文件夾 C:\temp\Plots。此目標文件夾作為存儲過程的一部分提供給R腳本的參數定義。可以通過更改變量mainDir的值更改此目標文件夾。

若要將文件輸出到另一個文件夾，請更改存儲過程中嵌入的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代碼的文件。

1. 轉到標準輸出消息中指示的文件夾（在示例中是C:\temp\plots)。
2. 打開rHistogram_Tipped.jpg顯示得到小費的行程與小費的行程數（此直方圖是與上一步中生成的圖類似）。
3. 打開rHistograms_Tip_and_Fare_Amount.pdf查看根據費用金額的小費繪制的金額分布。
   
4. 打開rXYPlots_Tip_vs_Fare_Amount.pdf查看x軸和y軸為小費金額的散點圖。
   

更多SQL Server實用教程敬請關注！

想要購買SQL Server正版授權，或者獲取更多該產品相關信息的朋友可以點擊" "~