topic Re: BigQuery Skill - jobs.query create table in Digital Exchange

BigQuery Skill - jobs.query create table

SebT — Mon, 17 Jan 2022 10:04:37 GMT

Hi all,

Im trying to fetch data from an existing view in BigQuery. From what I can read online, you cannot fetch data from a view using tabledata.list directly. You need to materialize the view into a table before you can use tabledata.list.

So what I am want to do is use bigquery.jobs.query to create a temp table based of a view, and then use tabledata.list. However, I cannot seem to get the bigquery.jobs.query to work.
The query im using is this:

"CREATE TEMP TABLE NewTable_ExistingView
AS
SELECT * FROM `ProjectID.DatasetID.ViewID`"

Input parameters for the API:

How the webservice is setup. Haven't changed anything after importing the skill.

API Request:

And the error I'm getting (shortened) :

HTTP Status Code: 400
"message": "Invalid JSON payload received. Unexpected token.\n\n\"queryParameters\": ,\r\n\"useLegacySql\": \"\n ^",

From my understanding "useLegacySql" is set to true as default, meaning that "queryParameters" is uncessary as it is only used with standardSQL?

Any help is greatly appreciated.

Br,
Seb

Re: BigQuery Skill - jobs.query create table

ewilson — Mon, 17 Jan 2022 17:30:12 GMT

Hi @Seb T,

I think the issue here is that Google expects an empty JSON array for queryParameters if the tag is included in the request. So there are a few things you could try here:

In the body template of the request, you could remove the queryParameters entry from the template. In fact, based on the example request you've shown you could strip the template down to something like this:

{
  "location": "[location]",
  "query": "[query]",
  "maxResults": "[maxResults]",
  "kind": "[kind]"
}

Alternatively, you could try entering "[]" (open and close brackets) as the value for the queryParameters data item. That signifies an empty array.

Cheers,
Eric

Re: BigQuery Skill - jobs.query create table

SebT — Wed, 19 Jan 2022 12:09:45 GMT

Did the trick, thanks again.