SQL开发人员的数据库内存分析——调用R函数浏览和可视化数据

Microsoft SQL Server是一款强大的关系型数据库管理系统，可以将原始数据转化为可发送到任何设备的有意义 告。SQL Server在TPC-E OLTP工作负载、TPC-H数据仓库工作负载和实际应用程序性能基准方面都有很好的表现。

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]ASBEGIN  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))); ENDGO

此脚本中的关键点包括：

• 该变量定义查询文本，该文本作为脚本输入变量的参数传递给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]: 0Enter 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): 4096Clock 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:tempPlots。此目标文件夹作为存储过程的一部分提供给R脚本的参数定义。可以通过更改变量mainDir的值更改此目标文件夹。

若要将文件输出到另一个文件夹，请更改存储过程中嵌入的R脚本中mainDir的变量值。并且还可以修改脚本以输出不同格式等等。

执行该存储过程

运行以下语句将二进制绘图数据导出到JPEG和PDF文件格式。

EXEC RPlotHist

结果

STDOUT message(s) from external script:[1] Creating output plot files:[1] C:tempplotsrHistogram_Tipped_18887f6265d4.jpg[1] C:tempplotsrHistograms_Tip_and_Fare_Amount_1888441e542c.pdf[1]C:tempplotsrXYPlots_Tip_vs_Fare_Amount_18887c9d517b.pdf

文件名称中的数字是随机生成，以确保在尝试写入到现有文件时，不会发生错误。

查看输出

若要查看该绘图，请打开目标文件夹并查看创建的存储过程中R代码的文件。

1. 转到标准输出消息中指示的文件夹（在示例中是C:tempplots)。
2. 打开rHistogram_Tipped.jpg显示得到小费的行程与小费的行程数（此直方图是与上一步中生成的图类似）。
3. 打开rHistograms_Tip_and_Fare_Amount.pdf查看根据费用金额的小费绘制的金额分布。
4. 打开rXYPlots_Tip_vs_Fare_Amount.pdf查看x轴和y轴为小费金额的散点图。

更多SQL Server实用教程敬请关注！

想要购买SQL Server正版授权，或者获取更多该产品相关信息的朋友可以点击” 咨询在线客服 “~

标签：大数据数据分析数据管理数据挖掘