berdl

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

BERDL Data Lakehouse Query Skill

Query the KBase BERDL Data Lakehouse containing pangenome and biochemistry data.

Compatibility Modes

• Full BERDL mode: KBase auth, proxy/session setup, and helper utilities available.
• Limited mode: if helper scripts are missing, run equivalent Spark SQL directly in the active environment.

Step 0: Environment Check

Run before anything else:

if [ -f scripts/berdl_env.py ]; then python3 scripts/berdl_env.py --check; else echo "berdl_env.py not found; perform manual environment checks"; fi

If it exits non-zero, follow the printed next steps exactly. Do not proceed with any query until this passes. The location reported (on-cluster vs off-cluster) selects the execution path for every subsequent step.

Discovery (live)

Run the canonical discovery flow before any query. The set of accessible databases depends on the authenticated user; do not assume from prose.

import berdl_notebook_utils

databases = berdl_notebook_utils.get_databases(return_json=False)            # → list[str]
tables    = berdl_notebook_utils.get_tables("DATABASE", return_json=False)   # → list[str]
schema    = berdl_notebook_utils.get_table_schema("DATABASE", "TABLE", detailed=True, return_json=False)  # → list[dict] with name, dataType, description, isPartition, ...

# For table-level COMMENT and TBLPROPERTIES:
spark.sql("DESCRIBE EXTENDED DATABASE.TABLE").toPandas()

Discovery returns JSON by default. Always pass return_json=False. Without it you get a JSON string, and in, iteration, and display() will silently misbehave.

For pattern guidance, read references/query-patterns.md (universal SQL safety/perf rules) and references/cross-database.md (cross-DB join recipes).

For curated database-specific gotchas (NULL conventions, ID formats, missing-column workarounds, JOIN-key surprises, large-table guards), check repo pitfall memory and capture new findings there:

• /memories/repo/berdl-pitfalls.md

Read query-patterns.md before writing any SQL — it contains mandatory safety rules and performance guidance.

Query Execution

Choose the query path from the environment check result (on-cluster vs off-cluster):

• On-cluster / BERDL JupyterHub: use the active Spark session directly. Do not add --berdl-proxy.
• Off-cluster / local machine: use /berdl-query or the local scripts/run_sql.py --berdl-proxy wrapper.

On-cluster, execute actual SQL with native Spark SQL:

df = spark.sql("SELECT * FROM database.table LIMIT 10")

Off-cluster, use the bounded wrapper when appropriate:

uv run scripts/run_sql.py --berdl-proxy --query "SELECT * FROM database.table ORDER BY id" --limit 500 --output /tmp/query_result.json

Use SQL for counts and samples:

SELECT COUNT(*) AS n FROM database.table;
SELECT * FROM database.table LIMIT 5;

Avoid BERDL MCP query operations for SQL execution, including mcp_query_table, mcp_select_table, mcp_count_table, mcp_sample_table, and REST /delta/tables/query.

Common Patterns

Pagination

SELECT * FROM database.table
ORDER BY id
LIMIT 1000 OFFSET 0  -- First page

SELECT * FROM database.table
ORDER BY id
LIMIT 1000 OFFSET 1000  -- Second page

Always use ORDER BY for deterministic pagination.

Output Formatting

... | python3 -m json.tool

Instructions for Claude

1. Read auth token from .env first
2. Read query-patterns.md — contains mandatory validation checklist and performance tiers
3. Read cross-database.md if the query spans multiple databases
4. Start with helper discovery if unfamiliar with available databases, tables, or schemas
5. Check row counts with bounded Spark SQL before querying large tables
6. Use Spark SQL through spark.sql(query) or scripts/run_sql.py
7. Run the validation checklist from query-patterns.md before executing any SQL query
8. Handle pagination for large result sets
9. Include ORDER BY in queries for consistent pagination

Query Validation (mandatory)

Before executing any query, verify against the checklist in query-patterns.md:

• Partitioned column filter present?
• Large tables guarded?
• Results bounded?
• Types cast correctly?
• Species IDs quoted?
• Annotation NULLs filtered?
• ORDER BY for pagination?
• Correct JOIN keys?

Performance Tiers

Expected Result Size	Strategy
< 100K rows	Bounded Spark SQL, .toPandas() OK
100K – 10M rows	Filter + aggregate in SQL first
> 10M rows	PySpark on JupyterHub only

Error Handling

Error	Meaning	User-facing message
504 Gateway Timeout	Query took too long	Simplify query, add filters, switch to JupyterHub
524 Origin Timeout	Server didn't respond	Retry after a few seconds
503 "cannot schedule new futures"	Spark executor restarting	Wait 30s, retry
Empty response	Query failed silently	Check query syntax, verify table exists
Auth errors	Invalid or expired token	Validate KBASE_AUTH_TOKEN in .env
S3 access denied / 403 / AccessControlException / Token denied	User lacks permission to this tenant's data	Never surface raw S3/token error text. Tell the user: "You don't have access to <database>.<table> (tenant: <tenant>). To request access, use the BERDL Tenant Browser."

Rule of thumb: Use spark.sql() directly in JupyterHub for complex, long-running, or large-result queries.

Access errors are expected when a user explores databases outside their tenant membership. Treat them as a permissions prompt, not a system failure — translate to a plain message and point to the Tenant Browser.

Adding New Databases

Use the /berdl-discover skill to introspect new databases and update notes in the local references/ directory when reusable query guidance emerges.

Script Availability

This repository may not include BERDL helper scripts locally. Use these rules:

• If local helper scripts exist, you may use them.
• If scripts are missing, run equivalent commands in the active Spark/Jupyter environment.
• For off-cluster helpers and proxy mechanics, consult berdl-query and its local references.

Pitfall Detection

When you encounter errors, unexpected results, retry cycles, performance issues, or data surprises during this task, follow the pitfall-capture protocol. Read .claude/skills/pitfall-capture/SKILL.md and capture reusable notes in repo memory.