I was searching for ways to try and measure peak RAM usage with dbplyr when I found this github issue. The poster asked about using copy_to() to copy from one database to another without bringing the tbl into RAM, and Hadley replies:
I think this is out of scope for dbplyr; the idea of these tools is to make it possible to work with smaller datasets. For anything large, you really need to deal with it in your own data pipeline.
I've been using dbplyr with MonetDBLite lately to work with some data sets that are too large for memory, sometimes using copy_to() to write intermediary data along the way, with mostly good results, so I'm confused as to what he means by this and if I am missing something important.
Working example
Just to work through a (toy) example, let's say I have the following .csv file:
# produces ~700Mb file, in ~25 seconds (on my machine)
library(tidyverse)
mtcars %>%
mutate(replicate = list(1:500000)) %>%
unnest(replicate) %>%
arrange(replicate) %>%
write_csv("big.csv")
I want to add 1 to all of the values using the dplyr front end and save the result for later processing.
With tibbles, the following seems to peak at about 4.3GB RAM usage:
big.modified <- read_csv("big.csv") %>%
mutate(across(everything(), ~ .x + 1))
With MonetDBLite + dbplyr, the following seems to run fine and uses a negligible amount of RAM:
library(DBI)
library(MonetDBLite)
con <- dbConnect(MonetDBLite::MonetDBLite(), "example.db")
# read, streaming
monetdb.read.csv(con, "big.csv", "big")
tbl(con, "big") %>%
mutate(across(everything(), ~ .x + 1)) %>%
copy_to(con, ., "big_modified", temporary = FALSE)
Same results:
all.equal(as.data.frame(big.modified),
as.data.frame(tbl(con, "big_modified") %>% collect()))
#> [1] TRUE
Unlike DBI::dbWriteTable(), which if used above seems to cost about 1.5GB peak RAM, copy_to() doesn't seem to be using hardly any memory.
Question
I've started using pipes similar in form to the example above in what I'd intend to eventually be production code, and copy_to() seems so far to successfully avoid copying via RAM. Unless I'm missing something, dbplyr would seem to deliver (partially) on the oft-requested ability to work in R with out-of-memory data as does SAS -- at least until you run into something dbplyr can't translate to SQL or which is unable to accept a tbl natively (e.g., many kinds of modeling functions, I would assume?).
Maybe the difference between my case and that of the github poster's is that I'm working with just one database, while they were trying to transfer between two, but I'm still confused as to Hadley's response. The dbplyr introduction explicitly states that one of the main purposes of the package is to make it easier to work with data that don't fit into RAM, so I'm confused as to what he means by it being intended for "smaller datasets." And does "deal with it in your own data pipeline" mean to write your own SQL? I'm trying to understand what limitation(s) he is talking about, as I've been (tentatively) adopting dbplyr in more of my code.