BigQuery standard SQL syntax: _TABLE_SUFFIX and .yesterday tables

Question

My goal is to query across multiple tables of a dataset using BigQuery standard SQL syntax.

I can successfully make it work when all tables of a dataset follow the same number pattern. However, for datasets that contain additional tables like .yesterday, I get an error: Views cannot be queried through prefix. Matched views are: githubarchive:day.yesterday

Here is the query I used:

SELECT
  COUNT(*)
FROM
  `githubarchive.day.*`
WHERE
  type = "WatchEvent"
  AND _TABLE_SUFFIX BETWEEN '20170101' AND '20170215'

Answer 1

Try using more of a prefix. For example,

SELECT
  COUNT(*)
FROM
  `githubarchive.day.2017*`
WHERE
  type = "WatchEvent"
  AND _TABLE_SUFFIX BETWEEN '0101' AND '0215';