BigQuery: external UDFs with standard SQL

Question

Today I tried to write a UDF in standard SQL language in the Web Editor UI, and I have already unchecked the option 'Use Legacy SQL', but it returned to me the following error message: Not Implemented: You cannot use legacy SQL UDFs with standard SQL queries. See https://cloud.google.com/bigquery/docs/reference/standard-sql/migrating-from-legacy-sql#differences_in_user-defined_javascript_functions

Therefore I tried an example of external UDF provided on the Google Cloud Platform: https://cloud.google.com/bigquery/docs/reference/standard-sql/user-defined-functions. But it still returns to me the same error message. In the following the example:

CREATE TEMPORARY FUNCTION multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS """
  return x*y;
""";
WITH numbers AS
  (SELECT 1 AS x, 5 as y
  UNION ALL
  SELECT 2 AS x, 10 as y
  UNION ALL
  SELECT 3 as x, 15 as y)
SELECT x, y, multiplyInputs(x, y) as product
FROM numbers;

Question: How to use external UDF with standard SQL in Web UI?

Elliott Brossard Elliott Brossard · Accepted Answer · 2017-04-05T10:48:04

Make sure not to enter the input in the "UDF Editor" panel. It should go with the rest of your query. See the topic in the migration guide for an example:

#standardSQL
-- Computes the harmonic mean of the elements in 'arr'.
-- The harmonic mean of x_1, x_2, ..., x_n can be expressed as:
--   n / ((1 / x_1) + (1 / x_2) + ... + (1 / x_n))
CREATE TEMPORARY FUNCTION HarmonicMean(arr ARRAY<FLOAT64>)
  RETURNS FLOAT64 LANGUAGE js AS """
var sum_of_reciprocals = 0;
for (var i = 0; i < arr.length; ++i) {
  sum_of_reciprocals += 1 / arr[i];
}
return arr.length / sum_of_reciprocals;
""";

WITH T AS (
  SELECT GENERATE_ARRAY(1.0, x * 4, x) AS arr
  FROM UNNEST([1, 2, 3, 4, 5]) AS x
)
SELECT arr, HarmonicMean(arr) AS h_mean
FROM T;

BigQuery: external UDFs with standard SQL

1 Answers