If you’re reporting a bug in dbplyr, it is much easier for me to help you if you can supply a reprex that I can run on my computer. Creating reprexes for dbplyr is particularly challenging because you are probably using a database that you can’t share with me. Fortunately, in many cases you can still demonstrate the problem even if I don’t have the complete dataset, or even access to the database system that you’re using.
This vignette outlines three approaches for creating reprexes that will work anywhere:
tbl_memdb() to easily
create datasets that live in an in-memory SQLite database.
tbl_lazy() to simulate
SQL generation of dplyr pipelines.
translate_sql() to simulate SQL generation of
The first place to start is with SQLite. SQLite is particularly appealing because it’s completely embedded instead an R package so doesn’t have any external dependencies. SQLite is designed to be small and simple, so it can’t demonstrate all problems, but it’s easy to try out and a great place to start.
You can easily create a SQLite in-memory database table using
mf <- memdb_frame(g = c(1, 1, 2, 2, 2), x = 1:5, y = 5:1) mf #> # Source: table<dbplyr_001> [?? x 3] #> # Database: sqlite 3.41.2 [:memory:] #> g x y #> <dbl> <int> <int> #> 1 1 1 5 #> 2 1 2 4 #> 3 2 3 3 #> 4 2 4 2 #> # ℹ more rows mf %>% group_by(g) %>% summarise_all(mean, na.rm = TRUE) #> # Source: SQL [2 x 3] #> # Database: sqlite 3.41.2 [:memory:] #> g x y #> <dbl> <dbl> <dbl> #> 1 1 1.5 4.5 #> 2 2 4 2
Reprexes are easiest to understand if you create very small custom
data, but if you do want to use an existing data frame you can use
Many problems with dbplyr come down to incorrect SQL generation.
Fortunately, it’s possible to generate SQL without a database using
tbl_lazy(). Both take an
con argument which takes a database “simulator” like
If you isolate the problem to incorrect SQL generation, it would be very helpful if you could also suggest more appropriate SQL.
In some cases, you might be able to track the problem down to
incorrect translation for a single column expression. In that case, you
can make your reprex even simpler with