hive semantic exception while inserting the data

Question

I created a hive table with ORC. If I insert data from the hive console it works perfectly.But If I insert data from the Jdbc code.It throws hive semantic exception.

Error

rg.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: SemanticException [Error 10293]: Unable to create temp file for insert values Expression of type TOK_TABLE_OR_COL not supported in insert/values org.apache.hive.service.cli.operation.Operation.toSQLException(Operation.java:326) org.apache.hive.service.cli.operation.SQLOperation.prepare(SQLOperation.java:102) org.apache.hive.service.cli.operation.SQLOperation.runInternal(SQLOperation.java:171) org.apache.hive.service.cli.operation.Operation.run(Operation.java:268) org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementInternal(HiveSessionImpl.java:410) org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementAsync(HiveSessionImpl.java:397) sun.reflect.GeneratedMethodAccessor24.invoke(Unknown Source) sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) java.lang.reflect.Method.invoke(Method.java:606) org.apache.hive.service.cli.session.HiveSessionProxy.invoke(HiveSessionProxy.java:78) org.apache.hive.service.cli.session.HiveSessionProxy.access$000(HiveSessionProxy.java:36) org.apache.hive.service.cli.session.HiveSessionProxy$1.run(HiveSessionProxy.java:63) java.security.AccessController.doPrivileged(Native Method) javax.security.auth.Subject.doAs(Subject.java:415) org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1671) org.apache.hive.service.cli.session.HiveSessionProxy.invoke(HiveSessionProxy.java:59) com.sun.proxy.$Proxy25.executeStatementAsync(Unknown Source) org.apache.hive.service.cli.CLIService.executeStatementAsync(CLIService.java:258) org.apache.hive.service.cli.thrift.ThriftCLIService.ExecuteStatement(ThriftCLIService.java:509) org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1313) org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1298) org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39) org.apache.thrift.TBaseProcessor.process(TBaseProcessor.java:39) org.apache.hive.service.auth.TSetIpAddressProcessor.process(TSetIpAddressProcessor.java:56) org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:285) java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) java.lang.Thread.run(Thread.java:745)

My insert query

result = name+","+age+","+job;
String stmt = "INSERT INTO table real_estate.addressinformation_orc VALUES(" + result + ")";

any help will be appreciated.

Roberto Congiu Roberto Congiu · Accepted Answer · 2016-02-04T04:00:36

You need to quote the strings. Assuming age is an integer, this will work, but it's a bad solution:

result = "'" + name + "'," + age + ",'" + job + "'"

It's a bas solution because if name and job countain a quote, that would break your code. A much better solution is to use prepared statements. With prepared statements, you don't have to worry about qoting:

preparedStatement = dbConnection.
  prepareStatement("INSERT INTO table " +     
    "real_estate.addressinformation_orc VALUES(?,?,?)");

preparedStatement.setString(1, name);
preparedStatement.setInt(2, age);
preparedStatement.setString(3, job);

// execute insert SQL stetement
preparedStatement.executeUpdate();

hive semantic exception while inserting the data

2 Answers