query causes bigrquery to fail, likely due to page_size memory issue

[ras44]

I have a table with 128 columns and about 10,000 rows in bigquery. In R Studio running on a Google Cloud instance, I have been assigning the result of a query to a variable with:

tm_orig <- query_exec("SELECT *
  FROM my_dataset.my_table
;
", project='my-project');

This has worked for the past few weeks with no problems. Today, I received the error:

0 bytes processed
Error: Internal Error

Solution:
If we change page_size to a smaller value (default is 10,000) the query succeeds, supposedly because results are spread across multiple page requests, and whatever (possibly memory issue?) is causing the internal error is not triggered. I'm skeptical as to whether it's a system memory issue, because I can continue to assign values to other variables in R Studio with no problems. It seems like there is an issue with iterating through the pages in the response.

If this paging exception were caught, outputting a "decrease page_size" suggestion might help others.

[YvesCR]

I had the same error and changing the page_size to a smaller value solved my issue

[mineshkumar]

Changing the page_size is not helping me. I've 20,000 rows with 175 columns. It processes some megabytes and then shows

Error: Internal Error

[hadley]

Can someone please attempt to create a reprex using publicly available data? Unfortunately the chances of me being able to locate and fix the underlying issue are slim.

[hadley]

Oh this is actually a duplicate of #209

[ras44]

#209 is actually an issue related to handling the response from the GBQ API. It is resolved (98dec6f)by outputting the "responseTooLarge" reason received from the API.

This issue is related to a memory error on the local machine while attempting to parse the response received with the default page_size=10,000. A reprex is available-memory-dependent, but by increasing the page_size I was able to reproduce it with the following on a machine with 4 GB of RAM:

query <- "
  SELECT *
  FROM `fh-bigquery.dbpedia.place`
"

query_exec(project=project,
  query=query,
  max_pages=Inf,
  page_size=500000,
  use_legacy_sql=FALSE
)

[hadley]

@ras44 thanks! I'll look into it, but it's unlikely I'll be able to do much more than give an informative error.

[ras44]

@hadley I attempted to reproduce this again today and it looks like it's not a memory issue: bigrquery is actually receiving a response from the GBQ API. With the results of #209 and 98dec6f, bigrquery now outputs the "Response too large" reason, which should help people understand that they need to reduce the page_size. I think we can close this one again if you agree.

[hadley]

It's also possible that you need to explicitly save to a temporary table: https://cloud.google.com/bigquery/docs/writing-results#large-results

[ras44]

I can confirm that in both cases (when I create a temporary table or just run the query) I receive the "responseTooLarge" reason from the API. It's as if the API has an internal limit with which it decides whether a response is too large. In either case, I think it's safe to say this is not an issue with R and the response is being handled appropriately now with the reason included in the output.