0

I have a simple query to run :

update`dataset.mytable`
set field_1 = cast( field_2 as int64) 
where field_2 not in ('AA', 'BB')

And I got the error:

Error: Resources exceeded during query execution: The query could not be executed in the allotted memory..

The table has only 1 billion rows...

bignano
  • 573
  • 5
  • 21
  • Possible duplicate of [Query Failed Error: Resources exceeded during query execution: The query could not be executed in the allotted memory](https://stackoverflow.com/questions/46005418/query-failed-error-resources-exceeded-during-query-execution-the-query-could-n) –  Oct 20 '17 at 16:30
  • 1
    @JarrodRoberson This is a different issue; there is no explicit ORDER BY. bignano, can you share a sample job ID that demonstrates the failure? I'd like to look more into what happened. – Elliott Brossard Oct 20 '17 at 16:36
  • Can you try running your query again? There is currently a [bug related to loading large files](https://issuetracker.google.com/issues/67890371) that seems like it caused problems here, but I was able to run a process over your table that should have resolved it in this particular case. – Elliott Brossard Oct 20 '17 at 17:09
  • oh great. you have fixed it. what magic did you perform? Also I ran a query earlier on this same table joining another table of the same size, and it took 7227.9s to complete, is that normal? – bignano Oct 20 '17 at 18:46
  • I'm glad that worked! I'll add an answer explaining in more detail. It could be that the problem with the table made joining slower than it should have been too. – Elliott Brossard Oct 20 '17 at 19:22

1 Answers1

2

This really was a bug, similar to this report. There wasn't a way for you to know in this case, but if you do find a bug in the future, you can submit it to the issue tracker since the broader StackOverflow community probably can't help.

As a summary of what happened:

  • It looks like the files loaded to the table were quite large, e.g. multiple gigabytes in size each. Loading smaller files would not have triggered the BigQuery bug.
  • When persisting the contents of these files to BigQuery storage, BigQuery did not sufficiently shard the table (this is the bug on BigQuery's side of things).
  • When executing the UPDATE statement, BigQuery tried to execute a large amount of work across only a small number of partitions, leading to the Resources exceeded message.
  • I was able to fix the problem by manually instructing BigQuery storage to reshard your table. This is something that isn't exposed through the BigQuery API, since it shouldn't be necessary unless there is a bug.
Elliott Brossard
  • 32,095
  • 2
  • 67
  • 99
  • I think it is in our best interests to keep this SO clean and comply with site's rules. If it were me - i would just create respective issue on behalf of OP on https://issuetracker.google.com/issues/ and dealt with this Q there. Just wanted to share my thoughts with you - who I respect a lot, so hope you will not get this personally! :o) – Mikhail Berlyant Oct 20 '17 at 20:05
  • I think it's fine just to close the question now. There is a bug that the OP can use to track the underlying issue, and I was able to help resolve the immediate problem in any case. Thanks! – Elliott Brossard Oct 20 '17 at 20:30
  • thank you for support/understanding I still voted up your answer as you well deserved it :o) – Mikhail Berlyant Oct 20 '17 at 20:33
  • Thank you for the input. I wonder what you mean by the files loaded to the table, the table in this question was created by joining two previous tables. Do you refer to the original files in GCS loaded into the BigQuery table ? When do you expect such bug be fixed, and before the bug fixed, how can I prevent this from happening on my side? – bignano Oct 20 '17 at 20:52
  • Also I just wonder if I export the big table into GCS and reload them into BigQuery, will that solve the problem since i notice the export function will create massively split small files in GCS. – bignano Oct 20 '17 at 21:02
  • 1
    Please follow up on the bug that I linked to and someone should be able to help explain more. – Elliott Brossard Oct 20 '17 at 21:27