Syntax Error with Validated Query when sent into BigQuery via Python API Client

Question

Here is my query:

SELECT hits.page.pagePath
FROM [(project_id):(dataset_id).ga_sessions_20151019]
GROUP BY hits.page.pagePath LIMIT 1

It runs in the web UI.

Here is my code:

from oauth2client.service_account import ServiceAccountCredentials
from httplib2 import Http
from apiclient.discovery import build
import json

query = "SELECT hits.page.pagePath FROM [(project_id):(dataset_id).ga_sessions_20151019] GROUP BY hits.page.pagePath LIMIT 1",

path = (filepath of credentials json file)
scopes = ['https://www.googleapis.com/auth/bigquery']
credentials = ServiceAccountCredentials.from_json_keyfile_name(path,scopes)
http_auth = credentials.authorize(Http())
bigquery = build('bigquery','v2',http=http_auth)

req_body = {
    "timeoutMs": 60000,
    "kind": "bigquery#queryRequest",
    "dryRun": False,
    "useQueryCache": True,
    "useLegacySql": False,
    "maxResults": 100,
    "query": query,
    "preserveNulls": True,
  }

bigquery.jobs().query(projectId=(project_id),body=req_body).execute()

When I run this, I get the following error:

HttpError: <HttpError 400 when requesting https://www.googleapis.com/bigquery/v2/projects/cardinal-path/queries?alt=json returned "Syntax error: Unexpected "["">

It doesn't seem to like the brackets in my query string, but I don't know how to escape them (if that is the issue). Does anyone see what I'm doing wrong? I don't thing it's an issue with my connection to the API because I am able to see all the jobs I've started (which have all failed due to the above HttpError / Syntax Error) by calling the service object's ('bigquery' above) jobs().list() function. Thanks!

Have you tried replacing the brackets in your query with parentheses? As weird as it may sound, that might fix your problem. If that doesn't fix it, you might also want to try to not include brackets at all. See if it works. — bbkglb
– bbkglb, Commented Mar 10, 2016 at 21:32
I'm pretty sure the brackets are required for BigQuery to know that I'm referring to a table. I tried removing them and replacing them with parentheses and the resultant queries don't run either in the web UI or through the API. Do you think it might be an encoding issue? — duuug
– duuug, Commented Mar 10, 2016 at 22:11

Mosha Pasumansky · Accepted Answer · 2016-03-11 22:03:49Z

4

I see you are setting useLegacySql to False in your query request.

Bracket-quoting literals like [projectid:datasetid.tableid] is part of the legacy BigQuery SQL dialect.

The new sql dialect uses back-ticks to quote literals. So try:

SELECT hits.page.pagePath FROM `project_id:dataset_id.ga_sessions_20151019` GROUP BY hits.page.pagePath LIMIT 1

Alternately, since you are passing project_id as the project you are running the job in, all dataset lookups will resolve to that project by default, so you can drop the projectid: prefix and just use datasetid.tableid like:

SELECT hits.page.pagePath FROM dataset_id.ga_sessions_20151019 GROUP BY hits.page.pagePath LIMIT 1

While this is convenient for user-typed queries, if all your queries are code generated it is probably safest to always use quoted fully-qualified referenced.

Update: Another alternative is to use SQL's standard dot separator with non legacy SQL dialect, i.e.

SELECT hits.page.pagePath 
FROM project_id.dataset_id.ga_sessions_20151019
GROUP BY hits.page.pagePath LIMIT 1

edited Mar 11, 2016 at 22:03

Mosha Pasumansky

14.1k5 gold badges36 silver badges57 bronze badges

answered Mar 11, 2016 at 5:03

Michael Sheldon

2,06711 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mikhail Berlyant Over a year ago

what is legacy dialect vs. new dialect? any details?

Jeremy Condit Over a year ago

Coming soon! Follow code.google.com/p/google-bigquery/issues/detail?id=448.

Collectives™ on Stack Overflow

Syntax Error with Validated Query when sent into BigQuery via Python API Client

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related