JGI Lakehouse Skill

JGI Lakehouse Skill

Quick Start

What is it? JGI's unified data warehouse (651 tables) + filesystem access to genome files.

Two data access methods:

1. Lakehouse (Dremio) → Metadata, annotations, taxonomy (no sequences)
2. JGI Filesystem → Actual genome files (FNA, FAA, GFF) via taxon OID

SQL Dialect: ANSI SQL (not PostgreSQL)

• Use CAST(x AS type) not ::
• Use REGEXP_LIKE() not ~
• Identifiers with dashes need double quotes: "gold-db-2 postgresql"

-- Quick test
SELECT gold_id, project_name FROM "gold-db-2 postgresql".gold.project
WHERE is_public = 'Yes' LIMIT 5;

---

When to Use

• Query JGI genomics metadata (GOLD, IMG, Mycocosm, Phytozome)
• Find genomes by taxonomy, ecosystem, or phenotype
• Download microbial genomes with IMG taxon OIDs
• Cross-reference GOLD projects with IMG annotations

---

Data Access: Lakehouse vs Filesystem

Critical insight: The Lakehouse is a METADATA warehouse. Genome sequences must be accessed from the JGI filesystem or downloaded from NCBI.

---

Key Data Sources

Full table catalog: See docs/​data-catalog.md

---

Downloading Genomes with IMG Taxon OIDs

Option 1: JGI Filesystem (Fastest)

# Genome packages are at:
/​clusterfs/​jgi/​img_merfs-ro/​img_web/​img_web_data/​download/​{taxon_oid}.tar.gz

# Example: Copy and extract
cp /​clusterfs/​jgi/​img_merfs-ro/​img_web/​img_web_data/​download/​8136918376.tar.gz .
tar -xzf 8136918376.tar.gz

Package contents:

• {taxon_oid}.fna - Genome assembly
• {taxon_oid}.genes.faa - Protein sequences
• {taxon_oid}.genes.fna - Gene nucleotide sequences
• {taxon_oid}.gff - GFF annotations
• {taxon_oid}.cog.tab.txt - COG annotations
• {taxon_oid}.pfam.tab.txt - Pfam annotations
• {taxon_oid}.ko.tab.txt - KEGG KO annotations

Option 2: Query Lakehouse + Download from NCBI

-- Get NCBI accessions for download
SELECT assembly_accession, organism_name, assembly_level, genome_size
FROM "gold-db-2 postgresql".gold.ncbi_assembly
WHERE assembly_level = 'Complete Genome' AND group_ = 'bacteria'
LIMIT 10;

# Download via NCBI datasets CLI
datasets download genome accession GCA_000219355.1 \
    --include genome,gff3,protein --filename genome.zip

Detailed workflow: See examples/​04-download-img-genomes.md

---

Common Queries

Find Bacterial Isolate Genomes

SELECT taxon_oid, taxon_display_name, phylum, genus, species
FROM "img-db-2 postgresql".img_core_v400.taxon
WHERE domain = 'Bacteria'
  AND genome_type = 'isolate'
  AND is_public = 'Yes'
  AND seq_status = 'Finished'
LIMIT 100;

Link GOLD Project to IMG Taxon

SELECT t.taxon_oid, t.taxon_display_name, t.sequencing_gold_id
FROM "img-db-2 postgresql".img_core_v400.taxon t
WHERE t.sequencing_gold_id IS NOT NULL
LIMIT 50;

Find Genomes with File Paths (Portal)

SELECT taxonOid, filePath
FROM "portal-db-1".portal.downloadRequestFiles
WHERE taxonOid IS NOT NULL
  AND filePath LIKE '%.tar.gz'
LIMIT 20;

---

Critical Pitfalls

---

Authentication

export DREMIO_PAT=$(cat ~/​.secrets/​dremio_pat)

Token setup: See docs/​authentication.md

---

API Access

REST API Base: http://lakehouse-1.jgi.lbl.gov:9047/​api/​v3

# Use scripts/​rest_client.py
from rest_client import query
results = query("SELECT * FROM ... LIMIT 10")

---

Documentation

• docs/​data-catalog.md - Complete table inventory (651 tables)
• docs/​sql-quick-reference.md - Dremio SQL syntax
• examples/​04-download-img-genomes.md - Download with taxon OIDs
• scripts/​download_img_genomes.py - Automated download script