Create a relational database from NCBI/SRA so that I can query SRA sample data and extract SRA Run IDs
I can't find documentation on how this database was built and I don't trust it. There is very little documentation on how to query their API. I'd rather just write database queries. Also, they didn't include the latitude and longitude (or any of the other attributes in the Samples table).
Because I need to look up hundreds of thousands of samples.
There is no straightforward way to export the list of sequencing run IDs for a given set of samples and I need the run IDs to download the raw reads.
NCBI's data is partially schema-less and I just wanted a quick way to query a
large quantity of mostly static documents.
I switched back to PostgreSQL and JSONB for the schema-less metadata part.
Install requirements:
pip3 install -r requirements.txt
Fetch data from NCBI. This will fetch a ~ 2 Gb .tar.gz
file containing XML
documents for SRA submissions. You do not need to extract this file!
Make sure you edit fetch-data.sh
and load.py
with the latest filename from
https://ftp-trace.ncbi.nih.gov/sra/reports/Metadata/
./fetch-data.sh
Set environment variables (same as genome observatory) for postgres database:
SOBS_DB_NAME=...
SOBS_DB_USER=...
SOBS_DB_PASSWORD=...
SOBS_DB_PORT=...
SOBS_DB_HOST=...
Load data into PostgreSQL
./load.py
This will build 3 tables in sra-db
:
samples
runs
experiments
The schema is roughly the same as the NCBI SRA (see their documentation) although I didn't extract everything from their XML dumps. To get from experiment to run, you have to join on sample. In SQL, this would look like:
select
scientific_name,
count(*)
from
experiment
left join
sample
on
experiment.sample_accession = sample.id
left join
run
on
run.experiment_accession = experiment.id
where
scientific_name = "soil metagenome"
group by
sample.scientific_name
order by
count desc
Output a list of SRR IDs (needed to download the fastq files) with some relevant metadata:
select
experiment.id as experiment_accession,
sample.id as sample_accession,
sample.scientific_name,
sample.taxid,
run.id as run_accession,
scientific_name,
experiment.library_name,
experiment.library_strategy,
experiment.library_source,
experiment.library_selection,
experiment.library_layout,
experiment.platform_type,
experiment.platform_instrument_model,
run.title,
attributes->'lat_lon' as lat_lon,
attributes->'geo_loc_name' as geo_loc_name,
attributes->'study name' as study_name
from
experiment
left join
sample
on
experiment.sample_accession = sample.id
left join
run
on
run.experiment_accession = experiment.id
where
scientific_name LIKE %s