hive自定义udf函数,在udf函数内怎么读取hive表数据
我想实现一个udf函数,传入一个参数,去读取hive自己的表数据进行业务测试
package com.hive;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.hive.ql.exec.persistence.BytesBytesMultiHashMap.Result;
import org.apache.hadoop.io.Text;
public class ConnHive extends UDF{
public Text evaluate(Text input) throws Exception
{
String driver="org.apache.hadoop.hive.jdbc.HiveDriver";
String url = "jdbc:hive://192.168.175.129:10000";
String userName = "";
String passWord = "";
String sql = "select name from test where id = "+Integer.parseInt(input.toString());
Class.forName(driver);
Connection con = DriverManager.getConnection(url,userName,passWord);
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);
StringBuffer sbf = new StringBuffer();
while(rs.next())
{
sbf.append(rs.getString(1)).append("|");
}
return new Text(sbf.toString().substring(0, sbf.length()-1));
}
}
测试表和数据
create table test
(
id int ,
name string
)
comment 'test'
row format delimited
fields terminated by '\t'
stored as textfile;
12 abc
345 cde
678 def
报错结果:
hive> select t.name,connhive(123) from ymdd.test t;
FAILED: SemanticException [Error 10014]: Line 1:14 Wrong arguments '123': org.apache.hadoop.hive.ql.metadata.HiveException: Unable to execute method public org.apache.hadoop.io.Text com.hive.ConnHive.evaluate(org.apache.hadoop.io.Text) throws java.lang.Exception on object com.hive.ConnHive@3ad8e7 of class com.hive.ConnHive with arguments {123:org.apache.hadoop.io.Text} of size 1
Text类在hadoop-common-2.6.1.jar中已经add jar操作了