BigQuery, Wildcard table over non partitioning tables and field based partitioning tables is not yet supported

1
votes

I'm trying to run a simple query with a wildcard table using standardSQL on Bigquery. Here's the code:

#standardSQL
SELECT dataset_id, SUM(totals.visits) AS sessions
FROM `dataset_*`
WHERE _TABLE_SUFFIX BETWEEN '20150518' AND '20210406'
GROUP BY 1

My sharded dataset contains one table each day since 18/05/2015. So today's table will be 'dataset_20150518'.

The error is: 'Wildcard table over non partitioning tables and field based partitioning tables is not yet supported, first normal table dataset_test, first column table dataset_20150518.'

I've tried different kinds of select and aggregations but the error won't fix. I just want to query on all tables in that timeframe.

1
google-bigquery

1 Answers

1
votes

This is because in the wildcard you have to have all the tables with same schema. In your case, you are also adding dataset_test which is not with the same schema than others (dataset_test is a partition table?)

You should be able to get around this limitation by deleting _test and other tables with different schema or by running this query:

#standardSQL
SELECT dataset_id, SUM(totals.visits) AS sessions
FROM `dataset_20*`
WHERE _TABLE_SUFFIX BETWEEN '150518' AND '210406'
GROUP BY 1

Official documentation