Big Query jobs .query issue

[SaiTeja3]

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 

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

[ewilson]

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

[SaiTeja3]

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

[ewilson]

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

[SaiTeja3]

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

[ewilson]

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

[SaiTeja3]

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