Query sequencing files from R with SQL. Rduckhts bundles DuckHTS, a DuckDB extension built on htslib. It reads the following formats as DuckDB tables:
Indexed files answer region queries without a full scan, and remote files are read over HTTPS and S3. Results stay in DuckDB until you collect them. htslib is bundled, so there is nothing else to install.
install.packages("Rduckhts")
# Development version
install.packages("Rduckhts", repos = c("https://rgenomicsetl.r-universe.dev",
"https://cloud.r-project.org"))
Rduckhts needs the duckdb R package, version 1.5.0 or newer.
With duckdb 1.5.x, two DuckDB bugs affect rduckhts_roh(),
rduckhts_roh_counts(), rduckhts_count_error_fit() and
rduckhts_somalier_matched_contamination(). An out-of-memory error can
end the R session instead of returning an error
(https://github.com/duckdb/duckdb/issues/26441). A failed query with 5
or more threads can keep its native memory until the R session ends
(https://github.com/duckdb/duckdb/issues/26871). Raise memory_limit
or pass fewer samples per query, and restart R after such a failure. The
DuckDB 2.0 branch fixes the first bug, and its development builds do not
show the second.
Every reader has the same shape,
rduckhts_<format>(con, table_name, path, ...). It creates a DuckDB
table that you query with SQL:
library(DBI)
library(Rduckhts)
con <- rduckhts_connect()
bcf <- system.file("extdata", "vcf_file.bcf", package = "Rduckhts")
rduckhts_bcf(con, "variants", bcf)
dbGetQuery(con, "
SELECT CHROM, count(*) AS variants, min(POS) AS first, max(POS) AS last
FROM variants GROUP BY CHROM ORDER BY CHROM
")
#> CHROM variants first last
#> 1 1 11 3000150 3184885
#> 2 2 1 3199812 3199812
#> 3 3 1 3212016 3212016
#> 4 4 2 3258448 3258501
Pass region to any indexed reader. Only the matching records are
decoded:
rduckhts_bcf(con, "variants_region", bcf, region = "1:3000150-3000151")
dbGetQuery(con, "SELECT CHROM, POS, REF, ALT FROM variants_region")
#> CHROM POS REF ALT
#> 1 1 3000150 C T
#> 2 1 3000151 C T
bam <- system.file("extdata", "range.bam", package = "Rduckhts")
rduckhts_bam(con, "reads", bam, region = "CHROMOSOME_I:1-1000")
dbGetQuery(con, "SELECT QNAME, FLAG, POS, MAPQ, CIGAR FROM reads")
#> QNAME FLAG POS MAPQ CIGAR
#> 1 HS18_09653:4:1315:19857:61712 145 914 23 78M1D22M
#> 2 HS18_09653:4:1308:11522:27107 161 934 0 58M1D42M
rduckhts_geno_sites() takes a table of requested alleles and returns
each one’s genotypes from a single indexed scan. A requested ALT inside
a multi-allelic record comes back with its index in ALT. Requests
without an exact match stay in the result with a match_status and no
fabricated genotype.
geno <- system.file("extdata", "geno_sites.bcf", package = "Rduckhts")
dbWriteTable(con, "requests", temporary = TRUE, data.frame(
request_id = 1:4, build = "GRCh38", chrom = c("chr1", "chr1", "chr1", "chr3"),
pos = c(100L, 200L, 300L, 10L), ref = c("A", "T", "G", "C"),
alt = c("G", "C", "A", "T")
))
fetched <- rduckhts_geno_sites(con, geno, "requests", source_build = "GRCh38",
format_fields = "DS")
fetched <- fetched[order(fetched$request_id), ]
rownames(fetched) <- NULL
fetched[, c("request_id", "chrom", "pos", "alt", "alt_index", "match_status")]
#> request_id chrom pos alt alt_index match_status
#> 1 1 chr1 100 G 2 matched
#> 2 1 chr1 100 G 2 matched
#> 3 2 chr1 200 C 1 matched
#> 4 3 chr1 300 A NA allele_not_at_site
#> 5 4 chr3 10 T NA absent
The test file holds two records at chr1:100, so request 1 matches twice, once per record. Request 3’s REF matches but its ALT is not at that site. Request 4’s position is not in the file.
GFF, GTF and GenBank readers can promote chosen attributes to typed columns:
gff <- system.file("extdata", "gff_attrs.gff3", package = "Rduckhts")
rduckhts_gff(con, "features", gff, attributes = c("ID", "Dbxref"))
dbGetQuery(con, "SELECT seqname, feature, start, \"end\", ID, Dbxref FROM features")
#> seqname feature start end ID Dbxref
#> 1 chr1 gene 1 10 g GeneID:1,HGNC:HGNC:1
Remote paths work wherever a local path does, once setup_hts_env() has
pointed htslib at its bundled network plugins. Call it once per session
before the first remote read. This reads a 100 kb slice of the UCSC
GRCh38 phyloP track and one region of a 1000 Genomes cohort VCF on S3,
fetching only the index and the blocks that the region needs:
setup_hts_env()
phylop <- "https://hgdownload.soe.ucsc.edu/goldenPath/hg38/phyloP100way/hg38.phyloP100way.bw"
rduckhts_bigwig(con, "conservation", phylop, region = "chr22:20000000-20099999")
dbGetQuery(con, "
SELECT count(*) AS intervals, round(min(VALUE), 3) AS min, round(max(VALUE), 3) AS max
FROM conservation
")
#> intervals min max
#> 1 96783 -10.787 9.602
cohort <- paste0(
"s3://1000genomes-dragen-v3.7.6/data/cohorts/gvcf-genotyper-dragen-3.7.6/",
"hg19/3202-samples-cohort/3202_samples_cohort_gg_chr22.vcf.gz"
)
rduckhts_bcf(con, "cohort", cohort, region = "chr22:16050000-16050500")
dbGetQuery(con, "SELECT count(*) AS variants FROM cohort")
#> variants
#> 1 11
S3 access needs a build with htslib’s S3 support; Windows (Rtools) builds do not include it.
Converters stream a file into Parquet and keep its header in the Parquet metadata:
parquet <- tempfile(fileext = ".parquet")
rduckhts_bcf_convert_parquet(con, bcf, parquet, overwrite = TRUE)
dbGetQuery(con, sprintf("SELECT count(*) AS rows FROM read_parquet('%s')", parquet))
#> rows
#> 1 15
rduckhts_connect(dbdir = "file.duckdb") works with database files too,
even read-only ones, and never changes the file’s catalog.
rduckhts_load(con), then rduckhts_install_macros(con).read_bcf(),
read_bam() and the rest) are available directly on the connection.DuckDB reports these mistakes with its own error messages, which do not
say what to change. first_line() below keeps the first line of each
error.
Quote end. end is an SQL keyword, and interval tables have an
end column. Write it as "end":
first_line <- function(expr) {
tryCatch(expr, error = function(e) strsplit(conditionMessage(e), "\n")[[1]][1])
}
targets <- system.file("extdata", "targets.bed", package = "Rduckhts")
rduckhts_bed(con, "targets", targets)
first_line(dbGetQuery(con, "SELECT chrom, start, end FROM targets"))
#> [1] "Parser Error: syntax error at or near \"end\""
dbGetQuery(con, 'SELECT chrom, start, "end" FROM targets')
#> chrom start end
#> 1 CHROMOSOME_I 0 10
#> 2 CHROMOSOME_I 10 20
#> 3 CHROMOSOME_II 0 8
#> 4 CHROMOSOME_III 0 6
Pass a column, not a string. A function that builds from a table
takes the table and column names as strings. A scalar function takes
column values, so 'end' there is the text “end”, not the column:
dbGetQuery(con, "SELECT * FROM duckhts_cgranges_from_table(
'targets_idx', 'targets', 'chrom', 'start', 'end')")
#> indexed
#> 1 TRUE
first_line(dbGetQuery(con, "SELECT duckhts_cgranges_count_overlaps(
'targets_idx', chrom, start, 'end') FROM targets"))
#> [1] "Invalid Error: Conversion Error: Could not convert string 'end' to INT64"
dbGetQuery(con, 'SELECT chrom, start, "end",
duckhts_cgranges_count_overlaps(\'targets_idx\', chrom, start, "end") AS overlaps
FROM targets')
#> chrom start end overlaps
#> 1 CHROMOSOME_I 0 10 1
#> 2 CHROMOSOME_I 10 20 1
#> 3 CHROMOSOME_II 0 8 1
#> 4 CHROMOSOME_III 0 6 1
Mind the coordinates. BED tables and the interval functions use
0-based, half-open coordinates: CHROMOSOME_I 0 10 covers bases 1 to
10. That is why each target above overlaps only itself, and 0-10 does
not overlap 10-20. VCF POS and region strings such as
"CHROMOSOME_I:1-1000" are 1-based and inclusive. To test a VCF
position against a BED interval, use start < POS AND POS <= "end", or
pass POS - 1, POS to an interval function.
Friendlier messages for these mistakes are discussed in #357.
rduckhts_functions() in R for the SQL catalog.rduckhts_htslib_config().GPL (>= 2). The bundled DuckHTS extension is MIT-licensed, and other
bundled third-party code keeps its own licence; see inst/COPYRIGHT.
The GenBank reader and FASTA converter were contributed by Ryan Ward of Nurture Bio. Thanks to all contributors, and to the htslib, bcftools, DuckDB and RBCFTools projects that DuckHTS builds on.