如何获得BigQuery的API以使用标准SQL查询?

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.

BigQuery使用遗留SQL而不是标准SQL，尽管我们使用“useLegacySQL: False”标记发送JSON有效负载。标准SQL被拒绝了，但是系统将接受遗留SQL和下面的JSON。

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?

据我们所知，我们正在遵循BigQuery的文档。要让BigQuery在这个API调用中使用标准SQL，我们需要什么?

Here is our BigQuery JSON payload:

这是我们的BigQuery JSON有效负载:

{
    '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 :

我们可以从BigQuery返回的消息中得知查询被拒绝，该消息接受我们的遗留SQL，但拒绝我们的标准SQL，并出现以下错误:

{'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 个解决方案

#1

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

我们刚刚注意到JSON键是区分大小写的。这是一个资本化问题。SQL不应该大写。正确的关键是:

'useLegacySql': False

“useLegacySql”:假的

#2

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

作为一种替代方法，与其定义useLegacySQL，不如在查询前面加上#standardSQL，如下所示:

#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)

(这在那些不能很好地处理额外API选项的库中非常有用)

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

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

#1