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 <nirmal.ku...@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/>*
