Importing @google-cloud/bigquery fails in JS snippets

[benminer]

@observablehq/framework: 1.9.0
NodeJS: 20.15.0

I got a static data loader working with BigQuery:

// data/bigquery.ts
import { BigQuery } from "@google-cloud/bigquery";

const bigquery = new BigQuery();

export const runQuery = async (query) => {
  const [rows] = await bigquery.query(query);
  return rows;
};

This works fine loading static data, like countries, from the database:

// data/countries.json.ts
import { runQuery } from "../bigquery";

process.stdout.write(
  JSON.stringify(
    await runQuery(`
SELECT country FROM $my-bg-table
`))
);

However, in the page I am trying to create, it needs to allow a viewer to pick a country from a dropdown. The dropdown works just fine when running, but I can't figure out how to do a dynamic query using my new bigquery.ts file. When I import in a "```js" tag, I get TypeError: Failed to resolve module specifier 'data/bigquery'. Running this with a `ts` tag simply displays the code, but doesn't run anything. Installing`@google-cloud/bigquery` via `npm i` also throws: `TypeError: Failed to resolve module specifier `@google-cloud/bigquery` - which doesn't make sense, since it's installed. Using the `npm:` prefix throws CJS bundle errors from esbuild.

// my dashboard.md
```js
const countries = await FileAttachment("data/countries.json").json();
\```

```js
const countrySelect = Inputs.select(
  countries.map((obj) => obj.country),
  {
    label: "Select Country",
    value: "ES",
  }
);
display(countrySelect); // this works!
\```

```js
import { runQuery } from "data/bigquery"; // this throws
\```

I've tried importing the BigQuery library directly in a js block, but that leads to bundle errors from Rollup:
Failed to bundle using Rollup v2.79.1: the file imports a not supported node.js built-in module "fs".

```js
import { BigQuery } from "npm:@google-cloud/bigquery"; // throws
import { BigQuery } from "@google-cloud/bigquery"; // throws
const { BigQuery } = await import('@google-cloud/bigquery') // throws
const bigquery = new BigQuery();
const data = await bigquery.query(`
SELECT country, ymd, hour
FROM \`my-bg-table\`
WHERE ymd >= "2024-06-20"
and country= ${countrySelect}
and hour is not null
  and region is null
order by ymd, hour
`);
display(data);
\```

I understand the bundle errors here, but there has got to be a way to run a dynamic BigQuery query in a snippet, right? I am new to Observable overall, so there may be something obvious here. Is there anyway to use the built in sql tag with connected data sources in my account, so I could just do a query as I would in a Notebook?

Any help is appreciated, thanks.

[Fil]

The module you are trying to import seems to work only with node.js, and there is no equivalent of "fs" for the browser. (I think this explains why Framework's attempts to massage the code with rollup fail.)

Our model with data loaders is that you handle all of this in "data snapshots" that download the data once (authenticating properly) and then present it statically to the browser. You can still operate queries again this data snapshot, using sql, but the browser doesn't get to talk to bigquery directly.

The recommendation (if there is a lot of data) is to save the snapshot in the efficient parquet format. If this still makes files too large, you can consider sharding the data into several static data loaders, for example one for each country or region (or, if you have a predominant region, one file for the large region and one file for the "rest of the world").

If you really must use "live bigquery", the official way to access BigQuery from the browser is through the http rest api—with a helper such as gapi to help with authentication (ref). But this implies that your users will have to authenticate against google—with implications in terms of security (and billing).

An alternative is to have a proxy with your authentication credits, that mediates the queries between the browser and the database. But this is also "live code" that you need to host somewhere, maintain, and also has security implications.