Using read_json (and other DuckDB extensions)

[rndmcnlly]

We are trying to use loadJSON in the wasm setting, but get an exception that seems ultimately related to DuckDB extension not being installed.

Uncaught (in promise) Error: Catalog Error: Function with name "read_json" is not in the catalog, but it exists in the json extension.

To install and load the extension, run:
INSTALL json;
LOAD json;
    at Go.runQuery (duckdb-browser-eh.worker.js:11:17201)
    at lc.onMessage (duckdb-browser-eh.worker.js:10:55670)
    at Qu.globalThis.onmessage (duckdb-browser-eh.worker.js:24:10976)

Questions on two levels:

• What is the right way to load bulk JSON data using Mosaic's wasm connector? (We are currently using building query strings to insert a batch of a few hundred rows at a time, but we'd like to just use the handy loadJSON function documented in the Mosaic SQL API.)
• What is the right way to load DuckDB extensions beyond those used by Mosaic itself? (Browsing the DuckDB docs, SQLite Scanner extension also looks relevant to our application.)

[jheer]

You should be able to call the coordinator exec method to run one or more queries that do not return table values.

I have not tested with the wasm connector, but one example might be:

await coordinator().exec(`INSTALL json; LOAD json;`);

We already perform similar calls internally in the node JS DuckDB server. I’m just unsure at the moment of what is supported (versus built-in) within DuckDB-WASM.

For JSON loading, the built-in methods for bulk loading should almost certainly be preferred for any non-trivially sized datasets.

[rndmcnlly]

When we try to INSTALL extensions in wasm, we get this message:

Uncaught (in promise) Error: IO Error: Can't find the home directory at '/home/web_user'
Specify a home directory using the SET home_directory='/path/to/dir' option.
    at Go.runQuery (duckdb-browser-eh.worker.js:11:17201)
    at lc.onMessage (duckdb-browser-eh.worker.js:10:55670)
    at Qu.globalThis.onmessage (duckdb-browser-eh.worker.js:24:10976)

We also get a similar result when executing INSTALL json; on https://shell.duckdb.org/

[rndmcnlly]

Dynamic extension loading seems to be an active issue in the upstream duckdb-wasm project, but I don't have enough familiarity with the project to see the implications for Mosaic: duckdb/duckdb-wasm#1202 Maybe Mosaic could be build with a slightly larger set of extensions pre-baked, or maybe it could offer and API for pushing extensions built by others into the virtual filesystem (unclear who builds/hosts them).

[rndmcnlly]

Our workaround has been to convert our (already-in-memory) JSON data into an Arrow table and then insert the Arrow table in one shot like this:

import {tableFromJSON, tableToIPC} from "https://cdn.jsdelivr.net/npm/[email protected]/+esm";
...
const table = tableFromJSON(jsonData);
const ipc = tableToIPC(table);
vg.coordinator().databaseConnector().con.insertArrowFromIPCStream(ipc, { name: "arrow_table" });