大网数据广告活动
数据库与应用安全

课程 1 浏览和可视化数据使用 R 和 T-SQL (SQL Server 机器学习)

· 52CE 我爱测

本教程演示如何在 SQL Server 中嵌入 R 存储过程和 T-SQL 函数

适用对象:yesSQL Server(仅限 Windows)noAzure SQL 数据库 noAzure SQL 数据仓库 no并行数据仓库

本文是有关如何在 SQL Server 中使用 R 的 SQL 开发人员教程的一部分。

在本课程中,将查看示例数据,然后生成使用某些图形rxHistogram从RevoScaleR和泛型Hist基础函数中已包含这些 R 函数R Services(数据库内)。

本课程中的主要目标演示如何调用 R 函数从Transact-SQL的存储过程中并保存应用程序文件格式中的结果:

  • 创建存储的过程使用RxHistogram生成 R 绘图作为 varbinary 数据。 使用bcp将导出到图像文件的二进制流。
  • 创建存储的过程使用Hist生成绘图,并将结果另存为 JPG 和 PDF 输出。

Note: 由于可视化是用于了解数据形状和分发此类的强大工具,R 来生成直方图、 散点图、 框绘图和其他数据探索图提供一系列函数和包。 R 通常创建 R 设备使用的图形输出,它可以捕获并存储为图像varbinary呈现应用程序中的数据类型。 此外可以将图像保存到任何支持文件格式 (。JPG、。PDF 等)。

查看数据

开发数据科学解决方案通常包括深入的数据探索和数据可视化。 如果你尚未准备好,首先花点时间查看示例数据。

在原始的公共数据集中,出租车标识符和行程记录提供在单独的文件。 但是,若要使示例数据易于使用,两个原始数据集上进行了联接的列_medallion_, hack_许可证,并且_pickup_datetime_。 仅获取 1% 的原始记录作为采样记录。 所形成的低采样率数据集有 1,703,957 行和 23 列。

出租车标识符

  • Medallion 列表示出租车的唯一 ID 号。

  • _Hack_许可证_列包含出租车司机的驾驶证编号 (匿名)。

行程和费用记录

  • 每条行程记录都包括上车和下车地点和时间,以及行程距离。

  • 每条费用记录都包括付费信息,如付款类型、总付款和小费金额。

  • 最后三列可用于各种机器学习任务。 _提示_量_列包含连续数值,并可用作标签回归分析。 tipped 列只有是/否值,用于二元分类。 _提示_类_列中有多个类标签并因此可以在该标签用于多类分类任务。

    本演练只演示了二元分类任务;欢迎尝试构建其他两个机器学习任务、回归和多级分类的模型。

  • 使用标签列的值都基于_提示_量_列中,使用以下业务规则:

    派生列名称 规则
    tipped 如果 tip_amount > 0,则 tipped = 1;否则 tipped = 0
    tip_class 级别 0:tip_amount = $0级别 1:tip_amount > $0 且 tip_amount <= $5级别 2:tip_amount > $5 且 tip_amount <= $10级别 3:tip_amount > $10 且 tip_amount <= $20级别 4:tip_amount > $20

创建使用 rxHistogram 绘制数据的存储的过程

若要创建该绘图,请使用rxHistogram,在提供的增强型 R 函数之一RevoScaleR。 此步骤中绘制基于数据的直方图Transact-SQL查询。 可以将此函数包装在存储过程PlotRxHistogram。

  1. 在中SQL Server Management Studio,在对象资源管理器中,右键单击NYCTaxi_Sample数据库并选择新查询。

  2. 粘贴以下脚本来创建一个存储的过程,绘制直方图。 此示例中名为 *RPlotRxHistogram。

    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
    

若要了解在此脚本中的关键点包括:

  • 变量 @query 定义查询文本 ('SELECT tipped FROM nyctaxi_sample'),并作为脚本输入变量 @input_data_1的参数传递给 R 脚本。 对于作为外部进程运行的 R 脚本,应具有对你的脚本的输入和输入之间的一对一映射sp_execute_external_script系统存储过程 SQL Server 启动 R 会话。

  • R 脚本,一个变量中 (image_file) 定义用于存储映像。

  • RxHistogram RevoScaleR 库中的函数调用以生成绘图。

  • R 设备设置为关闭因为正在为 SQL Server 中的外部脚本运行此命令。 通常在 R 中,当发出高级绘图命令时,R 将打开一个图形窗口,调用设备。 如果要对文件进行写入或处理输出某种其他方式,您可以关闭该设备。

  • R 图形序列化为 R 数据帧进行输出。

执行存储的过程,并使用 bcp 将二进制数据导出到图像文件

该存储过程返回的图像是一个 varbinary 数据流,显然无法直接查看该图像。 但是,可以使用 bcp 实用工具获取此 varbinary 数据,并将其保存为客户端计算机上的图像文件。

  1. 在 Management Studio中,运行以下语句:

    EXEC [dbo].[RxPlotHistogram]
    

    结果

    绘图 0xFFD8FFE000104A4649...

  2. 打开 PowerShell 命令提示符并运行以下命令,提供相应的实例名称、 数据库名称、 用户名和凭据作为参数。 对于使用 Windows 标识,您可以替换 -U并 -P与 -T。

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

    NOTE 对于 bcp 命令开关区分大小写。

  3. 如果连接成功,则将提示你输入有关图形文件格式的详细信息。

在每个提示符下按 ENTER 以接受默认设置,以下更改除外:

-   对于 **prefix-length of field plot**,请键入 0

-   如果想要保存输出参数供以后重复使用,则键入 **Y** 。

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

> [!TIP]
> 如果将格式设置信息保存到文件 (bcp.fmt),那么 **bcp** 实用工具将生成将来可应用于相似命令的格式定义,而不会提示输入图形文件的格式选项。 若要使用此格式文件,将 `-f bcp.fmt` 添加到任何命令行的末尾,放在 password 参数后面。
  1. 输出文件在和运行 PowerShell 命令相同的目录中创建。 若要查看图表,只需打开文件 plot.jpg。

    带提示和不带提示的出租车行程

创建存储的过程,请使用 Hist 和多个输出格式

通常情况下,数据科学家生成多个数据可视化来了解将数据从不同的角度。 在此示例中,您将创建调用的存储的过程RPlotHist用于编写直方图、 散和到其他 R 图形。JPG 和。PDF 格式。

此存储过程使用Hist函数来创建直方图,例如为常用格式导出的二进制数据。JPG、。PDF、 和。PNG。

  1. 在中SQL Server Management Studio,在对象资源管理器中,右键单击NYCTaxi_Sample数据库并选择新查询。

  2. 粘贴以下脚本来创建一个存储的过程,绘制直方图。 此示例中名为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 绘图函数来生成实际的图形文件。 大部分嵌入的 R 脚本表示这些图形函数的选项,如 plot 或 hist。

  • 所有文件将都保存到本地文件夹 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查看绘制根据费用金额的小费金额的分布。

直方图显示 tip_amount 和 fare_amount

  1. 打开rXYPlots_Tip_vs_Fare_Amount.pdf若要查看费用金额一个散点图 x 轴和 y 轴为小费金额。

    小费金额期所绘制的费用金额

下一课

第 2 课: 创建数据功能使用 T-SQL

上一课

设置演示 NYC 出租车数据

历史资料。此文对应 2018-11-30 的中文文档快照,适用产品版本和命令行为以原文为准。

来源与署名:Microsoft 和文档贡献者(原始作者元数据 HeidiSteen)。固定版本原文。文档采用 CC BY 4.0,示例代码采用 MIT 授权。本站展开固定版本仓库内的引用内容,将 Markdown 转为 HTML,规范化链接,移除外部媒体、发布器占位标签和导航标记。

资料来源与适用范围

资料来源
Microsoft / SQL Server 中文文档历史快照
适用范围
历史中文快照 2018-11-30;详见原文产品版本;文档日期 10/29/2018
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-16
授权说明
CC BY 4.0(文档)/ MIT(示例代码)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。