Unrecognized name 'column' when using _TABLE_SUFFIX in BigQuery

Question

I have the following BigQuery dataset:

A set of tables with name keywords_ab, keywords_ac, ..., keywords_zz.

Another set of tables with name keywords_different_schema_ab, keywords_different_schema_ac,...

These two set of tables have a different schema. I would like to run a query on the first set of tables, which contain a column named term which is not contained in keywords_different_schema_* instead.

I was normally running these kind of queries using the syntax FROM 'keywords_*' but this is giving me a Unrecognized name 'term' of course because the term column is not part of all the keywords_different_schema_* tables.

I have also tried to put in my query something like LENGTH(_TABLE_SUFFIX) = 2 but it seems not to work fine. Is there a way to have the wildcard working for this case? Or should I really do a UNION selecting manually all the keywords I am interested in ?

Answer 1

It turns out there is a way to solve this problem without having to rename the tables (even though it's quite a hacky way).

Apparently, BigQuery is looking at the last table you created that matches the wildcard.

What I did was to create a new table called keywords_dummy and giving them the UNION of all keywords and keywords_different_schema attributes.

At that point, my query SELECT term FROM 'keywords_*' WHERE LENGTH(_table_suffix)=2 worked since BigQuery was checking this keywords_dummy table that has the term column.