mysqlrdbplyrrmariadb

Save a tbl_sql in R to MySQL?

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.

Solution

  • 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.