Query with UDF in web UI

Question

I was trying to use a user defined query in web big query UI. According to documentation, https://cloud.google.com/bigquery/sql-reference/user-defined-functions, I did this step by step.

Uncheck the Use Legacy SQL checkbox.
type this in Query Editor

Codes below are the same as https://cloud.google.com/bigquery/sql-reference/user-defined-functions

CREATE TEMPORARY FUNCTION timesTwo(x INT64)
RETURNS INT64
  LANGUAGE js AS """
  return x*2;
""";

3. Below the UDF statement, type your query.

SELECT timesTwo(numbers) as doubles
FROM UNNEST([1, 2, 3, 4, 5]) AS numbers;

Then I clicked Run Query but gives me error like this

Not Implemented: UDFs are currently only supported for legacy SQL queries.

Is it because of the legacy SQL option? But I unchecked it as the document says.

Make sure that nothing is in the "UDF Editor" text box. There is an update to the UI coming soon that should remove it when "Use Legacy SQL" is unchecked. Note also the caveat in the documentation about INT64--due to the limitations of JavaScript, there is no way of losslessly representing an INT64 aside from as a decimal string. We're working on a change to indicate that FLOAT64 is the preferred way of passing numbers as input. — Elliott Brossard

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2016-09-06T15:31:51

Scalar UDF (in Standard more) is a "part" of query, thus all needs to be put in Query Editor (no UDF Editor needed here)

CREATE TEMPORARY FUNCTION timesTwo(x INT64)
RETURNS INT64
  LANGUAGE js AS """
  return x*2;
""";
SELECT timesTwo(numbers) as doubles
FROM UNNEST([1, 2, 3, 4, 5]) AS numbers;

Query with UDF in web UI

1 Answers