This topic describes the usage and limits of Java user-defined table-valued functions (UDTFs) and Python UDTFs.

Usage notes

The following statements show typical use cases of UDTFs in MaxCompute SQL:
select user_udtf(col0, col1, col2) as (c0, c1) from my_table; 
select user_udtf(col0, col1, col2) as (c0, c1) from (select * from my_table distribute by key sort by key) t;
select reduce_udtf(col0, col1, col2) as (c0, c1) from (select col0, col1, col2 from (select map_udtf(a0, a1, a2, a3) as (col0, col1, col2) from my_table) t1 distribute by col0 sort by col0, col1) t2;

For more information about UDTFs, see Java UDFs and Python 2 UDFs.

Limits

When you use UDTFs, take note of the following limits:
  • A SELECT statement cannot contain other expressions.
    select value, user_udtf(key) as mycol ...
  • UDTFs cannot be nested.
    select user_udtf1(user_udtf2(key)) as mycol...
  • A SELECT statement cannot be used with a GROUP BY, DISTRIBUTE BY, or SORT BY clause.
    select user_udtf(key) as mycol ... group by mycol;

Examples

This example shows how to use a built-in UDTF with LATERAL VIEW to split data of the ARRAY type in a single row into multiple rows.

The pageAds table contains three columns. The first column is pageid string. The second column is col1 array<int>. The third column is col2 array<string>. The following table provides the data of this table.
pageid col1 col2
front_page [1, 2, 3] ["a", "b", "c"]
contact_page [3, 4, 5] ["d", "e", "f"]
Split all the data in the col1 and col2 columns and display each piece of data by row. Sample statement:
select pageid,mycol1, mycol2 from pageAds 
    lateral view explode(col1) myTable1 as mycol1 
    lateral view explode(col2) myTable2 as mycol2;
The following result is returned:
+------------+------------+------------+
| pageid     | mycol1     | mycol2     |
+------------+------------+------------+
| front_page | 1          | a          |
| front_page | 1          | b          |
| front_page | 1          | c          |
| front_page | 2          | a          |
| front_page | 2          | b          |
| front_page | 2          | c          |
| front_page | 3          | a          |
| front_page | 3          | b          |
| front_page | 3          | c          |
| contact_page | 3          | d          |
| contact_page | 3          | e          |
| contact_page | 3          | f          |
| contact_page | 4          | d          |
| contact_page | 4          | e          |
| contact_page | 4          | f          |
| contact_page | 5          | d          |
| contact_page | 5          | e          |
| contact_page | 5          | f          |
+------------+------------+------------+