topic RE: Big Query jobs .query issue in Digital Exchange

Big Query jobs .query issue

SaiTeja3 — Thu, 01 Jun 2023 09:18:00 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

SELECT * FROM `ABC-datafoundation-qa.SAP_VENDOR_MGMT_REPORTING.V_MAN_HOURS_TIME_REPORT_TEST`"

This is the API Request

The input Paramters

ERROR: Internal : Unexpected error Error during Web API HTTP Request
HTTP Status Code: 400
HTTP Response Content: {
"error": {
"code": 400,
"message": "Encountered \" \"CREATE\" \"CREATE \"\" at line 1, column 1.\nWas expecting:\n \u003cEOF\u003e \n ",
"errors": [
{
"message": "Encountered \" \"CREATE\" \"CREATE \"\" at line 1, column 1.\nWas expecting:\n \u003cEOF\u003e \n ",
"domain": "global",
"reason": "invalidQuery",
"location": "q",
"locationType": "parameter"
}
],
"status": "INVALID_ARGUMENT"
}
}

Can some one please help me on this.

Thanks,

------------------------------
Teja Sai X (Contractor)
------------------------------

RE: Big Query jobs .query issue

ewilson — Thu, 01 Jun 2023 16:50:00 GMT

Hi @Sai Teja

In the display of your query it looks like you've included line breaks in the input data. First thing I would do is remove those line breaks, so your query input looks like this:

"CREATE TEMP TABLE NewTable_ExistingView AS SELECT * FROM `ABC-datafoundation-qa.SAP_VENDOR_MGMT_REPORTING.V_MAN_HOURS_TIME_REPORT_TEST`"

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Big Query jobs .query issue

SaiTeja3 — Thu, 01 Jun 2023 17:40:00 GMT

Hi Eric,

I have tried after doing the modification, but i am getting the same error.

Internal : Unexpected error Error during Web API HTTP Request

HTTP Status Code: 400

HTTP Response Content: {

"error": {

"code": 400,

"message": "Encountered \" \"CREATE\" \"CREATE \"\" at line 1, column 1.\nWas expecting:\n \u003cEOF\u003e\n[Try using standard SQL (https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-standard-sql)]",

"errors": [

{

"domain": "global",

"reason": "invalidQuery",

"location": "q",

"locationType": "parameter"

}

"status": "INVALID_ARGUMENT"

}

Thanks,

Sai

------------------------------
Sai Teja
------------------------------

RE: Big Query jobs .query issue

ewilson — Thu, 01 Jun 2023 22:02:00 GMT

@Sai Teja

Can you try executing your query from the Google API Explorer? You can find the specific REST endpoint here. Just populate the query parameter and then click the plus sign to add the location, kind, and maxResults parameters. I'd like to see if you get the same error.

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Big Query jobs .query issue

SaiTeja3 — Fri, 02 Jun 2023 06:31:00 GMT

Hi Eric,

when i try through the big query rest resource I am getting a different error now.

{
"error": {
"code": 403,
"message": "Access Denied: Project cnp-datafoundation-qa: User does not have bigquery.jobs.create permission in project cnp-datafoundation-qa.",
"errors": [
{
"message": "Access Denied: Project cnp-datafoundation-qa: User does not have bigquery.jobs.create permission in project cnp-datafoundation-qa.",
"domain": "global",
"reason": "accessDenied"
}
],
"status": "PERMISSION_DENIED"
}
}

But when i try with blue prism iam getting a different error.

Internal : Unexpected error Error during Web API HTTP Request

HTTP Status Code: 400

HTTP Response Content: {

"error": {

"code": 400,

"message": "Cannot reference a standard SQL view in a legacy SQL query.",

"errors": [

{

"message": "Cannot reference a standard SQL view in a legacy SQL query.",

"domain": "global",

"reason": "invalidQuery",

"location": "q",

"locationType": "parameter"

}

"status": "INVALID_ARGUMENT"

}

Thanks

------------------------------
Sai Teja
------------------------------

RE: Big Query jobs .query issue

ewilson — Fri, 02 Jun 2023 13:40:00 GMT

@Sai Teja

Can you assign yourself the CREATE permissions, or have you Google administrator do it temporarily for testing?

On the Blue Prism test, did you change anything? It seems odd that you're getting an entirely different error message now.

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Big Query jobs .query issue

SaiTeja3 — Fri, 02 Jun 2023 15:01:00 GMT

Hi Eric,

I am not sure how to get the permission for testing, I am working with my team on this, but the error in the blue prism is completely different, i have not changed anything, just i made the modification to the query as suggested by you. Can you please help me on this where it is going wrong .

Thanks

------------------------------
Sai Teja
------------------------------