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.
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):
Column 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:
One 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 26.04 LTS
#>
#> Matrix products: default
#> BLAS: /usr/lib/x86_64-linux-gnu/openblas-pthread/libblas.so.3
#> LAPACK: /usr/lib/x86_64-linux-gnu/openblas-pthread/libopenblasp-r0.3.32.so; LAPACK version 3.12.0
#>
#> locale:
#> [1] LC_CTYPE=en_US.UTF-8 LC_NUMERIC=C
#> [3] LC_TIME=en_US.UTF-8 LC_COLLATE=en_US.UTF-8
#> [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: Etc/UTC
#> tzcode source: system (glibc)
#>
#> attached base packages:
#> [1] stats4 stats graphics grDevices utils datasets methods
#> [8] base
#>
#> other attached packages:
#> [1] DuckDBDataFrame_0.99.23 IRanges_2.47.2 S4Vectors_0.51.6
#> [4] BiocGenerics_0.59.12 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.23-1
#> [4] digest_0.6.39 magrittr_2.0.5 evaluate_1.0.5
#> [7] grid_4.6.1 blob_1.3.0 fastmap_1.2.0
#> [10] jsonlite_2.0.0 Matrix_1.7-6 DBI_1.3.0
#> [13] BiocManager_1.30.27 purrr_1.2.2 jquerylib_0.1.4
#> [16] duckdb_1.5.5 abind_1.4-8 cli_3.6.6
#> [19] rlang_1.3.0 dbplyr_2.6.0 XVector_0.53.0
#> [22] withr_3.0.3 cachem_1.1.0 DelayedArray_0.39.5
#> [25] yaml_2.3.12 otel_0.2.0 S4Arrays_1.13.0
#> [28] tools_4.6.1 dplyr_1.2.1 assertthat_0.2.1
#> [31] buildtools_1.0.0 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 sys_3.4.3
#> [46] MatrixGenerics_1.25.0 knitr_1.51 htmltools_0.5.9
#> [49] rmarkdown_2.31 maketools_1.3.2 compiler_4.6.1