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

本文将通过查看示例数据，利用RevoScaleR和R基础Hist函数（这些R函数已经包含在R数据库内）中的rxHistogram生成一些平面图，来介绍如何在Microsoft SQL Server中使用R语言，以及演示如何调用R函数从Transact-SQL的存储过程中保存应用程序文件的结果。

查看数据

开发数据科学解决方案通常包括深入的数据探索和数据可视化。在原始的公共数据集中，出租车标识符和行程记录在单独的文件中提供。不过为了使示例数据易于使用，两个原始数据集和medallion列、hack_license以及pickup_datetime进行了连接。所形成的低采样率数据集有1703957行和23列。

出租车标识符

medallion列表示出租车的唯一id号。Hack_license包含出租车司机的驾驶证编号（匿名）。

行程和费用记录

每条行程记录都包括上车和下车地点、时间以及行程距离。每条费用记录都包括付费信息，如付款类型、总付款和小费金额。最后三列可用于各种机器学习任务。标签列的值都基于tip_amount列，使用以下业务规则：

派生列 名称规则

tipped： 如果tip_amount > 0，则tipped = 1；否则tipped = 0

tip_class：1.class 0:tip_amount = $0

2.class 1:tip_amount > $0 且 tip_amount <= $5

3.class 2:tip_amount > $5 且 tip_amount <= $10

4.class 3:tip_amount > $10 且 tip_amount <= $20

5.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:\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代码的文件。

转到标准输出消息中指示的文件夹（在示例中是C:\temp\plots)。打开rHistogram_Tipped.jpg显示得到小费的行程与小费的行程数（此直方图是与上一步中生成的图类似）。打开rHistograms_Tip_and_Fare_Amount.pdf查看根据费用金额的小费绘制的金额分布。

4.打开rXYPlots_Tip_vs_Fare_Amount.pdf查看x轴和y轴为小费金额的散点图。