UDT examples

Use Java arrays

A UDT gives you direct access to Java array syntax inside MaxCompute SQL. The following example demonstrates the most common operations—creating arrays, accessing elements by index, and converting between Java arrays and built-in ARRAY types—in a single query.

-- Prerequisites
set odps.sql.type.system.odps2=true;
set odps.sql.udt.display.tostring=true;

SELECT
    new Integer[10],                                              -- Create a fixed-size array of 10 elements.
    new Integer[] {c1, c2, c3},                                  -- Create an array initialized with three values.
    new Integer[][] { new Integer[] {c1, c2}, new Integer[] {c3, c4} }, -- Create a multidimensional array.
    new Integer[] {c1, c2, c3} [2],                              -- Access an element by index.
    java.util.Arrays.asList(c1, c2, c3)                          -- Convert to List<Integer>, compatible with ARRAY<INT>.
FROM VALUES (1,2,3,4) AS t(c1, c2, c3, c4);

Use JSON

The UDT runtime includes a Gson JSON library (version 2.2.4), so you can parse and serialize JSON without deploying additional JARs.

The MaxCompute runtime also includes commons-logging (1.1.1), commons-lang (2.5), commons-io (2.4), and protobuf-java (2.4.1).

-- Prerequisites
set odps.sql.type.system.odps2=true;
set odps.sql.session.java.imports=java.util.*,java,com.google.gson.*;  -- Separate multiple packages with commas.

@a := SELECT new Gson() gson;  -- Instantiate a Gson object.

SELECT
    gson.toJson(new ArrayList<Integer>(Arrays.asList(1, 2, 3))),            -- Serialize an object to a JSON string.
    cast(gson.fromJson('["a","b","c"]', List.class) AS List<String>)        -- Deserialize a JSON string, casting to List<String>.
FROM @a;

This approach deserializes JSON content directly into typed MaxCompute objects, which is simpler and more efficient than using String functions to extract and parse JSON manually.

Use complex data types

MaxCompute maps its built-in complex data types to Java interfaces as follows:

This mapping lets you call Java collection methods directly on ARRAY and MAP values in SQL.

-- Prerequisites
set odps.sql.type.system.odps2=true;
set odps.sql.session.java.imports=java.util.*;

SELECT
    size(new ArrayList<Integer>()),       -- Call the built-in size() function on an ArrayList.
    array(1,2,3).size(),                  -- Call the List.size() method on a built-in ARRAY value.
    sort_array(new ArrayList<Integer>()), -- Sort an ArrayList using the built-in sort_array() function.
    al[1],                                -- Index into an ARRAY value (java.util.List does not support indexing directly).
    Objects.toString(a),                  -- Convert an ARRAY value to a STRING.
    array(1,2,3).subList(1, 2)            -- Retrieve a sublist using the List.subList() method.
FROM (SELECT new ArrayList<Integer>(array(1,2,3)) AS al, array(1,2,3) AS a) t;

Aggregate data

UDTs do not have a native aggregate operator. To aggregate data with a UDT, first collect rows into a list using COLLECT_LIST or COLLECT_SET, then call a UDT scalar method on the resulting list.

The following example calculates the median of BigInteger values. The built-in MEDIAN function does not support java.math.BigInteger, so a UDT-based approach is used instead.

-- Prerequisites
set odps.sql.session.java.imports=java.math.*;

@test_data := SELECT * FROM VALUES (1),(2),(3),(5) AS t(value);
@a := SELECT collect_list(new BigInteger(value)) values FROM @test_data;   -- Collect all rows into a list.
@b := SELECT sort_array(values) AS values, values.size() cnt FROM @a;      -- Sort the list.
@c := SELECT
    if(
        cnt % 2 == 1,
        new BigDecimal(values[cnt div 2]),
        new BigDecimal(values[cnt div 2 - 1].add(values[cnt div 2])).divide(new BigDecimal(2))
    ) med
FROM @b;

SELECT med.toString() FROM @c;  -- Final output.

Limitation: COLLECT_LIST aggregates all rows in a group, not a subset. This is less efficient than built-in aggregate functions or user-defined aggregate functions (UDAFs) and can cause data skew for large groups. Use built-in aggregate functions whenever possible.

For aggregating all rows in a group into a concatenated string, the UDT approach is more efficient than WM_CONCAT.

Implement table-valued functions

A table-valued function accepts multiple input rows or columns and returns multiple output rows and columns. To implement one with a UDT:

1. Aggregate input rows into a list using COLLECT_LIST (see Aggregate data).

2. Call java.util.List or java.util.Map to build a collection, then use explode with lateral view to expand it into multiple rows.

3. Call getter methods on the UDT to return values in multiple columns.

The following example parses a JSON array string and returns each element's fields as separate columns.

@a := SELECT '[{"a":"1","b":"2"},{"a":"1","b":"2"}]' str;                                 -- Sample JSON input.
@b := SELECT new com.google.gson.Gson().fromJson(str, java.util.List.class) l FROM @a;   -- Deserialize JSON to a List.
@c := SELECT cast(e AS java.util.Map<Object,Object>) m FROM @b LATERAL VIEW explode(l) t AS e;  -- Expand the list into rows.
@d := SELECT m.get('a') AS a, m.get('b') AS b FROM @c;                                   -- Extract fields as columns.

SELECT a.toString() a, b.toString() b FROM @d;  -- Columns a and b are Object type; cast to STRING for output.

Overload functions

Standard UDFs overload functions by writing a separate evaluate method for each supported input type. This approach has two limitations:

• Generics are not supported, so each type requires its own method.

• ARRAY-typed inputs cannot be overloaded without a Resolve annotation.

• Python UDFs and user-defined table-valued functions (UDTFs) that rely on parameter count for dispatch cannot catch type errors at compile time.

UDTs eliminate these constraints. Because UDTs support generics, class inheritance, and variable-length parameters, a single method can cover a range of input types.

public class UDTClass {
    // Accepts any numeric type (TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, or a user-defined numeric type).
    public static Double doubleValue(Number input) {
        return input.doubleValue();
    }

    // Generic method: T must extend Number; R can be any type. Returns null or the value of R based on T.
    public static <T extends Number, R> R nullOrValue(T a, R b) {
        return a.doubleValue() > 0 ? b : null;
    }

    // Accepts ARRAY or LIST of any element type; returns the element count as BIGINT.
    public static Long length(java.util.List<? extends Object> input) {
        return input.size();
    }

    // Accepts MAP<Object, Object>. To pass a map<bigint,bigint>, use one of:
    //   1. Declare the parameter as java.util.Map<? extends Object, ? extends Object>.
    //   2. Cast the argument: UDTClass.mapSize(cast(mapObj as java.util.Map<Object, Object>)).
    public static Long mapSize(java.util.Map<Object, Object> input) {
        return input.size();
    }
}

To access the execution context inside a UDT (equivalent to the setup method in a standard UDF), call com.aliyun.odps.udt.UDTExecutionContext.get(). This returns the same com.aliyun.odps.udf.ExecutionContext object that UDFs receive via setup.

Reference embedded code in a UDT

Code-embedded UDFs let you place SQL scripts and third-party code in the same source file. This simplifies day-to-day development and maintenance.

For the full syntax and constraints, see Code-embedded UDFs.