在之前写的另外一篇文章“ Trafodion TMUDF(Table-Mapping UDF) ” http://blog.youkuaiyun.com/post_yuan/article/details/53173964, 我们提到了什么是Trafodion的Table-Mapping UDF以及如何去实现一个简单的TMUDF。
这篇文章介绍一个Build-In的TMUDF,可以用于访问远程的数据库,包括Trafodion,Oracle等其他支持JDBC的相关数据库。详细代码及测试用例请参考https://github.com/apache/incubator-trafodion/commit/a34771927c1afd3348fb4609b4c2944f5c47507a
基本用法如下,
select ... from udf(JDBC(
<name of JDBC driver jar>,
<name of JDBC driver class in the jar>,
<connection string>,
<user name>,
<password>,
<statement_type>,
<sql statement 1>
[ , <sql statements 2 ...n> ] )) ...
The first 7 arguments are required and must be
string literals that are available at compile
time.
Statement type:
'source': This statement produces a result
(only type allowed at this time)
(may support "target" to insert
into a table via JDBC later)
下面通过实例介绍如何获取远端的Trafodion数据库数据和Oracle数据库数据。
1 访问Trafodion数据库
SQL>select *
from udf(jdbc('jdbcT4.jar',
'org.trafodion.jdbc.t4.T4Driver',
'jdbc:t4jdbc://10.10.11.12:23400/:',
'trafodion',
'traf123',
'source',
'select * from (values (''Hello'', ''World''), (''Hallo'', ''Welt'')) T(a,b)'));
A B
---------- ----------
Hello World
Hallo Welt
--- 2 row(s) selected
2 访问Oracle数据库
(注:如果需要访问远程非Trafodion数据库,需要添加具体数据库对应的jdbc驱动包,具体方法参考下面第3点)
SQL>select *
from udf(jdbc('ojdbc6.jar',
'oracle.jdbc.driver.OracleDriver',
'jdbc:oracle:thin:@10.10.11.16:1521:esgyn',
'system',
'12345',
'source',
'select * from dual'));
DUMMY
-----
X
--- 1 row(s) selected.
SQL>select *
from udf(jdbc('ojdbc6.jar',
'oracle.jdbc.driver.OracleDriver',
'jdbc:oracle:thin:@10.10.11.16:1521:esgyn',
'system',
'12345',
'source',
'select to_char(a) as a,to_char(b) as b from test_jdbcudr'));
A B
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
1 2
--- 1 row(s) selected.
3 如何在Trafodion环境添加Oracle JDBC驱动包
1.选择一台Trafodion节点,把下载的ojdbc6.jar包拷贝到$MY_SQROOT/udr/external_libs下
[trafodion@n12 external_libs]$ pwd
/home/trafodion/esgynDB-2.2.0/udr/external_libs
[trafodion@n12 external_libs]$ ll
total 2676
-rw-rw-r-- 1 trafodion trafodion 2739670 Dec 26 20:40 ojdbc6.jar
2.pdcp把上述节点的ojdbc6.jar包复制到其他Trafodion节点
[trafodion@n12 external_libs]$ pdcp $MY_NODES ojdbc6.jar $PWD