SQL playground

Run SQL against the latest bioc-on-ice release in your browser with DuckDB-Wasm. Nothing to install.

Edit the query and press Run. It runs in your browser with DuckDB-Wasm, against latest/ attached as bioc (and selected with USE bioc, so annotation.gene works as well as bioc.annotation.gene). The first run downloads DuckDB itself, a few MB.

Queries that filter and use LIMIT are fast, because DuckDB fetches only the parts of each Parquet file it needs. Full scans of the big tables (raw.icite__metadata is 12 GB, annotation.icite__citation 8 GB) are slow and can run out of browser memory. For those, use DuckDB on your own machine. To browse tables and column docs, use the Explorer.

duckdb = import("https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm@1.33.1-dev57.0/+esm")

conn = {
  const bundle = await duckdb.selectBundle(duckdb.getJsDelivrBundles());
  const workerUrl = URL.createObjectURL(
    new Blob([`importScripts("${bundle.mainWorker}");`], { type: "text/javascript" })
  );
  const db = new duckdb.AsyncDuckDB(new duckdb.VoidLogger(), new Worker(workerUrl));
  await db.instantiate(bundle.mainModule, bundle.pthreadWorker);
  // Read byte ranges only. By default DuckDB-Wasm downloads each whole
  // Parquet file before querying it. All three flags are needed (omicidx
  // found leaving forceFullHTTPReads unset breaks every open).
  await db.open({
    filesystem: { reliableHeadRequests: true, allowFullHTTPReads: false, forceFullHTTPReads: false }
  });
  const c = await db.connect();
  await c.query("ATTACH 'https://data.bioconice.cancerdatasci.org/latest/bioconice.duckdb' AS bioc (READ_ONLY)");
  await c.query("USE bioc");
  return c;
}

viewof sql = Inputs.textarea({
  label: "SQL",
  value: `SELECT gene_id, symbol, source, gene_type
FROM annotation.gene
WHERE taxon_id = 9606 AND symbol = 'TP53' AND valid_to IS NULL;`,
  rows: 8,
  width: "100%",
  monospace: true,
  submit: "Run"
})

result = {
  const t0 = performance.now();
  try {
    const table = await conn.query(sql);
    // int64 columns arrive as BigInt, which the table view can't format.
    const rows = table.toArray().map((r) =>
      Object.fromEntries(Object.entries(r.toJSON()).map(([k, v]) =>
        [k, typeof v === "bigint" ? Number(v) : v]))
    );
    return { rows, seconds: (performance.now() - t0) / 1000 };
  } catch (e) {
    return { error: e.message };
  }
}

result.error
  ? html`<div class="alert alert-danger" role="alert"><pre class="mb-0">${result.error}</pre></div>`
  : html`<p class="small text-muted" id="playground-status">${result.rows.length} rows in ${result.seconds.toFixed(1)} s ·
      <a href="${window.bioconiceShellUrl(sql)}" target="_blank" rel="noopener">Open in DuckDB Shell ↗</a></p>`

Inputs.table(result.rows ?? [], { rows: 15 })

Queries to try

Copy one into the box, or open it in the DuckDB Shell.

-- Every table in the release, by schema
SELECT schema_name, view_name
FROM duckdb_views()
WHERE database_name = 'bioc' AND NOT internal
ORDER BY 1, 2;

-- Mouse genes by source
SELECT source, count(*) AS genes
FROM annotation.gene
WHERE taxon_id = 10090 AND valid_to IS NULL
GROUP BY source;

-- The provenance of this release
SELECT release, published_at, git_sha
FROM read_json('https://data.bioconice.cancerdatasci.org/latest/manifest.json');

-- One BugSigDB signature as a list of member names
SELECT bsdb_id, list(taxon_name ORDER BY member_index) AS members
FROM annotation.signature_taxon
WHERE bsdb_id = 'bsdb:83/1/1'
GROUP BY bsdb_id;