CODEWITHSUNDEEP
google-bigquery
user2820173
user2820173

Reputation: 328

bigquery wildcard table query not allowing to select some fields

At first I had sharded table TableA_YYMMDD with fields ColumnA, ColumnB, something like TableA_20200803, TableA_20200802 etc. Then a new table TableA_B_YYMMDD appeared, which had only ColumnA. How to query TableA only? I tried this:

SELECT ColumnA, ColumnB
FROM `TableA_*`
WHERE _TABLE_SUFFIX IN ('20200803')

and even though it calculates costs correctly, but is throwing an error, that ColumnB does not exists.

Upvotes: 1

Views: 524

Answers (1)

Hua Zhang
Hua Zhang

Reputation: 1551

When querying wildcard tables the most recently created table that matches the prefix is used, https://cloud.google.com/bigquery/docs/querying-wildcard-tables#schema_used_for_query_evaluation. In your case the schema of TableA_B_YYMMDD is used, which doesn't contain ColumnB.

Update your prefix to 'TableA_2020*' so it doesn't match TableA_B?

Upvotes: 1

Related Questions