Search Shortcut cmd + k | ctrl + k
plinking_duck
Downloads 5.4kthis week
GitHub stars 6
Extension descriptor (YAML)

Read and write PLINK 2 genomics file formats and run common genetic analyses directly in SQL

Maintainer(s): teaguesterling

Compatibility Matrix

This table covers all DuckDB releases that have not reached end-of-life, including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.

Platform 1.4.4 1.4.5 1.5.5 1.5.6 2.0.0
Linux AMD64
Linux ARM64
macOS AMD64
macOS ARM64
Windows AMD64
Windows ARM64
WebAssembly

Installing and Loading 

INSTALL plinking_duck FROM community;
LOAD plinking_duck;

Example 

-- Read variant metadata from a .pvar file
SELECT CHROM, POS, ID, REF, ALT
FROM read_pvar('cohort.pvar')
WHERE CHROM = '22';

-- Read genotypes in tidy format (one row per variant x sample)
SELECT chrom, pos, iid, genotype
FROM read_pfile('cohort', orient := 'genotype')
LIMIT 10;

-- Compute allele frequencies
SELECT * FROM plink_freq('cohort.pgen')
WHERE ALT_FREQ > 0.01;

-- Run a GWAS association test
SELECT * FROM plink_glm('cohort')
WHERE p_value < 5e-8;

About plinking_duck

PlinkingDuck brings PLINK 2 genotype, variant, and sample data into DuckDB, letting you query genomics datasets with standard SQL instead of format-specific command-line tools.

File readers:

  • read_pvar(path | [paths]) — variant metadata (.pvar/.bim); a list row-concatenates multiple files
  • read_psam(path) — sample metadata (.psam/.fam)
  • read_pgen(path) — binary genotypes (.pgen)
  • read_pfile(prefix | [prefixes]) — unified fileset reader with orient modes (variant/genotype/sample), sample subsetting, region and variant filtering; accepts a list of prefixes to read a variant-sharded fileset (identical samples) as one table
  • read_plink_vcf(path) — fast biallelic genotype extraction from VCF/VCF.gz (~3x faster than htslib)

Writing & catalogs:

  • COPY (…) TO 'out' (FORMAT pvar | psam | pgen | pfile) — write PLINK 2 filesets from SQL, including a shard_by '<col>' mode that splits a cohort into one fileset per key (e.g. per chromosome)
  • .howard catalog — a thin JSON catalog over a set of filesets (a shared .pvar/.psam plus per-file partition and genomic-range annotations); the sharded writer emits one as its manifest
  • read_howard_manifest(path) — parse, verify, and region-prune a .howard catalog, returning one row per surviving shard (with resolved companion paths, text or parquet)

Analysis functions:

  • plink_freq — per-variant allele frequencies via fast genotype counting
  • plink_hardy — Hardy-Weinberg equilibrium exact test
  • plink_missing — per-variant or per-sample missingness rates
  • plink_ld — pairwise linkage disequilibrium (r², D, D')
  • plink_score — polygenic risk scoring with mean imputation
  • plink_glm — per-variant GWAS regression (linear, logistic, Firth)
  • plink_pca — principal component analysis via randomized SVD

Genotype output modes:

  • genotypes='struct' — STRUCT with named fields per sample
  • genotypes='counts' — fast genotype counting (no decompression)
  • genotypes='stats' — counts + AF, MAF, missingness, heterozygosity

Flexible inputs:

  • Unified variants parameter: indices, rsids, CPRA strings/structs, ranges
  • Parquet/CSV/table companions for variant and sample metadata
  • af_range / ac_range filter pushdown, and include_genotypes (hardcall category) / genotype_range for fast carrier lookups — in orient := 'sample' only the matching subjects are materialized

Remote / cloud reads:

  • Read .pgen filesets directly from s3://, https://, and other DuckDB filesystems (companions too) — targeted/region queries fetch only the bytes they need via HTTP range reads. plinking_pgen_io := 'localize' downloads once for remote full scans. Split-index (.pgen.pgi) filesets are read transparently.

All functions support projection pushdown (skip genotype decompression for metadata-only queries), parallel scanning, sample subsetting, and region filtering. Legacy PLINK 1 formats (.bim/.fam) are auto-detected.

Built on pgenlib for efficient access to the compressed .pgen binary format without full decompression.

For full documentation, see plinking-duck.readthedocs.io.

Added Functions

function_name function_type description comment examples
pfile_partitions table Compute the ordinal-range partitions of a pfile along the variants dimension (half-open [lo, hi)). NULL [SELECT * FROM pfile_partitions('data/cohort')]
plink_freq table Compute allele frequencies and counts from PLINK files. NULL [SELECT * FROM plink_freq('data/cohort.pgen')]
plink_glm table Fit generalized linear models (linear/logistic regression) across variants. NULL [SELECT * FROM plink_glm('data/cohort.pgen', phenotype := 'trait')]
plink_hardy table Compute Hardy-Weinberg equilibrium exact test statistics. NULL [SELECT * FROM plink_hardy('data/cohort.pgen')]
plink_ld table Compute pairwise linkage disequilibrium metrics. NULL [SELECT * FROM plink_ld('data/cohort.pgen', window_kb := 500, r2_threshold := 0.2)]
plink_missing table Compute missingness rates per variant or per sample. NULL [SELECT * FROM plink_missing('data/cohort.pgen', mode := 'variant')]
plink_pca table Compute principal components analysis from PLINK files. NULL [SELECT * FROM plink_pca('data/cohort.pgen', n_pcs := 10)]
plink_score table Compute polygenic risk scores from variant weights. NULL [SELECT * FROM plink_score('data/cohort.pgen', weights := 'weights.tsv')]
plinking_duck_capabilities scalar List the capability tokens this plinking_duck build supports (for SQL feature detection). NULL [SELECT list_contains(plinking_duck_capabilities(), 'variant_range')]
read_pfile table Read PLINK dataset files into tabular format. NULL [SELECT * FROM read_pfile('data/cohort.pgen')]
read_pfile_catalog table Parse, verify, and prune a .pfile.json shard catalog, returning one row per surviving shard. The parse+verify+prune surface plinking_duck owns for pfile_catalog reading (write side = the sharded pfile writer). NULL [SELECT * FROM read_pfile_catalog('cohort.pfile.json'), SELECT prefix FROM read_pfile_catalog('cohort.pfile.json', region := '2:1-5000000')]
read_pgen table Read PLINK .pgen or .bed genotype files into tabular format. NULL [SELECT * FROM read_pgen('data/cohort.pgen')]
read_plink_vcf table Read VCF files using PLINK engine into tabular format. NULL [SELECT * FROM read_plink_vcf('data/cohort.vcf.gz')]
read_psam table Read PLINK .psam or .fam sample files into tabular format. NULL [SELECT * FROM read_psam('data/cohort.psam')]
read_pvar table Read PLINK .pvar or .bim variant files into tabular format. NULL [SELECT * FROM read_pvar('data/cohort.pvar')]

Overloaded Functions

This extension does not add any function overloads.

Added Types

This extension does not add any types.

Added Settings

name description input_type scope aliases typed_value
plinking_localize_dir Directory (local) for temp copies made by plinking_pgen_io := 'localize'. Empty (default) uses DuckDB's temporary_directory, else the current directory. Created if absent (one level only — parent dirs must already exist). Temps are per-query and removed when the query's bind data is destroyed. VARCHAR GLOBAL []  
plinking_max_matrix_elements Maximum genotype matrix elements for orient := 'sample' pre-read (variants x samples). Default 16 billion (~16 GB of int8). BIGINT GLOBAL [] 17179869184
plinking_max_threads Maximum threads for parallel scan operations. 0 = default (hardcoded cap of 16), >0 = cap at this value. BIGINT GLOBAL [] 0
plinking_pgen_io How .pgen bytes are read: 'auto' (default — remote/VFS paths via DuckDB's VFS, local via native fopen), 'native' (always native fopen; errors on remote), 'vfs' (always via DuckDB's VFS, even local), 'localize' (materialize a local temp copy then read natively — best for remote full scans; always copies, even a local source). VARCHAR GLOBAL [] auto
plinking_sample_counts_sparse orient := 'sample' + genotypes := 'counts'|'stats': when true, use the sparse (pgen difflist) accumulation path — reads only the non-hom_ref carriers of rare variants (auto-falls-back to the dense full-decode path per variant). When false, always use the dense path. Toggle to A/B time both; both paths produce identical counts. BOOLEAN GLOBAL [] false
plinking_use_parquet_companions Auto-discover .pvar.parquet and .psam.parquet companion files. When true, parquet companions are preferred over text formats. BOOLEAN GLOBAL [] true
temp_directory Set the directory to which temporary files are written. VARCHAR GLOBAL [] post.db.tmp