Dataflow SQL user-defined functions

Dataflow SQL supports user-defined functions (UDFs) written in SQL. These functions accept columns of input and perform actions, returning the result of those actions as a value.

Syntax

CREATE FUNCTION
    function_name
    ([named_parameter[, ...]])
  [RETURNS data_type]
  LANGUAGE sql
  AS (sql_expression)

named_parameter:
  param_name param_type

This syntax consists of the following components:

  • CREATE FUNCTION. Creates a function.

  • function_name. Specifies the name of the function.

  • named_parameter. Consists of a comma-separated param_name and param_type pair. The value of param_type is a Dataflow SQL data type.

  • [RETURNS data_type]. Specifies the data type that the function returns. For SQL UDFs, the RETURNS clause is optional.

  • sql_expression. Specifies the SQL expression that defines the function.

To invoke the function, include a SELECT statement after the CREATE FUNCTION statement. Separate multiple statements in the query using a semicolon. The last statement must be a SELECT statement, and you can use only one SELECT statement.

SQL UDF example

The following example creates a function named addFourAndMultiply and calls the function on a list of numbers.

CREATE FUNCTION addFourAndMultiply(x INT64, y INT64) AS ((x + 4) * y);

WITH numbers AS
  (SELECT 1 as val
  UNION ALL
  SELECT 3 as val
  UNION ALL
  SELECT 4 as val
  UNION ALL
  SELECT 5 as val)
SELECT val, addFourAndMultiply(val, 2) AS result
FROM numbers;