HIVE 1.x ACID features - Update and DELETE are not working

Question

As a part of HIVE 1.x ACID features, Update and Delete should work on ORC file tables (I am trying this in Cloudera CDH 5.4.4)

However, I see its not working. Can someone suggest. Error:

FAILED: SemanticException [Error 10294]: Attempt to do update or delete using transaction manager that does not support these operations.

Here is the table I created

create table table2_test (EmployeeID Int, FirstName String, Designation String,
Salary Int,Department String) 
clustered by (department) into 3 buckets 
stored as orc TBLPROPERTIES ('transactional'='true');

CLI Prompt:

    > set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
hive> set hive.compactor.initiator.on=true;
hive> set hive.compactor.worker.threads=1;
hive> set hive.support.concurrency=true;
hive> set hive.enforce.bucketing=true;
hive> set hive.exec.dynamic.partition.mode=nonstrict;
hive> Insert into table2_test values (102, 'Employee102','Designation102', 10101, 'Dept102'), (103, 'Employee103','Designation103', 10102, 'Dept103');
...........
Stage-Stage-1: Map: 1  Reduce: 3   Cumulative CPU: 9.14 sec   HDFS Read: 15568 HDFS Write: 1323 SUCCESS
Total MapReduce CPU Time Spent: 9 seconds 140 msec
OK
Time taken: 48.727 seconds
hive> update table2_test set salary=111111 where employeeid=20;
FAILED: SemanticException [Error 10294]: Attempt to do update or delete using transaction manager that does not support these operations.
hive> delete from table2_test where employeeid=19;
FAILED: SemanticException [Error 10294]: Attempt to do update or delete using transaction manager that does not support these operations.

dumitru dumitru · Accepted Answer · 2016-02-02T07:53:42

Configuration seems to be ok, but you are bucketing your table by department and in update clause, in where condition, you are using employeeid which is not bucketed. As far as I know tables must be bucketed along with the columns used in where condition for acid operations like update, delete. You can give it a try like this:

hive> update table2_test set salary=111111 where department=2;

You can also recreate your table with buckets on employeeid and use the same query as in your example.

Take a look on this example.

HIVE 1.x ACID features - Update and DELETE are not working

1 Answers