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