MaxCompute:SQL UDFs

Key concepts

Prerequisites

Before you begin, ensure that you have:

• An environment running in SQL script mode. For details, see SQL in script mode.

• Confirmed that the input parameter data types you plan to use are supported by MaxCompute. For a full list, see MaxCompute V2.0 data type edition.

• The required function-level permissions on your Alibaba Cloud account. For details, see MaxCompute permissions.

Create a permanent SQL UDF

Permanent SQL UDFs are stored in the MaxCompute metadata system. After creation, they appear in the function list and can be called from any phase.

Syntax

CREATE SQL FUNCTION <function_name>(@<parameter_in1> <datatype>[, @<parameter_in2> <datatype>...])
[RETURNS @<parameter_out> <datatype>]
AS [BEGIN]
<function_expression>
[END];

Parameters

Examples

Simple function — add 1 to a BIGINT input:

CREATE SQL FUNCTION my_add(@a BIGINT) AS @a + 1;

Multi-statement function using BEGIN / END:

CREATE SQL FUNCTION my_sum(@a BIGINT, @b BIGINT, @c BIGINT) RETURNS @my_sum BIGINT
AS BEGIN
    @temp := @a + @b;
    @my_sum := @temp + @c;
END;

Create a temporary SQL UDF

Temporary SQL UDFs are not stored in MaxCompute. They exist only within the SQL script where they are defined and cannot be called in other sessions or scripts.

Syntax

FUNCTION <function_name>(@<parameter_in1> <datatype>[, @<parameter_in2> <datatype>...])
[RETURNS @<parameter_out> <datatype>]
AS [BEGIN]
<function_expression>
[END];

The parameters are identical to those for CREATE SQL FUNCTION. Omit CREATE SQL to create a temporary UDF.

Example

FUNCTION my_add(@a BIGINT) AS @a + 1;

Query a SQL UDF

Only permanent SQL UDFs can be queried — temporary UDFs are not stored in MaxCompute.

To run DESC FUNCTION from the MaxCompute client (odpscmd), upgrade the client to version 0.34.0 or later. For installation and upgrade instructions, see MaxCompute client (odpscmd).

Syntax

DESC FUNCTION <function_name>;

Example

DESC FUNCTION my_add;

Output:

Name                  my_add
Owner                 ALIYUN$s***_****@**.aliyunid.com
Created Time          2021-05-08 11:26:02
SQL Definition Text   CREATE SQL FUNCTION MY_ADD(@a BIGINT) AS @a + 1

Call a SQL UDF

Call a SQL UDF the same way you call a built-in function.

• Permanent SQL UDFs can be called in any phase.

• Temporary SQL UDFs can only be called within the script where they are defined.

The data types of the arguments you pass must match the data types defined in the UDF.

Syntax

SELECT <function_name>(<column_name>[, ...]) FROM <table_name>;

Example

-- Create a table and insert sample data.
CREATE TABLE src (c BIGINT, d STRING);
INSERT INTO TABLE src VALUES (1, '100.1'), (2, '100.2'), (3, '100.3');

-- Call my_add on column c.
SELECT my_add(c) FROM src;

Output:

+------------+
| _c0        |
+------------+
| 2          |
| 3          |
| 4          |
+------------+

Drop a SQL UDF

Syntax

DROP FUNCTION <function_name>;

Example

DROP FUNCTION my_add;

Pass a function as a parameter

SQL UDFs support function-type parameters. When you declare a parameter as FUNCTION (<input_type>) RETURNS <output_type>, the caller can pass any compatible function — a built-in function, other UDFs (Java, Python, or SQL), or an anonymous function.

Example

-- Define a SQL UDF.
FUNCTION add(@a BIGINT) AS @a + 1;

-- Define a higher-order UDF that accepts a function as an argument.
FUNCTION op(@a BIGINT, @fun FUNCTION (BIGINT) RETURNS BIGINT) AS @fun(@a);

-- Call op, passing different functions as the second argument.
-- add is a SQL UDF; abs is a MaxCompute built-in function.
SELECT op(key, add), op(key, abs) FROM VALUES (1), (2) AS t(key);

Output:

+------------+------------+
| _c0        | _c1        |
+------------+------------+
| 2          | 1          |
| 3          | 2          |
+------------+------------+

_c0 is the result of add(key) (adds 1). _c1 is the result of abs(key) (absolute value). For details on the ABS function, see Mathematical functions.

For precautions on using Lambda expressions in MaxCompute, see Lambda functions.

Use anonymous functions

When calling a UDF with a function-type parameter, pass an inline anonymous function instead of a named function. The compiler infers the anonymous function's parameter types from the UDF signature.

Example

FUNCTION op(@a BIGINT, @fun FUNCTION (BIGINT) RETURNS BIGINT) AS @fun(@a);

-- Pass an anonymous function as the second argument.
SELECT op(key, FUNCTION (@a) AS @a + 1) FROM VALUES (1), (2) AS t(key);

FUNCTION (@a) AS @a + 1 is the anonymous function. Its parameter type is inferred from op's signature — no explicit type declaration is needed.

Example: simplify repeated SQL logic

Scenario: Convert date strings from yyyy-mm-dd to yyyymmdd. The input dates are 2020-11-21, 2020-1-01, 2019-5-1, and 19-12-1.

Using a SQL UDF (recommended)

Define the conversion logic once, then call the function wherever needed:

CREATE SQL FUNCTION y_m_d2yyyymmdd(@y_m_d STRING) RETURNS @yyyymmdd STRING
AS BEGIN
    @yyyymmdd := CONCAT(
        LPAD(SPLIT_PART(@y_m_d, '-', 1), 4, '0'),
        LPAD(SPLIT_PART(@y_m_d, '-', 2), 2, '0'),
        LPAD(SPLIT_PART(@y_m_d, '-', 3), 2, '0')
    );
END;

SELECT y_m_d2yyyymmdd(d) FROM VALUES ('2020-11-21'), ('2020-1-01'), ('2019-5-1'), ('19-12-1') AS t(d);

Output:

+------------+
| _c0        |
+------------+
| 20201121   |
| 20200101   |
| 20190501   |
| 00191201   |
+------------+

Without a SQL UDF

Inline the full expression at every call site — harder to maintain and error-prone when the logic needs to change:

SELECT CONCAT(
    LPAD(SPLIT_PART(d, '-', 1), 4, '0'),
    LPAD(SPLIT_PART(d, '-', 2), 2, '0'),
    LPAD(SPLIT_PART(d, '-', 3), 2, '0')
) FROM VALUES ('2020-11-21'), ('2020-1-01'), ('2019-5-1'), ('19-12-1') AS t(d);

Limits

What's next

• SQL in script mode

• MaxCompute V2.0 data type edition

• Lambda functions

• Mathematical functions

• LIST FUNCTIONS