# Interoperability with DuckDB and dbplyr

This article describes how to use the full power of DuckDB with duckplyr. Two options are discussed: interoperability with dbplyr and the use of DuckDB’s functions in duckplyr.

    library(conflicted)
    library(duckplyr)
    conflict_prefer("filter", "dplyr")
    #> [conflicted] Removing existing preference.
    #> [conflicted] Will prefer dplyr::filter over any other package.

## Introduction

The duckplyr package is a drop-in replacement for dplyr, designed to work with DuckDB as the backend. There is a translation layer that converts R function calls to DuckDB functions or macros, aiming at full compatibility with R. Many functions are translated already, and many more are not. For functions that cannot be translated, duckplyr falls back to the original R implementation, disrupting the DuckDB pipeline and materializing intermediate results.

Furthermore, DuckDB has functions with no R equivalent. These might be used already by code that interacts with DuckDB through dbplyr, either making use of its passthrough feature (unknown functions are translated to SQL verbatim), or by using the `mutate(x = sql(...))` pattern. When working with duckplyr, this functionality is still accessible, albeit through experimental interfaces:

- `as_tbl()` converts a duckplyr table to a duckdb `tbl` object
- for a duckplyr table, the escape hatch `dd$fun(...)` can be used to call arbitrary DuckDB functions

## From duckplyr to dbplyr

The experimental `as_tbl()` function, introduced in duckplyr 1.1.0, transparently converts a duckplyr frame to a dbplyr `tbl` object:

    df <- duckdb_tibble(a = 2L)
    df
    #> # A duckplyr data frame: 1 variable
    #>       a
    #>   <int>
    #> 1     2

    tbl <- as_tbl(df)
    tbl
    #> # Source:   table<as_tbl_duckplyr_PPyjeiXg8Q> [?? x 1]
    #> # Database: DuckDB v1.3.2 [kirill@Darwin 24.6.0:R 4.5.1//private/var/folders/dj/yhk9rkx97wn_ykqtnmk18xvc0000gn/T/Rtmp1Dqfm2/duckplyr/duckplyr115f7566f9262.duckdb]
    #>       a
    #>   <int>
    #> 1     2

It achieves this by creating a temporary view that points to the relational object created internally by duckplyr, in the same DBI connection as the duckplyr object. No data is copied in this operation. The view is discarded when the `tbl` object goes out of scope.

This allows using arbitrary SQL code, either through `sql()` or by relying on dbplyr’s passthrough feature.

    tbl %>%
      mutate(b = sql("a + 1"), c = least_common_multiple(a, b)) %>%
      show_query()
    #> <SQL>
    #> SELECT q01.*, least_common_multiple(a, b) AS c
    #> FROM (
    #>   SELECT as_tbl_duckplyr_PPyjeiXg8Q.*, a + 1 AS b
    #>   FROM as_tbl_duckplyr_PPyjeiXg8Q
    #> ) q01

There is no R function called `least_common_multiple()`, it is interpreted as a SQL function.

    least_common_multiple(2, 3)
    #> Error in least_common_multiple(2, 3): could not find function "least_common_multiple"

    tbl %>%
      mutate(b = sql("a + 1"), c = least_common_multiple(a, b))
    #> # Source:   SQL [?? x 3]
    #> # Database: DuckDB v1.3.2 [kirill@Darwin 24.6.0:R 4.5.1//private/var/folders/dj/yhk9rkx97wn_ykqtnmk18xvc0000gn/T/Rtmp1Dqfm2/duckplyr/duckplyr115f7566f9262.duckdb]
    #>       a     b     c
    #>   <int> <int> <dbl>
    #> 1     2     3     6

To continue processing with duckplyr, use `as_duckdb_tibble()`:

    tbl %>%
      mutate(b = sql("a + 1"), c = least_common_multiple(a, b)) %>%
      as_duckdb_tibble()
    #> # A duckplyr data frame: 3 variables
    #>       a     b     c
    #>   <int> <int> <dbl>
    #> 1     2     3     6

## Call arbitrary functions in duckplyr

The escape hatch, also introduced in duckplyr 1.1.0, allows calling arbitrary DuckDB functions directly from duckplyr, without going through SQL:

    duckdb_tibble(a = 2L, b = 3L) %>%
      mutate(c = dd$least_common_multiple(a, b))
    #> # A duckplyr data frame: 3 variables
    #>       a     b     c
    #>   <int> <int> <dbl>
    #> 1     2     3     6

The `dd` prefix has been picked for the following reasons:

- it is an abbreviation of “DuckDB”
- it is short and easy to type
- there is no package of this name
- objects are not commonly named `dd` in R

A prefix is necessary to avoid name clashes with existing R functions. If this is used widely, large-scale code analysis may help prioritize the translation of functions that are not yet supported by duckplyr.

The dd package, when attached, will provide a `dd` object containing many known DuckDB functions. This adds support for autocomplete:

Screenshot for autocomplete with the dd package

This package is not necessary to use duckplyr, and the list of functions is incomplete and growing. In case you’re wondering:

    duckdb_tibble(a = "dbplyr", b = "duckplyr") %>%
      mutate(c = dd$damerau_levenshtein(a, b))
    #> # A duckplyr data frame: 3 variables
    #>   a      b            c
    #>   <chr>  <chr>    <dbl>
    #> 1 dbplyr duckplyr     3

## Conclusion

While duckplyr is designed to be a drop-in replacement for dplyr, it still allows to harness most if not all of the power of DuckDB.

See `vignette("limits")` for limitations in the translation employed by duckplyr, `vignette("fallback")` for more information on fallback, and `vignette("telemetry")` for existing attempts to prioritize work on the translation layer.