使用 R 和 SQL Server 函数创建数据特征（演练）

项目
05/30/2023

适用于：SQL Server 2016 (13.x) 及更高版本

数据工程是机器学习的一个重要组成部分。通常需要先转换数据，然后才能使用数据进行预测建模。如果数据不具有所需的功能，可根据现有值进行创建。

对于此建模任务，想要以英里表示的上车和下车位置之间的距离，而不是使用这两个位置的原始纬度和经度值。若要创建此特征，需要使用 haversine 公式计算两个点之间的直线距离。

在此步骤中，学习用于通过数据创建特征的两种不同方法：

使用自定义 R 函数
在 Transact-SQL 中使用自定义 T-SQL 函数

目标在于创建新的 SQL Server 数据集，其中包括原始列以及新的数值特征 direct_distance。

先决条件

此步骤基于本演练之前的步骤假设一个正在进行的 R 会话。它使用在这些步骤中创建的连接字符串和数据源对象。以下工具和包用于运行脚本。

Rgui.exe 用于运行 R 命令
Management Studio 用于运行 T-SQL

使用 R 进行特征化

虽然 R 语言以其丰富多样的统计库而闻名，但用户可能仍需创建自定义数据转换。

首先，让我们按照 R 用户习惯的方式进行操作：将数据获取到笔记本电脑上，然后运行自定义 R 函数 ComputeDist，该函数计算由纬度和经度值指定的两个点之间的直线距离。

请记住，之前创建的数据源对象仅获取前 1000 行。因此，让我们定义获取所有数据的查询。

bigQuery <- "SELECT tipped, fare_amount, passenger_count,trip_time_in_secs,trip_distance, pickup_datetime, dropoff_datetime,  pickup_latitude, pickup_longitude,  dropoff_latitude, dropoff_longitude FROM nyctaxi_sample";

使用该查询创建新的数据源对象。
```
featureDataSource <- RxSqlServerData(sqlQuery = bigQuery,colClasses = c(pickup_longitude = "numeric", pickup_latitude = "numeric", dropoff_longitude = "numeric", dropoff_latitude = "numeric", passenger_count  = "numeric", trip_distance  = "numeric", trip_time_in_secs  = "numeric", direct_distance  = "numeric"), connectionString = connStr);
```
- RxSqlServerData 可以采用由有效 SELECT 查询（作为 sqlQuery 参数的自变量提供）组成的查询，也可以采用表对象的名称（作为 table 参数提供）。
- 如果想要从表中采样数据，则必须使用 sqlQuery 参数，使用 T-SQL TABLESAMPLE 子句定义采样参数，并将 rowBuffering 自变量设为 FALSE。

运行以下代码来创建自定义 R 函数。 ComputeDist 采用两对纬度和经度值，并计算它们之间的直线距离，以英里为单位返回距离。

env <- new.env();
env$ComputeDist <- function(pickup_long, pickup_lat, dropoff_long, dropoff_lat){
  R <- 6371/1.609344 #radius in mile
  delta_lat <- dropoff_lat - pickup_lat
  delta_long <- dropoff_long - pickup_long
  degrees_to_radians = pi/180.0
  a1 <- sin(delta_lat/2*degrees_to_radians)
  a2 <- as.numeric(a1)^2
  a3 <- cos(pickup_lat*degrees_to_radians)
  a4 <- cos(dropoff_lat*degrees_to_radians)
  a5 <- sin(delta_long/2*degrees_to_radians)
  a6 <- as.numeric(a5)^2
  a <- a2+a3*a4*a6
  c <- 2*atan2(sqrt(a),sqrt(1-a))
  d <- R*c
  return (d)
}

第一行定义一个新环境。在 R 中，环境可用于将命名空间封装在包及类似对象中。可以使用 search() 函数查看工作区中的环境。若要查看特定环境中的对象，请键入 ls(<envname>)。
以 $env.ComputeDist 开头的行包含定义 haversine 公式的代码，该公式计算球面上两点之间的 大圆距离 。

定义函数后，可将其应用到数据，以创建新的特征列 direct_distance。但在运行转换之前，请将计算上下文更改为本地。
```
rxSetComputeContext("local");
```
调用 rxDataStep 函数以获取特征工程数据，并将 env$ComputeDist 函数应用于内存中的数据。
```
start.time <- proc.time();

changed_ds <- rxDataStep(inData = featureDataSource,
transforms = list(direct_distance=ComputeDist(pickup_longitude,pickup_latitude, dropoff_longitude, dropoff_latitude),
tipped = "tipped", fare_amount = "fare_amount", passenger_count = "passenger_count",
trip_time_in_secs = "trip_time_in_secs",  trip_distance="trip_distance",
pickup_datetime = "pickup_datetime",  dropoff_datetime = "dropoff_datetime"),
transformEnvir = env,
rowsPerRead=500,
reportProgress = 3);

used.time <- proc.time() - start.time;
print(paste("It takes CPU Time=", round(used.time[1]+used.time[2],2)," seconds, Elapsed Time=", round(used.time[3],2), " seconds to generate features.", sep=""));
```
- rxDataStep 函数支持用于就地修改数据的各种方法。有关详细信息，请参阅此文章：如何在 Microsft R 中转换和子集化数据
但是，关于 rxDataStep 有几点值得注意：

在其他数据源中，可以使用自变量 varsToKeep 和 varsToDrop，但 SQL Server 数据源不支持这些自变量。因此，在此示例中，我们使用了 transforms 自变量来指定传递列和转换列。另外，在 SQL Server 计算上下文中运行时，inData 自变量只能采用 SQL Server 数据源。

当在较大的数据集上运行时，上述代码还会生成警告消息。当行数乘以要创建的列数超过设定值（默认值为 3,000,000）时，rxDataStep 将返回警告，并且返回的数据帧中的行数将被截断。若要删除警告，可以在 rxDataStep 函数中修改 maxRowsByCols 自变量。但是，如果 maxRowsByCols 太大，则在将数据帧加载到内存中时可能会遇到问题。
（可选）可以调用 rxGetVarInfo 来检查转换后的数据源的架构。
```
rxGetVarInfo(data = changed_ds);
```

使用 Transact-SQL 进行功能化

在本练习中，学习如何使用 SQL 函数（而不是自定义 R 函数）来完成同一任务。

切换到 SQL Server Management Studio 或其他查询编辑器，以运行 T-SQL 脚本。

使用名为 fnCalculateDistance 的 SQL 函数。该函数应该已经存在于 NYCTaxi_Sample 数据库中。在对象资源管理器中，通过导航以下路径来验证该函数是否存在：“数据库”>“NYCTaxi_Sample”>“可编程性”>“函数”>“标量值函数”>“dbo.fnCalculateDistance”。

如果该函数不存在，请使用 SQL Server Management Studio 在 NYCTaxi_Sample 数据库中生成该函数。

CREATE FUNCTION [dbo].[fnCalculateDistance] (@Lat1 float, @Long1 float, @Lat2 float, @Long2 float)
-- User-defined function calculates the direct distance between two geographical coordinates.
RETURNS decimal(28, 10)
AS
BEGIN
  DECLARE @distance decimal(28, 10)
  -- Convert to radians
  SET @Lat1 = @Lat1 / 57.2958
  SET @Long1 = @Long1 / 57.2958
  SET @Lat2 = @Lat2 / 57.2958
  SET @Long2 = @Long2 / 57.2958
  -- Calculate distance
  SET @distance = (SIN(@Lat1) * SIN(@Lat2)) + (COS(@Lat1) * COS(@Lat2) * COS(@Long2 - @Long1))
  --Convert to miles
  IF @distance <> 0
  BEGIN
    SET @distance = 3958.75 * ATAN(SQRT(1 - POWER(@distance, 2)) / @distance);
  END
  RETURN @distance
END

在 Management Studio 的新查询窗口中，从任何支持 Transact-SQL 的应用程序运行以下 Transact-SQL 语句，查看该函数如何工作。

USE nyctaxi_sample
GO

SELECT tipped, fare_amount, passenger_count,trip_time_in_secs,trip_distance, pickup_datetime, dropoff_datetime,
dbo.fnCalculateDistance(pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude) as direct_distance, pickup_latitude, pickup_longitude,  dropoff_latitude, dropoff_longitude 
FROM nyctaxi_sample

若要将值直接插入到新表（必须先创建它）中，可以添加指定表名的 INTO 子句。

USE nyctaxi_sample
GO

SELECT tipped, fare_amount, passenger_count, trip_time_in_secs, trip_distance, pickup_datetime, dropoff_datetime,
dbo.fnCalculateDistance(pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude) as direct_distance, pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude
INTO NewFeatureTable
FROM nyctaxi_sample

也可以从 R 代码调用 SQL 函数。切换回 Rgui 并将 SQL 特征化查询存储到 R 变量中。

featureEngineeringQuery = "SELECT tipped, fare_amount, passenger_count,
    trip_time_in_secs,trip_distance, pickup_datetime, dropoff_datetime,
    dbo.fnCalculateDistance(pickup_latitude, pickup_longitude,  dropoff_latitude, dropoff_longitude) as direct_distance,
    pickup_latitude, pickup_longitude,  dropoff_latitude, dropoff_longitude
    FROM nyctaxi_sample
    tablesample (1 percent) repeatable (98052)"

提示

此查询经过修改，可获取更小的数据样本，从而让本演练的速度变快。如果想要获取所有数据，可以删除 TABLESAMPLE 子句；但是，根据环境，可能无法将完整的数据集加载到 R 中，从而会导致错误。

使用下面的代码行来从 R 环境中调用 Transact-SQL 函数，并将其应用于 featureEngineeringQuery 中定义的数据。

featureDataSource = RxSqlServerData(sqlQuery = featureEngineeringQuery,
  colClasses = c(pickup_longitude = "numeric", pickup_latitude = "numeric",
    dropoff_longitude = "numeric", dropoff_latitude = "numeric",
    passenger_count  = "numeric", trip_distance  = "numeric",
    trip_time_in_secs  = "numeric", direct_distance  = "numeric"),
  connectionString = connStr)

现在，新特征已创建，请调用 rxGetVarsInfo 来创建特征表中的数据摘要。
```
rxGetVarInfo(data = featureDataSource)
```
结果
```
Var 1: tipped, Type: integer
Var 2: fare_amount, Type: numeric
Var 3: passenger_count, Type: numeric
Var 4: trip_time_in_secs, Type: numeric
Var 5: trip_distance, Type: numeric
Var 6: pickup_datetime, Type: character
Var 7: dropoff_datetime, Type: character
Var 8: direct_distance, Type: numeric
Var 9: pickup_latitude, Type: numeric
Var 10: pickup_longitude, Type: numeric
Var 11: dropoff_latitude, Type: numeric
Var 12: dropoff_longitude, Type: numeric
```
注意

在某些情况下，可能会遇到以下错误：The EXECUTE permission was denied on the object 'fnCalculateDistance'（EXECUTE 权限在对象“fnCalculateDistance”上被拒绝）。如果遇到这种情况，请确保所使用的登录名具有在数据库上运行脚本和创建对象的权限，而不仅仅是在实例上具有类似权限。检查 fnCalculateDistance 对象的架构。如果对象由数据库所有者创建，并且登录名属于 db_datareader 角色，则需要授予登录名显式权限才能运行脚本。

比较 R 函数和 SQL 函数

还记得这段用于对 R 代码计时的代码吗？

start.time <- proc.time()
<your code here>
used.time <- proc.time() - start.time
print(paste("It takes CPU Time=", round(used.time[1]+used.time[2],2)," seconds, Elapsed Time=", round(used.time[3],2), " seconds to generate features.", sep=""))

可以尝试将其与 SQL 自定义函数示例配合使用，查看调用 SQL 函数时数据转换需要多长时间。另外，尝试使用 rxSetComputeContext 切换计算上下文并比较计时。

时间可能会有很大差异，具体取决于网络速度和硬件配置。在我们测试的配置中，Transact-SQL 函数方法比使用自定义 R 函数更快。因此，在后续步骤中，我们将 Transact-SQL 函数用于这些计算。

提示

通常，使用 Transact-SQL 的特征工程将比 R 更快。例如，T-SQL 包括可应用于常用数据科学计算（例如滚动移动平均值和 n-tiles）的快速窗口化和排名函数。请根据你的数据和任务选择最高效的方法。

后续步骤

生成 R 模型并保存到 SQL

Share via