bigquery: storage write api support for streaming to tens of thousands of tables easily (in a single dataset)

[johnmck24]

Is your feature request related to a problem? Please describe.
We are inserting ~20,000 rows per second to ~200,000 different tables (spread relatively equally). Each table is used to store a user activity on our service (they are daily-partitioned using a custom timestamp column, and have specific settings like partition expiration that differs between users/tables).

We are experimenting migrating from the legacy .insertAll streaming insert API to the new storage write API, and the new API doesn't seem to be designed for mass-inserting to many different tables. In our case, this would mean opening tens of thousands of streams for a small write throughput on each, and spreading those over hundreds of connections. Aside from resulting in a very complex custom load-balanced implementation, this just doesn't seem to be a fit for a many-tables streaming insertion scenario, and won't scale as we grow.

Describe the solution you'd like
Ideally, we would want to use a stream to insert in different tables in a dataset (or any dataset), the same way the legacy .insertAll API works where an HTTP/2 stream is used to POST rows to any table.

Describe alternatives you've considered
Aside from sticking with the legacy .insertAll streaming insert API, we don't see any non-legacy alternatives.

Additional context
Our user base is growing rapidly, so any workaround that would work for 200,000 tables but not 500,000 tables is a no-go for us.

[shollyman]

We've been looking into a multiplexing feature for this use case with the write API. One of the interesting complexities is schema management. You mention there's a variety of partitioning/expiration settings, but what's the cardinality on schema diversity? For the 200k tables, are we dealing with 200k distinct schemas? Does a single writer tend to write messages of the same schema to a set of tables, or would a single writer be responsible for a both many tables and schemas?

[shollyman]

cc @yirutang

[johnmck24]

@shollyman In our case, all tables have the same schema and the same partitioning window (daily), only partition expiration varies between tables.

We are doing this horizontal split (vs. a single massive table) to:

• save/guarantee costs (clustering on some sort of user id didn't feel safe enough cost-wise), and we would lose the savings of long-term storage.
• get per-user automated rows expiration/deletion (via partition expiration).
• other minor reasons like the ability to delete all data belonging to a user instantly (deleting a table works, but a DELETE statement inside the streaming buffer won't work).

The legacy streaming insert API really fitted our use case perfectly, so ideally for us the same type of approach would be ideal (not having to deal with connections/streams ourselves). I do understand the issues with this combined with the use of protobuf plus not wanting to send the schema and table path for every insert.

Happy to provide additional information if needed.

[shollyman]

Marking this as a P3 feature request for now in the client. We need support from the backend to move forward on this, hence the lower priority.

[yirutang]

@GaoleMeng

[derekperkins]

This is an issue for us too. We have identical schema datasets per customer and are still using legacy streaming

[shollyman]

Happily closing this request. With https://github.com/googleapis/google-cloud-go/releases/tag/bigquery/v1.51.0, you can leverage connection multiplexing to share a single network connection between multiple/many default stream writers.

See the package documentation for more information:
https://pkg.go.dev/cloud.google.com/go/bigquery/storage/managedwriter#hdr-Connection_Sharing__Multiplexing_

If you have feedback about the behavior of the functionality, please open new issues. In particularly, load balancing is likely an area that will need some level of iteration, but concrete feedback here would be helpful.