Write a table in the database with dplyr

Question

Is there a way to make dplyr connected to database database data to a new table in that database without loading data locally?

I would like to do something like:

tbl(con, "mytable") %>% group_by(dt) %>% tally() %>% write_to(name = "mytable_2", schema = "transformed") 

Answer 1

While I completely agree with the proposal to learn SQL, you can take advantage of the fact that dplyr does not retrieve data until it is absolutely necessary and dplyr query using dplyr , add TO TABLE , and then run the SQL statement using dplyr::do() , as in:

 # CREATE A DATABASE WITH A 'FLIGHTS' TABLE library(RSQLite) library(dplyr) library(nycflights13) my_db <- src_sqlite("~/my_db.sqlite3", create = T) flights_sqlite <- copy_to(my_db, flights, temporary = FALSE, indexes = list( c("year", "month", "day"), "carrier", "tailnum")) # BUILD A QUERY QUERY = filter(flights_sqlite, year == 2013, month == 1, day == 1) %>% select( year, month, day, carrier, dep_delay, air_time, distance) %>% mutate( speed = distance / air_time * 60) %>% arrange( year, month, day, carrier) # ADD THE "TO TABLE" CLAUSE AND EXECUTE THE QUERY do(paste(unclass(QUERY$query$sql), "TO TABLE foo")) 

You can even write a little functoin that does this:

 to_table <- function(qry,tbl) dplyr::do(paste(unclass(qry$query$sql), "TO TABLE",tbl)) 

and program the query into this function as follows:

 filter(flights_sqlite, year == 2013, month == 1, day == 1) %>% select( year, month, day, carrier, dep_delay, air_time, distance) %>% mutate( speed = distance / air_time * 60) %>% arrange( year, month, day, carrier) %>% to_table('foo')