r-将dplyr查询保存到dbplyr中的不同架构中

Question

我有JDBC连接，希望从一个模式中查询数据并保存到另一个

library(tidyverse)
library(dbplyr)
library(rJava)
library(RJDBC)
# access the temp table in the native schema
tbl(conn, "temp")
temp_ed <- temp %*% mutate(new = 1)
# I would like to save temp_ed to a new schema "schmema_new"

我想使用类似dbplyr::compute()的东西，但要专门定义输出模式。似乎可以使用dbplyr::copy_to，但需要通过本地机器获取数据。

我想使用类似RJDBC::dbSendUpdate()的东西，但它最好能与上面的数据操作管道很好地集成。

Answer 1

我使用DBI包中的dbExecute来执行此操作。

关键思想是提取定义当前远程表的查询，并将其作为写入表的较大SQL查询中的子查询。这需要(1(模式存在，(2(您有权写入新表，(3(您知道正确的SQL语法。

直接这样做可能看起来像：

tbl(conn, "temp")
temp_ed <- temp %*% mutate(new = 1)
save_table_query = paste(
"SELECT * INTO my_database.schema_new.my_table FROM (n",
dbplyr::sql_render(temp_ed),
"n) AS sub_query"
)
dbExecute(conn, as.character(save_table_query))

INTO是在SQL server中编写新表的子句(我使用的SQL风格(。您需要为您的数据库找到等效的子句。

在实践中，我使用了一个看起来像这样的自定义函数：

write_to_database <- function(input_tbl, db, schema, tbl_name){
# connection
tbl_connection <- input_tbl$src$con
# SQL query
sql_query <- glue::glue(
"SELECT *n",
"INTO {db}.{schema}.{tbl_name}n",
"FROM (n",
dbplyr::sql_render(input_tbl),
"n) AS sub_query"
)
result <- dbExecute(tbl_connection, as.character(sql_query))
}

将此应用于您的上下文：

tbl(conn, "temp")
temp_ed <- temp %*% mutate(new = 1)
write_to_database(temp_ed, "my_database", "schema_new", "my_table")