使用 R 和 SQL Server 函数创建数据特征(演练)
适用于: 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 公式的代码,该公式计算球面上两点之间的 大圆距离 。
- 第一行定义一个新环境。 在 R 中,环境可用于将命名空间封装在包及类似对象中。 可以使用
定义函数后,可将其应用到数据,以创建新的特征列 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)的快速窗口化和排名函数。 请根据你的数据和任务选择最高效的方法。