Observable college degree browsing.

[bmschmidt]

Here's a stab at a 2-million-row in-browser database in Observable; data about US degree recipients counted at the school-year-major level. As I say in the notebook, I've looked at this same set (basically) before with raw arrow and arquero functions--I'm seeing a big (5-10x) speedup here, fast enough to make things like interactive maps clickable.

So, great job guys! The performance here here is everything I was hoping for.

On observation in doing this with observable is that the programming flow to and from Javascript is a little awkward--to keep the network topology straight, I end up writing a lot of dummy JS variables that exist solely to mirror internal duckdb tables. E.g. here's a table that needs to update whenever the all_inst_filters list changes, and then trigger another query:

school_filter_tb = {
  await client.query("DROP TABLE IF EXISTS school_filter")
  return await client.table(`CREATE TABLE school_filter AS SELECT UNITID, INSTNM FROM "characteristics@2" WHERE ${all_inst_filters}`)
}

In svelte I suspect I'll run into some similar issues. I feel like an idiomatic solution will either lead somewhere interesting, or towards somewhere that parts of the vegaverse have already gone.

And doing something like reinserting an observable table back into the database is clunky, as well--I wrote a bunch of WHERE field_col in ("A", "B", "C", "D", "E") where in Python I would have just registered an arrow dataframe inside duckdb and done an INNER JOIN.

[ankoh]

This is awesome, thanks a lot for sharing!

I think you raised an important point.
When gluing DuckDB-Wasm to Vega, I also found myself wrapping the imperative query APIs into reactive helpers to maintain database state on changed input.
You didn't touch this here but this gets even more annoying when replacing pre-calculations that vega has to do internally with database queries as well.
A low-hanging fruit, for example, is the explicit domain computation within DuckDB-Wasm.
So whenever one of your input parameters changes, you'll A) drop the table, B) recreate the table and C) re-evaluate any statistics that you need for axis scaling and so on.

Maybe this justifies thinking of a more reactive caching wrapper for DuckDB-Wasm.
E.g. something like

const rx = new duckdb.ReactiveTables(db);
const table = rx.createTableAs("foo", (props) => `SELECT ... ${..}`);
const cached = table.query((props) =>  `SELECT min(x), max(x), count(distinct x) FROM foo`);

// ...

const somefilter = ...;
await table.evaluate(somefilter); // noop if the same
const domain = cached.get();

[mbostock]

I recommend using Observable’s invalidation promise to clean up the table when the cell is redefined or its inputs change, similar to the cleanup function with React’s useEffect, e.g.,

school_filter_tb = {
  invalidation.then(() => client.query("DROP TABLE school_filter"));
  return client.table(`CREATE TABLE school_filter AS SELECT UNITID, INSTNM FROM "characteristics@2" WHERE ${all_inst_filters}`);
}

[bmschmidt]

That makes sense. These async functions resolve in unpredictable orders, I've found: does returning from inside the invalidation guarantee the DROP completes before the new creation?

school_filter_tb = {
  invalidation.then(() => return client.query("DROP TABLE school_filter"));
  return client.table(`CREATE TABLE school_filter AS SELECT UNITID, INSTNM FROM "characteristics@2" WHERE ${all_inst_filters}`);
}

[mbostock]

No. The old invalidation promise will resolve before the new variable definition is executed, but the runtime doesn’t know about any promises you chain off the invalidation promise. You’d need to have some other global state if you need to await the old definition’s invalidation.

[ankoh]

Interesting, thanks for the pointer.
The Observable client is more the territory of @domoritz , i've not really used Observable too much myself.
The invalidation being sync shouldn't be too problematic.
Albeit this might feel less clean, the drop table statement will always reach the web worker first through the workers event queue even if you just fire-and-forget the async query command.

[ankoh]

A small note on the parquet files here: The observable examples run into CORS issues very quickly with the attachments.
(IIRC observable does not set Access-Control-Allow-Headers: range that would allow us to partially read parquet attachments)
So right now, the notebook lags a bit at the initial load and would be quite a bit snappier if we wouldn't be forced to download all the files at once.

[bmschmidt]

Interesting. I can see a reactive-style language being useful, but I personally have never had the usage of props fully click for me, so I can't offer full comment here.

I can also move this parquet to my own server (part of the problem here is that I sort it after load to speed up queries).

Are there other major headers to facilitate parquet scanning besides:

• Access-Control-Allow-Headers: range
• Access-Control-Allow-Origin: *

---

Quick inchoate thoughts:

As a within-observable solution with some external benefit, there might be some benefit to a chunk-tag that could wrap up a lot of the function logic. Rstudio does this pretty well.

in an ideal world, I want to type:

viewof degrees = duck_table`SELECT * FROM TB_A NATURAL JOIN TB_B WHERE y AND z`;

Where duck_table handles the 'drop_tablefunction,CREATE TABLE`, etc, reactively. It might even also be the state manager in some way.

One thing that would help there which is probably(?) small is simply accessing the postgres query parser through duckdbwasm, so as to get:

1. spans for syntax highlighting in JS.
2. Access to the names of all tables referenced in a query for reactivity.

Is this possible?

[domoritz]

@mbostock would it be possible to support range requests from observable?

[mbostock]

We could definitely add Access-Control-Allow-Headers: range. It’d be swell if you could file an issue here, but if not, I’ll try to remember. https://github.com/observablehq/feedback/issues

[domoritz]

observablehq/feedback#283

[ankoh]

Interesting. I can see a reactive-style language being useful, but I personally have never had the usage of props fully click for me, so I can't offer full comment here.

Just wording, replace props with parameters.
The bottom line is that a reactive wrapper around DuckDB could compare parameters to check if a query has to be re-evaluated by shallow comparing props similar to React.
But this was just a morning thought, I haven't thought this through yet.
Less experimental would be a general support for reactive initialization, though.
I'm thinking of a useDB React hook or useConnection, those could go a long way.

Are there other major headers to facilitate parquet scanning besides

Those should be the important ones. But this is more trial and error.
Without Access-Control-Allow-Headers: range, the CORS pre-flight check for the range request will fail.

One thing that would help there which is probably(?) small is simply accessing the postgres query parser through duckdbwasm, so as to get
spans for syntax highlighting in JS.

We have preliminary support for this here https://shell.duckdb.org/docs/classes/index.AsyncDuckDB.html#tokenize
https://github.com/duckdb/duckdb-wasm/blob/6d99d0b195ed5952e9264203dd53e6500ef06cfb/packages/duckdb-wasm/test/tokenizer.test.ts
But I havent tested this extensively yet. (the wasm shell highlights that way, though)

Access to the names of all tables referenced in a query for reactivity.

I agree this would be cool!
Although you already get quite far with DESCRIBE for existing tables, probably.

But I like the idea to have this on a plain SQL text for upfront analysis.
E.g. a tabular version of EXPLAIN https://duckdb.org/docs/dev/profiling