---
title: "Introduction to 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{1. Introduction to DuckDBDataFrame}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
output:
  BiocStyle::html_document:
    number_sections: true
    toc: true
    toc_depth: 2
---

```{r setup, include=FALSE}
knitr::opts_chunk$set(collapse = TRUE, comment = "#>",
                      error = FALSE, warning = FALSE, message = FALSE)
```

# Introduction

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.

`r Biocpkg("DuckDBDataFrame")` is a `r Biocpkg("S4Vectors")` `DataFrame` backed by
[DuckDB](https://duckdb.org) 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: `r Biocpkg("DuckDBArray")`
(DuckDB-backed `DelayedArray`) and `r Biocpkg("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*.

## Relationship to arrow

If you have used `r CRANpkg("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:

- **No explicit `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.
- **DuckDB engine, DataFrame API.** Computation runs in DuckDB's SQL engine rather
  than the Arrow compute kernels, and the object behaves like a Bioconductor
  `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.
- **Multi-file datasets** are supported: point the constructor at a directory or a
  glob (e.g. `"data/*.parquet"`) and DuckDB scans the parts as one table.

## Installation

```{r install, eval=FALSE}
if (!require("BiocManager"))
    install.packages("BiocManager")
BiocManager::install("DuckDBDataFrame")
```

```{r load}
library(DuckDBDataFrame)
```

# Quick start

We write the built-in `mtcars` table to Parquet and open it as a
`DuckDBDataFrame`.

```{r quickstart}
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
```

Only 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:

```{r quickstart-ops}
dim(df)
df$mpg[1:5]
```

# Working with a DuckDBDataFrame

## Column access

`$` returns a single column as a `DuckDBColumn` (still lazy); `[` selects columns:

```{r columns}
df[, c("mpg", "cyl", "hp")]
```

## Row subsetting

Rows 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):

```{r rows}
df[df$mpg > 25, c("mpg", "cyl")]
```

## Computed columns

Assigning an expression of existing columns records a new lazy column; nothing is
evaluated until the values are pulled:

```{r computed}
df$efficiency <- df$mpg / df$hp
df[1:3, c("mpg", "hp", "efficiency")]
```

This 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()`.

## Column metadata

`mcols()` works as it does for any `DataFrame`:

```{r metadata}
mcols(df) <- DataFrame(row.names = colnames(df),
                       label = sub("\\..*", "", colnames(df)))
mcols(df)[1:3, , drop = FALSE]
```

# Columns come in three flavors

Depending on the underlying Parquet type, extracting a column yields:

- a **`DuckDBColumn`** for atomic columns, vector-like and lazy
  (`length()`, `[`, arithmetic, `mean()`), materialized with `as.vector()`;
- a **`DuckDBAtomicList`** for DuckDB `LIST[]` columns (variable-length list
  columns), supporting `elementNROWS()`, `[[`, `unlist()`;
- a **`DuckDBEmbeddings`** for DuckDB `ARRAY[n]` columns (fixed-length numeric
  vectors, e.g. embeddings), which behaves like a matrix with one row per element.

```{r column-flavors}
eff <- df$mpg / df$hp     # DuckDBColumn
class(eff)
as.vector(eff)[1:5]       # materialize on demand
```

# Reaching for SQL directly

Because the backend is DuckDB, its full SQL function library is available.
`sql_fun()` lists functions applicable to a column, and `sql_call()` applies one:

```{r sql}
sql_call(df$mpg, "round", 0)[1:5]
```

For custom work you can reach the shared connection with `dbconn(df)` and run
arbitrary `DBI::dbGetQuery()` calls against it.

# When to use DuckDBDataFrame

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*.

# Session information

```{r sessioninfo}
sessionInfo()
```