In duckdblabs/duckplyr: A 'DuckDB'-Backed Version of 'dplyr'

knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>",
  fig.path = "man/figures/README-",
  out.width = "100%"
)

pkgload::load_all()

set.seed(20230702)

clean_output <- function(x, options) {
  x <- gsub("0x[0-9a-f]+", "0xdeadbeef", x)
  x <- gsub("dataframe_[0-9]*_[0-9]*", "      dataframe_42_42      ", x)
  x <- gsub("[0-9]*\\.___row_number ASC", "42.___row_number ASC", x)

  index <- x
  index <- gsub("─", "-", index)
  index <- strsplit(paste(index, collapse = "\n"), "\n---\n")[[1]][[2]]
  writeLines(index, "index.md")

  x <- fansi::strip_sgr(x)
  x
}

options(cli.num_colors = 256)

local({
  hook_source <- knitr::knit_hooks$get("document")
  knitr::knit_hooks$set(document = clean_output)
})

Sys.setenv(DUCKPLYR_OUTPUT_ORDER = TRUE)

duckplyr

The goal of the duckplyr R package is to provide a drop-in replacement for dplyr that uses DuckDB as a backend for fast operation. DuckDB is an in-process OLAP database management system, dplyr is the grammar of data manipulation in the tidyverse.

duckplyr also defines a set of generics that provide a low-level implementer's interface for dplyr's high-level user interface.

Installation

Install duckplyr from CRAN with:

install.packages("duckplyr")

You can also install the development version of duckplyr from R-universe:

install.packages("duckplyr", repos = c("https://tidyverse.r-universe.dev", "https://cloud.r-project.org"))

Or from GitHub with:

# install.packages("pak", repos = sprintf("https://r-lib.github.io/p/pak/stable/%s/%s/%s", .Platform$pkgType, R.Version()$os, R.Version()$arch))
pak::pak("tidyverse/duckplyr")

Examples

library(conflicted)
library(dplyr)
conflict_prefer("filter", "dplyr")

There are two ways to use duckplyr.

To enable duckplyr for individual data frames, use duckplyr::as_duckplyr_tibble() as the first step in your pipe, without attaching the package.
By calling library(duckplyr), it overwrites dplyr methods and is automatically enabled for the entire session without having to call as_duckplyr_tibble(). To turn this off, call methods_restore().

The examples below illustrate both methods. See also the companion demo repository for a use case with a large dataset.

Usage for individual data frames

This example illustrates usage of duckplyr for individual data frames.

Use duckplyr::as_duckplyr_tibble() to enable processing with duckdb:

out <-
  palmerpenguins::penguins %>%
  # CAVEAT: factor columns are not supported yet
  mutate(across(where(is.factor), as.character)) %>%
  duckplyr::as_duckplyr_tibble() %>%
  mutate(bill_area = bill_length_mm * bill_depth_mm) %>%
  summarize(.by = c(species, sex), mean_bill_area = mean(bill_area)) %>%
  filter(species != "Gentoo")

The result is a tibble, with its own class.

class(out)
names(out)

duckdb is responsible for eventually carrying out the operations. Despite the late filter, the summary is not computed for the Gentoo species.

out %>%
  explain()

All data frame operations are supported. Computation happens upon the first request.

out$mean_bill_area

After the computation has been carried out, the results are available immediately:

out

Session-wide usage

This example illustrates usage of duckplyr for all data frames in the R session.

Use library(duckplyr) or duckplyr::methods_overwrite() to overwrite dplyr methods and enable processing with duckdb for all data frames:

duckplyr::methods_overwrite()

This is the same query as above, without as_duckplyr_tibble():

Sys.setenv(DUCKPLYR_FALLBACK_COLLECT = 0)

out <-
  palmerpenguins::penguins %>%
  # CAVEAT: factor columns are not supported yet
  mutate(across(where(is.factor), as.character)) %>%
  mutate(bill_area = bill_length_mm * bill_depth_mm) %>%
  summarize(.by = c(species, sex), mean_bill_area = mean(bill_area)) %>%
  filter(species != "Gentoo")

The result is a plain tibble now:

class(out)

Querying the number of rows also starts the computation:

nrow(out)

Restart R, or call duckplyr::methods_restore() to revert to the default dplyr implementation.

duckplyr::methods_restore()

dplyr is active again:

palmerpenguins::penguins %>%
  # CAVEAT: factor columns are not supported yet
  mutate(across(where(is.factor), as.character)) %>%
  mutate(bill_area = bill_length_mm * bill_depth_mm) %>%
  summarize(.by = c(species, sex), mean_bill_area = mean(bill_area)) %>%
  filter(species != "Gentoo")

Telemetry

We would like to guide our efforts towards improving duckplyr, focusing on the features with the most impact. To this end, duckplyr collects and uploads telemetry data, but only if permitted by the user:

No collection will happen unless the user explicitly opts in.
Uploads are done upon request only.
There is an option to automatically upload when the package is loaded, this is also opt-in.

The data collected contains:

The package version
The error message
The operation being performed, and the arguments
- For the input data frames, only the structure is included (column types only), no column names or data

The first time the package encounters an unsupported function, data type, or operation, instructions are printed to the console.

Sys.setenv(DUCKPLYR_FALLBACK_COLLECT = "")

palmerpenguins::penguins %>%
  duckplyr::as_duckplyr_tibble() %>%
  transmute(bill_area = bill_length_mm * bill_depth_mm) %>%
  head(3)

How is this different from dbplyr?

The duckplyr package is a dplyr backend that uses DuckDB, a high-performance, embeddable OLAP database. It is designed to be a fully compatible drop-in replacement for dplyr, with exactly the same syntax and semantics:

Input and output are data frames or tibbles
All dplyr verbs are supported, with fallback
All R data types and functions are supported, with fallback
No SQL is generated

The dbplyr package is a dplyr backend that connects to SQL databases, and is designed to work with various databases that support SQL, including DuckDB. Data must be copied into and collected from the database, and the syntax and semantics are similar but not identical to plain dplyr.

Extensibility

This package also provides generics, for which other packages may then implement methods.

library(duckplyr)

methods_overwrite()

# Create a relational to be used by examples below
new_dfrel <- function(x) {
  stopifnot(is.data.frame(x))
  new_relational(list(x), class = "dfrel")
}
mtcars_rel <- new_dfrel(mtcars[1:5, 1:4])

# Example 1: return a data.frame
rel_to_df.dfrel <- function(rel, ...) {
  unclass(rel)[[1]]
}
rel_to_df(mtcars_rel)

# Example 2: A (random) filter
rel_filter.dfrel <- function(rel, exprs, ...) {
  df <- unclass(rel)[[1]]

  # A real implementation would evaluate the predicates defined
  # by the exprs argument
  new_dfrel(df[sample.int(nrow(df), 3, replace = TRUE), ])
}

rel_filter(
  mtcars_rel,
  list(
    relexpr_function(
      "gt",
      list(relexpr_reference("cyl"), relexpr_constant("6"))
    )
  )
)

# Example 3: A custom projection
rel_project.dfrel <- function(rel, exprs, ...) {
  df <- unclass(rel)[[1]]

  # A real implementation would evaluate the expressions defined
  # by the exprs argument
  new_dfrel(df[seq_len(min(3, ncol(df)))])
}

rel_project(
  mtcars_rel,
  list(relexpr_reference("cyl"), relexpr_reference("disp"))
)

# Example 4: A custom ordering (eg, ascending by mpg)
rel_order.dfrel <- function(rel, exprs, ...) {
  df <- unclass(rel)[[1]]

  # A real implementation would evaluate the expressions defined
  # by the exprs argument
  new_dfrel(df[order(df[[1]]), ])
}

rel_order(
  mtcars_rel,
  list(relexpr_reference("mpg"))
)

# Example 5: A custom join
rel_join.dfrel <- function(left, right, conds, join, ...) {
  left_df <- unclass(left)[[1]]
  right_df <- unclass(right)[[1]]

  # A real implementation would evaluate the expressions
  # defined by the conds argument,
  # use different join types based on the join argument,
  # and implement the join itself instead of relaying to left_join().
  new_dfrel(dplyr::left_join(left_df, right_df))
}

rel_join(new_dfrel(data.frame(mpg = 21)), mtcars_rel)

# Example 6: Limit the maximum rows returned
rel_limit.dfrel <- function(rel, n, ...) {
  df <- unclass(rel)[[1]]

  new_dfrel(df[seq_len(n), ])
}

rel_limit(mtcars_rel, 3)

# Example 7: Suppress duplicate rows
#  (ignoring row names)
rel_distinct.dfrel <- function(rel, ...) {
  df <- unclass(rel)[[1]]

  new_dfrel(df[!duplicated(df), ])
}

rel_distinct(new_dfrel(mtcars[1:3, 1:4]))

# Example 8: Return column names
rel_names.dfrel <- function(rel, ...) {
  df <- unclass(rel)[[1]]

  names(df)
}

rel_names(mtcars_rel)