After we granted the SA with BigQuery Job User and BigQuery Data Viewer role, we still get the same error. I agree it's more than a permission issue, just don't know what it is actually complaining. There is not sufficient information to help troubleshooting from BigQuery API too.
Some other questions
- is formatted_date a column? It seems like your other date fields (year, quarter) are all the result of the FORMAT_DATE() function.
Yes it is a column. We want to return the year and quarter of the formatted_date as additional columns.
- Are you actually setting the token correctly? This part looks suspect:
<Headers>
<Header name="authorization">Bearer [access_token]</Header>
</Headers>
If you want variable substitution, you need to use curlies, not square brackets: Bearer {access_token} . But again, that seems less likely, because I get a 401, not a 404, when I send an access token of exactly [access_token].
I dummied it up for this post only. For my test, I hardcoded with the [access_token] bit.
- Why wouldn't you just use the builtin auto-authentication that Apigee provides? You can follow this guide.
...
<HTTPTargetConnection>
<!-- tell Apigee to invoke this with a Google Access Token -->
<Authentication>
<GoogleAccessToken>
<Scopes>
<Scope>https://www.googleapis.com/auth/cloud-platform</Scope>
</Scopes>
</GoogleAccessToken>
</Authentication>
When I tried this, I added an Accept header. Not sure if this would affect you.
<Headers>
<Header name="accept">application/json</Header>
</Headers>
It's what we planned to do - granting the Apigee SA with BigQuery Job User and Data Viewer roles and assuming that's all we need. However, it took us some time to get the permission granted so I tried out a different approach in the meantime. Now that we have the SA granted with the BQ roles, the proxy still returns with the same error.
- The other references I found to "Request couldn't be served" that were BigQuery things, didn't provide a clear resolution.
The link doesn't seem to bring to a page but a google search result?
In addition to using the REST API as target endpoint, I tried using it with the ServiceCallout policy and it actually returned 200 with the same request header and payload.
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ServiceCallout continueOnError="false" enabled="true" name="SC-TestCallout">
<DisplayName>SC-TestCallout</DisplayName>
<Properties/>
<Request>
<Set>
<Headers>
<Header name="authorization">Bearer [access_code]</Header>
</Headers>
<Verb>POST</Verb>
<Payload contentType="application/json">
{
"kind": "bigquery#queryRequest",
"query": "SELECT SUBSTR(CAL_ID,6,3) as state,formatted_date, FORMAT_DATE('%Y', formatted_date) as year , FORMAT_DATE('%Q', formatted_date) as quarter FROM `<project_id>.TestDataset.calendar_vw` where CAL_ID like 'A24AU%' and FORMAT_DATE('%Y', formatted_date) = '2024' order by state, formatted_date asc",
"defaultDataset": {
"datasetId": "TestDataset",
"projectId": "<project_id>"
},
"useQueryCache": true,
"useLegacySql": false,
"location": "australia-southeast1",
"timeoutMs": 60000
}
</Payload>
</Set>
<IgnoreUnresolvedVariables>false</IgnoreUnresolvedVariables>
</Request>
<Response>calloutResponse</Response>
<HTTPTargetConnection>
<Properties/>
<URL>https://bigquery.googleapis.com/bigquery/v2/projects/<project_id>/queries</URL>
</HTTPTargetConnection>
</ServiceCallout>
<project_id> and [access_token] are dummied up.
However, it is not what we want to implement.
Do you see what else we could be missing? Thank you for looking into this.
LinkedIn
Twitter
