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.

walter · Accepted Answer · 2022-06-23T01:01:54.940

2

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.

edited Jun 23 '22 at 01:01

answered Jun 22 '22 at 19:11

walter

518
3
8

Thank you. This solution is nice and simple. The first option worked, and should be sufficient for what I need, though it would be nice to know how to specify the schema should the need arise. When I run the second option, I get the following error: Error: 'in_schema' is not an exported object from 'namespace:DBI. Do you know how I might fix that? – dd_data Jun 22 '22 at 20:20
Sorry, in_schema is from dbplyr. I'll update the answer – walter Jun 23 '22 at 01:01

score 1 · Answer 2 · answered Jun 19 '22 at 20:48

1

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

answered Jun 19 '22 at 20:48

Simon.S.A.

6,240
7
22
41

Thank you very much. I tried to use the custom function you created in the first link. What is "src" in input_tbl$src$con? As of now, I'm getting an error that reads: 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 'INTO {db}.{schema}.....". Thanks again. – dd_data Jun 21 '22 at 03:39
And I am using the same "con" as I outlined above. thank you. – dd_data Jun 21 '22 at 03:58
The custom function extracts the database connection from the table. `input_tbl$src$con` is the where the database connection is stored within. In your question you have the connection stored in `con` and my answers refers to `tbl_connection`. If you have neither of these variables in local R memory (perhaps they have been overwritten) you could still recover the connection from the remote table using `df$src$con`. – Simon.S.A. Jun 21 '22 at 09:09
Regarding the SQL syntax error - the more likely cause is different versions of SQL - my custom function uses SQL Server syntax. But I think MySQL and SQL Server both use the same syntax for `INTO` - so the error must be somewhere else. This is a little more complex than I can answer in a comment. Consider asking a new question and linking to this one and the previous one. I would be happy to take a look. – Simon.S.A. Jun 21 '22 at 09:14
Thank you. Here is the [link](https://stackoverflow.com/questions/72704727/saving-dbplyr-query-tbl-sql-object-to-mysql-without-saving-data-locally) to my new question. – dd_data Jun 21 '22 at 17:18
Is there a reason you didn't just use `compute()`? – walter Jun 21 '22 at 19:00
`compute()` "stores results in a remote temporary table" see [here](https://dplyr.tidyverse.org/reference/compute.html). I interpreted the question as looking for a permanent, not a temporary table. – Simon.S.A. Jun 21 '22 at 20:46
If you pass `temporary = FALSE` to `compute ()`, it will store in a permanent table. – walter Jun 22 '22 at 09:30

Save a tbl_sql in R to MySQL?

2 Answers2

Linked