Saving dbplyr query (tbl_sql object) to MySQL without saving data locally

Question

This question expands on this question

Here, I'm using the custom function created by @Simon.S.A. shown in the answer to this question. I'm attempting to save a tbl_sql object in R to MySQL as a new table without first saving it locally. Here, the database and schema in my MySQL are named "test." The tbl_sql object in R is my_data, and I want to save this is a new table in MySQL labeled "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")

After running final line, I get the following error. I'm not sure how I can fix this.

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")

Please would you show how you call `write_to_database`? Also the error message looks cut off (e.g. `sub_quer` instead of `sub_query`), please would you check this is the complete message? — Simon.S.A., Jun 21 '22 at 20:59
@Simon.S.A. Appreciate you taking a look at the new post. Just updated the post with full warning. also, I call write to database only as shown: write_to_database(my_data, "test", "test", "car_data"). Is there a different way I should be doing it? Thanks so much. — dd_data, Jun 21 '22 at 22:49
Several things to check (1) are you using an in-memory database or a remote one? (2) does the `test` database and `test` schema exist in the database? (3) does it work if you remove the schema from `sql_query`? (4) if you `print(sql_query)` during `write_to_database` do you get valid SQL? (5) do other DBI functions work? try `DBI::dbGetQuery(con, "SELECT * FROM mtcars")` — Simon.S.A., Jun 22 '22 at 00:59
@Simon.S.A. Thank you very much. This was a huge help. By replacing the sql_query of your function with that provided by Parfait, the function works. — dd_data, Jun 22 '22 at 16:45

score 2 · Accepted Answer · answered Jun 22 '22 at 00:08

Creating a table with INTO command is an SQL Server (even MS Access) specific syntax and not supported in MySQL. Instead, consider the counterpart statement: CREATE TABLE...SELECT. Also, schema differs between RDBMS's. For MySQL, database is synonymous to schema.

Therefore, consider adjusted version of SQL build:

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"
    )

This solved the problem, thank you so much! Thanks for the note re: schema/db, I wasn't sure what the difference was between those two. — dd_data, Jun 22 '22 at 16:41

Saving dbplyr query (tbl_sql object) to MySQL without saving data locally

1 Answers1

Linked