Bigquery API间歇性地返回http错误400“错误请求”(Bigquery API Intermittently returns http error 400 “Bad Request”)

Bigquery API间歇性地返回http错误400“错误请求”(Bigquery API Intermittently returns http error 400 “Bad Request”)

我正在为特定查询间歇性地获取http错误400返回，但是当我检查查询的文本时，它似乎是正确的，如果我然后将查询复制到Bigquery GUI并运行它，它会毫无问题地执行。 查询正在node.js中构建，并通过gcloud node.js api提交。 我收到的响应，其中包含查询的文本太大，无法在此处发布，但我确实有路径名称：

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

只有在查询中包含live_seconds_viewed计算时，才会出现该错误。 如果包含live_seconds_viewed计算的任何部分，则查询会间歇性地失败。

该字段的初始计算是：

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

有时我只需更改表达式的顺序就可以执行查询。 但同样，这是间歇性的。

任何有关这方面的帮助将不胜感激。

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.

最满意答案

经过漫长而艰苦的试验和错误，我已经确定查询失败的原因仅仅是查询的字符串长度太长。 当从GUI执行查询时，显然白色空间被剥离，因此执行查询，因为没有空格，它足够短以通过大小限制。

当我操纵查询以确定导致问题的部分或部分时，我会无意中将查询的大小减小到关键限制以下并导致查询通过。

如果来自Bigquery的错误响应包含一些关于问题的暗示而不是触发400错误错误请求并调用它退出，那将是很好的。

如果Bigquery解析器在确定查询大小时会忽略空格，那就更好了。 通过这种方式，GUI上的行为将与通过API提交查询时的行为相匹配。

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.