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.

Step 0: Environment Check

Run before anything else:

python scripts/berdl_env.py --check

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 modules/query-patterns.md (universal SQL safety/perf rules) and modules/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), grep both the central archive and per-project memories:

grep -A 20 "^## kbase\.ke_pangenome$" docs/pitfalls.md         # frozen historical archive
grep -l "kbase\.ke_pangenome" projects/*/memories/pitfalls.md  # per-project gotchas (recent)

The central file is the historical archive; per-project memories may contain newer or project-specific gotchas hit during analysis.

Read the appropriate module for database-specific tables, schemas, and query patterns. 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 detected by scripts/detect_berdl_environment.py:

• 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 the appropriate module for the target database (pangenome, biochemistry, etc.)
4. Read cross-database.md if the query spans multiple databases
5. Start with helper discovery if unfamiliar with available databases, tables, or schemas
6. Check row counts with bounded Spark SQL before querying large tables
7. Use Spark SQL through spark.sql(query) or scripts/run_sql.py
8. Run the validation checklist from query-patterns.md before executing any SQL query
9. Handle pagination for large result sets
10. 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 generate module files.

Scripts

The following scripts exist and are referenced by skills. Do not invent script names — only the paths below exist. If you need behavior not covered here, ask the user.

Script	Purpose
scripts/berdl_env.py	Canonical environment check (Step 0 of every BERDL skill)
scripts/berdl_inventory.py	Pretty-printed inventory (tenant / database / table count / sample tables)
scripts/detect_berdl_environment.py	Underlying detector (called by berdl_env.py)
scripts/run_sql.py	Bounded SQL via Spark Connect (--berdl-proxy for off-cluster)
scripts/export_sql.py	SQL → MinIO export (--berdl-proxy for off-cluster)
scripts/get_minio_creds.py	MinIO credential resolver
scripts/configure_mc.sh	MinIO CLI alias setup
scripts/get_spark_session.py	Local drop-in for the BERDL get_spark_session()
scripts/bootstrap_client.sh	Create .venv-berdl and install query packages
scripts/bootstrap_ingest.sh	Install ingest packages on top of .venv-berdl
scripts/ingest_lib.py	JH-side ingest helpers
scripts/ingest_preflight.py	Pre-flight ingest plan printer
scripts/start_pproxy.sh	pproxy startup
scripts/discover_berdl_collections.py	UI snapshot builder
scripts/build_data_cache.py	UI data cache builder

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 follow its instructions to determine whether the issue should be added to the active project's projects/<id>/memories/pitfalls.md.