BigQuery REST API -无法使用函数

Question

我试图使用BigQuery REST API来执行一些查询，但是，由于某种原因，我不能使用SQL函数。我一直使用的端点如下:

• 文章https://bigquery.googleapis.com/bigquery/v2/projects/{项目id}/查询

这适用于常规查询(没有函数)，但如果我尝试使用EXTRACT或FORMAT_DATE函数，我总是得到一个400坏请求。

示例

---

有效载荷:

{
"query": "SELECT user_id, timestamp, (EXTRACT(ISOWEEK FROM timestamp)) as week FROM table_name WHERE DATE(_PARTITIONTIME) >= '2022-01-01' ORDER BY week DESC"
}

反应:

{
"error": {
"code": 400,
"message": "Encountered " "FROM" "FROM "" at line 1, column 45.nWas expecting:n    ")" ...n[Try using standard SQL (https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-standard-sql)]",
"errors": [
{
"message": "Encountered " "FROM" "FROM "" at line 1, column 45.nWas expecting:n    ")" ...n[Try using standard SQL (https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-standard-sql)]",
"domain": "global",
"reason": "invalidQuery",
"location": "q",
"locationType": "parameter"
}
],
"status": "INVALID_ARGUMENT"
}
}

第二个载荷:

{
"query": "SELECT user_id, timestamp, FORMAT_DATE('%Y%W',timestamp) as week FROM table_name WHERE DATE(_PARTITIONTIME) >= '2022-01-01' ORDER BY week DESC"
}

反应:

{
"error": {
"code": 400,
"message": "1.39 - 1.56: Unrecognized function format_daten[Try using standard SQL (https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-standard-sql)]",
"errors": [
{
"message": "1.39 - 1.56: Unrecognized function format_daten[Try using standard SQL (https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-standard-sql)]",
"domain": "global",
"reason": "invalidQuery",
"location": "q",
"locationType": "parameter"
}
],
"status": "INVALID_ARGUMENT"
}
}

---

是否有任何特定的方法来逃避REST API中的BigQuery函数?

谢谢你,

Answer 1

我怀疑(考虑到您直接提到了REST端点)您在不使用客户端库的情况下构造请求。

尝试设置useLegacySQL"字段设置为false，作为请求的一部分:

https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query QueryRequest

由于历史上的先例，并且为了避免在标准SQL方言的演变过程中破坏用户，this is字段的默认值为true。各种BigQuery客户端库倾向于为您自动处理此问题。