Help
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Big Query jobs .query issue

SaiTeja3
Level 3

‎01-06-23 10:18 AM

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 `ABC-datafoundation-qa.SAP_VENDOR_MGMT_REPORTING.V_MAN_HOURS_TIME_REPORT_TEST`"

This is the API Request 

5873.png

5875.png

 The input Paramters

5876.png

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)
------------------------------
0 Likes
6 REPLIES 6

ewilson
Staff
Staff

‎01-06-23 05:50 PM

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
------------------------------
0 Likes

SaiTeja3
Level 3
In response to ewilson

‎01-06-23 06:40 PM

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": [
      {
        "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)]",
        "domain": "global",
        "reason": "invalidQuery",
        "location": "q",
        "locationType": "parameter"
      }
    ],
    "status": "INVALID_ARGUMENT"
  }
}
 
Thanks,
Sai



------------------------------
Sai Teja
------------------------------
0 Likes

ewilson
Staff
Staff
In response to SaiTeja3

‎01-06-23 11:02 PM

@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
------------------------------
0 Likes

SaiTeja3
Level 3
In response to ewilson

‎02-06-23 07:31 AM

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
------------------------------

0 Likes

ewilson
Staff
Staff
In response to SaiTeja3

‎02-06-23 02:40 PM

@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
------------------------------
0 Likes

SaiTeja3
Level 3
In response to ewilson

‎02-06-23 04:01 PM

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
------------------------------
0 Likes