Save a tbl_sql in R to MySQL?

Question

I'm just learning MySQL with R and am wondering if this is possible.

I performed a query on a database that exists on MySQL with the RMariaDB package, as follows:

library(RMariaDB)

con <- DBI::dbConnect(RMariaDB::MariaDB(),
                      dbname = "",
                      host = "",
                      user = "",
                      password = "") #details omitted 

df <- tbl(con,"df")

I then made some adjustments to this object using dplyr commands. Can I save the new, cleaned table to mySQL as a new table without first converting it to a data frame or tibble? It's class, according to R is:

class(df)
[1] "tbl_MariaDBConnection" "tbl_dbi"               "tbl_sql"              
[4] "tbl_lazy"              "tbl"    

Trying to follow methods for saving normal data frames gives me various errors, usually along the lines of "no applicable method for object of class tbl_MariaDBConnection"

Thank you.

Answer 1

You can use the compute() function to write the result to a table in the database. With no other arguments, it will write to a temporary table (that will be cleared when your session ends), but you can specify to make it non-temporary. The documentation for compute() on a tbl_sql is at https://dbplyr.tidyverse.org/reference/collapse.tbl_sql.html.

As to your code, something like

compute(df, temporary=FALSE, name="my_table_name")

will work; if you need it in a schema, you should be able to specify with

compute(df, temporary=FALSE, name = dbplyr::in_schema("my_schema", "my_table_name"))

This method is nice as you don't have to write SQL yourself, and it therefore handles differences in SQL syntax between different database backends.

Answer 2

I use the process described in this answer. But you might also be interested in the answers to this question.

In your context it probably looks like:

sql_query <- glue::glue(
    "SELECT *\n",
    "INTO {db}.{schema}.{tbl_name}\n",
    "FROM (\n",
    dbplyr::sql_render(df),
    "\n) AS sub_query"
  )

dbExecute(tbl_connection, as.character(sql_query))