企业智能化解决方案服务商

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

翻译 使用教程 | 编辑:Elyn | 2019-01-11 11:44:27| 阅读 0 有用 (0) 评论 (0) 收藏


概述:本文主要介绍面向SQL开发人员的数据库内存分析之如何调用R函数浏览和可视化数据

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  -d  NYCTaxi_Sample  -U  -P  -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正版授权,或者获取更多该产品相关信息的朋友可以点击" 咨询在线客服 "~

本站文章除注明转载外,均为本站原创或翻译。
欢迎任何形式的转载,但请务必注明出处,尊重他人劳动成果
文章转载自: https://docs.microsoft.com/zh-cn/sql/advanced-analytics/tutorials/sqldev-in-database-r-for-sql-developers?view=sql-server-2017

用户评论: 您的宝贵经验,能为更多人带来帮助,登录后才能评论。
评论加载中...



    在线客服 在线QQ 电话咨询
    400-700-1020