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 and push filters and arithmetic into
DuckDB’s columnar engine (predicate pushdown, direct Parquet scans).
DuckDBTable() accepts a Parquet or CSV path, or an
existing dplyr connection:
Classes 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:
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.
For partitioned data, dimension tables map index values to partition groups so a query can prune to the relevant Parquet partitions:
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"Other suite packages build directly on this foundation:
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.6 IRanges_2.47.2 S4Vectors_0.51.5
#> [4] BiocGenerics_0.59.10 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 blob_1.3.0 fastmap_1.2.0
#> [10] jsonlite_2.0.0 Matrix_1.7-5 DBI_1.3.0
#> [13] BiocManager_1.30.27 purrr_1.2.2 jquerylib_0.1.4
#> [16] duckdb_1.5.4.3 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.3
#> [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.11.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