Hive Errors

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;

Error 2: Created table statement with transaction property set to true will fail if 'STORED AS' fileformat is specified as TextFile.


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.

Error 3: Data load to the table containing numeric data type will fail with below error if number field in JSON is defined as string(within double quote).


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.

Error 4: Data copy from 1 hive table to partitioned hive table will fail if dynamic partition mode is not set 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: Execute the below command to set dynamic partition mode to non-strict.


SET hive.exec.dynamic.partition.mode=nonstrict;

Error 5: Alter table to replace columns will only work if datatypes of old and new columns are compatible with each other. Otherwise command will fail with below error.


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.

Error 6: Load from file to table will only work if data format is same in file and Hive table, if there is difference then it will fail with below error.


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.

Error 7: Support for creating Index has been removed from the Hive starting from Hive version 3.0. And if users will try to create indexing in new version then user will get unrecognized input error as shown below.


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