AnnData DuckDB Extension

This extension provides DuckDB with the ability to read AnnData (.h5ad) files, which are the standard format for single-cell genomics data analysis.

Quick Start

Install from the Community Extension Repository

-- Install the first time you use it
INSTALL anndata; 

-- Load the extension before using 
LOAD anndata;

Install from the Custom Extension Repository (not usually needed)

-- Allow unsigned extensions (required for non-community extensions)
SET allow_unsigned_extensions = true;

-- Install from the custom repository (first time or updating)
FORCE INSTALL anndata FROM 'https://software-releasers.s3.us-west-2.amazonaws.com';

-- Load the extension before using
LOAD anndata;

Use it!

The ATTACH syntax provides a the most intuitive way to work with AnnData files, similar to how DuckDB handles SQLite databases:

-- Attach an AnnData file
ATTACH 'data.h5ad' AS scdata (TYPE ANNDATA);

-- Specify which var columns to use for gene names and IDs
ATTACH 'data.h5ad' AS scdata (
    TYPE ANNDATA,
    VAR_NAME_COLUMN 'gene_symbols',
    VAR_ID_COLUMN 'ensembl_id'
);

-- Query using schema-qualified table names
SELECT * FROM scdata.obs WHERE cell_type = 'T cell';
SELECT * FROM scdata.var WHERE highly_variable = true;
SELECT * FROM scdata.X LIMIT 100;

-- Access dimensional reductions
SELECT * FROM scdata.obsm_X_pca;
SELECT * FROM scdata.obsm_X_umap;

-- Access layers
SELECT * FROM scdata.layers_raw;

-- Detach when done
DETACH scdata;

Remote Files (HTTP/HTTPS and S3)

The extension supports reading AnnData files from remote locations via HTTP/HTTPS and S3. This requires the httpfs extension.

HTTPS Files

-- Load the httpfs extension for remote access
INSTALL httpfs;
LOAD httpfs;

-- Attach an AnnData file from HTTPS URL
ATTACH 'https://example.com/data/pbmc3k.h5ad' AS remote (TYPE ANNDATA);

-- Query as usual
SELECT * FROM remote.obs LIMIT 10;

S3 Files (Anonymous/Public Buckets)

-- Load the httpfs extension
INSTALL httpfs;
LOAD httpfs;

-- For public buckets, just use the s3:// URL directly
ATTACH 's3://my-public-bucket/data.h5ad' AS s3data (TYPE ANNDATA);

SELECT COUNT(*) FROM s3data.obs;

S3 Files (Authenticated Access)

The easiest way to authenticate is using your existing AWS profile or environment variables:

-- Load the httpfs extension
INSTALL httpfs;
LOAD httpfs;

-- Use your default AWS profile (reads from ~/.aws/credentials)
CREATE SECRET my_s3_secret (
    TYPE S3,
    PROVIDER CREDENTIAL_CHAIN
);

-- Or use a specific named profile
CREATE SECRET my_s3_secret (
    TYPE S3,
    PROVIDER CREDENTIAL_CHAIN,
    CHAIN 'config',
    PROFILE 'my-profile',
    REGION 'us-west-2'
);

-- Attach and query
ATTACH 's3://my-private-bucket/data.h5ad' AS s3data (TYPE ANNDATA);
SELECT * FROM s3data.obs LIMIT 10;

You can also pass credentials explicitly if needed:

CREATE SECRET my_s3_secret (
    TYPE S3,
    KEY_ID 'your-access-key-id',
    SECRET 'your-secret-access-key',
    REGION 'us-west-2'
);

For custom S3-compatible endpoints (e.g., MinIO):

CREATE SECRET minio_secret (
    TYPE S3,
    KEY_ID 'your-access-key',
    SECRET 'your-secret-key',
    ENDPOINT 'localhost:9000',
    URL_STYLE 'path',
    USE_SSL false
);

ATTACH 's3://bucket/data.h5ad' AS miniodata (TYPE ANNDATA);

You can also use the function syntax:

-- Load the extension
LOAD anndata;

-- Get file information
SELECT * FROM anndata_info('data.h5ad');

-- Query observation (cell) metadata
SELECT * FROM anndata_scan_obs('data.h5ad');

-- Query variable (gene) metadata
SELECT * FROM anndata_scan_var('data.h5ad');

-- Query expression matrix
SELECT * FROM anndata_scan_x('data.h5ad');

Multi-File Wildcard Queries

Query multiple AnnData files at once using glob patterns. Works with both local and remote (S3) files.

-- Query all .h5ad files matching a pattern (intersection mode - default)
-- Only columns/genes common to ALL files are returned
SELECT * FROM anndata_scan_obs('samples/*.h5ad');

-- Union mode - all columns from all files, NULL where missing
SELECT * FROM anndata_scan_obs('samples/*.h5ad', schema_mode := 'union');

-- A _file_name column is automatically added to identify source files
SELECT _file_name, cell_type, COUNT(*)
FROM anndata_scan_obs('samples/*.h5ad')
GROUP BY _file_name, cell_type;

-- Works with all scan functions
SELECT * FROM anndata_scan_x('samples/*.h5ad');
SELECT * FROM anndata_scan_x('samples/*.h5ad', schema_mode := 'union');
SELECT * FROM anndata_scan_layers('samples/*.h5ad', 'raw');
SELECT * FROM anndata_scan_obsm('samples/*.h5ad', 'X_pca');
SELECT * FROM anndata_scan_obsp('samples/*.h5ad', 'distances');

-- Works with S3 paths
SELECT * FROM anndata_scan_obs('s3://bucket/project/*.h5ad');

Schema modes:

• intersection (default): Only columns/genes present in ALL files are included. Safest for analysis across heterogeneous datasets.
• union: All columns/genes from all files are included. Missing values are filled with NULL.

Features

• Read-only access to AnnData HDF5 files
• Remote file support for HTTP/HTTPS and S3 (including authenticated S3)
• Multi-file wildcard queries with glob patterns (*.h5ad) and schema harmonization
• Attach to an AnnData file like a database
• Query observation (cell) metadata from .obs
• Query variable (gene) metadata from .var
• Query expression matrix (.X) in wide format with genes as columns
• Query dimensional reductions from .obsm and .varm (PCA, UMAP, etc.)
• Query pairwise matrices from .obsp and .varp
• Query alternative expression matrices from .layers
• Query unstructured metadata from .uns
• Configurable gene name columns for expression matrix
• Auto-detection of gene name columns with manual override options
• Efficient HDF5 data reading with proper memory management
• Thread-safe operation on all platforms

Limitations

• Read only
• Windows HDF5 library limitations mean that we don't support threading on Windows. This limits the throughput of more complicated queries on Windows.

Usage

Table Functions

Core Data

-- Observation (cell) metadata
SELECT * FROM anndata_scan_obs('data.h5ad');

-- Variable (gene) metadata
SELECT * FROM anndata_scan_var('data.h5ad');

-- Expression matrix in wide format (rows=cells, columns=genes)
SELECT * FROM anndata_scan_x('data.h5ad');

-- Use custom gene name column (default is '_index')
SELECT * FROM anndata_scan_x('data.h5ad', 'gene_symbols');

Dimensional Reductions (obsm/varm)

-- List available matrices
SELECT * FROM anndata_info('data.h5ad');

-- Query PCA coordinates
SELECT * FROM anndata_scan_obsm('data.h5ad', 'X_pca');

-- Query UMAP coordinates
SELECT * FROM anndata_scan_obsm('data.h5ad', 'X_umap');

-- Query variable embeddings
SELECT * FROM anndata_scan_varm('data.h5ad', 'PCs');

Pairwise Matrices (obsp/varp)

-- Query cell-cell distances/connectivities (sparse format)
SELECT * FROM anndata_scan_obsp('data.h5ad', 'distances');
SELECT * FROM anndata_scan_obsp('data.h5ad', 'connectivities');

-- Query gene-gene relationships
SELECT * FROM anndata_scan_varp('data.h5ad', 'correlations');

Layers

-- Query alternative expression matrices
SELECT * FROM anndata_scan_layers('data.h5ad', 'raw');
SELECT * FROM anndata_scan_layers('data.h5ad', 'normalized');

Unstructured Data (uns)

-- Query unstructured metadata
SELECT * FROM anndata_scan_uns('data.h5ad');

-- Filter to view only scalar values
SELECT key, dtype, value
FROM anndata_scan_uns('data.h5ad')
WHERE type = 'scalar';

Gene Name Column Configuration

When attaching an AnnData file, the extension automatically detects which var columns contain gene names and IDs using heuristics. You can override this with explicit options:

-- Auto-detection (default behavior)
-- Prints: "Note: Using var_name='gene_symbols', var_id='ensembl_id'. Override with VAR_NAME_COLUMN/VAR_ID_COLUMN options."
ATTACH 'data.h5ad' AS scdata (TYPE ANNDATA);

-- Explicit specification
ATTACH 'data.h5ad' AS scdata (
    TYPE ANNDATA,
    VAR_NAME_COLUMN 'gene_symbols',  -- Column for gene names (used as X matrix column names)
    VAR_ID_COLUMN 'ensembl_id'       -- Column for gene IDs
);

-- Override just one column (other is auto-detected)
ATTACH 'data.h5ad' AS scdata (TYPE ANNDATA, VAR_NAME_COLUMN 'gene_symbols');

Auto-detection priority:

• For gene names: gene_symbols, gene_symbol, gene_names, gene_name, symbol, symbols, feature_name, name, names
• For gene IDs: gene_ids, gene_id, ensembl_id, ensembl, feature_id, id, ids
• Falls back to _index if no suitable column is found

Common Patterns

-- Join expression data with cell metadata
SELECT o.cell_type, AVG(x.Gene_000) as avg_expression
FROM anndata_scan_x('data.h5ad') x
JOIN anndata_scan_obs('data.h5ad') o
  ON x.obs_idx = o.obs_idx
GROUP BY o.cell_type;

-- Combine data from multiple files using wildcard patterns
SELECT _file_name, * FROM anndata_scan_obs('sample*.h5ad');

-- Export to Parquet
COPY (SELECT * FROM anndata_scan_obs('data.h5ad'))
TO 'obs_metadata.parquet';

-- Combine with other data sourcesL
select count(*)
from scdata.var
where scdata.var.gene_ids in (
  select human_EnsemblID
  from read_csv('https://raw.githubusercontent.com/AllenInstitute/GeneOrthology/refs/heads/main/csv/mouse_human_marmoset_macaque_orthologs_20231113.csv')
);

Development

This extension uses the DuckDB extension template and VCPKG for dependency management.

Prerequisites

For macOS:

# Required for timeout command in tests
brew install coreutils

• CMake 3.5+
• C++11 compatible compiler
• Git
• VCPKG (will be installed automatically if not present)
• ninja and ccache (optional, for faster builds)

Quick Setup

Run the setup script to install dependencies:

./setup-dev.sh

This will:

• Install VCPKG if not present
• Configure VCPKG environment variables
• Install HDF5 via VCPKG
• Set up Python environment (if uv is installed)

Build Commands

# Standard build (recommended: use uv for reproducible Python environment)
uv run make

# Build with ninja (faster)
GEN=ninja uv run make

# Debug build
uv run make debug

# Clean build
make clean

The built extension will be located at: build/release/extension/anndata/anndata.duckdb_extension

Code Quality

Before committing code, always run format and tidy checks:

# Check and fix code formatting (uses DuckDB's clang-format rules)
uv run make format

# Run clang-tidy static analysis
uv run make tidy-check

The CI/CD pipeline will fail if format or tidy checks don't pass.

Testing

# Run all SQL tests
./build/release/test/unittest --test-dir test "[sql]"

# Run specific test file
./build/release/test/unittest --test-dir test "*layers*"

# Run DuckDB with the extension loaded (for manual testing)
./build/release/duckdb -unsigned

Version Management

Use the version bump script to update the version number:

# Bump patch version (e.g., 0.1.0 -> 0.1.1)
uv run python scripts/bump_version.py patch

# Bump minor version (e.g., 0.1.0 -> 0.2.0)
uv run python scripts/bump_version.py minor

# Bump major version (e.g., 0.1.0 -> 1.0.0)
uv run python scripts/bump_version.py major

# Set a specific version
uv run python scripts/bump_version.py set 0.3.0

The script updates the VERSION file and adds a new section to CHANGELOG.md. Note: You must manually edit CHANGELOG.md to fill in the Added/Changed/Fixed sections with your actual changes. See docs/UPDATING.md for the complete version bump procedure.

Project Structure

├── src/
│   ├── anndata_extension.cpp        # Extension entry point
│   ├── anndata_scanner.cpp          # Table function implementations
│   ├── h5_reader_multithreaded.cpp  # Thread-safe HDF5 data reading
│   ├── glob_handler.cpp             # Wildcard pattern expansion (local & S3)
│   ├── schema_harmonizer.cpp        # Multi-file schema union/intersection
│   └── include/
│       ├── h5_reader_multithreaded.hpp  # HDF5 reader interface
│       ├── h5_file_cache.hpp            # File handle caching & thread safety
│       ├── glob_handler.hpp             # Glob pattern handling
│       └── schema_harmonizer.hpp        # Schema harmonization
├── spec/                        # Design specifications
├── test/sql/                    # SQL-based tests
├── vcpkg.json                   # VCPKG manifest (HDF5 dependency)
├── CMakeLists.txt              # Build configuration
└── extension_config.cmake      # DuckDB extension config

License

MIT