Add support for including detailed information in BigQuery.listTables

[ebyhr]

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

Metadata handling is too slow when getting table definitions in bulk.

Describe the solution you'd like

Add a new TableListOption that allows specifying fields so that BigQuery.listTables returns detailed information.

Describe alternatives you've considered

I already tried getting table definitions in parallel on client side, but it's still slow.
Another solution is introducing cache, but we want to avoid as much as possible.

Additional context

trinodb/trino#4580 & trinodb/trino#10741

[ebyhr]

@anoopj What do you think about it?

[anoopj]

This will require API changes in BigQuery v2 API. If there are many tables, could we run an INFORMATION_SCHEMA query instead?

[ebyhr]

We've observed intermittent SLA failure when using com.google.cloud.bigquery.BigQuery#query on Trino CI. Switching to INFORMATION_SCHEMA doesn't increase such failure possiblity?

[anoopj]

This is a BigQuery SQL query - so it's capable of processing very large amounts of data. The SLO failures you may be seeing may be related to capacity allocated to your reservation (if you're using reservations). On-demand does not guarantee specific latencies since it uses a shared resource pool.

The alternative proposed here (ie sending detailed info on tables.list API) may be risky. BigQuery has no limits on the number of tables in a dataset, so even with pagination, it will be a lot of data returned.

[ebyhr]

I'm trying to use INFORMATION_SCHEMA now. Are there helper methods converting string type names to BigQuery type in this SDK? I can convert easily in case of simple types, but it looks little hard in case of complex types having sub fields.

[anoopj]

I'm not aware of any helper methods. @PhongChuong do you know?

[ebyhr]

I have another question. I wanted to use TABLE_OPTIONS for table comments, but the
value is quoted with unknown rule. https://cloud.google.com/bigquery/docs/information-schema-table-options. Can you share the quotation rule and add helper method to this SDK if possible?

+--------------+
| option_value |
+--------------+
| "test data"  |
| "test data"  |
+--------------+