Filter tables fetched by Retool from BigQuery?

Queries and Resources
1

Hi,

We are heavy users of BigQuery and so we have a lot of tmp[1] tables generated by BigQuery: Retool seems to import these tables and so we've hit the "first 3000 tables" limit which is preventing us from utilising Retool's features that rely on Retool knowing that a table exists. For example, I can query the table(s) in SQL mode but I can't use the GUI mode.

I'm checking if there's a workaround at our end (to prevent the Retool service account from listing any tmp table) but it would be helpful in the meantime to know if Retool can filter out tmp tables. I checked Resource settings and I do not see an option so I don't think it is possible but it would be great to know for sure (or even, to see if the option can be added).

Thank you,

[1] Even with 30 day expiry enabled, we're far exceeding the 3000 limit.

2

As a follow up: we have tried to introduce an exclusion so that the Retool service account cannot access the tmp dataset but we're now experiencing an error when trying to refresh the list:

There was an error when fetching the schema: Access Denied: Table project:tmp.INFORMATION_SCHEMA.COLUMNS: User does not have permission to query table project:tmp.INFORMATION_SCHEMA.COLUMNS, or perhaps it does not exist in location US.

We're trying to rule out whether or not this is a caching issue (e.g: Retool has cached the existence of tmp and can only refresh if it can access tmp) or if this indicates an issue with the workaround.

Does Retool publish any information about how it finds the 3,000 tables: is it a query to the BigQuery API ordered by creation date?

Thank you,

3

Hey @sam_ipinfo!

Thanks for surfacing this. Right now, Retool fetches a list of schema from INFORMATION_SCHEMA.SCHEMATA and then, for each of those schemas, fetches table data from INFORMATION_SCHEMA.COLUMNS.

I could be missing something, but I'm not seeing a way to limit access to either view through BigQuery permissions so, unfortunately, I don't have a workaround for you at the moment. I've surfaced this with the dev team though to look into and if they implement a way to better support this from the Retool side, or if there is something else you can do I'll let you know.

1 Like