Trafodion TMUDF(Table-Mapping UDF)

来源：互联网发布：网络拓扑算法编辑：程序博客网时间：2024/06/01 10:17

通过前面文章我们已经知道，Trafodin提供了几种UDR(User-Defined Routines )，包括SPJ存储过程,标量UDF等。本文则继续介绍一种UDR，称为TMUDF，即Table-Mapping函数。
与前两者不同的是，TMUDF主要用于FROM子句中，即它的返回值可以看成是一个SQL TABLE。TMUDF目前可以使用C++和Java两种语言实现。
下面就介绍下如何实现一个简单的TMUDF并调用它，以Java实现为例，

1 编写基础Java类，需要实现UDR接口
（注：UDR接口在$MY_SQROOT/export/lib/trafodion-sql*.jar）

import org.trafodion.sql.udr.*;class HelloJavaWorld extends UDR{  // default constructor  public HelloJavaWorld()  {}  // override the runtime method  @Override  public void processData(UDRInvocationInfo info,                          UDRPlanInfo plan)    throws UDRException  {    // set the output column    info.out().setString(0, "Hello Java world!");    // produce a single output row    emitRow(info);  }}

2 编译Java类生成Class文件，并打包成Jar文件

[trafodion@cent-2 ~]$ /opt/jdk1.7.0_67/bin/javac  -cp trafodion-sql-1.3.0.jar tmudf.java[trafodion@cent-2 ~]$ /opt/jdk1.7.0_67/bin/jar cvf HelloJavaWorld.jar HelloJavaWorld.class[trafodion@cent-2 ~]$ ls -la HelloJavaWorld.*-rw-rw-r-- 1 trafodion trafodion 696 Nov 15 17:23 HelloJavaWorld.class-rw-rw-r-- 1 trafodion trafodion 854 Nov 15 17:25 HelloJavaWorld.jar

3 创建LIBRARY

SQL>create library helloworldlib file '/home/trafodion/HelloJavaWorld.jar';--- SQL operation complete.SQL>get libraries;Libraries in Schema TRAFODION.SEABASE=====================================HELLOWORLDLIB--- SQL operation complete.

4 创建Table Mapping UDF

SQL>create table_mapping function helloworld()+>returns (col1 char(40))+>external name 'HelloJavaWorld'+>language java+>library helloworldlib;--- SQL operation complete.SQL>get table_mapping functions;Table_mapping functions in Schema TRAFODION.SEABASE===================================================HELLOWORLD--- SQL operation complete.

5 调用UDF

SQL>select * from udf(helloworld());COL1----------------------------------------Hello Java world!--- 1 row(s) selected.

6 查看TMUDF定义

SQL>showddl table_mapping function helloworld;CREATE TABLE_MAPPING FUNCTION TRAFODION.SEABASE.HELLOWORLD  (  )  RETURNS  (    OUT COL1 CHAR(40) CHARACTER SET ISO88591  )  EXTERNAL NAME 'HelloJavaWorld'  LIBRARY TRAFODION.SEABASE.HELLOWORLDLIB  LANGUAGE JAVA  NO SQL  SAFE EXECUTION MODE  ;-- GRANT EXECUTE ON FUNCTION TRAFODION.SEABASE.HELLOWORLD TO DB__ROOT WITH GRANT OPTION;--- SQL operation complete.

1 0