run_async_query in Python gcloud BigQuery using Standard SQL instead of Legacy SQL

Question

I need to run an async query using the gcloud python BigQuery library. Furthermore I need to run the query using the beta standard sql instead of the default legacy sql.

According to the documentation here, here, and here I believe I should be able to just set the use_legacy_sql property on the job to False. However, this still results in an error due to the query being processed against Legacy SQL. How do I successfully use this property to indicate which SQL standard I want the query to be processed with?

Example Python code below:

stdz_table = stdz_dataset.table('standardized_table1')
job_name = 'asyncjob-test'
query = """
    SELECT TIMESTAMP('2016-03-30 10:32:15', 'America/Chicago') AS special_date 
    FROM my_dataset.my_table_20160331;
    """
stdz_job = bq_client.run_async_query(job_name,query)
stdz_job.use_legacy_sql = False
stdz_job.allow_large_results = True
stdz_job.create_disposition = 'CREATE_IF_NEEDED'
stdz_job.destination = stdz_table
stdz_job.write_disposition = 'WRITE_TRUNCATE'
stdz_job.begin()

# wait for job to finish
while True:
    stdz_job.reload()
    if stdz_job.state == 'DONE':
        # print use_legacy_sql value, and any errors (will be None if job executed successfully)
        print stdz_job.use_legacy_sql
        print json.dumps(stdz_job.errors)
        break
    time.sleep(1)

This outputs:

False
[{"reason": "invalidQuery", "message": "2.20 - 2.64: Bad number of arguments. Expected 1 arguments.", "location": "query"}]

which is the same error you'd get if you ran it in the BigQuery console using Legacy SQL. When I copy paste the query in BigQuery console and run it using Standard SQL, it executes fine. Note: The error location (2.20 - 2.64) might not be exactly correct for the query above since it is a sample and I have obfuscated some of my personal info in it.

Why are you checking on job.state/job.errors if you created stdz_job. What is the job object here ? — Mosha Pasumansky
Good eye Mosha, I adapted my code to make this sample--in the original I'm looping through multiple stdz job instances, keeping them in a list, and later looping through them all again to check the status. I edited the code to reflect the single job scenario in this sample. — KevinTydlacka
Are your other properties (e.g. destination, allow_large_results) being set properly too via this method? I peeked through the client code and this seems like it should just work. It looks like support for the useLegacySql parameter was added about a week ago: do you have the most up to date version of the client? — Danny Kitt
@DannyKitt Yes the other properties are being set successfully. I am using the most recent pypi version available--gcloud 0.17.0 (which I installed with pip). — KevinTydlacka

Adam Adam · Accepted Answer · 2016-08-05T23:08:24

The use_legacy_sql property did not exist as of version 0.17.0, so you'd have needed to check out the current master branch. However it does now exist of as release 0.18.0 so after upgrading gcloud-python via pip you should be good to go.

run_async_query in Python gcloud BigQuery using Standard SQL instead of Legacy SQL

1 Answers