SemanticException [Error 10265] while running simple hive select query on a transactional hive table

Question

I created the table in hive:

CREATE TABLE test_table (COL1 string, COL2 string, COL3 string, COL4 string) CLUSTERED BY(COL2) INTO 4 BUCKETS STORED AS ORC tblproperties("transactional"="true");

now trying to query using putty in hive prompt:

select * from test_db.test_table;

this fails with the below message:

FAILED: SemanticException [Error 10265]: This command is not allowed on an ACID table test_db.test_table with a non-ACID transaction manager. Failed command: null

Please help me with this error.

Answer 1

Hive transaction manager must be set to org.apache.hadoop.hive.ql.lockmgr.DbTxnManager in order to work with ACID tables.

SET hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;

Additionally, Set these properties to turn on transaction support

Client Side

SET hive.support.concurrency=true;
SET hive.enforce.bucketing=true;
SET hive.exec.dynamic.partition.mode=nonstrict;

Server Side (Metastore)

SET hive.compactor.initiator.on=true;
SET hive.compactor.worker.threads=1;

Note: Add these properties in hive-site.xml to set them permanently.