将dbplyr查询（TBL_SQL对象）保存到MySQL而不在本地保存数据的情况下

Question

这个问题在

上@simon.sa创建的自定义函数在答案中显示的这个问题。我正在尝试将tbl_sql对象保存在r中，以作为新表作为新表，而不先保存本地。在这里，我的MySQL中的数据库和架构被命名为“测试”。 r中的tbl_sql对象是my_data，我想保存这是mysql标记为“ car_data”的新表。

library(DBI)
library(tidyverse)
library(dbplyr)


#establish connection and import data from MySQL

con <- DBI::dbConnect(RMariaDB::MariaDB(),
                      dbname = "test",
                      host = "127.0.0.1",
                      user = "user",
                      password = "password")

my_data <- tbl(con, "mtcars")
my_data <- my_data %>% filter(mpg >= 22)

# write function to save tbl_sql as a new table in 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))
  }


# execute function

write_to_database(my_data, "test", "test", "car_data")

运行最后一行后，我会收到以下错误。我不确定如何解决此问题。

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.test.car_data
FROM (
SELECT *
FROM `mtcars`
WHERE (`mpg` >= 22.0)
) AS sub_quer' at line 2 [1064]
12.
stop(structure(list(message = "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.test.car_data\nFROM (\nSELECT *\nFROM `mtcars`\nWHERE (`mpg` >= 22.0)\n) AS sub_quer' at line 2 [1064]", 
call = NULL, cppstack = NULL), class = c("Rcpp::exception", 
"C++Error", "error", "condition")))
11.
result_create(conn@ptr, statement, is_statement)
10.
initialize(value, ...)
9.
initialize(value, ...)
8.
new("MariaDBResult", sql = statement, ptr = result_create(conn@ptr, 
statement, is_statement), bigint = conn@bigint, conn = conn)
7.
dbSend(conn, statement, params, is_statement = TRUE)
6.
.local(conn, statement, ...)
5.
dbSendStatement(conn, statement, ...)
4.
dbSendStatement(conn, statement, ...)
3.
dbExecute(tbl_connection, as.character(sql_query))
2.
dbExecute(tbl_connection, as.character(sql_query))
1.
write_to_database(my_data, "test", "test", "car_data")

Answer 1

用命令创建一个表格是SQL Server（甚至是MS访问）特定语法，而MySQL中不支持。相反，请考虑对应语句： 创建表...选择 。此外，架构在RDBMS的不同之处。对于mySQL， database 是 schema 的同义词。

因此，考虑SQL构建的调整后版本：

sql_query <- glue::glue(
      "CREATE TABLE {db}.{tbl_name}\n AS \n",
      "SELECT * \n",
      "FROM (\n",
      dbplyr::sql_render(input_tbl),
      "\n) AS sub_query"
    )