DuckDBDataFrame 0.99.20
Bioconductor objects increasingly carry large tabular metadata: a
SingleCellExperiment with millions of cells has a colData of the same height, a
big sample sheet, per-feature annotations. Holding such a table fully in memory is
wasteful when a workflow only ever reads a few columns or a filtered subset of rows.
DuckDBDataFrame is a S4Vectors DataFrame backed by
DuckDB over columnar Parquet. It behaves like an ordinary
DataFrame, $, [, mcols(), cbind(), but the data stays on disk and
operations are recorded as lazy SQL queries: you can subset, add computed columns,
and aggregate without loading the table into memory. Each column read comes back
only when you ask for it.
The typical pattern is that an upstream tool (or an earlier pipeline step) writes a
table, often larger than memory, to Parquet, and DuckDBDataFrame opens it in
place: you explore and query it with the familiar DataFrame API while DuckDB reads
only the columns and rows each operation touches.
It is the tabular foundation of the BiocDuckDB suite: DuckDBArray
(DuckDB-backed DelayedArray) and DuckDBGRanges (DuckDB-backed
GRanges) both build on it.
This vignette is a practical introduction. For the design of the underlying
DuckDBTable and how other packages extend it, see
Design and extension of DuckDBDataFrame.
If you have used arrow, a DuckDBDataFrame is lazy over Parquet in
much the same way as an Arrow FileSystemDataset: the data stays on disk and
operations are deferred. The differences are in the interface and the engine:
collect(). An Arrow dataset requires collect() (or
as.data.frame()) to realize results before most R operations. A
DuckDBDataFrame presents the full DataFrame API directly, head(), [,
$, arithmetic, mean(), aggregation all work on the lazy object, and only the
values you actually extract (e.g. as.vector(), as.data.frame()) are pulled
into memory. Each operation is pushed down to DuckDB and evaluated on demand.DataFrame (so it drops into SingleCellExperiment, SummarizedExperiment,
etc.) rather than a dplyr/Arrow pipeline. The tradeoff is a dependency on DuckDB
and that results are materialized through it."data/*.parquet") and DuckDB scans the parts as one table.if (!require("BiocManager"))
install.packages("BiocManager")
BiocManager::install("DuckDBDataFrame")library(DuckDBDataFrame)We write the built-in mtcars table to Parquet and open it as a
DuckDBDataFrame.
library(arrow)
mtcars_df <- cbind(model = rownames(mtcars), mtcars)
path <- tempfile(fileext = ".parquet")
write_parquet(mtcars_df, path)
df <- DuckDBDataFrame(path, datacols = colnames(mtcars),
keycol = list(model = mtcars_df$model))
df
#> DuckDBDataFrame with 32 rows and 11 columns
#> mpg cyl disp hp drat wt
#> <double> <double> <double> <double> <double> <double>
#> Mazda RX4 21.0 6 160 110 3.90 2.620
#> Mazda RX4 Wag 21.0 6 160 110 3.90 2.875
#> Datsun 710 22.8 4 108 93 3.85 2.320
#> Hornet 4 Drive 21.4 6 258 110 3.08 3.215
#> Hornet Sportabout 18.7 8 360 175 3.15 3.440
#> ... ... ... ... ... ... ...
#> Lotus Europa 30.4 4 95.1 113 3.77 1.513
#> Ford Pantera L 15.8 8 351.0 264 4.22 3.170
#> Ferrari Dino 19.7 6 145.0 175 3.62 2.770
#> Maserati Bora 15.0 8 301.0 335 3.54 3.570
#> Volvo 142E 21.4 4 121.0 109 4.11 2.780
#> qsec vs am gear carb
#> <double> <double> <double> <double> <double>
#> Mazda RX4 16.46 0 1 4 4
#> Mazda RX4 Wag 17.02 0 1 4 4
#> Datsun 710 18.61 1 1 4 1
#> Hornet 4 Drive 19.44 1 0 3 1
#> Hornet Sportabout 17.02 0 0 3 2
#> ... ... ... ... ... ...
#> Lotus Europa 16.9 1 1 5 2
#> Ford Pantera L 14.5 0 1 5 4
#> Ferrari Dino 15.5 0 1 5 6
#> Maserati Bora 14.6 0 1 5 8
#> Volvo 142E 18.6 1 1 4 2Only the path is required, DuckDBDataFrame(path) opens every column and uses
generated row numbers. The other arguments are optional refinements:
datacols selects (and orders) which columns to expose; omit it to take all.keycol designates the row key, the DuckDBDataFrame equivalent of a
DataFrame’s row names. It takes two forms: a column name already in the file
(keycol = "model"), which promotes that column to the key; or a named list of
values (keycol = list(model = mtcars_df$model)), which supplies the key from R,
handy when the key is not stored as a column. Both give a DataFrame keyed by
model; they differ only in where the key comes from (and therefore its row
order). With no keycol, rows are addressed by generated numbers instead of names.
The deeper key-dimension model is covered in Design and extension of
DuckDBDataFrame.It looks and behaves like a DataFrame, but the values live in the Parquet file:
dim(df)
#> [1] 32 11
df$mpg[1:5]
#> DuckDBColumn of length 5
#> Mazda RX4 Mazda RX4 Wag Datsun 710 Hornet 4 Drive
#> 21.0 21.0 22.8 21.4
#> Hornet Sportabout
#> 18.7$ returns a single column as a DuckDBColumn (still lazy); [ selects columns:
df[, c("mpg", "cyl", "hp")]
#> DuckDBDataFrame with 32 rows and 3 columns
#> mpg cyl hp
#> <double> <double> <double>
#> Mazda RX4 21.0 6 110
#> Mazda RX4 Wag 21.0 6 110
#> Datsun 710 22.8 4 93
#> Hornet 4 Drive 21.4 6 110
#> Hornet Sportabout 18.7 8 175
#> ... ... ... ...
#> Lotus Europa 30.4 4 113
#> Ford Pantera L 15.8 8 264
#> Ferrari Dino 19.7 6 175
#> Maserati Bora 15.0 8 335
#> Volvo 142E 21.4 4 109Rows can be selected by name, by a logical column, or by position (note that positional order is not guaranteed, as rows map to a set on disk):
df[df$mpg > 25, c("mpg", "cyl")]
#> DuckDBDataFrame with 6 rows and 2 columns
#> mpg cyl
#> <double> <double>
#> Fiat 128 32.4 4
#> Porsche 914-2 26.0 4
#> Honda Civic 30.4 4
#> Toyota Corolla 33.9 4
#> Fiat X1-9 27.3 4
#> Lotus Europa 30.4 4Assigning an expression of existing columns records a new lazy column; nothing is evaluated until the values are pulled:
df$efficiency <- df$mpg / df$hp
df[1:3, c("mpg", "hp", "efficiency")]
#> DuckDBDataFrame with 3 rows and 3 columns
#> mpg hp efficiency
#> <double> <double> <double>
#> Mazda RX4 21.0 110 0.190909
#> Mazda RX4 Wag 21.0 110 0.190909
#> Datsun 710 22.8 93 0.245161This changes only the in-memory DuckDBDataFrame object: efficiency is stored as
a SQL expression (mpg / hp) in the object’s query and computed on demand. The
Parquet file on disk is not modified, the new column exists only for this
object and any results derived from it. To persist a derived table (including
computed columns) back to disk, materialize it (as.data.frame()) and write it out
explicitly, e.g. with arrow::write_parquet().
mcols() works as it does for any DataFrame:
mcols(df) <- DataFrame(row.names = colnames(df),
label = sub("\\..*", "", colnames(df)))
mcols(df)[1:3, , drop = FALSE]
#> DataFrame with 3 rows and 1 column
#> label
#> <character>
#> mpg mpg
#> cyl cyl
#> disp dispDepending on the underlying Parquet type, extracting a column yields:
DuckDBColumn for atomic columns, vector-like and lazy
(length(), [, arithmetic, mean()), materialized with as.vector();DuckDBAtomicList for DuckDB LIST[] columns (variable-length list
columns), supporting elementNROWS(), [[, unlist();DuckDBEmbeddings for DuckDB ARRAY[n] columns (fixed-length numeric
vectors, e.g. embeddings), which behaves like a matrix with one row per element.eff <- df$mpg / df$hp # DuckDBColumn
class(eff)
#> [1] "DuckDBColumn"
#> attr(,"package")
#> [1] "DuckDBDataFrame"
as.vector(eff)[1:5] # materialize on demand
#> Mazda RX4 Mazda RX4 Wag Datsun 710 Hornet 4 Drive
#> 0.1909091 0.1909091 0.2451613 0.1945455
#> Hornet Sportabout
#> 0.1068571Because the backend is DuckDB, its full SQL function library is available.
sql_fun() lists functions applicable to a column, and sql_call() applies one:
sql_call(df$mpg, "round", 0)[1:5]
#> DuckDBColumn of length 5
#> Mazda RX4 Mazda RX4 Wag Datsun 710 Hornet 4 Drive
#> 21 21 23 21
#> Hornet Sportabout
#> 19For custom work you can reach the shared connection with dbconn(df) and run
arbitrary DBI::dbGetQuery() calls against it.
A good fit when the table is larger than memory (or you want to keep memory
free), when the workload is columnar (filtering, aggregation, selecting a few
columns of a wide table), or when the data already lives on disk as Parquet that
other tools should read. An in-memory DataFrame remains preferable for small tables
and for row-wise or heavy random-access work.
For how the DuckDBTable abstraction works and how to build on it, see
Design and extension of DuckDBDataFrame.
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] arrow_25.0.0 DuckDBDataFrame_0.99.20 IRanges_2.47.2
#> [4] S4Vectors_0.51.6 BiocGenerics_0.59.11 generics_0.1.4
#> [7] bit64_4.8.2 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] pkgconfig_2.0.3 bslib_0.12.0 pillar_1.11.1
#> [40] glue_1.8.1 xfun_0.60 tibble_3.3.1
#> [43] tidyselect_1.2.1 MatrixGenerics_1.25.0 knitr_1.51
#> [46] htmltools_0.5.9 rmarkdown_2.31 compiler_4.6.1