How to flatten with a table wildcard in BigQuery?

1
votes

We recently switched to a standard setup with tables that are labeled by month (foo_2015_05) with a common format that contains a repeated field. Originally when I created a view based on one, large table, it forces me to FLATTEN the table on the repeated field.

When trying to update my view to account for the monthly tables, I can't seem to have both a table wildcard AND flatten at the same time.

SELECT blah
FROM FLATTEN(TABLE_QUERY(dataset, "tableid CONTAINS 'foo_'"), repeated_field)

gives me the following error: Table name cannot be resolved: dataset name is missing

Am I missing something? Or is there a work-around for this?

1
google-bigquery

1 Answers

5
votes

I believe the issue is that FLATTEN does not work a union of tables, which TABLE_QUERY is eventually rewritten to, if the TABLE_QUERY evaluates to multiple tables. A workaround is to wrap the TABLE_QUERY in a subselect, making the FLATTEN operate over a single source, the subselect.

SELECT blah
FROM FLATTEN(
  (SELECT * FROM TABLE_QUERY(dataset, "tableid CONTAINS 'foo_'")), 
  repeated_field)