:UDTF usage

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;

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;

For example, 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 in 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          |
+------------+------------+------------+