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.
Solution: Set below properties before executing create table statement.
FAILED: SemanticException [Error 10265]: This command is not allowed on an ACID table default.hiveFirstTransTable with a non-ACID transaction manager
SET hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; SET hive.support.concurrency=true;
Solution: Change the 'STORED AS' fileformat to ORC.
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: Either change the data type to String in the Hive table or modify the JSON to send number value as number.
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: Execute the below command to set dynamic partition mode to non-strict.
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: Correct the datatypes in the alter statement to make them compatible.
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: Create stage table with same data format as file to load data first and then copy data from this stage table to main table.
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.
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