CODEWITHSUNDEEP
google-bigquery
foxwendy
foxwendy

Reputation: 2929

Column name not found

I ran new query to overwrite a set of existing tables. The table schema changed as one of the expected results. So, as of the schema, one new column was added and two others were deleted. Suppose the one column added is named A

After a couple of minutes, when I query :

select A
from 'table_*'
WHERE _TABLE_SUFFIX = '20170831'

I had error: Name A not found

But this query just worked:

select A
from 'table_20170831'

Just wondering why. Because I really need to query for a longer period of time by using _TABLE_SUFFIX.....

Upvotes: 1

Views: 2915

Answers (2)

Elliott Brossard
Elliott Brossard

Reputation: 33765

It's because the * matches a table that doesn't contain the newly added columns, and the schema of the wildcard table comes from the "latest" table based on its creation time. For example, you may have:

  • table_20170831: contains two new columns
  • table_foo: does not contain two new columns

In this case the * will match the second table. To fix this, use a longer prefix, such as:

SELECT *
FROM `table_2017*`
WHERE _TABLE_SUFFIX >= '0831';

For more reading, refer to the documentation on the schema used for evaluation of wildcard tables.

Upvotes: 1

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

Just wondering why

The reason why you are getting this error is because the schema is being identified by earliest available sharded table - so in your case it means that in earliest table (table_*) there is/was no field named A

To workaround this - I would recommend creating dummy table with lowest date and with schema that consists all fields that you plan to query

Upvotes: 2

Related Questions