CODEWITHSUNDEEP
javajdbchive
blackSmith
blackSmith

Reputation: 3154

Hive query failing on INSERT OVERWRITE

I'm running queries on hive(v 0.11) using jdbc connection. The code is as follows : 

Connection con = DriverManager.getConnection(
                "jdbc:hive://192.168.1.10:10000", "", "");
Statement stmt = con.createStatement();
stmt.execute("some query");

It runs the following queries successfully:

CREATE TABLE testdb.test(name string,id int);

SELECT * FROM testdb.test;

However fails on executing any queries containing INSERT OVERWRITE clause. Eg:

INSERT OVERWRITE DIRECTORY '/user/jim/dir' SELECT * FROM space.test;

INSERT OVERWRITE TABLE testdb.t2 select name,id from testdb.test;

with following trace :

java.sql.SQLException: Query returned non-zero code: 1, cause: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.MapRedTask
at org.apache.hadoop.hive.jdbc.HivePreparedStatement.executeImmediate(HivePreparedStatement.java:178)
at org.apache.hadoop.hive.jdbc.HivePreparedStatement.executeQuery(HivePreparedStatement.java:141)
at my.pack.test.HiveTest.main(HiveTest.java:31)
  Caused by: HiveServerException(message:Query returned non-zero code: 1, cause: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.MapRedTask, errorCode:1, SQLState:08S01)
at org.apache.hadoop.hive.service.ThriftHive$execute_result$execute_resultStandardScheme.read(ThriftHive.java:1494)
at org.apache.hadoop.hive.service.ThriftHive$execute_result$execute_resultStandardScheme.read(ThriftHive.java:1480)
at org.apache.hadoop.hive.service.ThriftHive$execute_result.read(ThriftHive.java:1430)
at org.apache.thrift.TServiceClient.receiveBase(TServiceClient.java:78)
at org.apache.hadoop.hive.service.ThriftHive$Client.recv_execute(ThriftHive.java:116)
at org.apache.hadoop.hive.service.ThriftHive$Client.execute(ThriftHive.java:103)
at org.apache.hadoop.hive.jdbc.HivePreparedStatement.executeImmediate(HivePreparedStatement.java:176)
... 2 more

The main problem is that these queries can be executed from the hive console successfully.

Please help anyone if I'm missing something here. Or there is some better way to achieve this with jdbc ?

N.B. - Each queries in the above blocks are executed separately without the semicolon. I just put them for ease in readability.

Upvotes: 1

Views: 3325

Answers (1)

vijay kumar
vijay kumar

Reputation: 2049

hi i tried your example case, it worked , use like this while executing the query for JDBC Client :

String sql = "INSERT OVERWRITE DIRECTORY '/user/jim/dir' select * from " + tableName;

stmt.execute(sql);

Note :

  1. make sure /user/jim/dir is writable , if not make it writable as

    hadoop fs -chmod a+rwx /user/jim/dir

  2. use stmt.execute(sql) not stmt.executeQuery(sql);

PS: problem still exists means let me know, will share the complete code.

Upvotes: 2

Related Questions