BigQuery Standard SQL using Python cannot use OFFSET keyword
<p>I am trying to use BigQuery Standard SQL with Python API, though I cannot execute the query that ran successfully in WEB UI.</p>

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

<pre><code>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,
</code></pre>

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

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

<p>Any help is appreciated.</p>

<p>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:</p>

<pre><code>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" ...
</code></pre>

<p>Edit: The example linked from <a href="https://cloud.google.com/bigquery/sql-reference/enabling-standard-sql" rel="nofollow">Enabling Standard SQL</a> is incorrect. Rather than <code>useLegacySQL</code>, the option is <code>useLegacySql</code> (with <code>q</code> and <code>l</code> in lowercase). The tracking issue is at <a href="https://code.google.com/p/google-bigquery/issues/detail?id=701" rel="nofollow">https://code.google.com/p/google-bigquery/issues/detail?id=701</a>.</p>

python google-bigquery