CODEWITHSUNDEEP
sqlgoogle-bigquery
winnie99
winnie99

Reputation: 123

How do I convert this standard sql to legacy sql code in bigquery?

CREATE TEMPORARY FUNCTION
  s2id(lit FLOAT64,
    low FLOAT64,
    level FLOAT64)
  RETURNS STRING
  LANGUAGE js AS """
return litLowToId(lit,low,level);
"""
OPTIONS
  (library="test.js");

Big query is highlighting the lit part without telling me why. It just says encountered "" in line 2. What is happening here?

Upvotes: 0

Views: 442

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173028

Big query is highlighting the lit part without telling me why. It just says encountered "" in line 2. What is happening here?

The reason you are getting above error in UI is because you are using Legacy SQL
Switch to Standard SQL (uncheck Use Legacy SQL checkbox in Options Panel [in Classic UI] or check Standard SQL dialect radiobox [in new UI] ) Alternatively you can just add below line to the very top (as a first line) of your whole code
See Enabling Standard SQL for more details

#standardSQL

In addition you have to provided a valid path - something like below 

library="gs://my-bucket/path/to/test.js"

And finally, you need to have actual query below your function

How do I convert this standard sql to legacy sql code in bigquery?

I strongly recommend you to stay with BigQuery Standard SQL
In case you you locked by existing legacy sql code - you might rather want to migrate your sql query from legacy to standard instead

In any case UDF for BigQuery legacy and standard dialect are quite different - see Differences in user-defined JavaScript functions

Upvotes: 1

Related Questions