Response too large to return

The result of the query is larger than the response size BigQuery allows, which the quotas page puts at 10 GB compressed. The fixes are to return fewer rows or columns, to aggregate, or to write the result to a destination table, where the limit does not apply.

Response too large to return. Consider specifying a destination table in your job configuration. For more details, see https://cloud.google.com/bigquery/troubleshooting-errors

What BigQuery means

The query ran and produced a result, and the result is too big to hand back. Every query writes its result to a hidden table first, and the response is read from there. There is a cap on that response. The quotas page lists “Maximum response size” as 10 GB compressed, and warns that the size varies with how well the data compresses, so the real size before compression can be well above 10 GB. The cap is lifted when you write the result to a destination table.

The error reason is responseTooLarge. It has nothing to do with memory: Resources exceeded is the error for that. It does not depend on how many bytes the query scans either. A query that scans 1 MB can fail this way, and a query that scans 1 TB and returns one row cannot.

In the BigQuery command line tool the message comes after Error processing job '<project>:<job id>':.

The smallest query that reproduces it

This query reads no table. It builds 300 million rows with three UUID columns, a result of about 34 GB:

SELECT GENERATE_UUID() AS u, GENERATE_UUID() AS v, GENERATE_UUID() AS w
FROM UNNEST(GENERATE_ARRAY(1, 20000)) AS a
CROSS JOIN UNNEST(GENERATE_ARRAY(1, 15000)) AS b

Response too large to return. Consider specifying a destination table in your job configuration. For more details, see https://cloud.google.com/bigquery/troubleshooting-errors

It took four to five minutes to fail, because the whole result is computed first. GENERATE_ARRAY has a cap on the elements it returns, so the rows come from two arrays joined together. A single GENERATE_ARRAY(1, 300000000) fails earlier with GENERATE_ARRAY(1, 300000000, 1) produced too many elements.

The fixes

Return fewer rows. LIMIT is the plain fix when you only need to look at the data:

SELECT GENERATE_UUID() AS u, GENERATE_UUID() AS v, GENERATE_UUID() AS w
FROM UNNEST(GENERATE_ARRAY(1, 20000)) AS a
CROSS JOIN UNNEST(GENERATE_ARRAY(1, 15000)) AS b
LIMIT 1000

That query returned in under two seconds. Put the LIMIT on the outermost query, so it caps the rows that come back.

Aggregate. A result of a few rows cannot be too large, however many rows go in:

SELECT COUNT(*) AS n
FROM UNNEST(GENERATE_ARRAY(1, 20000)) AS a
CROSS JOIN UNNEST(GENERATE_ARRAY(1, 15000)) AS b

This returned 300000000.

Select fewer columns, and filter. The size is rows times width. A SELECT * on a table with large string or JSON columns is the usual cause on real tables. The best-practices page for query computation lists the same options: filters, a LIMIT, or writing to a destination table.

Write the result to a destination table. The size cap does not apply to a destination table. It is a setting of the query job (destinationTable in the API). In the command line it is a flag:

bq query --nouse_legacy_sql --destination_table=mydataset.big_result --replace < query.sql

The failing query above, run this way, finished and wrote a table of 300,000,000 rows and 34.2 GB. You pay for storing that table. Read from it afterwards in pieces, or export it to Cloud Storage.

Legacy SQL. In legacy SQL a destination table alone is not enough. You must also set allowLargeResults, and a top-level ORDER BY, TOP or LIMIT cannot be used with it. See the writing results page.

The variants of the message

The text above is what BigQuery returns for a standard SQL query today. In the API response, the error has the reason responseTooLarge.

Queries that look wrong but run

A very large scan that returns little. The limit applies to the response. Bytes read do not count. A query over a multi-terabyte table that ends in COUNT(*) or GROUP BY with a few groups returns a small response.

A big result written to a destination table. It runs, as shown above. The cap is on what comes back to the client.

A large LIMIT. LIMIT 1000 is far below the cap. A LIMIT of a hundred million rows of wide data might not be.

Different errors that look like this one

Resources exceeded during query execution: The query could not be executed in the allotted memory. A worker ran out of memory, usually in an ORDER BY. See Resources exceeded.

Quota exceeded: Your project exceeded quota for free query bytes scanned. A limit on bytes read. The error on this page is about bytes returned. See Quota exceeded.

Cannot query over table ... without a filter over column(s) ... that can be used for partition elimination. The table requires a partition filter. See Cannot query over table without a filter.

In Querylab.io

The editor does not mark this error. The query above showed no diagnostics, because the size of the result depends on the data.

After a failed run, Querylab.io offers one button, Add LIMIT 1000, on the status bar. It appears when the error is Response too large to return (or has the reason responseTooLarge) and the query you ran is one query statement that this tab started, has no template markup, parses, and has no LIMIT on its outermost query. A query that already has a LIMIT, and one you have edited since the run, get no button. The LIMIT is added to the outermost query: after the final SELECT of a WITH query, and after the trailing ORDER BY of a UNION or other set operation.