2

I'm trying to use the BigQuery REST API to execute some queries, but, for some reason, I can't use SQL functions. The endpoint I've been using is the following:

This works for regular queries (with no functions), but if I try to use the EXTRACT or FORMAT_DATE functions I always get a 400 Bad Request.

Examples

Payload:

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

Response:

{
    "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"
    }
}

Second Payload:

{
    "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"
}

Response:

{
    "error": {
        "code": 400,
        "message": "1.39 - 1.56: Unrecognized function format_date\n[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_date\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"
    }
}

Is there any particular way to escape BigQuery functions in the REST API?

Thank you,

João Azevedo
  • 29
  • 2

1 Answers1

1

I suspect (give that you mention the REST endpoint directly) you're constructing requests without the use of a client library.

Try setting the "useLegacySQL" field to false as part of the request:

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

Due to historical precedent and to avoid breaking users during the evolution of the standard SQL dialect, the default value of this is field is true. The various BigQuery client libraries tend to handle this automatically for you.

shollyman
  • 4,216
  • 19
  • 17
  • First of all, thank you for your response. Yes, I'm not using any library. I'm managing the HTTP requests on my own. I've tried to set the "useLegacySQL" to false, but the result is the same. I got the 400 bad request status with the same message I have posted before. – João Azevedo Jun 28 '22 at 08:48