--- title: "Design and extension of DuckDBDataFrame" author: - name: Patrick Aboyoun email: aboyounp@gene.com affiliation: Genentech, Inc. date: "Compiled: `r BiocStyle::doc_date()`; Modified: 6 July 2026" package: DuckDBDataFrame vignette: > %\VignetteIndexEntry{2. Design and extension of DuckDBDataFrame} %\VignetteEngine{knitr::rmarkdown} %\VignetteEncoding{UTF-8} output: BiocStyle::html_document: number_sections: true toc: true toc_depth: 3 --- ```{r setup, include=FALSE} knitr::opts_chunk$set(collapse = TRUE, comment = "#>", error = FALSE, warning = FALSE, message = FALSE) ``` # Scope This vignette is for developers: it documents the `DuckDBTable` abstraction that underlies `r Biocpkg("DuckDBDataFrame")`, how R operations become SQL, and how other packages build on it. For day-to-day use see *Introduction to DuckDBDataFrame*. ```{r load} library(DuckDBDataFrame) ``` # The classes `r Biocpkg("DuckDBDataFrame")` extends the `r Biocpkg("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 `r Biocpkg("DuckDBArray")` wraps for arrays. # The DuckDBTable abstraction 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. ## Construction `DuckDBTable()` accepts a Parquet or CSV path, or an existing dplyr connection: ```{r construct} 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) ``` Only `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: - a **column name** in the source, e.g. `keycol = "model"`, which *promotes* that stored column to be the key; or - a **named list of values**, e.g. `keycol = 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. ```{r construct-keycol} 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 isTRUE(all.equal(d_str, d_list)) # but not all.equal (row order) ``` ## The contract 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. ## Key-dimension semantics 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: ```{r rownum} tbl0 <- DuckDBTable(path, datacols = colnames(mtcars)) has_row_number(tbl0) # unkeyed: rows addressed by generated number dfk <- DuckDBDataFrame(path, datacols = colnames(mtcars), keycol = list(model = mtcars_df$model)) has_row_number(dfk) # keyed DataFrame: row names, no row number ``` A `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): ```{r table-vs-df} tbl1 <- DuckDBTable(path, datacols = colnames(mtcars)) df1 <- DuckDBDataFrame(path, datacols = colnames(mtcars)) identical(tbl1, df1) isTRUE(all.equal(tbl1, df1)) ``` # From R to SQL 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 `r Biocpkg("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. # Connection management `r Biocpkg("DuckDBDataFrame")` keeps a single shared DuckDB connection per session, acquired lazily: ```{r conn} conn <- acquireDuckDBConn() identical(dbconn(tbl), conn) ``` 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 `r CRANpkg("DBI")`. # Dimension tables 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: ```{r dimtbls} 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)) ``` # Extending DuckDBDataFrame 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: ```{r extend} 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 dim(adf) # inherited, still lazy adf@annotation # the added slot ``` The suite's own packages extend the foundation at the `DuckDBTable` level: - `r Biocpkg("DuckDBArray")` wraps a `DuckDBTable` with `nkey >= 2` as a `DelayedArray` backend (see that package's *Implementing the DuckDBArray backend* vignette). - `r Biocpkg("DuckDBGRanges")` uses a `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. # Session information ```{r sessioninfo} sessionInfo() ```