3

I've had this query in BigQuery that I have been updating every day for the last few months. It's been fine - some occasional errors, but retrying has solved the problem.

Bet last few days I am getting the error: The job encountered an error during execution. Retrying the job may solve the problem.

The error description says that it's an external error, so how can I fix that?

I have been retrying (with rather long pauses in between), but I still get the error. JobID example: bquxjob_152ced5d_169917f0145

Does anyone have any idea what's going on? Is there any data/time limitations I might encounter (but why just the last few days then)?

Improve this question
3
  • Have you tried checking GCP monitoring of BigQuery to see if something might explain the issue cloud.google.com/bigquery/docs/monitoring? Can you also share your query maybe there is a way to optimize it? Commented Mar 19, 2019 at 7:18
  • Thanks for the quick reply. :) But can you tell more about how the monitoring tool can help with the problem? As far as I can see in the page it looks that it is a tool to see when and what errors are happening, but well, I already see that. I just don't understand what are the reason for an error. About the query - it is a bit complex thing - consists of several views inside each other. Bu well, it contains of some UNIONS and LEFT JOINS and stuff like that. Nothing too advanced. It also uses one google spreadsheet that updates every day. Commented Mar 19, 2019 at 8:55
  • P.S. I just tried one thing - deleted the google spreadsheet source - when I took off this source, the query finished quite quickly. I'll look into this more, let you know what I find. Commented Mar 19, 2019 at 8:55

4 Answers 4

Reset to default
4

You can use CGP stackdriver to monitor your BigQuery process using this URL

Interesting information you can find among others are the queryTime heatmap and the Slot usage which might help you understand your problems better

enter image description here

On the subject of the external table usage, you can use Google transfer (See this link for details) to schedule a repeated transfer from CSV to BigQuery table. The below Image show you how to get to the transfer set up page from the webUI

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks I'll look into it.
Happy to help. If you found my answer helpful please consider voting up and accepting the answer (See (why)[meta.stackexchange.com/questions/5234/… this is important in SO)
1

I encountered this dreadfully useless error in a scheduled query. It was working great and then one day it stopped working at all and has been failing ever since without any other explanation. The StackDriver (now "Logs Explorer") showed nothing more enlightening:

jobStatus: {
  errorResult: {
    code: 14
    message: "Error encountered during execution. Retrying may solve the problem."
    }
    errors: [
      0: {
      code: 14
      message: "Error encountered during execution. Retrying may solve the problem."
    }
  ]
  jobState: "DONE"
}

Figuring out the actual issue takes a long time because scheduled queries start slowly since they use BATCH priority. What I found in my case was that the partitioned table and "Partition field" setting in the scheduled query was the culprit. I dropped the table and removed the partition field and voila the thing works again (although far from ideal since I need partitioning).

I hope this helps someone else running up against that useless error but in any case, I hope the good folks working on BigQuery find a better error to bubble up.

Improve this answer

Comments

1

This kind of issue probably has a lot to do with BigQuery quota errors : https://cloud.google.com/bigquery/docs/troubleshoot-quotas#ts-number-column-partition-quota, as mentionned by other answers, such as the 4000 partitions-by-table quota.

Improve this answer

Comments

0

I ran into this problem when replacing the contents of a partitioned table. Two retries did not help. When I removed the --range_partitioning from the command the update was processed correctly. The table remained partitioned.

So there seems to be an issue about updates to partitioned tables, and when that is the cause these errors might not benefit from retry. I don't know whether there are other causes of this error.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.