Google-api-python-client: intermittent error 400 when calling bigquery API

[danicat]

I'm experiencing a cryptic error message while running a long bigquery job:

  File "/usr/local/lib/python3.6/site-packages/googleapiclient/http.py", line 851, in execute
    raise HttpError(resp, content, uri=self.uri)
googleapiclient.errors.HttpError: <HttpError 400 when requesting https://www.googleapis.com/bigquery/v2/projects/<project-name>/queries/job_<id>?pageToken=<page_token>&alt=json returned "{0}">

The point of attention is the returned "{0}" part of the message where the reason was supposed to be. When looking at the job history with the bq --format=prettyjson show -j <jobid> command it also doesn't show the reason field, so that may be the root cause.

Environment details

• Python version: 3.6.5
• google-api-python-client: 1.7.8
• google-cloud-bigquery: 1.11.2
• google-cloud-storage: 1.15.0

Steps to reproduce

This error occurs somewhat randomly while processing a long running bigquery job.

After retrying a few times the job usually succeeds. I wonder if this is related with cause resourcesExceeded documented in https://cloud.google.com/bigquery/troubleshooting-errors. The other possible causes doesn't seem to be related.

[busunkim96]

Hi @danicat!

It looks like you're using both google-cloud-bigquery and google-api-python-client to talk to the BigQuery API. We would generally recommend you use google-cloud-bigquery which is maintained in googleapis/google-cloud-python repo. Is there a feature you're only able to get through this library?

[busunkim96]

There is also an active issue with bigquery. See link.

[danicat]

I'm sorry, my bad, I should've explained this better. This is actually run on Airflow, using the bigquery_hook (https://github.com/apache/airflow/blob/master/airflow/contrib/hooks/bigquery_hook.py). I have never looked how this hook connects to bq, but it seems to use pandas_gbq which supposedly uses the bigquery client you suggested.

Since the requirements.txt file I've used to base my answers above contains requirements for DAGs that use other hooks / libraries that may have created this confusion.

The relevant stack trace is:

    bq_results = cursor.fetchmany(size=BATCH_SIZE)
  File "/usr/local/lib/python3.6/site-packages/airflow/contrib/hooks/bigquery_hook.py", line 1899, in fetchmany
    one = self.fetchone()
  File "/usr/local/lib/python3.6/site-packages/airflow/contrib/hooks/bigquery_hook.py", line 1838, in fetchone
    return self.next()
  File "/usr/local/lib/python3.6/site-packages/airflow/contrib/hooks/bigquery_hook.py", line 1856, in next
    pageToken=self.page_token).execute())
  File "/usr/local/lib/python3.6/site-packages/googleapiclient/_helpers.py", line 130, in positional_wrapper
    return wrapped(*args, **kwargs)
  File "/usr/local/lib/python3.6/site-packages/googleapiclient/http.py", line 851, in execute
    raise HttpError(resp, content, uri=self.uri)
googleapiclient.errors.HttpError: <HttpError 400 when requesting https://www.googleapis.com/bigquery/v2/projects/<project>/queries/job_<job>?pageToken=<token>&alt=json returned "{0}">

[busunkim96]

@tswast Do you have any thoughts on what could be causing this?

[danicat]

I don't think my issue is related to the above bigquery issue, as our region is EU, not US. We do have an active support contract so I've raised an issue with the support team. The thing that concerns me the most is that the command line tool doesnt show any error message on the given job.

[tswast]

CC @leahecole as FYI re: BigQuery and Airflow / Composer (and maybe motivation to switch Airflow to google-cloud-bigquery)

[tswast]

Usually when I see this kind of error, it's because the project ID or the job ID wasn't actually populated in the request, but that doesn't seem to apply here since the error is intermittent.

400 isn't the expected error code for resourcesExceeded, but perhaps it's related to some backend change.

CC @shollyman

[danicat]

Usually when I see this kind of error, it's because the project ID or the job ID wasn't actually populated in the request, but that doesn't seem to apply here since the error is intermittent.

400 isn't the expected error code for resourcesExceeded, but perhaps it's related to some backend change.

CC @shollyman

Yes, and in this particular execution the job managed to fetch several rows before hitting this error.

The resourcesExceeded error was documented under HTTP 400 on https://cloud.google.com/bigquery/troubleshooting-errors. The other 400 errors doesn't seem to make any sense here.

[leahecole]

Ack. @tswast - would love to chat about what that switch would entail

[shollyman]

@danicat, it sounds like this may be a case where you're getting the first page of results, but constructing the request(s) to fetch subsequent pages of results are having issues?

Can you provide more details on how you're triggering the error? It looks like fetchmany() may be confusing rows returned with pages returned, but I'd like to understand more about how you're getting to this state.

[danicat]

@shollyman Actually its not the second request that has an issue. We are able to process many requests before hitting this error (ie: 10's or 100's even). Last time we saw this error was on the 16th, so it hasn't occurred again for the past 3 days. No changes to the code were made during this time window.

It may or may not be related, but we've seen a similar 400 error with message "{0}" on Dataflow as well. It occurred on the 18th and 19th. Same scenario: jobs usually works but randomly hits this error. Maybe this is something the BigQuery instead of the client library, since it happened in two completely different processes and tech stacks - Airflow (python) and Dataflow (java).

[shollyman]

It smells like a backend error, but absent more information we'll have trouble tracking it down. If you can provide more details about when it last happened and values like the project id, we might be able to find something useful in the backend logs. Feel free to send the details to me privately (my github username at google.com) if you'd prefer not to disclose them in the public issue.

[danicat]

I've just sent you more info by e-mail. Let me know if you need anything else.

[shollyman]

Filed 133138422 internally for investigation.

[OmerJog]

CC @leahecole as FYI re: BigQuery and Airflow / Composer (and maybe motivation to switch Airflow to google-cloud-bigquery)

My reported issue:
https://github.com/googleapis/google-api-python-client/issues/646

Is also from Airflow but it's unrelated (as you can see I provided a str param where it expected list) However The library simply doesn't give good error messages. They are very general and they don't explain what exactly is the problem. There is so much that Airflow can enforce.

[shollyman]

@danicat It looks like the BigQuery engineering team has identified a situation in the tabledata backend that can trigger the unusual 400 response with the poorly formatted response. No details/ETA on resolution yet, but thanks for the report helping to identify this. I'll keep this issue open until the internal issue is resolved.

[shollyman]

Checked this morning, it looks like the backend changes to address the malformed response have been deployed to tasks in all regions. Cases where requests were getting the 400 response and the {0} body were representative of a backend component where error propagation was not being handled correctly. Now such conditions should yield an appropriate retryable HTTP error, and a properly formatted response.

@danicat Thanks again for reporting this, the additional details you were able to provide about specific requests were helpful in tracking this down.

[danicat]

thank you @shollyman !