How to use GCP REST API to execute a query

Question

In GCP, Big Query editor, https://console.cloud.google.com/bigquery, I can execute my query like this:

#standardSQL
SELECT COUNT(*) AS num_downloads
FROM `the-psf.pypi.file_downloads`
WHERE file.project = 'pytest'
  -- Only query the last 30 days of history
  AND DATE(timestamp)
    BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
    AND CURRENT_DATE()

But when I try to use the REST API to execute the query, i.e. go to https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query and click 'Try it', and put this in the request body:

{
  "query": "    #standardSQL
    SELECT COUNT(*) AS num_downloads
    FROM `the-psf.pypi.file_downloads`
    WHERE file.project = 'pytest'
      -- Only query the last 30 days of history
      AND DATE(timestamp)
        BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
        AND CURRENT_DATE()"
}

I get error like "Select an underlined section to see more details. You have invalid or missing required parameter values above." So how can I fix the problem in order to use the Query REST API?

You are only filling out the query field. Review the required fields. cloud.google.com/bigquery/docs/reference/rest/v2/jobs/… Example: "kind":"bigquery#queryRequest","useLegacySql":false,"query":YOUR_QUERY — John Hanley

guillaume blaquiere guillaume blaquiere · Accepted Answer · 2020-12-20T12:16:50

JSON spec doesn't support multiline string. You need to flatten your string

{
  "query": "#standardSQL \n SELECT COUNT(*) AS num_downloads FROM `the-psf.pypi.file_downloads` WHERE file.project = 'pytest' \n -- Only query the last 30 days of history \n AND DATE(timestamp) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) AND CURRENT_DATE()"
}

You also need to add a \n to isolate the comments (standardSQL and you comment in the middle of your query)

How to use GCP REST API to execute a query

1 Answers