如何在只读PostgreSQL数据库中关联R数据框与数据库表？

问题描述 #

我拥有PostgreSQL数据库的只读访问权限，无法向该数据库写入数据。请问是否可以构建并执行SQL查询，将R中的data frame（或其他R对象）与只读PostgreSQL数据库内的表进行关联？此需求用于访问WRDS的数据。以下是我的伪代码尝试：

# 建立数据库连接
con <- dbConnect( Postgres(), 
          host = 'host.org',
          port = 1234,
          dbname = 'db_name',
          sslmode = 'require',
          user = 'username', password = 'password')

# 创建R数据框
df <- data.frame( customer_id = c('a123', 'a-345', 'b0')  )

# 编写要执行的SQL查询
sql_query <- "
SELECT t.customer_id, t.* FROM df t
   LEFT JOIN table_name df
      on t.customer_id = df.customer_id
"

my_query_results <- dbSendQuery(con, sql_query)
temp <- dbFetch(res, n = 1)
dbClearResult(res)
my_query_results

注：示例查询仅为演示简化编写，实际场景中我可能需要基于3个及以上列、数百万行进行关联。

可行解决方案 #

由于无法向数据库写入数据，不能直接在SQL中引用R本地的df，但可以通过以下几种方法实现关联：

方法1：用dbplyr实现语法层面的关联（推荐） #

借助dbplyr包将R数据框和远程数据库表统一为"可操作对象"，用dplyr语法编写关联逻辑，底层会自动生成包含R数据框内容的SQL语句（无需写入数据库）：

library(dplyr)
library(dbplyr)
library(RPostgres)

# 建立数据库连接
con <- dbConnect(Postgres(), 
                 host = 'host.org',
                 port = 1234,
                 dbname = 'db_name',
                 sslmode = 'require',
                 user = 'username', password = 'password')

# 将数据库表转为远程操作对象
remote_db_table <- tbl(con, "table_name")

# 关联本地数据框与远程表，最后将结果拉回R
result_df <- df %>%
  left_join(remote_db_table, by = "customer_id") %>% # 多列关联可写c("col1"="col1", "col2"="col2")
  collect()

# 关闭连接
dbDisconnect(con)

这种方法代码简洁，自动处理多列关联逻辑，适合中小规模数据场景。

方法2：手动构造带VALUES子句的SQL查询 #

对于数百万行的大规模数据，可以将R数据框内容转换为SQL的VALUES子句，直接嵌入查询中：

library(glue)

# 构造VALUES子句内容（字符串类型加引号，数值类型可去掉此步骤）
values_content <- df %>%
  mutate(across(everything(), ~paste0("'", ., "'"))) %>%
  unite(row, sep = ", ") %>%
  pull() %>%
  paste0("(", ., ")", collapse = ", ")

# 编写完整关联SQL
sql_query <- glue("
  SELECT df.customer_id, t.*
  FROM (VALUES {values_content}) AS df(customer_id)
  LEFT JOIN table_name t ON df.customer_id = t.customer_id
")

# 执行查询并获取结果
result_df <- dbGetQuery(con, sql_query)

注意：多列关联时，要确保VALUES中的列顺序与df(c1,c2,c3)的列名一一对应。

方法3：尝试会话级临时表（视WRDS权限而定） #

部分WRDS环境允许创建仅当前连接有效的临时表（不会写入永久存储），如果权限允许，这是大规模数据关联的高效方式：

# 创建会话级临时表（连接关闭后自动销毁）
dbWriteTable(con, name = "#temp_local_df", value = df, temporary = TRUE)

# 执行关联查询
sql_query <- "
  SELECT t.customer_id, t.*
  FROM #temp_local_df df
  LEFT JOIN table_name t ON df.customer_id = t.customer_id
"
result_df <- dbGetQuery(con, sql_query)

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