4

I am querying the Google cloud Data using Bigquery.

When i am running the query it return about 8 millions of row. But it throws error :

Response too large to return

How i can get all 8 million records,can anybody help.

arjun kori
  • 1,090
  • 2
  • 14
  • 32
  • One way can be split the request, million by million, or something like that. – BrTkCa Sep 16 '16 at 14:28
  • yes,i am spliting the records but after that it again through error like Response too large to return – arjun kori Sep 16 '16 at 14:30
  • Make sure that you indeed want to see all 8 million rows. It's kinda absurd you want all, and most probably you put the problem wrong, and you want to aggregate the results, and paginate them. – Pentium10 Sep 16 '16 at 14:48
  • You have a low rate. Important on SO, you have to mark accepted answers by using the tick on the left of the posted answer, below the voting. This will increase your rate. See how this works by visinting this link: http://meta.stackoverflow.com/questions/5234/how-does-accepting-an-answer-work#5235 – Pentium10 Sep 16 '16 at 14:48
  • dont worry ,i will mark accepted once i get the result – arjun kori Sep 16 '16 at 15:03
  • you have a long history, all your past questions are with unaccepted answers – Pentium10 Sep 16 '16 at 15:17

2 Answers2

2

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

As it's mentioned on Quota-policy queries maximum response size: 128 MB 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 128 MB of compressed data means.

When you run a normal query in BigQuery, the response size is limited to 128 MB of compressed data. Sometimes, it is hard to know what 128 MB 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)

Community
  • 1
  • 1
Pentium10
  • 204,586
  • 122
  • 423
  • 502
1

try this,

Under the query window, there is an button 'Show Options', click that and then you will see some options,

  1. select or create a new destination table;
  2. click the 'Allow Large Results'

run your query, and see whether it works.

Haipeng Su
  • 2,341
  • 2
  • 15
  • 30
  • It will create a new table, but you can delete it later. Or you can set up a table that for this purpose, and overwrite it every time, then it kind of temporary table. It is just my short path to solve this issue. – Haipeng Su Oct 17 '16 at 19:48