Bigquery API Intermittently returns http error 400 "Bad Request"

Question

I am getting http error 400 returns intermittently for a particular query, yet when I examine the text of the query it appears to be correct, and if I then copy the query to the Bigquery GUI and run it, it executes without any problems. The query is being constructed in node.js and submitted though the gcloud node.js api. The response I receive, which contains the text of the query is too large to post here, but I do have the path name:

"pathname":"/bigquery/v2/projects/rising-ocean-426/queries/job_aSR9OCO4U_P51gYZ2xdRb145YEA"

The error seems to occur only if the live_seconds_viewed calculations are included in the query. If any part of the live_seconds_viewed calculation is included then the query fails intermittently.

The initial calculation of this field is:

CASE WHEN event = 'video_engagement'
     AND range IS NULL
     AND INTEGER(video_seconds_viewed) > 0
     THEN 10
     ELSE 0 END AS live_seconds_viewed,

Sometimes I can get the query to execute simply by changing the order of the expressions. But again, it is intermittent.

Any help with this would be greatly appreciated.

score 2 · Accepted Answer · answered Sep 17 '15 at 15:33

After long and arduous trial and error, I've determined that the reason why the query is failing is simply that the string length of the query is too long. When the query is executed from the GUI, apparently the white space is stripped so the query executes because without the white space it is short enough to pass the size limit.

When I manipulated the query to determine what part or parts were causing the problem, I would inadvertently reduce the size of the query below the critical limit and cause the query to pass.

It would be great if the error response from Bigquery included some hint about what the problem is rather than firing off a 400 error bad request and calling it quits.

It would be even better if the Bigquery parser would ignore white space when determining the size of the query. In this way the behavior on the GUI would match the behavior when submitting the query through the API.

Hey! The two points you make are definitely valid (both the error should be better phrased, and the API and GUI should match behavior). I would suggest reporting this in two separate issues here https://code.google.com/p/google-bigquery/issues/list (that's the issue tracker for the BigQuery team. — Patrice, Sep 17 '15 at 18:34
I suspect there's something else going on here. The limit for query length is quite large (256 KB), and the UI does not strip any whitespace. — Jeremy Condit, Sep 24 '15 at 17:13

Bigquery API Intermittently returns http error 400 "Bad Request"

1 Answers1