cached bigquery results for a public notebook

[sdstrowes]

Is your feature request related to a problem? Please describe.

I'd like to easily share notebooks that have queried a bigquery dataset, and for the notebook to retain the results of those queries once published.

I can edit a notebook privately using my GCP credentials, which are tied to a GCP service account and not for sharing. When I publish, observable intentionally breaks the database part, rendering some of my notebooks much less useful for the public without manual intervention. (I totally understand why.)

Our data in bigquery is available to the world, but users themselves must have an account and funds in order to query it. So this isn't a use-case where the database is considered fully free to use (like the public datasets); if the database connection were to ever be active, users would have to prop up their own service account.

Describe the solution you'd like

I'd like the ability to publish with the locally cached results of completed query/queries when I press the button. That could be all results without question, or selectively across each connection or query.

It'd be awesome if those results were transparently uploaded as a file with the notebook when published, and it was clear that this was a cached result set (rather errors on the DatabaseClient()/query() calls).

Describe alternatives you've considered

I can manually save and upload query results as a file for the published version, or I guess individual users can set up their own credentials to fork and access the bigquery datasets directly.

Manually saving and uploading is definitely do-able, but more ideally I'd like to show the query and the cached results as part of the workflow.

[tomlarkworthy]

I have done it.

Encrypt the service account credentials with a password and instantiate a Google API client to perform BQ queries client side

Use Firebase Storage and allow anonymous read access to the cache (no login), and allow only the service account user to write to the cache.

All these pieces have notebooks and here is a tutorial on how to assemble them
https://observablehq.com/@endpointservices/cache-bigquery