ATTACH 'https://omicidx.cancerdatasci.org/data/latest/omicidx.duckdb'
AS omicidx (READ_ONLY);
USE omicidx;Query with DuckDB
Query the public OmicIDX Parquet with DuckDB over anonymous HTTPS. Covers the published database, the SRAdb- and GEOmetadb-compatible tables, and the raw source tables.
OmicIDX publishes every table as Parquet over anonymous HTTPS. DuckDB reads only the parts of each file that a query needs, so you can query tens of millions of rows from a laptop without downloading anything first.
The examples on this page ran against latest/ when the page was built. Each SQL example has an Open in DuckDB Shell link that runs it in your browser, and the playground has an editable query box.
What’s published
The base URL is:
https://omicidx.cancerdatasci.org/data/(The older https://data.omicidx.cancerdatasci.org/ serves the same files.)
Under it, latest/ is rebuilt every night. Each night also gets an immutable v{YYYY-MM-DD}/ folder, kept for 30 days. See Versions and citation for which to use, and Releases for what’s there now. Each folder contains:
| Path | What it is |
|---|---|
<table>.parquet |
One file per source table: sra_studies, sra_samples, sra_experiments, sra_runs, sra_accessions, geo_series, geo_samples, geo_platforms, geo_series_with_rnaseq_counts, biosamples, bioprojects, pubmed_articles |
sradb/<table>.parquet |
SRAdb-compatible tables |
geometadb/<table>.parquet |
GEOmetadb-compatible tables |
omicidx.duckdb |
A small DuckDB database with a view over every Parquet file |
views.sql |
The same views as plain SQL |
restore.sql |
Builds a local copy of every table (see Work offline). Older dated folders don’t have it |
manifest.json |
Publish date, transform commit, lake snapshot id, row counts |
You can browse a folder listing in your browser, e.g. latest/.
Attach the database
omicidx.duckdb is a few hundred KB of view definitions. Attach it over HTTPS and query the views. The data stays remote.
SELECT run_accession, study_title
FROM sradb.rnaseq_runs
WHERE taxon_id = 9606 -- human
LIMIT 10;| run_accession | study_title |
|---|---|
| DRR391952 | Spatial transcriptome analysis of human oral cancers |
| DRR391953 | Spatial transcriptome analysis of human oral cancers |
| DRR391954 | Spatial transcriptome analysis of human oral cancers |
| DRR391955 | Spatial transcriptome analysis of human oral cancers |
| DRR391969 | Transcriptome analysis of human gingival epithelial cells treated with short-chain fatty acids |
| DRR391970 | Transcriptome analysis of human gingival epithelial cells treated with short-chain fatty acids |
| DRR391971 | Transcriptome analysis of human gingival epithelial cells treated with short-chain fatty acids |
| DRR394097 | Single Cell RNA sequence of clinical breast cancer stem-like cells |
| DRR394098 | Single Cell RNA sequence of clinical breast cancer stem-like cells |
| DRR394099 | Single Cell RNA sequence of clinical breast cancer stem-like cells |
DuckDB loads its httpfs extension automatically for https:// paths. If your install can’t auto-load extensions, run INSTALL httpfs; LOAD httpfs; first.
Read a Parquet file directly
Every table is a plain file, so you can skip the database and use read_parquet:
SELECT count(*) AS n
FROM read_parquet('https://omicidx.cancerdatasci.org/data/latest/biosamples.parquet');| n |
|---|
| 60484224 |
SELECT study_accession, title, study_type
FROM 'https://omicidx.cancerdatasci.org/data/latest/sra_studies.parquet'
WHERE bioproject = 'PRJNA1147303';| study_accession | title | study_type |
|---|---|---|
| SRP525855 | Platelets Transfer Aldolase via Extracellular Vesicles to Facilitate the Metabolic Reprogramming of Kupffer Cells in Acute Liver Injury | Transcriptome Analysis |
Filters and column selection are pushed down, so only the matching parts of a file are fetched. pubmed_articles.parquet is about 37 GB, so filter it in place.
SRAdb-compatible tables (sradb)
A modernized SRAdb schema. Legacy column names are kept where the data still exists, always-empty columns are dropped, and BioProject, GEO, and PubMed cross-references are added.
| Table | One row per |
|---|---|
sradb.study |
SRA study (SRP/ERP/DRP) |
sradb.sample |
SRA sample (SRS/ERS/DRS) |
sradb.experiment |
SRA experiment (SRX/ERX/DRX) |
sradb.run |
SRA run (SRR/ERR/DRR) |
sradb.sra |
run, joined with its experiment, sample, and study |
sradb.run_with_study |
run, with study title, type, and BioProject |
sradb.rnaseq_runs, sradb.wgs_runs |
run_with_study filtered by library strategy |
sradb.human_runs, sradb.mouse_runs |
run_with_study filtered by taxon |
SELECT study_accession, study_title, bioproject_accession, pubmed_ids
FROM sradb.study
WHERE study_accession = 'SRP000001';| study_accession | study_title | bioproject_accession | pubmed_ids |
|---|---|---|---|
| SRP000001 | Paired-end mapping reveals extensive structural variation in the human genome | PRJNA33627 | 17901297 |
-- RNA-seq runs for one BioProject
SELECT run_accession, platform, instrument_model, total_bases
FROM sradb.run_with_study
WHERE bioproject = 'PRJNA1147303' AND library_strategy = 'RNA-Seq';| run_accession | platform | instrument_model | total_bases |
|---|---|---|---|
| SRR41116924 | DNBSEQ | DNBSEQ-T7 | 6633217200 |
| SRR41116925 | DNBSEQ | DNBSEQ-T7 | 6633141600 |
| SRR30213718 | ILLUMINA | Illumina NovaSeq 6000 | 6078034200 |
| SRR30213719 | ILLUMINA | Illumina NovaSeq 6000 | 7097461460 |
| SRR30213720 | ILLUMINA | Illumina NovaSeq 6000 | 5913186964 |
| SRR30213721 | ILLUMINA | Illumina NovaSeq 6000 | 5453278002 |
| SRR30213722 | DNBSEQ | DNBSEQ-T7 | 1194440800 |
| SRR30213723 | DNBSEQ | DNBSEQ-T7 | 1190760850 |
| SRR30213724 | DNBSEQ | DNBSEQ-T7 | 1194007300 |
| SRR30213725 | DNBSEQ | DNBSEQ-T7 | 1194538850 |
GEOmetadb-compatible tables (geometadb)
A modernized GEOmetadb schema: series (gse), samples (gsm), platforms (gpl), the gse_gsm and gse_gpl link tables, and geo_supplemental_files.
-- Series with NCBI-computed RNA-seq counts
SELECT gse, title
FROM geometadb.gse
WHERE has_geo_computed_rnaseq
LIMIT 10;| gse | title |
|---|---|
| GSE240902 | Single-nucleoid architecture reveals heterogeneous packaging of mitochondrial DNA [RNA-seq] |
| GSE241020 | CD56-mediated activation of human natural killer cells is triggered by Aspergillus fumigatus galactosaminogalactan |
| GSE241164 | Impact of bacterial derived microvesicles on gene expression of Caco2 cells. |
| GSE241167 | Bacterial lipopolysaccharide-induced inflammation promotes iron accumulation in human adipocytes |
| GSE241430 | Longitudinal modeling of human neuronal aging reveals the contribution of the RCAN1-TFEB pathway to Huntington’s disease neurodegeneration |
| GSE241621 | Mutation-specific pathophysiological mechanisms of AFF3 differently influence transcriptome profiles |
| GSE241649 | Pharmaceutical inhibition of RAS overcomes cytokine mediated resistance to FLT3 inhibition in FLT3-ITD+ AML [RNA-seq] |
| GSE241702 | Mucosal CCL28 Chemokine Improves Protection against Genital Herpes through Mobilization of Antiviral Effector Memory CCR10+CD44+ CD62L-CD8+ T Cells and Memory CCR10+B220+CD27+ B Cells into the Infected Vaginal Mucosa |
| GSE241796 | The helicase domain of human Dicer prevents RNAi-independent activation of antiviral and inflammatory pathways |
| GSE241816 | Cytoglobin overexpression-dependent transcriptomic changes in A375 melanoma cells |
-- All samples in a series
SELECT gsm FROM geometadb.gse_gsm WHERE gse = 'GSE118849';| gsm |
|---|
| GSM3349112 |
| GSM3349115 |
| GSM3349119 |
| GSM3349120 |
| GSM3349118 |
| GSM3349110 |
| GSM3349113 |
| GSM3349117 |
| GSM3349109 |
| GSM3349111 |
Source tables
The source tables (sra_runs, geo_samples, biosamples, …) keep the upstream record shape, including nested lists of attributes and identifiers. In omicidx.duckdb they’re views in the default schema, so after USE omicidx you can write FROM sra_studies. Older dated databases contain only the sradb and geometadb views; use read_parquet for source tables there.
-- Unnest SRA study attributes
SELECT accession, a.tag, a.value
FROM 'https://omicidx.cancerdatasci.org/data/latest/sra_studies.parquet',
unnest(attributes) AS t(a)
LIMIT 10;| accession | tag | value |
|---|---|---|
| SRP687251 | parent_bioproject | PRJNA1106569 |
| SRP692237 | parent_bioproject | PRJNA832731 |
| SRP707907 | parent_bioproject | PRJNA489243 |
| SRP655435 | parent_bioproject | PRJNA1223840 |
| SRP363785 | parent_bioproject | PRJNA815648 |
| SRP595562 | parent_bioproject | PRJNA1295909 |
| SRP595664 | parent_bioproject | PRJNA81359 |
| SRP584239 | parent_bioproject | PRJNA1261087 |
| SRP651796 | parent_bioproject | PRJNA1090498 |
| SRP569400 | parent_bioproject | PRJNA1120815 |
Work offline
For heavy use, make a local copy. restore.sql copies every table of a folder into a local DuckDB file, with the same schemas and names as the views, then checks each table’s row count against the folder’s manifest.json:
curl -O https://omicidx.cancerdatasci.org/data/latest/restore.sql
duckdb omicidx-local.duckdb < restore.sql
duckdb -readonly omicidx-local.duckdb -c "SELECT count(*) FROM sradb.study"A full copy is tens of GB, most of it pubmed_articles and biosamples. In R, omicidx_download() does the same. To copy only a few tables, download their Parquet files and point DuckDB at them:
curl -O https://omicidx.cancerdatasci.org/data/latest/sradb/study.parquet
duckdb -c "SELECT count(*) FROM 'study.parquet'"Notes
- Requests are anonymous HTTPS range requests. Behind a strict firewall, allow
omicidx.cancerdatasci.org. - Any recent DuckDB release works. This page was built with DuckDB 1.5.6 (R package).