R中合并Redshift tbl与本地data.table失败的技术咨询

尝试在R中合并Amazon Redshift的dplyr tbl与本地data.table失败。Redshift中的tbl包含30亿行记录，无法载入48G内存，只能通过dplyr tbl进行查询；本地data.table是从txt文件读取的参考表。执行合并时出现以下错误：

Error in UseMethod("op_vars") : 
  no applicable method for 'op_vars' applied to an object of class "NULL"

使用的代码如下：

library(tidyverse)
library(data.table)
# tbl point to the data 
concept <- con %>% tbl(in_schema('database','concept'))
concept_l <- concept %>% filter(id=='Measurement')
# change local lookup file into tbl
loincs <- setattr(loinc,"class",c('tbl','tbl_lazy',"tbl_Redshift","tbl_dbi","tbl_sql"))
# merge info into large data table for further process               
concept_c <- left_join(concept_l,loincs,by=c('concept_code'='code'),copy=T)

咨询两个问题：

1. 是否可以合并tbl与data.table？若可以，该如何操作？
2. 是否有更优的方法直接用data.table处理Redshift中的大数据？若没有，还有哪些比当前方案更好的处理方式？

1. 合并dplyr tbl与本地data.table的可行方法 #

手动修改data.table的类是错误操作，会导致对象结构不兼容进而触发op_vars错误。正确做法是利用dplyr的copy参数将本地表推送到Redshift，在数据库层面完成关联：

• 无需修改data.table的类，直接调用left_join并指定copy = TRUE，dplyr会自动将本地data.table上传到Redshift临时表，再执行关联查询。
• 修正后的代码示例：

library(tidyverse)
library(data.table)

# 连接Redshift并获取远端tbl
concept <- con %>% tbl(in_schema('database','concept'))
concept_l <- concept %>% filter(id == 'Measurement')

# 直接用本地data.table关联，dplyr自动处理上传
concept_c <- left_join(concept_l, loinc, by = c('concept_code' = 'code'), copy = TRUE)

• 注意事项：确保本地data.table的关联列与Redshift表的列类型匹配；若本地表体积较大，可先过滤出仅需关联的列和行，减少上传时间。

2. 处理Redshift大数据的替代方案 #

直接用data.table处理Redshift数据的可行性 #

data.table没有对接Redshift的原生接口，无法像dplyr tbl那样实现数据库层面的懒加载查询。若要使用data.table，只能分批拉取Redshift数据到本地，但30亿行数据无法全部载入内存，此方法不适用。

更优处理方式 #

• Redshift内部预处理：优先在Redshift中创建临时表或视图，先对30亿行数据做过滤、聚合等预处理，减少关联数据量后再拉取到本地。也可将参考表上传为Redshift永久表，避免每次关联都临时上传。
• 用Redshift COPY命令上传参考表：将本地data.table导出为CSV，通过Redshift的COPY命令上传为永久表，之后直接在数据库层面执行关联，性能远优于临时上传本地表。
• 使用dbplyr原生SQL语法：若dplyr关联语法不够灵活，可直接编写原生SQL在Redshift执行关联：

# 先将本地表上传到Redshift临时表
db_write_table(con, "temp_loinc", loinc, temporary = TRUE)

# 原生SQL关联查询
concept_c <- con %>% tbl(sql("
    SELECT c.*, l.*
    FROM database.concept c
    LEFT JOIN temp_loinc l ON c.concept_code = l.code
    WHERE c.id = 'Measurement'
"))

• 分批拉取处理：若必须拉取到本地，可按主键或时间列分批查询Redshift数据，每次处理一部分再合并结果，但此方法效率较低，仅适用于无法在数据库层面完成的复杂逻辑。

内容的提问来源于stack exchange，提问作者ponyhd