Repository navigation
Replies: 2 comments
|
Let's discuss supporting the seqforge to develop a full GEO metadata storage and management database + api
|
|
The goal and its four stages are now tracked in #18. Design choices stay open until each stage's map is built. |
Uh oh!
There was an error while loading. Please reload this page.
Moved from seqforge discussion #500 on
2026-10-14. seqforge needs a metadata table covering every GEO study, and bulk data management
belongs here: labdata already owns per-accession GEO and SRA access (Entrez, SDL), and seqforge
already imports it. This thread designs the bulk counterpart, with seqforge as its first consumer.
Proposed split
Series,Sample,Run)SdlClientArchiveRecordSetfetch_records, per accessionThe dependency runs one way: seqforge imports labdata, and labdata never sees a seqforge type.
Open questions
AGENTS.mdkeeps non-NCBI sources out of scope for now. ENA'sread_runpullcarries GSE, GSM and taxid in one file (source 5 below). Leaning: admit ENA, for metadata only.
pandas today. Leaning: Parquet plus DuckDB, since NCBI already ships the SRA table as Parquet.
~/.cache/liulab-data) or one shared copy on thecluster. Leaning: one shared copy, because the first download is tens of GB.
Series,Sampleand
Runreading the mirror first and falling back to Entrez. Leaning: the module first; thelazy objects read from it once it exists.
[UDAT]feed picks which SOFT files to fetch again, and the SRAParquet is downloaded again in full (it has no increments). Untested: whether a regenerated
SOFT file is byte-identical.
The measured note follows. It is unchanged from seqforge except that seqforge's "rung 0" wording
now reads "the table".
Measured 2026-10-13. The question: which bulk sources, as opposed to per-study API calls, could
feed one structured table of every GEO series (GSE) and sample (GSM)? The table
needs species, assay class, platform (GPL) and instrument, SRA library
strategy/source/selection/layout, sample count, the SRA, BioProject and BioSample accessions, the
PubMed id, and dates. A text layer needs the free text: title, summary, overall design,
characteristics, protocols and supplementary file lists.
Evidence classes: measured means a listing, HEAD request or query was run on 2026-10-13 and the
number is its output. Read means an official page or source file says it. Inferred means
reasoned from measured facts and not checked directly.
Verdict
No single dump covers GEO. The cheapest complete design has three parts:
sra.metadataon S3 (13.8 GB,regenerated daily) gives every sequencing field at run grain: layout, instrument, the three
library fields, organism, and the SRX, SRS, SRP, SAMN and PRJNA accessions. It joins to GEO
through
sample_name/library_name= GSM.change list. Its family SOFT file is the only source for the free-text fields, the GPL and the
PubMed id, short of the E-utilities summary. The median file is 4 KB. The mean is 14 MB because
microarray series embed their data tables.
esearch db=gdswith an[UDAT]update-date range returnsthe GSE and GSM records changed in a window: 902 GSE in the week of 2026-10-06 to 10-12. Use it
to choose which family files to fetch again. FTP modification times are useless for this,
because GEO regenerates every family file on a rolling cycle whether or not it changed.
GEOmetadb, the usual answer to this question, is dead. Its SQLite file was last written on
2024-03-06, and SRAmetadb's on 2023-12-10.
Sizing: how big is GEO
esearch db=gds term=gse[ETYP]GSE0nnn…GSE350nnn)https://ftp.ncbi.nlm.nih.gov/geo/series/*/gsm[ETYP]gpl[ETYP]GDS6248dated 2016-08-22gds[ETYP];https://ftp.ncbi.nlm.nih.gov/geo/datasets/GDS6nnn/gse[ETYP] AND "high throughput sequencing"[PTYP]gsm[ETYP][GTYP][GTYP]+ "Homo sapiens"[ORGN][PDAT]ranges[PDAT][UDAT]vs[PDAT][UDAT]count result=read_run query=study_alias="GSE*"GEO's own statistics page (
https://www.ncbi.nlm.nih.gov/geo/summary/) served an anti-bot challengeto curl, so these counts come from Entrez instead. Entrez supports the
UDATfield ("Update Date"),measured from
einfo.fcgi?db=gds. The 31-series gap between Entrez and the FTP is the lag betweenthe two.
Sources
1. GEO FTP, per series
https://ftp.ncbi.nlm.nih.gov/geo/series/GSE{n}nnn/GSE{id}/{soft,miniml,matrix,suppl}/.The layout is documented in
https://ftp.ncbi.nlm.nih.gov/geo/README.txt(read; the README waslast updated 2019-07-16).
GSExxx_family.soft.gzis line-based!Key = value.GSExxx_family.xml.tgz(MINiML) is the same content as XML, and the README calls the two "generally contain the same
data".
GSExxx_series_matrix.txt.gzholds the series and sample headers plus a value matrix.unpacked), and the matrix 40 KB. The files are in
a local scratch directory. Keys present:
title,summary,overall_design,type,pubmed_id,submission_date,last_update_date,platform_id,platform_taxid,sample_taxid,supplementary_file,relation(SubSeries of: GSE207086andBioProject: …/PRJNA853582), andsample_id×1440.title,source_name_ch1,organism_ch1,taxid_ch1,characteristics_ch1,molecule_ch1,extract_protocol_ch1,growth_protocol_ch1,data_processing,description,library_strategy,library_source,library_selection,instrument_model,platform_id,supplementary_file_1,submission_date,last_update_date, andrelation× 2. The two relations areBioSample: …SAMN…andSRA: …sra?term=SRX….technology,title,organism,taxid.counts and the SRA study (SRP). The GSM→SRX link is a URL inside
!Sample_relation = SRA:. Getlayout and runs from SRA.
is 4 KB, the 90th percentile 13 MB, the maximum 1.7 GB, and the mean 14 MB
(measured). A naive full crawl would be about 4 TB (inferred: mean × 298,373), nearly all of
it microarray data tables. An HTS-only crawl is small (inferred from the 4 KB median and the
52 KB file for 1440 samples).
files carry a modification time between 2026-07 and 2026-10. GSE207085's file is dated
2026-09-27, but its
!Series_last_update_dateis 2024-09-30. GSE1000 and GSE15701 wereregenerated on 2026-10-05. GEO rewrites family files on a rolling cycle of about 3–4 months
(inferred), so polling FTP modification times would refetch everything. The per-GSE
directory modification time stays fixed (GSE207085 → 2022-07-13) and does not move when its
files are regenerated.
under 10 minutes at 8 in parallel, give every public GSE id. The list is useful as a
completeness check against Entrez.
is empty, so the matrix and SOFT carry the same structured fields (inferred). The README warns that
values "are extracted directly from the original records with no consideration as to whether the
values are directly comparable".
geo/datasets(GDS): frozen since 2016 (measured above) and microarray-era. Not a source forthe table.
/geo/holds onlydatasets/ platforms/ samples/ series/ README.txt(measured), and the README describes no dump.caveat that submitters may claim rights. Source:
https://www.ncbi.nlm.nih.gov/home/about/policies/(read).2. GEO through E-utilities: summary and change feed
Not a dump, but the only change feed GEO has, and its
esummarypacks most of the table's series fieldsinto one call.
esearch.fcgi?db=gds&term=gse[ETYP] AND YYYY/MM/DD:YYYY/MM/DD[UDAT]returned 902 records for one week and 5,417 for September.
gsm[ETYP]with the same filterreturned 15,043 for the week.
esummary db=gds(measured on uid 200207085):accession,title,summary,gpl,taxon,gdstype(assay class),n_samples,pdat,suppfile(extension classes),pubmedids,bioproject,ftplink,samples[](GSM and title),relationsandextrelations.extrelationsis empty andrelationsis[], althoughthe series has SRA links and is a SubSeries. Do not take SRA links or the SuperSeries from the
summary.
10 with one (read previously in
https://www.ncbi.nlm.nih.gov/books/NBK25497/; the page served abot challenge to curl today, so this was not re-verified). A summary of all 298k series at 500
ids per POST is about 600 calls (inferred).
3. SRA metadata, bulk (NCBI)
(a) Parquet table on S3, the recommended SRA source.
s3://sra-pub-metadata-us-east-1/sra/metadata/, anonymous (--no-sign-request), asdocumented at
https://www.ncbi.nlm.nih.gov/sra/docs/sra-athena/(read). It is also reachableby plain HTTPS:
https://sra-pub-metadata-us-east-1.s3.amazonaws.com/?list-type=2&prefix=sra/metadata/.UTC (file names
20261013_202221_…). The magic bytes arePAR1and the footer namestrino_schema.https://www.ncbi.nlm.nih.gov/sra/docs/sra-cloud-based-metadata-table/):acc(SRR),assay_type(library strategy),center_name,consent,experiment(SRX),sample_name,instrument,librarylayout,libraryselection,librarysource,platform,sample_acc(SRS),biosample,organism(name, not taxid),sra_study,releasedate,bioproject,mbytes,loaddate,avgspotlen,mbases,insertsize,library_name,biosamplemodel_sam,collection_date_sam,geo_loc_name_*,ena_first_public_run,ena_last_update_run,sample_name_sam,datastore_*,attributes(key/value array),jattr(JSON) andrun_file_version.alias (inferred from the
Aliascolumn in (c) and from ENA'ssample_alias=GSM…).regeneration is inferred. There are no increments, so the 13.8 GB is downloaded again.
BigQuery (
nih-sra-datastore.sra.metadata) is billed to the user. BigQuery's free tier is 1 TBof queries a month (read,
https://www.ncbi.nlm.nih.gov/sra/docs/sra-bigquery/).(b) XML tarballs on FTP.
https://ftp.ncbi.nlm.nih.gov/sra/reports/Metadata/.NCBI_SRA_Metadata_Full_YYYYMMDD.tar.gzis 14 GB (2025-10) growingto 17 GB (2026-09). Daily
NCBI_SRA_Metadata_YYYYMMDD.tar.gzfiles are 6.9 GB each, and havegrown from 5.9 GB (2025-09) to 6.9 GB.
folders, each with
submission,experiment,runandsampleXML. They range fromSRA17xxxxtoSRA256xxxx, plus DRA and ERA folders. A daily is therefore a set of recentlychanged submissions, not a one-day delta. Because the size is flat at about 6.9 GB, the window
is rolling (inferred).
third-party tool,
https://github.com/indraniel/srasearch, describes the files as "monthly fulland daily incremental" (read, third-party).
only if
EXPERIMENT/DESIGN/LIBRARY_CONSTRUCTION_PROTOCOLtext is needed, since the Parquetcolumns lack it.
(c)
SRA_Accessions.tabandSRA_Run_Members.tab.Content-Length: 35236482561), rewritten daily(modified 2026-10-13 05:16 GMT).
SRA_Run_Members.tabis 3.9 GB.Accession Submission Status Updated Published Received Type Center Visibility Alias Experiment Sample Study Loaded Spots Bases Md5sum BioSample BioProject ReplacedBy.include
SRX16351035 … EXPERIMENT GEO public GSM6352570 - SRS13988109. GEO-brokered experimentscarry
Center=GEOandAlias=GSM…, and the RUN rows carry Experiment, Sample, Study, BioSample andBioProject.
Updatedis a per-object change timestamp, the only record-level change signalamong the NCBI dumps.
lexicographically, so a byte-range binary search keyed on the string fails. Stream it once.
4. BioSample and BioProject
https://ftp.ncbi.nlm.nih.gov/biosample/biosample_set.xml.gzis 4.6 GB and was rewritten2026-10-13 12:34 (measured).
https://ftp.ncbi.nlm.nih.gov/bioproject/bioproject.xmlis 3.9 GB, uncompressed, and wasrewritten 2026-10-13 12:23 (measured).
bioproject/summary.txt(77 MB) is stale, dated 2025-04-08 (measured). Do not use it.attributesandjattr. That makes it redundant for the table once the Parquet is in hand(inferred). Neither file has an increment, so each is a full re-download.
5. ENA Portal API
https://www.ebi.ac.uk/ena/portal/api/search(and/count,/returnFields,/results)./results, 2026-10-12):read_runhas 45,056,966 records,study1,107,325 andsample60,618,142.read_runhas 195 return fields, includingstudy_alias,sample_alias,experiment_alias,tax_id,scientific_name,instrument_model, all four library fields,read_count,base_count,first_public,last_updated,fastq_ftp,fastq_md5andlibrary_construction_protocol.study_alias=GSE207085andsample_alias=GSM6278248.query=study_alias="GSE*"counts 7,718,764 runs, andsample_alias="GSM*"counts 7,324,783. Forresult=study,geo_accession="GSE*"counts271,440 and
secondary_study_alias="GSE*"counts 165,905.TSV in 54 s. All 7.72 M GEO runs would be about 1.7 GB and about 70 min in one streamed
limit=0call or a few windowed calls (inferred by linear extrapolation).last_updated>=YYYY-MM-DD(inferred from the field list; not run).https://ftp.ebi.ac.uk/pub/databases/ena/has no read-metadata dump (measuredlisting:
assembly/ coding/ … report/ … wgs/).report/livelist.gz(2.1 GB) is an accessionlist, not metadata (measured listing).
tax_id, GSE and GSM as aliases, andlibrary_construction_protocol, all in one TSV.hashing must be done on our side.
those provided by the data owner" (read,
https://www.ebi.ac.uk/about/terms-of-use).6. Third-party mirrors
https://gbnci.cancer.gov/geo/GEOmetadb.sqlite.gz; URL from the package sourceGEOmetadb/R/getSQLiteFile.Rinhttps://bioconductor.org/packages/release/bioc/src/contrib/GEOmetadb_1.74.0.tar.gz;gh api repos/seandavi/GEOmetadbhttps://gbnci.cancer.gov/backup/SRAmetadb.sqlite.gz;SRAdb_1.74.0sourcegh api repos/saketkc/pysradbgh api repos/pepkit/geofetchgh api repos/pachterlab/ffqhttps://maayanlab.cloud/archs4/download.html7. ArrayExpress and BioStudies (for the later adapter)
https://ftp.ebi.ac.uk/pub/databases/biostudies/is organised per collection(
E-MTAB-/,A-GEOD-/,S-BSST/…), with per-study directoriesE-MTAB-/{nnn}/E-MTAB-{id}/Files/(measured). There is no whole-archive metadata dump. Underthe measured layout, the IDF and SDRF files sit per study under
Files/(inferred from thelayout; not opened).
https://www.ebi.ac.uk/biostudies/api/v1/arrayexpress/searchreportstotalHits: 80971and is paged (measured). The searchE-GEODreturns 0 hits (measured).ArrayExpress no longer mirrors GEO as E-GEOD (inferred), so the AE adapter covers only native
E-MTAB and similar studies.
8. Publications: GSE → PMID
!Series_pubmed_idin SOFT andpubmedidsin esummary (measured onGSE207085 → 39196043). Submitter-entered, and often added after release, which bumps
UDAT(inferred).
https://ftp.ebi.ac.uk/pub/databases/pmc/TextMinedTerms/geo.csvis 19 MB with columnsgeo,PMCID,EXTID,SOURCEand was refreshed 2026-10-06; the siblingfiles
bioproject.csv(4.4 MB) andbiosample.csvshare the listing (measured). It isopen-access full text only, and covers papers that cite a GSE without being its primary paper.
It is a recall supplement, not the submitter's link (inferred).
Recommendation: cheapest bulk source per field
organism!Sample_organism_ch1!Sample_taxid_ch1,!Series_sample_taxidtax_id(run)gdstypeor SOFT!Series_typegpl; SOFT!Sample_platform_idinstrument!Sample_instrument_modelassay_type,librarysource,libraryselection!Sample_library_*(GSM grain; GEO source vocabulary differs, e.g.transcriptomic single cell)librarylayoutlibrary_layout. Absent from GEO.n_samples!Series_sample_idSRA_Accessions.tabEXPERIMENT rows (Center=GEO,Alias=GSM)!Sample_relation = SRA:SRA_Accessions.tabRUN rowsbioproject!Series_relation = BioProject:!Series_relationpubmedidsgeo.csv!*_submission_date,!*_last_update_datepdat(release only)!*_supplementary_file*suppl/listingIncremental strategy for the whole table:
platform. Fetch their family SOFT; the HTS-only volume is far below the 4 TB all-GEO figure
(inferred). Download the S3 Parquet (13.8 GB) once, and stream
SRA_Accessions.tabonce (33 GB)for the GSM→SRX map.
esearch gds [UDAT]over the window since the last run, for GSE and GSM(about 900 GSE a week, measured), and fetch again only those family SOFT and esummary records.
Fetch the S3 Parquet again: it is a full regeneration with no increment, at 13.8 GB. Do not
poll FTP modification times: they are a rolling regeneration cycle, not a change signal.
unchanged file must hash the same, which needs a check that the regeneration is byte-stable
(inferred, untested).
windows that duplicate the Parquet), BioSample and BioProject XML (redundant with the Parquet
attributes), and GDS (frozen since 2016).read_runpull withquery=study_alias="GSE*", about1.7 GB and 70 min. It adds
tax_idand the GSE and GSM keys directly. Prefer it if the jointhrough the GSM alias proves lossy (7.72 M GSE-aliased runs versus 7.32 M GSM-aliased runs is
a 0.4 M gap to explain first).
Method
All fetches ran on 2026-10-13 from the local machine using curl over HTTPS. No sequencing data
was downloaded. The largest download was the two Bioconductor source tarballs (0.65 MB and
0.9 MB), plus range reads of at most 40 MB.
/geo/,/geo/series/and all 351 range directories (crawl output ina local scratch file).
soft/listings for 300 GSE drawn at random (seed 1).Downloaded
GSE207085_family.soft.gzandGSE207085_family.xml.tgz.README.txt.einfo db=gds, thenesearchcounts withETYP,PTYP,GTYP,ORGN,PDATandUDAT, andesummary db=gds id=200207085./sra/reports/and/sra/reports/Metadata/. A range read of the first40 MB of
NCBI_SRA_Metadata_20261013.tar.gz, piped totar tzv. Range reads of the header andof offsets at 50/80/95 % of
SRA_Accessions.tab. The S3 ListObjectsV2 call onsra-pub-metadata-us-east-1/sra/metadata/, and the Parquet magic bytes and footer.sra-bigquery,sra-athena,sra-cloud-based-metadata-tableand/home/about/policies/, fetched with a browser user agent. The GEO summary page and theE-utilities book returned a bot challenge.
/results,/count,/searchFields,/returnFields, a filereport on SRP383998, andone timed 100k-row
search./pub/databases/ena/,/ena/report/,/biostudies/and/pmc/TextMinedTerms/, plus the header ofgeo.csv. The BioStudies search API.source.
gh api repos/...for pushed dates.Not done:
last_updatedfilter.seqforge's demo dataset PRJNA1027859 has no GEO series: Entrez
gdsreturns 0 hits, and ENA shows adirect SRA submission (SRP502277). GSE207085 was used as the worked example instead.
All reactions