CODEWITHSUNDEEP
pythongoogle-app-enginegoogle-bigquery
Leonardo Murri
Leonardo Murri

Reputation: 40

Google BigQuery - Python Query not being parsed correctly

I am trying to set up a simple Google BigQuery application using the Python API. I followed the quick-start guide: 

import argparse

from googleapiclient.discovery import build
from googleapiclient.errors import HttpError
from oauth2client.client import GoogleCredentials


def main(project_id):
    print "hello"
    # [START build_service]
    # Grab the application's default credentials from the environment.
    credentials = GoogleCredentials.get_application_default()
    # Construct the service object for interacting with the BigQuery API.
    bigquery_service = build('bigquery', 'v2', credentials=credentials)
    # [END build_service]

    query_request = bigquery_service.jobs()
    query_data = {
    'query': (
          'SELECT ticker,close1'
          'FROM Data.data_7 '
          'WHERE ticker = "GTIM"'
          'LIMIT 10')
    }

    query_response = query_request.query(
                                 projectId=project_id,
                                 body=query_data).execute()

   print('Query Results:')
   for row in query_response['rows']:
        print('\t'.join(field['v'] for field in row['f']))


main("sqlserver-1384")

And was able to successfully run the above query. However whenever I change it to:

   'query': (
          'SELECT ticker,close1'
          'FROM Data.data_7 '
          'ORDER BY close1 ASC'
          'LIMIT 10')
    }

I get the following error:

Traceback (most recent call last):
  File "server1.py", line 57, in <module>
    main("sqlserver-1384")
  File "server1.py", line 50, in main
    body=query_data).execute()
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/oauth2client/util.py", line 135, in positional_wrapper
    return wrapped(*args, **kwargs)
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/googleapiclient/http.py", line 832, in execute
    raise HttpError(resp, content, uri=self.uri)
googleapiclient.errors.HttpError: <HttpError 400 when requesting https://www.googleapis.com/bigquery/v2/projects/sqlserver-1384/queries?alt=json returned "Encountered " <ID> "ASCLIMIT "" at line 1, column 54.
Was expecting:
    <EOF>">

Is something wrong with my format? I ran the same query on the Google BigQuery Web Console and it worked fine.

Thank you

Upvotes: 1

Views: 220

Answers (1)

mgilson
mgilson

Reputation: 309821

When the query string gets concatenated by the python parser, you're left with the word ASCLIMIT which isn't valid BQ SQL. Add a space after ASC in the query and you should be OK.

{
    'query': (
          'SELECT ticker,close1 '  # Space at the end of this line too
          'FROM Data.data_7 '
          'ORDER BY close1 ASC '  # Space at the end of this line
          'LIMIT 10')
}

Alternatively, write your query using triple quoted strings:

'''
SELECT ticker, close1
FROM Data.data_7
ORDER BY close1 ASC
LIMIT 10
'''

As then newlines will be preserved.

Upvotes: 3

Related Questions