注：

• 以上只是程序示例，关于如何在ODPS中运行UDTF,方法与UDF类似，具体实现步骤请参考运行UDF。

在SQL中可以这样使用这个UDTF，假设在ODPS上创建UDTF时注册函数名为user_udtf：

      select user_udtf(col0, col1) as (c0, c1) from my_table;

假设my_table的col0， col1的值为：

       +------+------+
       | col0 | col1 |
       +------+------+
       | A B  | 1    |
       | C D  | 2    |
       +------+------+

则select出的结果为：

       +----+----+
       | c0 | c1 |
       +----+----+
       | A  | 1  |
       | B  | 1  |
       | C  | 2  |
       | D  | 2  |
       +----+----+

使用说明

UDTF在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;

但使用UDTF有如下使用限制：

• 同一个SELECT子句中不允许有其他表达式

     select value, user_udtf(key) as mycol ...

• UDTF不能嵌套使用

     select user_udtf1(user_udtf2(key)) as mycol...

• 不支持在同一个select子句中与 group by / distribute by / sort by 联用

    select user_udtf(key) as mycol ... group by mycol

其他UDTF示例

在UDTF中，用户可以读取ODPS的 资源 。下面将介绍利用udtf读取ODPS资源的示例。

1. 编写udtf程序，编译成功后导出jar包(udtfexample1.jar)。

     package com.aliyun.odps.examples.udf;
    import java.io.BufferedReader;
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.InputStreamReader;
    import java.util.Iterator;
    import com.aliyun.odps.udf.ExecutionContext;
    import com.aliyun.odps.udf.UDFException;
    import com.aliyun.odps.udf.UDTF;
    import com.aliyun.odps.udf.annotation.Resolve;
    /**
    * project: example_project 
    * table: wc_in2 
    * partitions: p2=1,p1=2 
    * columns: colc,colb
    */
    @Resolve({ "string,string->string,bigint,string" })
    public class UDTFResource extends UDTF {
    ExecutionContext ctx;
    long fileResourceLineCount;
    long tableResource1RecordCount;
    long tableResource2RecordCount;
    @Override
    public void setup(ExecutionContext ctx) throws UDFException {
    this.ctx = ctx;
    try {
      InputStream in = ctx.readResourceFileAsStream("file_resource.txt");
      BufferedReader br = new BufferedReader(new InputStreamReader(in));
      String line;
      fileResourceLineCount = 0;
      while ((line = br.readLine()) != null) {
        fileResourceLineCount++;
      }
      br.close();
      Iterator<Object[]> iterator = ctx.readResourceTable("table_resource1").iterator();
      tableResource1RecordCount = 0;
      while (iterator.hasNext()) {
        tableResource1RecordCount++;
        iterator.next();
      }
      iterator = ctx.readResourceTable("table_resource2").iterator();
      tableResource2RecordCount = 0;
      while (iterator.hasNext()) {
        tableResource2RecordCount++;
        iterator.next();
      }
    } catch (IOException e) {
      throw new UDFException(e);
    }
   }
   @Override
   public void process(Object[] args) throws UDFException {
    String a = (String) args[0];
    long b = args[1] == null ? 0 : ((String) args[1]).length();
    forward(a, b, "fileResourceLineCount=" + fileResourceLineCount + "|tableResource1RecordCount="
        + tableResource1RecordCount + "|tableResource2RecordCount=" + tableResource2RecordCount);
   }
   }

2. 添加资源到ODPS:

   Add file file_resource.txt;
   Add jar udtfexample1.jar;
   Add table table_resource1 as table_resource1;
   Add table table_resource2 as table_resource2;

3. 在ODPS中创建UDTF函数(my_udtf)：

   create function mp_udtf as com.aliyun.odps.examples.udf.UDTFResource using 'udtfexample1.jar, file_resource.txt, table_resource1, table_resource2';

4. 在odps创建资源表table_resource1、table_resource2, 物理表tmp1。并插入相应的数据。

5. 运行该udtf：

select mp_udtf("10","20") as (a, b, fileResourceLineCount) from table_resource1;  
 返回：
+-------+------------+-------+
| a | b      | fileResourceLineCount |
+-------+------------+-------+
| 10    | 2          | fileResourceLineCount=3|tableResource1RecordCount=0|tableResource2RecordCount=0 |
| 10    | 2          | fileResourceLineCount=3|tableResource1RecordCount=0|tableResource2RecordCount=0 |
+-------+------------+-------+