Loading Dataset into Chado

Session Overview

This session uses uploadDataset.sh to load a full training dataset into the Chado schema. The script automates organism lookup, intermediate CSV generation, and bulk copy into PostgreSQL in a single command.

What the Script Does

• Validates input files (sample list, SNP positions, HDF5 file)
• Looks up or inserts CV terms, organism, and dbxref entries
• Generates intermediate CSV files for each target table:
  • StockSample.csv
  • SampleVarietySet.csv
  • SnpFeature.csv
  • Variant_variantset.csv
  • Snp_FeatureLoc.csv
• Updates sequence values to prevent ID conflicts
• Performs efficient bulk copy using \copy

Related Files

Download these before starting the lab:

Using uploadDataset.sh

uploadDataset.sh handles the full load — organism lookup, CSV generation, and bulk copy — in one command. Download it from the Related Files section above.

Prerequisites

• PostgreSQL client (psql) and database access credentials
• Reference genome already loaded (completed in Session 08)
• Input files ready:
  • Sample list file (e.g., sample_list.txt)
  • SNP positions file (e.g., pos_500_per_chr.txt)
  • HDF5 file (e.g., test_file.h5)

Step 1: Make the Script Executable

chmod +x uploadDataset.sh

Step 2: Run the Script

Make sure uploadDataset.sh and your input files are in the same directory, then run:

./uploadDataset.sh sampleds sampleds sample_list.txt pos_500_per_chr.txt 'Japonica nipponbare' test_file.h5

Verify the Load

Run these checks inside psql after the script completes.

Check Row Counts

SELECT COUNT(*) FROM stock_sample;
SELECT COUNT(*) FROM snp_feature;
SELECT COUNT(*) FROM variantset;

Expected Outcome

• Stock, stock_sample, SNP, and variant data are fully inserted.
• Relationships between samples and SNPs are properly linked.
• Data is queryable using SQL joins and integrated into SNP-Seek.

SNP-Seek Chado — Sample Queries

A beginner's reference for common lookups  ·  Database Curation & Loading Training  ·  1k1 Rice Genome Project

How to use this sheet: Each query is a complete, runnable example with a plain-English description and what it returns. They are deliberately simple lookups — the building blocks. Table and column names match the SNP-Seek Chado schema. Replace the example values (in capitals or quotes) with your own.

A. Vocabulary lookups (cvterm)

1. Find the cvterm for "gene"

Look up a single ontology term by name. This is the term that feature rows point to.

SELECT cvterm_id, name, definition
FROM   cvterm
WHERE  name = 'gene';

Returns: The cvterm_id you can use elsewhere, plus the term's definition.

2. List all feature types in use

See every distinct type a feature can be (gene, SNP, chromosome, mRNA, …).

SELECT DISTINCT c.name AS feature_type
FROM   feature f
JOIN   cvterm  c ON f.type_id = c.cvterm_id
ORDER  BY feature_type;

Returns: One row per feature type present in the database.

B. Finding features (genes & SNPs)

3. Find a feature by name

The simplest lookup — fetch one feature by its unique name.

SELECT feature_id, name, uniquename
FROM   feature
WHERE  uniquename = 'LOC_Os01g01010';

Returns: The matching feature row (or nothing if the name isn't found).

4. List all genes

Filter the feature table to just one type by joining to cvterm.

SELECT f.uniquename, f.name
FROM   feature f
JOIN   cvterm  c ON f.type_id = c.cvterm_id
WHERE  c.name = 'gene'
LIMIT  50;

Returns: Up to 50 gene features. (Always use LIMIT while exploring — these tables are large.)

5. Count features by type

A quick health-check: how many of each type are loaded?

SELECT c.name AS feature_type, COUNT(*) AS n
FROM   feature f
JOIN   cvterm  c ON f.type_id = c.cvterm_id
GROUP  BY c.name
ORDER  BY n DESC;

Returns: Each feature type with its row count, largest first.

6. List genes for one rice line

Combine a type filter with an organism filter — the two most common WHERE clauses together.

SELECT f.uniquename, f.name
FROM   feature  f
JOIN   cvterm   c ON f.type_id     = c.cvterm_id
JOIN   organism o ON f.organism_id = o.organism_id
WHERE  c.name = 'gene'
AND    o.common_name = 'Japonica nipponbare'
LIMIT  50;

Returns: Genes belonging to the named reference genome.

C. Where things sit (featureloc)

7. Get the position of one feature

featureloc holds the coordinates. srcfeature_id is the chromosome (itself a feature) the feature sits on.

SELECT fl.fmin, fl.fmax, fl.strand
FROM   featureloc fl
JOIN   feature    f ON fl.feature_id = f.feature_id
WHERE  f.uniquename = 'LOC_Os01g01010';

Returns: Start (fmin), end (fmax) and strand for that feature.

8. List features on chromosome 1

Find everything located on a given chromosome by matching the source feature's name.

SELECT f.uniquename, fl.fmin, fl.fmax
FROM   featureloc fl
JOIN   feature    f   ON fl.feature_id    = f.feature_id
JOIN   feature    chr ON fl.srcfeature_id = chr.feature_id
WHERE  chr.name = 'Chr1'
ORDER  BY fl.fmin
LIMIT  50;

Returns: Features on Chr1, ordered by start position.

D. Notes & properties (featureprop)

9. Get all properties of a feature

featureprop holds descriptions, scores and notes. Its own type_id (a cvterm) says what kind of property each row is.

SELECT c.name AS property_type, fp.value
FROM   featureprop fp
JOIN   feature     f ON fp.feature_id = f.feature_id
JOIN   cvterm      c ON fp.type_id    = c.cvterm_id
WHERE  f.uniquename = 'LOC_Os01g01010';

Returns: Each property attached to the feature, labelled by its type.

E. Alternative names (synonyms)

10. List all names of a feature

Show every alternate name attached to a feature, labelled by which naming system it came from.

SELECT s.name AS synonym, c.name AS naming_system
FROM   feature_synonym fs
JOIN   synonym s ON fs.synonym_id = s.synonym_id
JOIN   feature f ON fs.feature_id = f.feature_id
JOIN   cvterm  c ON s.type_id     = c.cvterm_id
WHERE  f.uniquename = 'LOC_Os01g01010';

Returns: Each known synonym for the feature, with its source system.

11. Find a feature by any of its names

The synonym-aware lookup — search the feature's own name OR any synonym, in one query.

SELECT f.uniquename, f.name
FROM   feature f
WHERE  f.name = 'Os01g0100100'

UNION

SELECT f.uniquename, f.name
FROM   feature f
JOIN   feature_synonym fs ON f.feature_id = fs.feature_id
JOIN   synonym s          ON fs.synonym_id = s.synonym_id
WHERE  s.name = 'Os01g0100100';

Returns: The same feature, whether the search term was its primary name or a synonym.

F. Samples & accessions (stock)

12. List rice accessions

stock holds the physical material — the accessions/germplasm.

SELECT stock_id, name, uniquename
FROM   stock
LIMIT  50;

Returns: Up to 50 accessions.

13. Find one accession by name

A direct lookup of a single accession.

SELECT stock_id, name, uniquename, description
FROM   stock
WHERE  name = 'IR64';

Returns: The matching accession row.

G. Variant sets

14. List variant sets

A variantset is a named group of variants (positions). This lists the groups themselves.

SELECT variantset_id, name
FROM   variantset
ORDER  BY name;

Returns: Each named variant set.

15. Count variants in each set

Use the join table variant_variantset to count how many variants belong to each set.

SELECT vs.name, COUNT(*) AS n_variants
FROM   variant_variantset vv
JOIN   variantset vs ON vv.variantset_id = vs.variantset_id
GROUP  BY vs.name
ORDER  BY n_variants DESC;

Returns: Each variant set with the number of variants it contains.

A note on the genotype matrix

These queries all read metadata from PostgreSQL/Chado — what things are, where they sit, which accessions exist. The actual genotype calls (which accession has which allele at each position — roughly 60 billion data points) live in HDF5 files, not in these tables. You will not find the allele values with SQL; those come through the genotype API / HDF5 layer.