R集成#

简介#

从SQL Server 2016发布起，Microsoft在安装中随附了一个开放源代码版本的R，以及由Microsoft提供且支持分布式和/或并行处理的包。

运行R脚本的能力通过一项新功能实现：SQL Server R Services。

该架构的设计支持使用R的外部脚本在与SQL Server不同的进程中运行，并允许在SQL Server引擎与R之间传递数据。

在SQL Server“内部”运行的R代码通过调用存储过程来执行。因此，任何可以进行存储过程调用的应用程序都可以启动R代码的执行。此后，SQL Server管理R代码的执行，如下图所示：

1. 对R运行时的请求由传递给存储过程sp_execute_external_script的参数@language='R'指示。SQL Server将此请求发送到启动板服务
2. 启动板服务启动相应的启动程序；此时是RLauncher
3. RLauncher启动外部R进程
4. BxlServer与R运行时协调，以管理与SQL Server的数据交换和处理结果的存储
5. SQL Satellite管理与SQL Server有关的任务和进程的通信
6. BxlServer使用SQL Satellite向SQL Server传达状态和结果
7. SQL Server获得结果并关闭相关任务和进程

在使用R语言集成之前，您必须先为您的SQL Server实例启用对外部脚本的支持，但在此之前，您必须确保R Services已正确安装并正常运行。请参阅设置SQL Server R Services（数据库内） ⧉，以了解如何安装和配置SQL Server R集成。

安装检查清单#

要检查R集成是否成功完成，并运行第一个测试脚本，请执行以下步骤：

1. 使用SQL Server Management Studio连接到SQL Server数据库（其中应已安装了R Services）
2. 运行exec sp_configure并检查“external scripts enabled”变量。如果running值已经设为1，请转至步骤6。否则，请继续执行下一步
3. 运行 exec sp_configure 'external scripts enabled',1; reconfigure with override;
4. 重新启动SQL Server服务。这还应重新启动SQL Server可信启动板服务。
5. 重新运行exec sp_configure 'external scripts enabled'，并检查running值。现在此值应已设置为1
6. 运行以下测试脚本：

exec sp_execute_external_script  @language =N'R', @script=N'OutputDataSet<-InputDataSet', @input_data_1 =N'select 1 as hello' with result sets (([hello] int not null));
go

如果所有步骤都正确执行，则应获得以下结果：

使用外部工具#

您也可以使用RStudio ⧉等R客户端连接到SQL Server R Services。

SQL Server与RStudio之间的连接是使用RODBC库建立的。在使用RODBC包之前，必须在RStudio中安装RODBC包。

启动RStudio后，打开命令窗口并运行Install.packages("RODBC")。这将下载并安装所有必要的包和功能。

正确安装后，请运行library(RODBC)以在当前环境中加载库。

现在，您可以使用以下命令连接到SQL Server：（请更改“SQL Server服务器名称”和“数据库名称”，以与您的凯睿德制造软件MES安装相匹配，例如CMF-VM-CLT-DB1\ONLINE2016和ProductionODS）

cn<-odbcDriverConnect(connection="Driver={SQL Server Native Client 11.0};server=<SQL Server Server name>;database=<Database Name>;trusted_connection=yes;")

“cn”变量现在保存了SQL Server连接，必须使用它来检索数据并将数据直接保存到SQL Server

示例：

table<-sqlFetch(cn,"table name",colnames=FALSE,rows_at_time=1000)
machinefailuresdata<-sqlQuery(cn,"select * from analytics.MachineFailures")
sqlSave(cn,machinefailuresdata, rownames=FALSE, tablename="MachineFailures_R",colname=FALSE,append=FALSE)
odbcClose(cn)

有关详细的包文档和使用示例，请参阅“RODBC”包 ⧉。

凯睿德制造软件MES R集成#

凯睿德制造软件MES使用R集成构建丰富的内容图形和统计信息分析。可以创建丰富的内容图形，并配合使用SQL Server报表生成器，直接以图像形式显示在报表中。

为了实现这一目标，凯睿德制造软件MES建立了一组过程，这些过程已经处理了必要的R连接、图像处理和清理，因此可以无缝集成。

除了标准的R图形库(Graphics)外，凯睿德制造软件MES还使用GGplot2、Lattice ⧉库。

对于统计分析，凯睿德制造软件MES使用CAR和Stats ⧉库。

由于这些库并不包含在R标准发行程序包中，您必须从CRAN ⧉下载并安装它们。安装必须使用管理权限，并且直接在运行R Services的服务器（SQL Server也在相同位置运行）中完成。

为此，请执行以下步骤：

1. 打开与运行SQL Server的服务器之间的远程连接
2. 使用管理权限打开命令提示符
3. 转到C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES\bin或R Services二进制文件所在的位置
4. 通过该目录中的R.exe应用程序，可以直接打开一个运行R安装额终端
5. 运行R.exe，以便直接从互联网或zip文件安装必要的包
6. 要从CRAN（通过互联网访问）安装包，只需运行install.packages("package_name")命令。此命令会妥善处理下载、其依赖项以及所需包的安装
7. 要从本地zip文件安装包，请键入 install.packages("package_zip_file_name", repos=null)

为ggplot2、lattice、car和stats运行步骤6或7。完成此操作后，您当前的R安装和凯睿德制造软件MES程序包即可广泛地使用这些包。

可用的凯睿德制造软件MES R包#

如前所述，为了实现对R Services的访问，凯睿德制造软件MES安装内置了一组存储过程，这些过程包括一组有用的R图形和统计函数。这些函数可以通过SQL Server Reporting Services（图形和统计信息）或SQL Server Management Studio（统计信息）直接访问。

每个图形过程的输出都是一个二进制字符串，以jpeg格式表示结果。然后，通过创建具有以下通用过程调用的数据集，可以在SQL Server报表生成器中直接将此输出字符串用作图像：

DECLARE @id uniqueidentifier=NEWID()
exec p_dynamic_plot
    @id,@DependentVar,@IndependentVar,@Parameter3,@Parameter4,
    @Table,
    @GraphName,
    @Flip,
    @Position,
    @Width,
    @Scalefill,
    @FacetWrap,
    @Smooth,
    @Jitter,
    @DateTimeColumn,
    @BeginDate,
    @EndDate,
    @AdditionalFilter

SELECT plot FROM GraphPlots where identifier=@id
DELETE FROM GraphPlots where identifier=@id

这将创建一个包含一列（绘图）和一条记录的数据集。此列将包含代表jpeg图像的二进制字符串。

然后，您可以向报表中添加图像对象，指定源为“数据库”、MIME类型为图像/jpeg，然后选择该字段作为包含以下内容的表达式：=First(Fields!plot.Value, "Plot") （此代码假定上述创建的数据集名为“Plot”）

统计检验返回键/值(K/V)对数据集。这些K/V对在每个单独检验中都有所不同，它们代表检验本身的输出。

与图形集成类似，统计检验也可以与SQL Server报表生成器配合使用。为此，请使用通用过程调用创建数据集：

DECLARE @id uniqueidentifier=NEWID()

exec p_dynamic_statistic_test @id,
                              @TestName,
                              @Table,
                              @DependentVar,
                              @IndependentVar,
                              @Parameter3,
                              @DateTimeColumn,
                              @BeginDate,
                              @EndDate,
                              @AdditionalFilter,
                              @Paired,
                              @Method,
                              @Alternative,
                              @ConfidenceLevel,
                              @Ordered,
                              @MedianTest

SELECT * FROM StatisticsTestTable where identifier=@id
DELETE FROM StatisticsTestTable where identifier=@id

这将输出一个包含四列（行号、描述、值和标识符）的表。数据集将包含输出检验结果所必需的行数。

然后，您可以通过使用以下配置创建矩阵，将此数据集作为数据透视表插入到报表中：

图形#

凯睿德制造软件MES包含一组存储过程，用作R图形函数的包装器。这些过程的主要入口点是dbo.p_dynamic_plot。此过程可接受多个参数。某些参数在所有图形过程之间共享（例如，包含原始数据的表名称），而其他参数则是可选的，具体取决于调用的图形类型。

PROCEDURE p_dynamic_plot(@id uniqueidentifier,
       @Parameter1 nvarchar(256),
       @Parameter2 nvarchar(256),
       @Parameter3 nvarchar(256),
       @Parameter4 nvarchar(256),
       @Table varchar(256),
       @GraphName varchar(256),
       @Flip integer,
       @Position nvarchar(256),
       @Width Decimal,
       @Scalefill nvarchar(256),
       @FacetWrap integer,
       @Smooth integer,
       @Jitter integer,
       @DateTimeColumn varchar(256),
       @BeginDate datetime,
       @EndDate datetime,
       @AdditionalFilter varchar(256))

Table: R - 图形

下表描述了所有可用的图形类型以及它们实现的R函数

Table: R - 可用的图形类型

统计检验#

与图形过程类似，所有统计检验过程都以名为dbo.p_dynamic_statistic_test的通用存储过程包装。此过程也可接受多个参数。某些参数在所有统计过程之间共享（例如，包含原始数据的表名称），而其他参数则是可选的，具体取决于调用的检验类型。

PROCEDURE p_dynamic_statistic_test(@id uniqueidentifier,
           @TestName varchar(256),
           @Table varchar(256),
           @Parameter1 nvarchar(256),
           @Parameter2 nvarchar(256),
           @Parameter3 nvarchar(256),
           @DateTimeColumn varchar(256) NULL,
           @BeginDate datetime NULL,
           @EndDate datetime NULL,
           @AdditionalFilter varchar(256) NULL,
           @Paired integer NULL,
           @methodCor varchar(256) NULL,
           @alternat varchar(256)NULL,
           @conflevel float NULL,
           @Ordered integer,
           @median integer)

Table: R - 统计检验

下表描述了所有可用的统计检验类型以及它们实现的R函数

Table: R - 统计检验类型