7

I am storing event data in BigQuery, partitioned by day - one table per day. The following query failed:

select count(distinct event) 
from TABLE_DATE_RANGE(my_dataset.my_dataset_events_, SEC_TO_TIMESTAMP(1391212800), SEC_TO_TIMESTAMP(1393631999))

Each table is about 8GB in size.

Has anyone else experienced this error? Seems like it's limited by table size, because in this query, I've only limited it to just one column. When I use a smaller time range, it works.. but the whole point of using BigQuery was its support for large datasets.

Improve this question
4
  • 1
    hey moderators, please vote to re-open question, since it's not a duplicate question (different error) Commented Jul 1, 2014 at 17:13
  • Cool, looking forward to the great feedback. Commented Jul 1, 2014 at 18:15
  • anyway try: select count(event) from TABLE_DATE_RANGE(my_dataset.my_dataset_events_, SEC_TO_TIMESTAMP(1391212800), SEC_TO_TIMESTAMP(1393631999) group each by event Commented Jul 2, 2014 at 13:45
  • Question has been re-opened. I added an answer below. Sorry for letting this go to the duplicate bin! Commented Jul 2, 2014 at 22:34

1 Answer 1

Reset to default
5

"Query too large" in this case means that the TABLE_RANGE is getting expanded internally to too many tables, generating an internal query that is too large to be processed.

This has 2 workarounds:

  • Query less tables (could you aggregate these tables into a bigger one?).
  • Wait until the BQ team solves this issue internally. Instead of using a workaround, you should be able to run this query unchanged. Just not today :).
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Do you know if this was issue fixed by the BQ team now please? (I'm expecting daily tables with 30,000,000 records, size will be at least 5GB).
Size of the tables is not an issue. The issue is querying too many tables. No need to worry about table size!
Ok thank you very much; do you know if there is a known limit for the number of tables please? e.g.: if I wanted to generate a report for one year of data, there would be at least 365 tables involved. Is 365+ tables involved in the same query something unrealistic? (normally the solution will be restricted to monthly reports, so 31 tables will be involved at most, but some persons may have specific needs..)
One thousand is the limit. So 2 years of daily tables is OK.
Another factor is the schema size of the tables: if the tables have huge schemas, the actual query request (which contains the schemas as metadata) can get too large.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.