Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Update and Delete are not working in Hive ?

avatar
Contributor

Hi all,

 

I am not able to delete or update in hive table .

 


create table testTableNew(id int ,name string ) clustered by (id) into 2 buckets stored as orc TBLPROPERTIES('transactional'='true');

 

insert into table testTableNew values('101','syri');

 

select * from testtablenew;

 

1102syam
2101syri
3101syri

 

 

delete from testTableNew where id = '101';

 

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

update testTableNew
set name = praveen
where id = 101;

 

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

 

I have added few properties in hive-site.xml also :


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
2


hive.in.test
true

 

After restart the Hive service also same error i am facing.

Quick Start VM  - 5.8 and Hive version - 1.1.0.

Please guide me to sort this issue.

 

Thanks,

Syam.

 

 

24 REPLIES 24

avatar
Contributor

Hive version : Hive 1.1.0-cdh5.13.0,

Still i didnt get the solution...

avatar
Champion

Hi  @AlexMulti @syamsri @UjjwalRana @Suribharu

 

are you still looking for solution ? 

 

what version of hive are you using ? 

what file format does your table has ? 

what client tool are using hiveshell or beeline ? 

did you perform the pre-requsites before performing the hive delete operation? 

avatar
Explorer

Hi @csguna,

 

I haven't found any solutions to the problem (tried Hive CLI and HUE query editor). I will try to do a workaround...

 

What do you mean by "prerequisites"?

 

Version:

CDH 5.12.1

Hive 1.1

 

Thanks!

avatar
Champion

@AlexMulti

 

Deletes can only be performed on tables that support ACID. More over your tables's file format should be ORC

Below are the properties that needs to be set .

 

hive.support.concurrency	true (default is false)
hive.enforce.bucketing	true (default is false) (Not required as of Hive 2.0)
hive.exec.dynamic.partition.mode	nonstrict (default is strict)

Also i would suggest you to take look into  kudu. 

 

Reference https://cwiki.apache.org/confluence/display/Hive/Hive+Transactions#HiveTransactions-NewConfiguration...

avatar
Explorer

@csgunaThank you for the reply!

 

I am using Parquet and you are right, it is not supported for UPDATE and DELETE operations. Only ORC is supported.

 

@syamsriI suggest the last post from csguna should be marked as the correct answer.