delete or update operations is not working on hive 0.14

2
votes

Anyone knows why the delete/update operation is not working in hive 0.14 (it's supposed to be working starting 0.14 version) even I do follow the steps/format to create table and get:

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

upon running delete operation. Please help me on this. 

CREATE TABLE STUDENT
(
STD_ID INT,
STD_NAME STRING,
AGE INT,
ADDRESS STRING
)
CLUSTERED BY (ADDRESS) into 3 buckets
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED as orc tblproperties('transactional'='true');
2
hadoophiveoperations

2 Answers

0
votes

1.Set the properties below in hive-site.xml

2.Create the table newly again

3.Load the data in to table

4.Try for CRUD operations. It will work. Good luck

<property>
  <name>hive.support.concurrency</name>
  <value>true</value>
</property>

<property>
  <name>hive.enforce.bucketing</name>
  <value>true</value>
</property>

<property>
  <name>hive.exec.dynamic.partition.mode</name>
  <value>nonstrict</value>
</property>

<property>
  <name>hive.txn.manager</name>
  <value>org.apache.hadoop.hive.ql.lockmgr.DbTxnManager</value>
</property>

<property>
  <name>hive.compactor.initiator.on</name>
  <value>true</value>
</property>

<property>
  <name>hive.compactor.worker.threads</name>
  <value>1</value>
</property>
0
votes

Delete and update works from 0.14 onwards. I was able to achieve the same. You need to set new configuration parameters in Hive

 hive.support.concurrency – true
 hive.enforce.bucketing – true
 hive.exec.dynamic.partition.mode – nonstrict
 hive.txn.manager –org.apache.hadoop.hive.ql.lockmgr.DbTxnManager
 hive.compactor.initiator.on – true
 hive.compactor.worker.threads – 1

Then create a table with ACID support,

     CREATE TABLE STUDENT
(
STD_ID INT,
STD_NAME STRING,
AGE INT,
ADDRESS STRING
)
CLUSTERED BY (ADDRESS) into 3 buckets
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED as orc tblproperties('transactional'='true');

and do CURD opertaions.

update STUDENT 
    set AGE = 24 
    where STD_ID = 19;

Please follow these steps