How to get column names (and types) for a table

[will-moore]

I'm working on an app where users can load a csv table of their choosing, and then add various charts. E.g. plot one number column against another.

I need a way to determine what columns are in the table, and whether they contain numbers or strings.

I was hoping to be able to simply query the DuckDB table with DESCRIBE (https://duckdb.org/docs/stable/guides/meta/describe.html), but trying this as below didn't give me anything. So I'm clearly doing this wrong...

Thanks

import { coordinator, Selection, DuckDBWASMConnector } from '@uwdata/mosaic-core';
import { loadCSV } from '@uwdata/mosaic-sql';
import * as vg from '@uwdata/vgplot';

const wasm = new DuckDBWASMConnector({ log: false });
coordinator().databaseConnector(wasm);

await vg.coordinator().exec([
  loadCSV("my_table", `https://raw.githubusercontent.com/will-moore/ome2024-ngff-challenge/refs/heads/biofile_finder_csvs/samples/idr0010_images_bff.csv`)
]);

// Trying to get table columns, but this returns empty array...
wasm.query("DESCRIBE my_table").then(res => console.log("describe my_table", res));

[domoritz]

Not sure why the data is not ready yet there but that seems to be outside of Mosaic.

Rather than asking the connector, you can query the coordinator: https://idl.uw.edu/mosaic/api/core/coordinator.html#query.

And if you are building a client for example with https://idl.uw.edu/mosaic/web-clients/, you can get a summary of the data in prepare or get the description in 'fieldInfo'.

[will-moore]

Thanks, but if I do coordinator.query() like this:

const wasm = new DuckDBWASMConnector({ log: false });
coordinator().databaseConnector(wasm);
await vg.coordinator().exec([
  loadCSV("my_table", `https://raw.githubusercontent.com/will-moore/ome2024-ngff-challenge/refs/heads/biofile_finder_csvs/samples/idr0010_images_bff.csv`)
]);

coordinator.query("DESCRIBE my_table").then(res => console.log("describe my_table", res));

I get Uncaught TypeError: coordinator.query is not a function

Then I tied the client...

let client = makeClient({
      coordinator,
      selection,
      prepare: async () => {
        // Preparation work before the client starts.
        // Here we get the total number of rows in the table.
        let result = await coordinator.query(
          Query.from(tableName).select({ count: count() })
        );
        totalCount = result.get(0).count;
        console.log("totalCount", totalCount);
      },

      fieldInfo: async (info) => {
        // Return information about the fields in the table.
        console.log("fieldInfo", info);
      }
    });

But this gave me:

make-client.ts:41 Uncaught TypeError: coordinator2.connect is not a function

So I seem to be working with the wrong objects or doing something strange here?

My code (one commit) at will-moore/parade-crossfilter@6fe808c is deployed at
https://deploy-preview-58--parade-crossfilter.netlify.app/

[will-moore]

Ah - I worked out that I needed to call coordinator() first...
based on the example from https://github.com/uwdata/mosaic/blob/main/packages/examples/svelte-example/src/lib/Count.svelte

const coord = coordinator();

let client = makeClient({
  coordinator: coord,
  selection,
  prepare: async () => {
    // Preparation work before the client starts.
    // Here we get the total number of rows in the table.
    let result = await coord.query(
      Query.from(TABLE_NAME).select({ count: count() })
    );
    let totalCount = result.get(0).count;
    console.log("totalCount", totalCount);
    document.getElementById("totalCount").innerText = totalCount;
  },

  fieldInfo: async (info) => {
    // Return information about the fields in the table.
    console.log("fieldInfo", info);
  },

...
})

However, I don't see fieldInfo() being called at-all, so I'm still not able to get a handle on the table columns (although it did occur to me that I could just parse the table html to find the table columns)!

[domoritz]

makeClient doesn't use fieldInfo. Instead, use prepare.

[will-moore]

Ah, thanks.
I found that this works great:

prepare: async () => {
    coord.query("describe " + TABLE_NAME).then((data) => {
      const col_info = data.toArray();
    });