README.Rmd

---
output: downlit::readme_document
---

<!-- README.md is generated from README.Rmd. Please edit that file -->

```{r, include = FALSE}
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)
  x
}

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

Sys.setenv(DUCKPLYR_OUTPUT_ORDER = TRUE)
```

# duckplyr

<!-- badges: start -->
[![Lifecycle: experimental](https://img.shields.io/badge/lifecycle-experimental-orange.svg)](https://lifecycle.r-lib.org/articles/stages.html#experimental)
[![R-CMD-check](https://github.com/duckdblabs/duckplyr/actions/workflows/R-CMD-check.yaml/badge.svg)](https://github.com/duckdblabs/duckplyr/actions/workflows/R-CMD-check.yaml)
<!-- badges: end -->

The goal of duckplyr is to provide a drop-in replacement for dplyr that uses [DuckDB](https://duckdb.org/) as a backend for fast operation.
DuckDB is an in-process SQL OLAP database management system.

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:

``` r
install.packages("duckplyr")
```

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

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

Or from [GitHub](https://github.com/) with:

``` r
# 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("duckdblabs/duckplyr")
```


## Examples

```{r attach}
library(conflicted)
library(dplyr)
conflict_prefer("filter", "dplyr")
```

There are two ways to use duckplyr.

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

The examples below illustrate both methods.
See also the companion [demo repository](https://github.com/Tmonster/duckplyr_demo) 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_df()` to enable processing with duckdb:

```{r}
out <-
  palmerpenguins::penguins %>%
  # CAVEAT: factor columns are not supported yet
  mutate(across(where(is.factor), as.character)) %>%
  duckplyr::as_duckplyr_df() %>%
  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 data frame or tibble, with its own class.

```{r}
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.

```{r}
out %>%
  explain()
```

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

```{r}
out$mean_bill_area
```

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

```{r}
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:

```{r}
duckplyr::methods_overwrite()
```

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

```{r echo = FALSE}
Sys.setenv(DUCKPLYR_FALLBACK_COLLECT = 0)
```

```{r}
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:

```{r}
class(out)
```

Querying the number of rows also starts the computation:

```{r}
nrow(out)
```

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

```{r}
duckplyr::methods_restore()
```

dplyr is active again:

```{r}
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.
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.

```{r echo = FALSE}
Sys.setenv(DUCKPLYR_FALLBACK_COLLECT = "")
```

```{r}
palmerpenguins::penguins %>%
  duckplyr::as_duckplyr_df() %>%
  transmute(bill_area = bill_length_mm * bill_depth_mm) %>%
  head(3)
```

## Extensibility

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

```{r extensibility}
library(duckplyr)
```


```{r overwrite, echo = FALSE}
methods_overwrite()
```

```{r extensibility2}
# 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)
```