duckplyr is a drop-in replacement for dplyr, powered by DuckDB. It runs existing dplyr code with the same results, using DuckDB's execution engine where possible and falling back to dplyr where it is not. It can also analyze larger-than-memory datasets directly from files on disk or on the web. duckplyr is developed by Posit, cynkra, and DuckDB Labs.
Unlike dbplyr, duckplyr does not generate SQL strings: it translates dplyr verbs directly into DuckDB's relational API. For the full documentation, visit duckplyr.tidyverse.org.
Installation
duckplyr is available on CRAN and installs the DuckDB R client as a dependency:
install.packages("duckplyr")
Usage as a Drop-In Replacement
Loading duckplyr overrides the dplyr methods for data frames for the rest of the session. Existing dplyr code then runs on DuckDB without changes:
library("duckplyr")
flights_df() |>
filter(!is.na(arr_delay), !is.na(dep_delay)) |>
mutate(inflight_delay = arr_delay - dep_delay) |>
summarize(
.by = c(year, month),
mean_inflight_delay = mean(inflight_delay),
median_inflight_delay = median(inflight_delay),
) |>
filter(month <= 6) |>
arrange(month)
# A tibble: 6 × 4
year month mean_inflight_delay median_inflight_delay
<int> <int> <dbl> <dbl>
1 2013 1 -3.86 -5
2 2013 2 -5.15 -6
3 2013 3 -7.36 -9
4 2013 4 -2.67 -5
5 2013 5 -9.37 -10
6 2013 6 -4.24 -7
To restore the original dplyr methods, call methods_restore() or restart the R session.
Usage for Individual Data Frames
To use DuckDB only for selected data frames, convert them with as_duckdb_tibble() and use dplyr as usual. In this case, there is no need to attach duckplyr:
library("dplyr")
df <- duckplyr::as_duckdb_tibble(data.frame(n = 1:10))
df |>
mutate(m = n + 1) |>
filter(m > 5) |>
count()
The duckdb_tibble() function creates a duckplyr data frame from columns, similar to tibble(). To inspect the DuckDB query plan of a pipeline, use explain().
Reading and Writing Files
duckplyr uses DuckDB's readers to process files without loading them into R's memory first:
read_csv_duckdb()for CSV filesread_parquet_duckdb()for Parquet filesread_json_duckdb()for JSON filesread_file_duckdb()for any other format supported by a DuckDB table function
These functions accept local paths, glob patterns, and URLs. To query remote files, install and load the httpfs extension first:
library("duckplyr")
db_exec("INSTALL httpfs")
db_exec("LOAD httpfs")
flights <- read_parquet_duckdb(
"https://blobs.duckdb.org/flight-data-partitioned/Year=2024/data_0.parquet"
)
flights |>
count(Reporting_Airline, sort = TRUE) |>
head(3) |>
collect()
To write the result of a pipeline to a file without materializing it in R, use compute_parquet() or compute_csv():
flights |>
filter(Month == 1) |>
compute_parquet("flights_january.parquet")
The db_exec() function runs any SQL statement on the default DuckDB connection used by duckplyr, e.g., to load extensions or to change settings.
Lazy Evaluation and Prudence
Computations in duckplyr are lazy: DuckDB only runs the query when the result is accessed, e.g., when it is printed or when a column is accessed. To protect memory, the prudence setting controls when a duckplyr data frame is materialized automatically:
| Prudence | Automatic materialization | Default for |
|---|---|---|
lavish |
Always | duckdb_tibble(), as_duckdb_tibble() |
thrifty |
Only for small results (up to 1 million cells for local data) | read_*_duckdb() functions |
stingy |
Never | – |
When automatic materialization is not allowed, accessing the data, e.g., with nrow(), throws an error. In this case, use collect() to materialize the result explicitly:
flights |> collect() |> nrow()
The prudence level can be set with the prudence argument of as_duckdb_tibble() and the read_*_duckdb() functions. For details, see the “Memory protection” article.
Fallback to dplyr
duckplyr supports the most common dplyr verbs and functions. If an operation cannot be translated to DuckDB, duckplyr falls back to dplyr transparently, and the result is computed in R. With the stingy prudence level, fallbacks are blocked, which ensures that a pipeline runs entirely in DuckDB. To review the fallback status, use fallback_sitrep(). For the supported verbs and functions, see the “Translations” article.
Further Reading
duckplyrdocumentation- “duckplyr: dplyr Powered by DuckDB” blog post
- “Analyzing Open Government Data with duckplyr” blog post