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

我有一个 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()但理想情况下，它可以与上面的数据操作管道很好地集成。

我这样做使用dbExecute来自DBI包裹。

关键思想是提取定义当前远程表的查询，并将其作为写入该表的较大 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")