将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

我使用来自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是用于在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")