Hi All,
I am trying to add UDF jar from the JDBC Java Client program but somehow
getting some errors.
I am using HiveServer2 and here is my conf/hive-site.xml
<configuration>
<property> <name>hive.metastore.uris</name>
<value>thrift://localhost:9083</value> </property>
<property>
<name>hive.server2.enable.doAs</name>
<value>false</value>
<description>Setting this property to true will have hive server2 execute
hive operations as the user making the calls to it.</description></property>
</configuration>
Here is my Java JDBC Client code snippet:
Statement stmt = null;
ResultSet res = null;
Connection con = getHiveConnection(host, port, db);
try {
stmt = con.createStatement();
String[] args = new String[2];
args[0] = "add jar /home/nirmal/Teradata/udf/hiveUDF-1.0-SNAPSHOT.jar";
args[1] = "CREATE TEMPORARY FUNCTION zeroifnull AS
'com.impetus.ilabs.etloffload.ZeroIfNullUDF'";
for (String queryUDF : args) {
stmt.execute(queryUDF);
}
}
java.sql.SQLException: org.apache.thrift.protocol.TProtocolException: Required
field 'statement' is unset!
Struct:TExecuteStatementReq(sessionHandle:TSessionHandle(sessionId:THandleIdentifier(guid:A1
4A 70 6F BE 4F 48 FE 89 00 44 95 3C 5E D6 ED, secret:FB 5D D5 DE 9D 3C 46 C3
93 9E AF 2A 47 D5 7A DD)), statement:null, confOverlay:{}, runAsync:true)
at org.apache.hive.jdbc.HiveStatement.execute(HiveStatement.java:236)
at
org.impetus.etloffload.hive.HiveJdbcClient.executeExplain(HiveJdbcClient.java:57)
at org.impetus.etloffload.hive.HiveJdbcClient.main(HiveJdbcClient.java:26)
Caused by: org.apache.thrift.protocol.TProtocolException: Required field
'statement' is unset!
Struct:TExecuteStatementReq(sessionHandle:TSessionHandle(sessionId:THandleIdentifier(guid:A1
4A 70 6F BE 4F 48 FE 89 00 44 95 3C 5E D6 ED, secret:FB 5D D5 DE 9D 3C 46 C3
93 9E AF 2A 47 D5 7A DD)), statement:null, confOverlay:{}, runAsync:true)
at
org.apache.hive.service.cli.thrift.TExecuteStatementReq.validate(TExecuteStatementReq.java:562)
at
org.apache.hive.service.cli.thrift.TCLIService$ExecuteStatement_args.validate(TCLIService.java:4055)
at
org.apache.hive.service.cli.thrift.TCLIService$ExecuteStatement_args$ExecuteStatement_argsStandardScheme.write(TCLIService.java:4112)
at
org.apache.hive.service.cli.thrift.TCLIService$ExecuteStatement_args$ExecuteStatement_argsStandardScheme.write(TCLIService.java:4081)
at
org.apache.hive.service.cli.thrift.TCLIService$ExecuteStatement_args.write(TCLIService.java:4032)
at org.apache.thrift.TServiceClient.sendBase(TServiceClient.java:63)
at
org.apache.hive.service.cli.thrift.TCLIService$Client.send_ExecuteStatement(TCLIService.java:219)
at
org.apache.hive.service.cli.thrift.TCLIService$Client.ExecuteStatement(TCLIService.java:211)
at org.apache.hive.jdbc.HiveStatement.execute(HiveStatement.java:230)
... 2 more
When I use stmt.executeQuery(queryUDF); instead of stmt.execute(queryUDF); in
the above code I get the below error:
java.sql.SQLException: The query did not generate a result set!
at org.apache.hive.jdbc.HiveStatement.executeQuery(HiveStatement.java:356)
at
org.impetus.etloffload.hive.HiveJdbcClient.executeExplain(HiveJdbcClient.java:57)
at org.impetus.etloffload.hive.HiveJdbcClient.main(HiveJdbcClient.java:26)
I also tried putting the UDF jar in HDFs and then referring by absolute hdfs
path but still same error:
add jar hdfs://localhost:9000/udfjars/hiveUDF-1.0-SNAPSHOT.jar
I also verified both from Hive CLI and Beeline and able to add jars and test
the UDF function:
Hive CLI
hive> ADD JAR /home/nirmal/Teradata/udf/hiveUDF-1.0-SNAPSHOT.jar;
Added /home/nirmal/Teradata/udf/hiveUDF-1.0-SNAPSHOT.jar to class path
Added resource: /home/nirmal/Teradata/udf/hiveUDF-1.0-SNAPSHOT.jar
hive> CREATE TEMPORARY FUNCTION zeroifnull AS
'com.impetus.ilabs.etloffload.ZeroIfNullUDF';
OK
Time taken: 0.064 seconds
hive> select name,zeroifnull(age) from employeeudf;
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1407273867669_0006, Tracking URL =
http://nirmal-Vostro-3560:8088/proxy/application_1407273867669_0006/
Kill Command = /home/nirmal/hadoop/hadoop-2.1.0.2.0.5.0-67/bin/hadoop job
-kill job_1407273867669_0006
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2014-08-06 13:30:06,600 Stage-1 map = 0%, reduce = 0%
2014-08-06 13:30:11,931 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.33
sec
MapReduce Total cumulative CPU time: 1 seconds 330 msec
Ended Job = job_1407273867669_0006
MapReduce Jobs Launched:
Job 0: Map: 1 Cumulative CPU: 1.33 sec HDFS Read: 271 HDFS Write: 43 SUCCESS
Total MapReduce CPU Time Spent: 1 seconds 330 msec
OK
John 25
Smith 25
Bush 0
Joan 35
reena 0
0
Time taken: 11.851 seconds, Fetched: 6 row(s)
Beeline
nirmal@nirmal-Vostro-3560 ~/hadoop/apache-hive-0.13.1-bin $ bin/beeline
Beeline version 0.13.1 by Apache Hive
beeline> !connect jdbc:hive2://localhost:10000 scott tiger
org.apache.hive.jdbc.HiveDriver
Connecting to jdbc:hive2://localhost:10000
Connected to: Apache Hive (version 0.13.1)
Driver: Hive JDBC (version 0.13.1)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://localhost:10000> ADD JAR
/home/nirmal/Teradata/udf/hiveUDF-1.0-SNAPSHOT.jar;
No rows affected (0.05 seconds)
0: jdbc:hive2://localhost:10000> CREATE TEMPORARY FUNCTION zeroifnull AS
'com.impetus.ilabs.etloffload.ZeroIfNullUDF';
No rows affected (0.597 seconds)
0: jdbc:hive2://localhost:10000> select name,zeroifnull(age) from employeeudf;
+--------+------+
| name | _c1 |
+--------+------+
| John | 25 |
| Smith | 25 |
| Bush | 0 |
| Joan | 35 |
| reena | 0 |
| | 0 |
+--------+------+
6 rows selected (50.319 seconds)
Kindly provide me some pointers.
Thanks,
-Nirmal
________________________________
NOTE: This message may contain information that is confidential, proprietary,
privileged or otherwise protected by law. The message is intended solely for
the named addressee. If received in error, please destroy and notify the
sender. Any use of this email is prohibited when received in error. Impetus
does not represent, warrant and/or guarantee, that the integrity of this
communication has been maintained nor that the communication is free of errors,
virus, interception or interference.
