DuckDBDataFrame 0.99.20
This vignette is for developers: it documents the DuckDBTable abstraction that
underlies DuckDBDataFrame, how R operations become SQL, and how other
packages build on it. For day-to-day use see Introduction to DuckDBDataFrame.
library(DuckDBDataFrame)DuckDBDataFrame extends the S4Vectors framework with DuckDB-backed versions of its core tabular classes:
| Class | Extends | Purpose |
|---|---|---|
DuckDBTable |
RectangularData |
N-dimensional table with a SQL backend |
DuckDBDataFrame |
DataFrame + DuckDBTable |
2-D tabular data with row/column names |
DuckDBColumn |
Vector |
a single extracted (atomic) column |
DuckDBAtomicList |
List |
list columns (DuckDB LIST[]) |
DuckDBEmbeddings |
matrix-like | fixed-length arrays (DuckDB ARRAY[n]) |
DuckDBTable is the foundation. DuckDBDataFrame is the 2-D case (it adds the
constraint nkey(x) <= 1); the same DuckDBTable with two or more key dimensions is
what DuckDBArray wraps for arrays.
A DuckDBTable records a query, not data. Its slots are:
conn: a tbl_duckdb_connection (dplyr/dbplyr) over a DuckDB relation:
Parquet/CSV files or an in-memory table.datacols: a named expression; each element is a column reference
(as.name("mpg")) or a computation (call("/", as.name("mpg"), as.name("hp")))
that translates to SQL.keycols: a named list of dimension index vectors: length 0 (row numbers
generated internally), 1 (row names, a DuckDBDataFrame), or ≥2 (array indices).dimtbls: an optional locked environment of dimension lookup tables for
partition pruning.Operations build up datacols/keycols and the conn query lazily; materialization
(as.data.frame(), as.vector()) is deferred until values are needed. This is what
lets a DuckDBTable describe data larger than memory: filters and arithmetic are
pushed down into DuckDB’s columnar engine, so a row filter (a predicate, such as
mpg > 25) is applied while scanning the Parquet file and only the matching rows and
requested columns are read, rather than loading the whole table first.
DuckDBTable() accepts a Parquet or CSV path, or an existing dplyr connection:
mtcars_df <- cbind(model = rownames(mtcars), mtcars)
path <- tempfile(fileext = ".parquet")
arrow::write_parquet(mtcars_df, path)
tbl <- DuckDBTable(path, datacols = colnames(mtcars),
keycols = list(model = mtcars_df$model))
dim(tbl)
#> [1] 32 11Only path is required; datacols and keycols are optional. datacols
selects and orders the value columns (default: all columns of the source).
keycols (or the singular keycol on DuckDBDataFrame) defines the dimension
index, the row-name equivalent, and accepts two forms:
keycol = "model", which promotes that
stored column to be the key; orkeycol = list(model = mtcars_df$model), which
supplies the key from R (useful when the key is not a stored column).Both yield a table keyed by model with the same key names, but they are not
all.equal(): the key is sourced differently (an on-disk column versus a supplied
vector), so the resulting row order can differ.
d_str <- DuckDBDataFrame(path, datacols = colnames(mtcars), keycol = "model")
d_list <- DuckDBDataFrame(path, datacols = colnames(mtcars),
keycol = list(model = mtcars_df$model))
setequal(rownames(d_str), rownames(d_list)) # same key set
#> [1] TRUE
isTRUE(all.equal(d_str, d_list)) # but not all.equal (row order)
#> [1] FALSEClasses that extend DuckDBTable or use it as a backend rely on:
nrow()/ncol()/dim() (from keycols/datacols), [ for sub-tables,
keynames()/keydimnames()/colnames() accessors, and as.data.frame() for
materialization.
The number of key dimensions determines the shape: 0 → no row names (row numbers
generated), 1 → a DuckDBDataFrame, ≥2 → an array. With no keycols, DuckDBTable
uses a compact row-number encoding; supplying a 1-D key instead names the rows and
drops the generated row number:
tbl0 <- DuckDBTable(path, datacols = colnames(mtcars))
has_row_number(tbl0) # unkeyed: rows addressed by generated number
#> [1] TRUE
dfk <- DuckDBDataFrame(path, datacols = colnames(mtcars),
keycol = list(model = mtcars_df$model))
has_row_number(dfk) # keyed DataFrame: row names, no row number
#> [1] FALSEA DuckDBDataFrame is a DuckDBTable constrained to nkey <= 1 and carrying
DataFrame semantics. Without a key the two hold identical data but differ in
class and behavior, identical() is FALSE (distinct classes) while
all.equal() is TRUE (same underlying table):
tbl1 <- DuckDBTable(path, datacols = colnames(mtcars))
df1 <- DuckDBDataFrame(path, datacols = colnames(mtcars))
identical(tbl1, df1)
#> [1] FALSE
isTRUE(all.equal(tbl1, df1))
#> [1] TRUEColumn operations become SQL, at the table level. A DuckDBColumn computation like
df$mpg / df$hp records call("/", as.name("mpg"), as.name("hp")) in datacols;
row/column summaries on arrays (via DuckDBArray) become GROUP BY
aggregations. The sql_fun() / sql_call() helpers expose DuckDB’s function catalog
so any SQL scalar function can be applied without leaving R.
DuckDBDataFrame keeps a single shared DuckDB connection per session, acquired lazily:
conn <- acquireDuckDBConn()
identical(dbconn(tbl), conn)
#> [1] TRUEOne process per session gives consistent semantics and efficient resource use;
releaseDuckDBConn() tears it down (rarely needed). The connection also configures a
writable extension directory so extension install/load works on shared or read-only
R libraries. Advanced callers can run arbitrary SQL through dbconn() with
DBI.
Dimension tables help when the data is physically partitioned on disk (for
example Hive-style region=West/... directories, or separate Parquet files per
group). A dimtbl maps each key value to the partition attribute(s) it belongs to
(here, each state to a region). When a query filters or groups on a partition
attribute, DuckDB can then read only the matching partition files and skip the
rest, so the cost scales with the partitions touched rather than the full
dataset. For a small single-file table like the one below there is nothing to prune
and hence no speedup; the benefit appears at scale, when the alternative is scanning
every partition. The dimension table is what carries the key→partition mapping that
makes that pruning possible:
state_df <- data.frame(
state = rep(rownames(state.x77), times = ncol(state.x77)),
metric = rep(colnames(state.x77), each = nrow(state.x77)),
value = as.vector(state.x77))
sp <- tempfile(fileext = ".parquet"); arrow::write_parquet(state_df, sp)
tbl2 <- DuckDBTable(sp, datacols = "value",
keycols = list(state = rownames(state.x77),
metric = colnames(state.x77)))
dimtbls(tbl2) <- list(state = DataFrame(
row.names = rownames(state.x77),
region = rep(c("West", "East"), length.out = nrow(state.x77))))
names(dimtbls(tbl2))
#> [1] "state"The simplest way to build on DuckDBDataFrame is to define an S4 class that
contains it. The subclass inherits the lazy backend, SQL translation, and the
full DataFrame API for free, and adds only its own slots or methods:
setClass("AnnotatedDuckDBDataFrame",
contains = "DuckDBDataFrame",
representation(annotation = "character"))
df <- DuckDBDataFrame(path, datacols = colnames(mtcars),
keycol = list(model = mtcars_df$model))
adf <- new("AnnotatedDuckDBDataFrame", df, annotation = "mtcars demo")
is(adf, "DuckDBDataFrame") # inherits the backend
#> [1] TRUE
dim(adf) # inherited, still lazy
#> [1] 32 11
adf@annotation # the added slot
#> [1] "mtcars demo"The suite’s own packages extend the foundation at the DuckDBTable level:
DuckDBTable with nkey >= 2 as a
DelayedArray backend (see that package’s Implementing the DuckDBArray backend
vignette).DuckDBDataFrame to hold genomic coordinates.Both inherit the SQL translation and lazy evaluation described here, so a new
backend mostly needs to define how its data maps onto keycols/datacols and which
operations to push into SQL.
sessionInfo()
#> R version 4.6.1 (2026-06-24)
#> Platform: x86_64-pc-linux-gnu
#> Running under: Ubuntu 24.04.4 LTS
#>
#> Matrix products: default
#> BLAS: /home/biocbuild/bbs-3.24-bioc/R/lib/libRblas.so
#> LAPACK: /usr/lib/x86_64-linux-gnu/lapack/liblapack.so.3.12.0 LAPACK version 3.12.0
#>
#> locale:
#> [1] LC_CTYPE=en_US.UTF-8 LC_NUMERIC=C
#> [3] LC_TIME=en_GB LC_COLLATE=C
#> [5] LC_MONETARY=en_US.UTF-8 LC_MESSAGES=en_US.UTF-8
#> [7] LC_PAPER=en_US.UTF-8 LC_NAME=C
#> [9] LC_ADDRESS=C LC_TELEPHONE=C
#> [11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C
#>
#> time zone: America/New_York
#> tzcode source: system (glibc)
#>
#> attached base packages:
#> [1] stats4 stats graphics grDevices utils datasets methods
#> [8] base
#>
#> other attached packages:
#> [1] DuckDBDataFrame_0.99.20 IRanges_2.47.2 S4Vectors_0.51.6
#> [4] BiocGenerics_0.59.11 generics_0.1.4 bit64_4.8.2
#> [7] BiocStyle_2.41.0
#>
#> loaded via a namespace (and not attached):
#> [1] sass_0.4.10 SparseArray_1.13.2 lattice_0.22-9
#> [4] digest_0.6.39 magrittr_2.0.5 evaluate_1.0.5
#> [7] grid_4.6.1 bookdown_0.47 blob_1.3.0
#> [10] fastmap_1.2.0 jsonlite_2.0.0 Matrix_1.7-6
#> [13] DBI_1.3.0 BiocManager_1.30.27 purrr_1.2.2
#> [16] jquerylib_0.1.4 abind_1.4-8 duckdb_1.5.5
#> [19] cli_3.6.6 rlang_1.3.0 dbplyr_2.6.0
#> [22] XVector_0.53.0 withr_3.0.3 cachem_1.1.0
#> [25] DelayedArray_0.39.4 yaml_2.3.12 otel_0.2.0
#> [28] S4Arrays_1.13.0 tools_4.6.1 dplyr_1.2.1
#> [31] assertthat_0.2.1 vctrs_0.7.3 R6_2.6.1
#> [34] matrixStats_1.5.0 lifecycle_1.0.5 bit_4.6.0
#> [37] arrow_25.0.0 pkgconfig_2.0.3 bslib_0.12.0
#> [40] pillar_1.11.1 glue_1.8.1 xfun_0.60
#> [43] tibble_3.3.1 tidyselect_1.2.1 MatrixGenerics_1.25.0
#> [46] knitr_1.51 htmltools_0.5.9 rmarkdown_2.31
#> [49] compiler_4.6.1