How to get BigQuery's API to use Standard SQL for queries?

3
votes

BigQuery is using Legacy SQL instead of Standard SQL despite us sending a JSON payload using the flag "useLegacySQL: False". Standard SQL is getting rejected - but the system will accept Legacy SQL with our JSON below.

As far as we can tell we are following BigQuery's documentation. What do we need to get BigQuery to use Standard SQL with this API call?

Here is our BigQuery JSON payload: 

{
    'jobReference': {
        'jobId': '####',
        'projectId': 'healthlabs-4'
    },
    'configuration': {
        'query': {
            'destinationTable': {
                'tableId': u 'our_table_name',
                'datasetId': 'our_dataset_id',
                'projectId': 'our_project_id'
            },
            'useLegacySQL': False,
            'priority': 'INTERACTIVE',
            'query': u "SELECT ... FROM our_table WHERE ... GROUP BY ...   ORDER BY ...",
            'allowLargeResults': True
        }
    }
}

We can tell the query is rejected from the returning message from BigQuery which accepts our Legacy SQL but rejects our Standard SQL with this error : 

{'create_table_error': [{u'location': u'query', u'message': u'Encountered " "FROM" "FROM "" at line 1, column 333.\nWas expecting:\n    ")" ...\n    ', u'reason': u'invalidQuery'}], 'bq_table_exists': 'no'}
2
pythonsqlgoogle-bigquery

2 Answers

3
votes

We just noticed that the JSON keys are case sensitive. This is a capitalization issue. SQL should not be capitalized. The correct key is:

'useLegacySql': False

1
votes

As an alternative, instead of defining useLegacySQL, prefix your queries with #standardSQL, as in:

#standardSQL
SELECT COUNT(*)
FROM `bigquery-public-data.noaa_gsod.gsod2016`

(this works great when going through libraries that don't handle extra API options well)

https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-standard-sql#sql-prefix