How to use BigQuery UDF in Standard SQL - WebUI?

Question

I would like to know how to use BigQuery UDF in the new Standard SQL - WebUI.

UDF functions, only seems to work in "Use Legacy SQL Mode" enabled, but not in new the Standard SQL one.

This is the UDF function in the UDF Editor:

// UDF registration
bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  // The UDF
function urlDecode(row, emit) {
  emit({title: decodeHelper(row.title),
        requests: row.num_requests});
}

// Helper function for error handling
function decodeHelper(s) {
  try {
    return decodeURI(s);
  } catch (ex) {
    return s;
  }
}
);

And this is the query in the Query Editor:

SELECT requests, title
FROM
  urlDecode(
    SELECT
      title, sum(requests) AS num_requests
    FROM
      [fh-bigquery:wikipedia.pagecounts_201504]
    WHERE language = 'fr'
    GROUP EACH BY title
  )
WHERE title LIKE '%ç%'
ORDER BY requests DESC
LIMIT 100

If I remove the tick from "Use Legacy Mode" in the UDF Editor, a message appears saying: "Only inline UDFs are supported in standard SQL". Then a red message appears in Bigquery's validator saying: "Error: Syntax error: Expected ")" but got keyword SELECT at [4:5]" ... this last refers to the query and it underlines the select sentence in red.

So, the next questions come to my mind:

Is there a problem in the UDF function? Maybe it is not written inline?
The previous error message says: "Only inline UDFs are supported in standard SQL". So, this means that I should remove the tick from "Use Legacy Mode"?

Thanks for your help.

If answer has helped you solve your problem and you accepted it - you might also consider voting it up. See more at stackoverflow.com/help/someone-answers and Upvote section in meta.stackexchange.com/questions/5234/… — Mikhail Berlyant

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2016-10-12T01:03:28

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;

See more for User-Defined Functions in Standard SQL

For your particular query in question - try below

CREATE TEMPORARY FUNCTION urlDecode(x STRING)
RETURNS STRING
  LANGUAGE js AS """
  // Helper function for error handling
  function decodeHelper(s) {
    try {
      return decodeURI(s);
    } catch (ex) {
      return s;
    }
  }
  return decodeHelper(x);
""";

SELECT
  urlDecode(title) AS title, SUM(requests) AS requests
FROM
  `fh-bigquery.wikipedia.pagecounts_201504`
WHERE LANGUAGE = 'fr'
GROUP BY title
HAVING title LIKE '%ç%'
ORDER BY requests DESC
LIMIT 100

How to use BigQuery UDF in Standard SQL - WebUI?

1 Answers