Bigquery Public

Input Formats

Workflow

When the user asks to query BigQuery public data:

1. Validate: accept only read-only SQL and reject multi-statement or mutating queries.
2. Authenticate: try Python ADC, then fall back to logged-in bq CLI.
3. Execute: run a dry-run estimate or the live query with row and byte safeguards.
4. Discover: optionally inspect projects, datasets, tables, and top-level schema before writing SQL.
5. Generate: write report.md, result.json, tables/results.csv, and a reproducibility bundle.

CLI Reference

# Inline SQL
python skills/bigquery-public/bigquery_public.py \
  --query "SELECT corpus, word, word_count FROM \`bigquery-public-data.samples.shakespeare\` LIMIT 5" \
  --output /tmp/bigquery_public

# SQL file
python skills/bigquery-public/bigquery_public.py \
  --input path/to/query.sql \
  --output /tmp/bigquery_public

# Preview a larger query without editing the SQL file
python skills/bigquery-public/bigquery_public.py \
  --input path/to/query.sql \
  --preview 20 \
  --output /tmp/bigquery_preview

# Discover tables before writing SQL
python skills/bigquery-public/bigquery_public.py \
  --list-tables isb-cgc.TCGA_bioclin_v0 \
  --output /tmp/bigquery_tables

# Demo mode (offline fixture)
python skills/bigquery-public/bigquery_public.py --demo --output /tmp/bigquery_demo

# Via ClawBio runner
python clawbio.py run bigquery --demo
python clawbio.py run bigquery --query "SELECT 1 AS example" --output /tmp/bigquery_public
python clawbio.py run bigquery --describe isb-cgc.TCGA_bioclin_v0.Clinical --output /tmp/bigquery_schema

Demo

To verify the skill works:

python clawbio.py run bigquery --demo

Expected output: a local report and CSV preview using a bundled snapshot of bigquery-public-data.samples.shakespeare.

Algorithm / Methodology

1. Normalize query: strip comments, mask literals, reject non-read-only SQL.
2. Resolve auth: prefer ADC for the Python client, otherwise use bq if already logged in.
3. Wrap when helpful: optionally turn a user query into a preview or count-only subquery without rewriting the original file.
4. Run safely: apply --max-bytes-billed, --max-rows, and optional dry-run.
5. Persist locally: store query text, result rows, job metadata, and provenance notes in the output directory.

Key parameters:

• Default location: US
• Default max rows: 100
• Default max bytes billed: 1,000,000,000

Example Queries

• "Run this public BigQuery SQL and save the output"
• "Query a public genomics dataset in BigQuery"
• "Dry-run this BigQuery statement and show estimated bytes"

Output Structure

output_directory/
├── report.md
├── result.json
├── tables/
│   └── results.csv
└── reproducibility/
    ├── commands.sh
    ├── environment.yml
    ├── job_metadata.json
    ├── provenance.json
    └── query.sql

Dependencies

Required:

• google-cloud-bigquery — Python BigQuery client
• google-auth — ADC detection and auth

Optional:

• bq CLI — fallback backend when ADC is missing

Safety

• Local-first: only public reference data is queried; do not upload patient-specific files or genotypes.
• Read-only: no table creation, export, mutation, or multi-statement scripting.
• Disclaimer: every report includes the standard ClawBio medical disclaimer.
• Cost control: dry-run and billed-byte caps are enabled by default.

Integration with Bio Orchestrator

This v1 skill is intended for explicit invocation through clawbio.py run bigquery. Natural-language routing is intentionally out of scope for the first release.

Citations

• BigQuery public datasets
• BigQuery authentication