CODEWITHSUNDEEP
hivehive-hplsql
David Wang
David Wang

Reputation: 41

Hive hplsql stored procedure cannot insert timestamp values

I try to use Hive HPL/SQL stored procedure's insert data into hive table. But it is unable to insert timestamp value. My table defined as following:

     col_name    |   data_type   | comment  |
+----------------+---------------+----------+
| id             | bigint        |          |
| clean_batchid  | bigint        |          |
| job_name       | varchar(50)   |          |
| table_name     | varchar(30)   |          |
| begin_time     | timestamp     |          |
| end_time       | timestamp     |          |
| clean_type     | varchar(20)   |          |
| total_count    | bigint        |          |
| clean_count    | bigint        |          |
| description    | varchar(500)  |

I use the hive cli to insert value without any problem.

insert into nbl_clean_batch 
values(12345, 1, 'tryme','B', '2016-12-12 12:52:43', '2016-12-12 12:56:19', 0, 10, 5, 'bbb');

but when I use the hplsql to execute the following script:

START_DATE := SYSDATE;
END_DATE := SYSDATE;

INSERT INTO TABLE NBL_CLEAN_BATCH
(
    ID, CLEAN_BATCHID, JOB_NAME, TABLE_NAME, BEGIN_TIME, END_TIME,  CLEAN_TYPE, TOTAL_COUNT, CLEAN_COUNT, DESCRIPTION
)
VALUES (
    23456, 1, 'bbb', 'B', START_DATE, END_DATE, 0, 9, 2, 'line'
);

it throws the following exception:

Unhandled exception in HPL/SQL
org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: ParseException line 2:27 mismatched input '-' expecting ) near '2016' in value row constructor
at org.apache.hive.jdbc.Utils.verifySuccess(Utils.java:267) at org.apache.hive.jdbc.Utils.verifySuccessWithInfo(Utils.java:253) at org.apache.hive.jdbc.HiveStatement.runAsyncOnServer(HiveStatement.java:310) at org.apache.hive.jdbc.HiveStatement.execute(HiveStatement.java:250) at org.apache.hive.hplsql.Conn.executeSql(Conn.java:110) at org.apache.hive.hplsql.Exec.executeSql(Exec.java:592) at org.apache.hive.hplsql.Stmt.insertValues(Stmt.java:800) at org.apache.hive.hplsql.Stmt.insert(Stmt.java:713) at org.apache.hive.hplsql.Exec.visitInsert_stmt(Exec.java:1116) at org.apache.hive.hplsql.Exec.visitInsert_stmt(Exec.java:52) at org.apache.hive.hplsql.HplsqlParser$Insert_stmtContext.accept(HplsqlParser.java:10330) at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visitChildren(AbstractParseTreeVisitor.java:70) at org.apache.hive.hplsql.Exec.visitStmt(Exec.java:1009) at org.apache.hive.hplsql.Exec.visitStmt(Exec.java:52) at org.apache.hive.hplsql.HplsqlParser$StmtContext.accept(HplsqlParser.java:1015) at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visitChildren(AbstractParseTreeVisitor.java:70) at org.apache.hive.hplsql.HplsqlBaseVisitor.visitBlock(HplsqlBaseVisitor.java:28) at org.apache.hive.hplsql.HplsqlParser$BlockContext.accept(HplsqlParser.java:449) at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visitChildren(AbstractParseTreeVisitor.java:70) at org.apache.hive.hplsql.Exec.visitProgram(Exec.java:916) at org.apache.hive.hplsql.Exec.visitProgram(Exec.java:52) at org.apache.hive.hplsql.HplsqlParser$ProgramContext.accept(HplsqlParser.java:392) at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:42) at org.apache.hive.hplsql.Exec.run(Exec.java:771) at org.apache.hive.hplsql.Exec.run(Exec.java:747) at org.apache.hive.hplsql.Hplsql.main(Hplsql.java:23) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.hadoop.util.RunJar.run(RunJar.java:221) at org.apache.hadoop.util.RunJar.main(RunJar.java:136) Caused by: org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: ParseException line 2:27 mismatched input '-' expecting ) near '2016' in value row constructor at org.apache.hive.service.cli.operation.Operation.toSQLException(Operation.java:394) at org.apache.hive.service.cli.operation.SQLOperation.prepare(SQLOperation.java:199) at org.apache.hive.service.cli.operation.SQLOperation.runInternal(SQLOperation.java:282) at org.apache.hive.service.cli.operation.Operation.run(Operation.java:334) at org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementInternal(HiveSessionImpl.java:505) at org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementAsync(HiveSessionImpl.java:492) at sun.reflect.GeneratedMethodAccessor43.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.hive.service.cli.session.HiveSessionProxy.invoke(HiveSessionProxy.java:78) at org.apache.hive.service.cli.session.HiveSessionProxy.access$000(HiveSessionProxy.java:36) at org.apache.hive.service.cli.session.HiveSessionProxy$1.run(HiveSessionProxy.java:63) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAs(Subject.java:422) at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1657) at org.apache.hive.service.cli.session.HiveSessionProxy.invoke(HiveSessionProxy.java:59) at com.sun.proxy.$Proxy38.executeStatementAsync(Unknown Source) at org.apache.hive.service.cli.CLIService.executeStatementAsync(CLIService.java:297) at org.apache.hive.service.cli.thrift.ThriftCLIService.ExecuteStatement(ThriftCLIService.java:506) at org.apache.hive.service.rpc.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1437) at org.apache.hive.service.rpc.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1422) at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39) at org.apache.thrift.TBaseProcessor.process(TBaseProcessor.java:39) at org.apache.hive.service.auth.TSetIpAddressProcessor.process(TSetIpAddressProcessor.java:56) at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:286) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) Caused by: java.lang.RuntimeException: org.apache.hadoop.hive.ql.parse.ParseException:line 2:27 mismatched input '-' expecting ) near '2016' in value row constructor at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:207) at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:166) at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:465) at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1301) at org.apache.hadoop.hive.ql.Driver.compileAndRespond(Driver.java:1279) at org.apache.hive.service.cli.operation.SQLOperation.prepare(SQLOperation.java:197)

Any idea why?

Thanks,

Upvotes: 0

Views: 489

Answers (1)

Ravinder Karra
Ravinder Karra

Reputation: 307

I have used this format before ... 

hive> set CURRENT_DATE=FROM_UNIXTIME(UNIX_TIMESTAMP());    
hive> select ${hiveconf:CURRENT_DATE}, * from tab1;
OK
2016-12-14 13:43:13     1       100
2016-12-14 13:43:13     2       235325
2016-12-14 13:43:13     3       3242
2016-12-14 13:43:13     4       32543
Time taken: 1.377 seconds, Fetched: 4 row(s)
hive> set CURRENT_TIMESTAMP = CURRENT_TIMESTAMP;
hive> select ${hiveconf:CURRENT_TIMESTAMP}, * from tab1;
OK
2016-12-14 13:43:35.425 1       100
2016-12-14 13:43:35.425 2       235325
2016-12-14 13:43:35.425 3       3242
2016-12-14 13:43:35.425 4       32543
Time taken: 1.32 seconds, Fetched: 4 row(s)
hive>

Upvotes: 0

Related Questions