“Response too large to return” even with --allow_large_results

Question

Sometimes after many successful processing of 1 million rows dataset, I got “Response too large to return” error message. Same message was thrown when I tried 2 million rows datasets. Is there a limit for transaction size?

Error message: BigQuery error in query operation: Error processing job '...': Response too large to return.

Query submitted: bq --project_id=... query --destination_table ... --append_table --use_cache --allow_large_results "select ... from ... limit 1000000"

Answer 1

Queries that have a LIMIT clause can cause this error even when --allow_large_results is set. Enforcing the LIMIT clause forces all the output data through a single node, which removes the ability to return large results. Try removing the limit 1000000 from your query.

We're working on adding this to the limitations in our documentation for large query results, but it hasn't been added there yet.