You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Nirmal Kumar <ni...@impetus.co.in> on 2014/08/06 10:25:41 UTC
Unable to Add UDF Jar from JDBC Java Client
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.
RE: Unable to Add UDF Jar from JDBC Java Client
Posted by Nirmal Kumar <ni...@impetus.co.in>.
Apologies for my silly coding mistake.
String[] args = new String[3];
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);
}
Initialized a String array of 3 and added just 2 strings. So passing a null inside for loop.
Now its working great :-)
Thanks,
-Nirmal
________________________________
From: Nirmal Kumar
Sent: Wednesday, August 6, 2014 2:20 PM
To: user@hive.apache.org
Subject: RE: Unable to Add UDF Jar from JDBC Java Client
Thanks Matouk,
But there are some more UDFs that I have developed.
-Nirmal
________________________________
From: Matouk IFTISSEN <ma...@ysance.com>
Sent: Wednesday, August 6, 2014 2:10 PM
To: user@hive.apache.org
Subject: Re: Unable to Add UDF Jar from JDBC Java Client
Hello,
why you try to re make un udf that exists,
If you want to turn 'null' to some value like '0' in your case use the udf function built dirctely in Hive : COALESCE
exemple :
select name,COALESCE(age, CAST(0 AS BIGINT)) from employee;
hope this help you ;)
2014-08-06 10:25 GMT+02:00 Nirmal Kumar <ni...@impetus.co.in>>:
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.
--
Matouk IFTISSEN | Consultant BI & Big Data
[http://www.ysance.com]
24 rue du sentier - 75002 Paris - www.ysance.com<http://www.ysance.com/><http://www.ysance.com/>
Fax : +33 1 73 72 97 26
Ysance sur :Twitter<http://twitter.com/ysance> | Facebook<https://www.facebook.com/pages/Ysance/131036788697> | Google+<https://plus.google.com/u/0/b/115710923959357341736/115710923959357341736/posts> | LinkedIn<http://www.linkedin.com/company/ysance> | Newsletter<http://www.ysance.com/nous-contacter.html>
Nos autres sites : ys4you<http://wwww.ys4you.com/> | labdecisionnel<http://www.labdecisionnel.com/> | decrypt<http://decrypt.ysance.com/>
________________________________
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.
________________________________
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.
RE: Unable to Add UDF Jar from JDBC Java Client
Posted by Nirmal Kumar <ni...@impetus.co.in>.
Thanks Matouk,
But there are some more UDFs that I have developed.
-Nirmal
________________________________
From: Matouk IFTISSEN <ma...@ysance.com>
Sent: Wednesday, August 6, 2014 2:10 PM
To: user@hive.apache.org
Subject: Re: Unable to Add UDF Jar from JDBC Java Client
Hello,
why you try to re make un udf that exists,
If you want to turn 'null' to some value like '0' in your case use the udf function built dirctely in Hive : COALESCE
exemple :
select name,COALESCE(age, CAST(0 AS BIGINT)) from employee;
hope this help you ;)
2014-08-06 10:25 GMT+02:00 Nirmal Kumar <ni...@impetus.co.in>>:
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.
--
Matouk IFTISSEN | Consultant BI & Big Data
[http://www.ysance.com]
24 rue du sentier - 75002 Paris - www.ysance.com<http://www.ysance.com/><http://www.ysance.com/>
Fax : +33 1 73 72 97 26
Ysance sur :Twitter<http://twitter.com/ysance> | Facebook<https://www.facebook.com/pages/Ysance/131036788697> | Google+<https://plus.google.com/u/0/b/115710923959357341736/115710923959357341736/posts> | LinkedIn<http://www.linkedin.com/company/ysance> | Newsletter<http://www.ysance.com/nous-contacter.html>
Nos autres sites : ys4you<http://wwww.ys4you.com/> | labdecisionnel<http://www.labdecisionnel.com/> | decrypt<http://decrypt.ysance.com/>
________________________________
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.
Re: Unable to Add UDF Jar from JDBC Java Client
Posted by Matouk IFTISSEN <ma...@ysance.com>.
Hello,
why you try to re make un udf that exists,
If you want to turn 'null' to some value like '0' in your case use the udf
function built dirctely in Hive : *COALESCE *
*exemple :*
* select **name,*COALESCE(age, CAST(0 AS BIGINT))* from employee;*
hope this help you ;)
2014-08-06 10:25 GMT+02:00 Nirmal Kumar <ni...@impetus.co.in>:
> 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.
>
--
*Matouk IFTISSEN | Consultant BI & Big Data [image: http://www.ysance.com] *
24 rue du sentier - 75002 Paris - www.ysance.com <http://www.ysance.com/>
Fax : +33 1 73 72 97 26
*Ysance sur* :*Twitter* <http://twitter.com/ysance>* | Facebook
<https://www.facebook.com/pages/Ysance/131036788697> | Google+
<https://plus.google.com/u/0/b/115710923959357341736/115710923959357341736/posts>
| LinkedIn
<http://www.linkedin.com/company/ysance> | Newsletter
<http://www.ysance.com/nous-contacter.html>*
*Nos autres sites* : *ys4you* <http://wwww.ys4you.com/>* | labdecisionnel
<http://www.labdecisionnel.com/> | decrypt <http://decrypt.ysance.com/>*