Microsoft SQL Server是一款強(qiáng)大的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，可以將原始數(shù)據(jù)轉(zhuǎn)化為可發(fā)送到任何設(shè)備的有意義報(bào)告。SQL Server在TPC-E OLTP工作負(fù)載、TPC-H數(shù)據(jù)倉庫工作負(fù)載和實(shí)際應(yīng)用程序性能基準(zhǔn)方面都有很好的表現(xiàn)。

本文將通過查看示例數(shù)據(jù)，利用RevoScaleR和R基礎(chǔ)Hist函數(shù)（這些R函數(shù)已經(jīng)包含在R數(shù)據(jù)庫內(nèi)）中的rxHistogram生成一些平面圖，來介紹如何在Microsoft SQL Server中使用R語言，以及演示如何調(diào)用R函數(shù)從Transact-SQL的存儲(chǔ)過程中保存應(yīng)用程序文件的結(jié)果。

SQL Server免費(fèi)資源下載試用>>

查看數(shù)據(jù)

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

出租車標(biāo)識符

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

行程和費(fèi)用記錄

• 每條行程記錄都包括上車和下車地點(diǎn)、時(shí)間以及行程距離。
• 每條費(fèi)用記錄都包括付費(fèi)信息，如付款類型、總付款和小費(fèi)金額。
• 最后三列可用于各種機(jī)器學(xué)習(xí)任務(wù)。
• 標(biāo)簽列的值都基于tip_amount列，使用以下業(yè)務(wù)規(guī)則：

  派生列名稱	規(guī)則
  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創(chuàng)建存儲(chǔ)過程來繪制數(shù)據(jù)

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

在SQL Server Management Studio中，右鍵單擊NYCTaxi_Sample數(shù)據(jù)庫并選擇New Query。粘貼以下腳本來創(chuàng)建一個(gè)存儲(chǔ)的過程，繪制直方圖。

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

此腳本中的關(guān)鍵點(diǎn)包括：

• 該變量定義查詢文本，該文本作為腳本輸入變量的參數(shù)傳遞給R腳本。對于作為外部進(jìn)程運(yùn)行的R腳本，應(yīng)具有在你的腳本輸入和在SQL Server中啟動(dòng)R會(huì)話的sp_execute_external_script系統(tǒng)存儲(chǔ)過程的輸入之間一對一的映射。
• R腳本中一個(gè)用于定義存儲(chǔ)映像的變量。
• 調(diào)用RevoScaleR庫中的函數(shù)rxHistogram以生成繪圖。
• R設(shè)備設(shè)置為關(guān)閉，因?yàn)檎谑褂肧QL Server中的外部腳本運(yùn)行此命令。通常在R中發(fā)出高級繪圖命令時(shí)，R將打開一個(gè)圖形窗口來調(diào)用設(shè)備。如果要對文件進(jìn)行寫入或處理其他某種輸出方式，您可以關(guān)閉該設(shè)備。
• R圖形序列化為R數(shù)據(jù)幀進(jìn)行輸出。

執(zhí)行存儲(chǔ)的過程，并使用bcp將二進(jìn)制數(shù)據(jù)導(dǎo)出到圖像文件

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

1. 在Management Studio中，運(yùn)行以下語句：

   EXEC [dbo].[RxPlotHistogram]

2. 打開PowerShell命令提示符并運(yùn)行以下命令，提供相應(yīng)的實(shí)例名稱、數(shù)據(jù)庫名稱、用戶名和憑據(jù)作為參數(shù)。

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

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

   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]:

   結(jié)果

   Starting copy...
   1 rows copied.
   Network packet size (bytes): 4096
   Clock Time (ms.) Total     : 3922   Average : (0.25 rows per sec.)

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

使用Hist和多個(gè)輸出格式創(chuàng)建存儲(chǔ)過程

通常情況下，數(shù)據(jù)科學(xué)家生成多個(gè)可視化數(shù)據(jù)從不同的角度來了解數(shù)據(jù)。在此示例中，您將創(chuàng)建名為RPlotHist的存儲(chǔ)過程用于編寫直方圖、散點(diǎn)圖和其他R圖形到JPG和PDF格式。此存儲(chǔ)過程使用Hist函數(shù)來創(chuàng)建直方圖，為例如JPG、PDF和PNG等常用格式導(dǎo)出二進(jìn)制數(shù)據(jù)。

在SQL Server Management Studio中，右鍵單擊NYCTaxi_Sample數(shù)據(jù)庫并選擇New Query。粘貼以下腳本來創(chuàng)建一個(gè)存儲(chǔ)的過程，繪制直方圖。此示例中名為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

此存儲(chǔ)過程內(nèi)SELECT查詢的輸出存儲(chǔ)在默認(rèn)的R數(shù)據(jù)幀InputDataSet中，然后，可以調(diào)用各種R繪圖函數(shù)來生成實(shí)際的圖形文件。

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

若要將文件輸出到另一個(gè)文件夾，請更改存儲(chǔ)過程中嵌入的R腳本中mainDir的變量值。并且還可以修改腳本以輸出不同格式等等。

執(zhí)行該存儲(chǔ)過程

運(yùn)行以下語句將二進(jìn)制繪圖數(shù)據(jù)導(dǎo)出到JPEG和PDF文件格式。

EXEC RPlotHist

結(jié)果

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

文件名稱中的數(shù)字是隨機(jī)生成，以確保在嘗試寫入到現(xiàn)有文件時(shí)，不會(huì)發(fā)生錯(cuò)誤。

查看輸出

若要查看該繪圖，請打開目標(biāo)文件夾并查看創(chuàng)建的存儲(chǔ)過程中R代碼的文件。

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

更多SQL Server實(shí)用教程敬請關(guān)注！

想要購買SQL Server正版授權(quán)，或者獲取更多該產(chǎn)品相關(guān)信息的朋友可以點(diǎn)擊" "~