BigQuery Standard SQL using Python cannot use OFFSET keyword

Question

I am trying to use BigQuery Standard SQL with Python API, though I cannot execute the query that ran successfully in WEB UI.

Basically, I am splitting a string and then using OFFSET keyword to get the value at a particular index. As follows:

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,

The above code runs without an issue in WEB UI, whereas using Python API and setting useLegacySQL = False, I get the following error

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

Any help is appreciated.

Elliott Brossard Elliott Brossard · Accepted Answer · 2016-09-19T15:56:10

It looks like the query is being executed using legacy SQL based on the error message. I see the same message when I try to execute this using legacy SQL, for instance:

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

Edit: The example linked from Enabling Standard SQL is incorrect. Rather than useLegacySQL, the option is useLegacySql (with q and l in lowercase). The tracking issue is at https://code.google.com/p/google-bigquery/issues/detail?id=701.

BigQuery Standard SQL using Python cannot use OFFSET keyword

1 Answers