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.

JasonMArcher
  • 14,195
  • 22
  • 56
  • 52
j_c
  • 313
  • 1
  • 3
  • 7

1 Answers1

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 :).
Felipe Hoffa
  • 54,922
  • 16
  • 151
  • 325
  • 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). – ggo Jan 08 '15 at 08:38
  • 1
    Size of the tables is not an issue. The issue is querying too many tables. No need to worry about table size! – Felipe Hoffa Jan 08 '15 at 19:43
  • 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..) – ggo Jan 09 '15 at 08:19
  • One thousand is the limit. So 2 years of daily tables is OK. – Felipe Hoffa Jan 09 '15 at 19:46
  • 1
    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. – A.Wan Jan 19 '16 at 17:47
  • What is a "huge" schema? In my case i have about 10 fields. Same problem. 400k rows, 100mb size. Only ONE table. – Guilherme Ferreira Oct 22 '20 at 14:10