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

有关 R 和 SQL 数据类型和对象 (SQL Server 机器学习) 的快速入门

· 52CE 我爱测

在本快速入门教程,了解如何使用数据类型与 R 和 SQL Server 中的数据对象。

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

在本快速入门,获取 R 和 SQL Server 之间移动数据时出现的常见问题的动手介绍。 使用您自己的脚本中的数据时,通过此练习中获得的体验提供了基本的背景。

若要了解得更清楚的常见问题包括:

  • 数据类型有时不匹配
  • 隐式转换可能会发生
  • 有时需要执行强制转换和转换操作
  • R 和 SQL 使用不同的数据对象

必要條件

上一个快速入门中, Hello World R 和 SQL 中、 提供的信息和链接设置为本快速入门所需的 R 环境。

始终返回数据帧

当脚本将结果从 R 返回给 SQL Server 时,必须以 data.frame 的形式返回数据。 在脚本中生成的其他任何对象类型(不管是列表、因子、向量还是二进制数据)都必须转换为数据框架,才能将其输出为存储过程结果的一部分。 幸运的是,有多个 R 函数支持将其他对象更改为数据框架。 你甚至可以序列化二进制模型并在数据框架中返回该模型。本教程稍后将介绍此操作。

首先,让我们实验一些 R 基本 R 对象 — 向量、 矩阵和列表,并查看如何转换成数据帧更改传递到 SQL Server 的输出。

比较在 R 中的这两个"Hello World"脚本脚本看上去大致相同,但第一个返回三个值的单个列,而第二个返回三个列具有单个值每个。

示例 1

EXECUTE sp_execute_external_script
       @language = N'R'
     , @script = N' mytextvariable <- c("hello", " ", "world");
       OutputDataSet <- as.data.frame(mytextvariable);'
     , @input_data_1 = N' ';

示例 2

EXECUTE sp_execute_external_script
        @language = N'R'
      , @script = N' OutputDataSet<- data.frame(c("hello"), " ", c("world"));'
      , @input_data_1 = N'  ';

标识架构和数据类型

结果为何有这么大的差别?

使用 R str() 命令通常可以找到答案。 在 R 脚本中的任何位置添加函数 str(object_name) 可使指定 R 对象的数据架构作为信息性消息返回。 若要查看消息,请查看 Visual Studio Code 中的“消息”窗格,或者查看 SSMS 中的“消息”选项卡。

若要找出示例 1 和示例 2 的结果为何有这么大的差别,请在每个语句的 @script 变量定义末尾插入 str(OutputDataSet) 行,如下所示:

添加使用 str 函数的示例 1

EXECUTE sp_execute_external_script
        @language = N'R'
      , @script = N' mytextvariable <- c("hello", " ", "world");
      OutputDataSet <- as.data.frame(mytextvariable);
      str(OutputDataSet);'
      , @input_data_1 = N'  '
;

添加使用 str 函数的示例 2

EXECUTE sp_execute_external_script
  @language = N'R', 
  @script = N' OutputDataSet <- data.frame(c("hello"), " ", c("world"));
    str(OutputDataSet);' , 
  @input_data_1 = N'  ';

现在,查看“消息”中的文本,了解输出为何不相同的原因。

结果 - 示例 1

STDOUT message(s) from external script:
'data.frame':   3 obs. of  1 variable:
$ mytextvariable: Factor w/ 3 levels " ","hello","world": 2 1 3

结果 - 示例 2

STDOUT message(s) from external script:
'data.frame':   1 obs. of  3 variables:
$ c..hello..: Factor w/ 1 level "hello": 1
$ X...      : Factor w/ 1 level " ": 1
$ c..world..: Factor w/ 1 level "world": 1

可以看到,对 R 语法进行轻微的更改会给结果的架构造成很大的影响。 我们不会深究原因,,因为 R 数据类型的差异更全面解释了这篇文章中的作者是 Hadley Wickham: R 数据结构。

暂时,你只需在将 R 对象强制转换成数据框架时注意检查预期的结果。

Tip:

此外可以使用 R 标识函数,如is.matrix, is.vector,以返回有关内部数据结构的信息。

数据对象的隐式转换

每个 R 数据对象都有自身的规则,指定在将值与其他数据对象组合时,如果两个数据对象具有相同的维度数目,或者如果任意数据对象包含异构数据类型,应如何处理值。

例如,假设你要运行以下语句以使用。 执行矩阵乘法具有三个值的单列矩阵乘以包含四个值的数组,因此期望 4x3 矩阵。

EXECUTE sp_execute_external_script
    @language = N'R'
    , @script = N'
        x <- as.matrix(InputDataSet);
        y <- array(12:15);
    OutputDataSet <- as.data.frame(x %*% y);'
    , @input_data_1 = N' SELECT [Col1]  from RTestData;'
    WITH RESULT SETS (([Col1] int, [Col2] int, [Col3] int, Col4 int));

在幕后,包含三个值的列将转换为单列矩阵。 由于矩阵只是 R 中数组的特殊表示形式,数组 y 将隐式强制转换为单列矩阵,使这两个参数相符。

结果

Col1 Col2 Col3 Col4
12 13 14 15
120 130 140 150
1200 1300 1400 1500

但请注意更改数组的大小会发生什么情况y。

execute sp_execute_external_script
   @language = N'R'
   , @script = N'
        x <- as.matrix(InputDataSet);
        y <- array(12:14);
   OutputDataSet <- as.data.frame(y %*% x);'
   , @input_data_1 = N' SELECT [Col1]  from RTestData;'
   WITH RESULT SETS (([Col1] int ));

现在,R 返回单个值作为结果。

结果

Col1
1542

原因是什么? 在这种情况下,两个参数可以为相同长度的向量进行处理,因为 R 将以矩阵形式返回内部乘积。 根据线性代数的规则,这种行为符合预期;但是,如果下游应用程序预期输出架构永远不变,则这种行为可能会导致问题!

Tip:

收到错误? 这些示例要求表RTestData。 如果尚未创建测试数据表,请返回到本主题并创建表:处理输入和输出。

如果你已创建表,但仍收到错误,请确保在包含数据库的表,而不是在上下文中运行存储的过程主或另一个数据库。

此外,我们建议您不要使用临时表,这些示例。 某些 R 客户端将终止批处理,删除临时表之间的连接。

对不同长度的列进行合并或相乘

R 提供了极大的灵活性,使用矢量不同的大小,以及将这些类似于列的结构组合成数据帧。 向量列表可能看起来像表,但它们并不遵循控制数据库表的所有规则。

例如,以下脚本定义长度为 6 的数字数组,并将其存储在 R 变量 df1 中。 该数值数组然后组合与整数的 RTestData 表,其中包含三 (3) 值,以使新的数据帧df2。

EXECUTE sp_execute_external_script
    @language = N'R'
    , @script = N'
               df1 <- as.data.frame( array(1:6) );
               df2 <- as.data.frame( c( InputDataSet , df1 ));
               OutputDataSet <- df2'
    , @input_data_1 = N' SELECT [Col1]  from RTestData;'
    WITH RESULT SETS (( [Col2] int not null, [Col3] int not null ));

为了填充数据框架,R 将以所需的次数重复从 RTestData 检索的元素,以匹配数组 df1 中的元素数目。

结果

Col2 Col3
1 1
10 2
100 3
1 4
10 5
100 6

请记住,数据框架只是看起来像表,但实际上是向量列表。

强制转换或转换 SQL Server 数据

R 和 SQL Server 使用的数据类型不同,因此,如果你在 SQL Server 中运行查询来获取数据,然后将该数据传递给 R 运行时,通常会发生某种类型的隐式转换。 将数据从 R 返回到 SQL Server 时,会发生另一套转换。

  • SQL Server 将数据从查询推送到快速启动板服务管理的 R 进程,并将其转换为更高的效率的内部表示形式。
  • R 运行时将数据加载到 data.frame 变量中,并对数据执行其自身的操作。
  • 数据库引擎使用受保护的内部连接将数据返回到 SQL Server,并以 SQL Server 数据类型呈现数据。
  • 若要获取数据,可以使用能够发出 SQL 查询并处理表格数据集的客户端或网络库连接到 SQL Server。 此客户端应用程序可能会通过其他方式影响数据。

若要阐释其工作方式上, 运行的查询类似于本AdventureWorksDW数据仓库。 此视图返回用于创建预测的销售数据。

USE AdventureWorksDW
GO

SELECT ReportingDate
         , CAST(ModelRegion as varchar(50)) as ProductSeries
         , Amount
           FROM [AdventureWorksDW].[dbo].[vTimeSeries]
           WHERE [ModelRegion] = 'M200 Europe'
           ORDER BY ReportingDate ASC

Note:

可以使用任何版本的 AdventureWorks,也可以创建不同的查询使用你自己的数据库。 要点是尝试处理一些包含文本、 日期时间和数字值的数据。

现在,请将此查询粘贴作为存储过程的输入。

EXECUTE sp_execute_external_script
       @language = N'R'
      , @script = N' str(InputDataSet);
      OutputDataSet <- InputDataSet;'
      , @input_data_1 = N'
           SELECT ReportingDate
         , CAST(ModelRegion as varchar(50)) as ProductSeries
         , Amount
           FROM [AdventureWorksDW].[dbo].[vTimeSeries]
           WHERE [ModelRegion] = ''M200 Europe''
           ORDER BY ReportingDate ASC ;'
WITH RESULT SETS undefined;

如果遇到错误,可能需要对查询文本进行一些编辑。 例如,必须用两组单引号将 WHERE 子句中的字符串谓词括起来。

正常运行查询后,请查看 str 函数的结果,了解 R 如何处理输入数据。

结果

STDOUT message(s) from external script: 'data.frame':    37 obs. of  3 variables:
STDOUT message(s) from external script: $ ReportingDate: POSIXct, format: "2010-12-24 23:00:00" "2010-12-24 23:00:00"
STDOUT message(s) from external script: $ ProductSeries: Factor w/ 1 levels "M200 Europe",..: 1 1 1 1 1 1 1 1 1 1
STDOUT message(s) from external script: $ Amount       : num  3400 16925 20350 16950 16950
  • 日期时间列已使用 R 数据类型 POSIXct 进行处理。
  • 已标识为"ProductSeries"的文本列身份,这意味着一个分类变量。 默认情况下,字符串值将作为因子处理。 如果将某个字符串传递给 R,该字符串将转换为整数供内部使用,然后映射回到输出中的字符串。

总结

从这些简短的示例,可以看到需要传递 SQL 查询中用作输入时,检查数据转换的效果。 因为某些 SQL Server 数据类型不受 R,请考虑这些方法,从而避免发生错误:

  • 提前测试你的数据并验证列或架构中可能存在问题时传递给 R 代码中的值。
  • 在输入数据源中单独指定列而不要使用 SELECT *,并知道如何处理每个列。
  • 准备输入数据时请根据需要执行显式强制转换,避免出现意外。
  • 避免传递列的数据 (如 GUID 或 rowguid) 会导致错误并不是适用于建模。

有关支持的和不受支持的数据类型的详细信息,请参阅R 库和数据类型。

有关的运行时将字符串转换为数字因子对性能的影响的信息,请参阅SQL Server R Services 性能优化。

下一步

在下一步的快速入门中,将了解如何将 R 函数应用到 SQL Server 数据。

SQL Server 数据的快速入门: 使用 R 函数

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

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

资料来源与适用范围

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

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

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