CODEWITHSUNDEEP
google-bigquery
selva kumar
selva kumar

Reputation: 1156

Google BigQuery unable to process larger result set getting "Response too large to return" or "Resources exceeded during query execution"

I am currently working with large table (~105M Records) in C# application.

  1. When query the table with 'Order by' or 'Order Each by' clause, then i am getting "Resources exceeded during query execution" error.

  2. If i remove 'Order by' or 'Order Each by' clause, then i am getting Response too large to return error.

Here is the sample query for two scenarios (I am using Wikipedia public table)

  1. SELECT Id,Title,Count(*) FROM [publicdata:samples.wikipedia] Group EACH by Id, title Order by Id, Title Desc

  2. SELECT Id,Title,Count(*) FROM [publicdata:samples.wikipedia] Group EACH by Id, title

Here are the questions i have

  1. What is the maximum size of Big Query Response?
  2. How do we select all the records in Query Request not in 'Export Method'?

Upvotes: 3

Views: 2430

Answers (1)

Pentium10
Pentium10

Reputation: 208042

1. What is the maximum size of Big Query Response?

As it's mentioned on Quota-policy queries maximum response size: 10 GB compressed (unlimited when returning large query results)

2. How do we select all the records in Query Request not in 'Export Method'?

If you plan to run a query that might return larger results, you can set allowLargeResults to true in your job configuration.

Queries that return large results will take longer to execute, even if the result set is small, and are subject to additional limitations:

  • You must specify a destination table.
  • You can't specify a top-level ORDER BY, TOP or LIMIT clause. Doing so negates the benefit of using allowLargeResults, because the query output can no longer be computed in parallel.
  • Window functions can return large query results only if used in conjunction with a PARTITION BY clause.

Read more about how to paginate to get the results here and also read from the BigQuery Analytics book, the pages that start with page 200, where it is explained how Jobs::getQueryResults is working together with the maxResults parameter and int's blocking mode.

Update:

Query Result Size Limitations - Sometimes, it is hard to know what 10 GB of compressed data means.

When you run a normal query in BigQuery, the response size is limited to 10 GB of compressed data. Sometimes, it is hard to know what 10 GB of compressed data means. Does it get compressed 2x? 10x? The results are compressed within their respective columns, which means the compression ratio tends to be very good. For example, if you have one column that is the name of a country, there will likely be only a few different values. When you have only a few distinct values, this means that there isn’t a lot of unique information, and the column will generally compress well. If you return encrypted blobs of data, they will likely not compress well because they will be mostly random. (This is explained on the book linked above on page 220)

Upvotes: 3

Related Questions