Users may face various errors while using Hive. Some of the errors are listed below with their solution.
Error 1: Created table statement with transaction property set to true will fail if some properties are not defined.
FAILED: SemanticException [Error 10265]: This command is not allowed on an ACID table default.hiveFirstTransTable with a non-ACID transaction manager
Solution: Set below properties before executing create table statement.
SET hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
SET hive.support.concurrency=true;
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:The table must be stored using an ACID compliant format (such as ORC): default.hiveFirstTransTable)
Solution: Change the 'STORED AS' fileformat to ORC.
Failed with exception java.io.IOException:org.apache.hadoop.hive.serde2.SerDeException: org.codehaus.jackson.JsonParseException: Current token (VALUE_STRING) not numeric, can not use numeric value accessors
Solution: Either change the data type to String in the Hive table or modify the JSON to send number value as number.
FAILED: SemanticException [Error 10096]: Dynamic partition strict mode requires at least one static partition column. To turn this off set hive.exec.dynamic.partition.mode=nonstrict
Solution: Execute the below command to set dynamic partition mode to non-strict.
SET hive.exec.dynamic.partition.mode=nonstrict;
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Unable to alter table. The following columns have types incompatible with the existing columns in their respective positions :
id
Solution: Correct the datatypes in the alter statement to make them compatible.
FAILED: SemanticException Unable to load data to destination table. Error: The file that you are trying to load does not match the file format of the destination table.
Solution: Create stage table with same data format as file to load data first and then copy data from this stage table to main table.
CREATE INDEX index_test ON TABLE orders(order_id)
AS 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'
IN TABLE orders_index_test;
FAILED: ParseException line 1:7 cannot recognize input near 'CREATE' 'INDEX' 'index_test' in ddl statement