name: berdl description: Query the KBase BERDL (BER Data Lakehouse) pangenome database. Use when the user asks to explore pangenome data, query species information, get genome statistics, analyze gene clusters, or access functional annotations from the kbase_ke_pangenome database. allowed-tools: Bash, Read

BERDL Data Lakehouse Query Skill

This skill provides instructions for querying the KBase BERDL Data Lakehouse, which contains pangenome data for 293,059 genomes across 27,690 microbial species.

Database Information

Primary Database: kbase_ke_pangenome

Key Tables:

genome (293,059 rows) - Genome metadata and file paths
pangenome (27,690 rows) - Per-species pangenome statistics
gtdb_species_clade (27,690 rows) - Species taxonomy and ANI statistics
gene_cluster - Gene family classifications (core/accessory/singleton)
gene_genecluster_junction - Gene-to-cluster memberships
gene - Individual gene records
eggnog_mapper_annotations - Functional annotations (COG, GO, KEGG, EC, PFAM)
gapmind_pathways - Metabolic pathway predictions
genome_ani - Pairwise ANI values between genomes
gtdb_metadata - CheckM quality, assembly stats, GC%
gtdb_taxonomy_r214v1 - GTDB taxonomy
sample, ncbi_env - Environment metadata

Authentication

All BERDL API requests require authentication using the token from the .env file:

AUTH_TOKEN=$(grep "KB_AUTH_TOKEN" .env | cut -d'"' -f2)

API Endpoints

Base URL: https://hub.berdl.kbase.us/apis/mcp/

1. List Databases

curl -s -X POST \
  -H "Authorization: Bearer $AUTH_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"use_hms": true, "filter_by_namespace": true}' \
  https://hub.berdl.kbase.us/apis/mcp/delta/databases/list

2. List Tables in a Database

curl -s -X POST \
  -H "Authorization: Bearer $AUTH_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"database": "kbase_ke_pangenome", "use_hms": true}' \
  https://hub.berdl.kbase.us/apis/mcp/delta/databases/tables/list

3. Get Table Schema

curl -s -X POST \
  -H "Authorization: Bearer $AUTH_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"database": "kbase_ke_pangenome", "table": "genome"}' \
  https://hub.berdl.kbase.us/apis/mcp/delta/databases/tables/schema

4. Count Rows in a Table

curl -s -X POST \
  -H "Authorization: Bearer $AUTH_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"database": "kbase_ke_pangenome", "table": "genome"}' \
  https://hub.berdl.kbase.us/apis/mcp/delta/tables/count

5. Sample Data from a Table

curl -s -X POST \
  -H "Authorization: Bearer $AUTH_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"database": "kbase_ke_pangenome", "table": "genome", "limit": 5}' \
  https://hub.berdl.kbase.us/apis/mcp/delta/tables/sample

6. Execute SQL Query (Most Flexible)

curl -s -X POST \
  -H "Authorization: Bearer $AUTH_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"query": "SELECT * FROM kbase_ke_pangenome.genome LIMIT 10", "limit": 1000, "offset": 0}' \
  https://hub.berdl.kbase.us/apis/mcp/delta/tables/query

Important Notes:

Use ORDER BY in queries for deterministic pagination
Default limit is 1000 rows
Use offset for pagination through large results
Queries support JOINs, WHERE, GROUP BY, HAVING, aggregations

7. Structured SELECT Query (SQL-injection safe)

curl -s -X POST \
  -H "Authorization: Bearer $AUTH_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "database": "kbase_ke_pangenome",
    "table": "pangenome",
    "columns": [
      {"column": "gtdb_species_clade_id"},
      {"column": "no_genomes"}
    ],
    "order_by": [{"column": "no_genomes", "direction": "DESC"}],
    "limit": 20
  }' \
  https://hub.berdl.kbase.us/apis/mcp/delta/tables/select

Common Query Patterns

Get Species Information

SELECT
  s.GTDB_species,
  s.GTDB_taxonomy,
  p.no_genomes,
  p.no_core,
  p.no_aux_genome,
  p.no_singleton_gene_clusters,
  s.mean_intra_species_ANI,
  s.ANI_circumscription_radius
FROM kbase_ke_pangenome.pangenome p
JOIN kbase_ke_pangenome.gtdb_species_clade s
  ON p.gtdb_species_clade_id = s.gtdb_species_clade_id
WHERE s.GTDB_species LIKE '%Escherichia_coli%'

Get Genomes for a Species

SELECT
  g.genome_id,
  g.ncbi_biosample_id,
  m.checkm_completeness,
  m.checkm_contamination,
  m.genome_size,
  m.gc_percentage
FROM kbase_ke_pangenome.genome g
LEFT JOIN kbase_ke_pangenome.gtdb_metadata m
  ON g.genome_id = m.accession
WHERE g.gtdb_species_clade_id = 's__Escherichia_coli--RS_GCF_000005845.2'
LIMIT 100

Pangenome Statistics

SELECT
  COUNT(*) as total_species,
  AVG(no_genomes) as avg_genomes_per_species,
  AVG(no_core) as avg_core_genes,
  AVG(no_singleton_gene_clusters) as avg_singletons
FROM kbase_ke_pangenome.pangenome

Find Species with Most Genomes

SELECT
  s.GTDB_species,
  p.no_genomes,
  p.no_core,
  p.no_aux_genome,
  s.mean_intra_species_ANI
FROM kbase_ke_pangenome.pangenome p
JOIN kbase_ke_pangenome.gtdb_species_clade s
  ON p.gtdb_species_clade_id = s.gtdb_species_clade_id
ORDER BY p.no_genomes DESC
LIMIT 20

Instructions for Claude

When the user asks to query BERDL or explore pangenome data:

Read the auth token from .env file first
Start with schema exploration if unfamiliar with table structure
Use appropriate endpoint:
- Simple data inspection: Use /sample endpoint
- Counts: Use /count endpoint
- Complex queries: Use /query endpoint with SQL
Format output using | python3 -m json.tool for readable JSON
Handle pagination for large result sets using limit and offset
Join tables when combining data (e.g., genome + metadata, pangenome + species info)
Include ORDER BY in queries for consistent pagination

Table Relationships

Key foreign key relationships:

genome.gtdb_species_clade_id → gtdb_species_clade.gtdb_species_clade_id
genome.gtdb_taxonomy_id → gtdb_taxonomy_r214v1.gtdb_taxonomy_id
genome.genome_id → gtdb_metadata.accession
pangenome.gtdb_species_clade_id → gtdb_species_clade.gtdb_species_clade_id
gene_cluster.gtdb_species_clade_id → gtdb_species_clade.gtdb_species_clade_id
gene_genecluster_junction.gene_cluster_id → gene_cluster.gene_cluster_id
gene_genecluster_junction.gene_id → gene.gene_id
genome_ani.genome1_id → genome.genome_id
genome_ani.genome2_id → genome.genome_id

Error Handling

If you encounter "cannot schedule new futures after shutdown", retry the query
Large table operations may timeout; use pagination with smaller limits
Always validate that the auth token exists before making requests

berdl

SKILL.md