BigQuery error “Response too large to return” when using COUNT(DISTINCT …)

Question

I have a dataset with ~20M rows and I'm observing the following behavior.

The query below returns the error "Response too large to return". The 'id' field is shared among multiple records and the 'field' field has some arbitrary value for each record. I would expect that the result set should only contain 10 rows, well below the query response limit.

SELECT id, COUNT(DISTINCT field)
FROM [my.dataset]
GROUP BY id
LIMIT 10

However, when the DISTINCT keyword is removed from the COUNT aggregation function, BigQuery returns 10 results as expected.

SELECT id, COUNT(field)
FROM [my.dataset]
GROUP BY id
LIMIT 10

I don't understand why the first query returns an error and the second completes successfully. Shouldn't both queries return the same number of rows?

Michael Manoochehri Michael Manoochehri · Accepted Answer · 2012-06-13T22:01:17

It's not the result size that is causing this response, it's the intermediate size of the data generated by your COUNT DISTINCT query.

Note: COUNT DISTINCT returns a statistical approximation after 1000 values - you can alter the approximation by choosing a particular value for the limit in which DISTINCT will return an approximation.. such as: COUNT(DISTINCT your_field, 500)

See: https://developers.google.com/bigquery/docs/query-reference#aggfunctions

This behavior is due to the design of BigQuery, which makes it so quick: data is queried via separate nodes, and results are aggregated at mixers. A COUNT will tally the total number of results and combine the answer, but COUNT DISTINCT needs to keep track of potentially millions of separate sums, and then combine those values later. Therefore a COUNT DISTINCT can create a lot of data, and could potentially be over internal maximum for individual nodes.

Note also that currently, BigQuery LIMIT clauses are applied after the entire result set is determined.

BigQuery error “Response too large to return” when using COUNT(DISTINCT …)

1 Answers