使用Python的BigQuery标准SQL不能使用OFFSET关键字

Question

我正在尝试将BigQuery标准SQL与pythonapi结合使用，尽管我无法执行在webui中成功运行的查询。你知道吗

基本上，我是拆分一个字符串，然后使用OFFSET关键字来获取特定索引处的值。具体如下：

CASE WHEN t.depth = 1 THEN '' WHEN t.depth = 2 THEN '' WHEN t.depth = 3 THEN '' WHEN t.depth = 4 THEN '' WHEN t.depth = 5 THEN '' WHEN t.depth = 6 THEN t.curr WHEN t.depth = 7 THEN SPLIT(t.ancestry,'/')[OFFSET(6)] ELSE '' END AS level7,


CASE WHEN t.depth = 1 THEN '' WHEN t.depth = 2 THEN '' WHEN t.depth = 3 THEN '' WHEN t.depth = 4 THEN '' WHEN t.depth = 5 THEN t.curr WHEN t.depth = 6 THEN SPLIT(t.ancestry,'/')[OFFSET(5)] WHEN t.depth = 7 THEN SPLIT(t.ancestry,'/')[OFFSET(5)] ELSE '' END AS level6,

上面的代码在webui中运行时没有问题，但是使用pythonapi和设置useLegacySQL = False，我得到以下错误

raise HttpError(resp, content, uri=self.uri)
googleapiclient.errors.HttpError: <HttpError 400 when requesting https://www.googleapis.com/bigquery/v2/projects/************* 
returned "Encountered " "]" "[OFFSET(6)] "" at line 7, column 217. Was expecting: "END" ...">

感谢您的帮助。你知道吗

Answer 1

看起来查询是使用基于错误消息的遗留SQL执行的。当我尝试使用遗留SQL执行此操作时，我会看到相同的消息，例如：

SELECT
  CASE s
    WHEN 'first' THEN SPLIT(arr, ',')[OFFSET(0)]
    WHEN 'second' THEN SPLIT(arr, ',')[OFFSET(1)]
    ELSE NULL
  END AS val
FROM (SELECT '1,2' AS arr, 'second' AS s);

Error: Encountered " "]" "[OFFSET(0)] "" at line 1, column 48. Was expecting: "END" ...

编辑：从Enabling Standard SQL链接的示例不正确。选项不是useLegacySQL，而是useLegacySql（小写q和l）。跟踪问题在https://code.google.com/p/google-bigquery/issues/detail?id=701。你知道吗