Hive update , delete and insert ERROR in cdh 5.4.2

[#1]

Hi ,

I am getting following errorhi in cdh 5.4.2 

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

I followed the steps and limitations:

Following are my steps........

1. New Configuration Parameters for Transactions

2. Creates Hive table with ACID support

3. Load data into Hive table

4. Do UPDATE,DELETE and INSERT

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

CREATE TABLE abc1 (
empwork_key int,
empwork_id int,
empwork__name string,
empwork_email string,
emp_wrk_phone string,
CLUSTERED BY (empwork_id) into 2 buckets
STORED AS ORC TBLPROPERTIES ('transactional' = 'true');

-- the data is inserted from an external table which is textfile format.

INSERT INTO TABLE abc1 
SELECT
empwork_key ,
empwork_id ,
empwork_name,  
empwork_email ,
emp_wrk_phone ,
FROM test.abc1
;

update abc1 SET empwork_name = "Raj" where empwork_key = 70;

Please help if any suggestions or configuration changes needed.

I am setting all properties from hive shell

[#2]

Are you using the Hive CLI or Beeline+HS2 for this? Have you tried setting the properties into the configuration file instead, does that work?

The property appears to be set correctly, but the check is failing likely cause the default session configuration is checked for the transaction manager instance, and not the query configuration.

[#3]

I am using Beeline+HS2

I configured the below properties at the shell level. But the script is failing with the same error:

set hive.auto.convert.join.noconditionaltask.size = 10000000;
set hive.support.concurrency = true;
set hive.enforce.bucketing = true;
set hive.exec.dynamic.partition.mode = nonstrict;
set hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
set hive.compactor.initiator.on = true;
set hive.compactor.worker.threads = 1 ;

UPDATE update_test SET style_code="TEST" where style_code="xxxxx";

Error: Error while compiling statement: FAILED: SemanticException [Error 10294]: Attempt to do update or delete using transaction manager that does not support these operations. (state=42000,code=10294)

Any Help is appreciated...

[#4]

The transaction manager cannot be set on a per-query basis - it can be set at the HS2 config.

Note that we do not recommend use of the transaction manager features currently: http://www.cloudera.com/content/cloudera/en/documentation/core/latest/topics/cdh_rn_hive_ki.html

[#5]

Hi,

I have also set the values as below, but I used the hive-site.xml Snippet in the Cloudera Manager (5.4.8). After I restarted the cluster I also checked the hive-site.xml in the directory "/run/cloudera-scm-agent/process/" and found the entries as well (so everything seems to be fine).

However, it is still not possible to delete table entries in Hive, I still get the error message (as mentioned above):

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

hive.auto.convert.join.noconditionaltask.size = 10000000;
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 ;

Maybe I missed something but could you please help me?

Thanks a lot in advance,

Matthias

[#6]

Does anyone know what are the risks if I set those values?

[#7]

I'm bringing this back from the dead.

We're getting an error when trying to delete via beeline;

INFO  : OK
+-------------+--+
| new123.foo  |
+-------------+--+
| foo         |
+-------------+--+
1 row selected (0.106 seconds)
0: jdbc:hive2://svqxbdcn6cdh57sparkn1:10000/d> delete from new123 where foo='foo';
Error: Error while compiling statement: FAILED: SemanticException [Error 10294]: Attempt to do update or delete using transaction manager that does not support these operations. (state=42000,code=10294)
0: jdbc:hive2://svqxbdcn6cdh57sparkn1:10000/d>

This is in CDH 5.7 in an unsecured configuration. I haven't added any of the configuration changes yet, but was looking to see if there was a definitive fix out there, or at least something that would help me understand what was happening.

[#8]

With the suggested changes above I get;

0: jdbc:hive2://svqxbdcn6cdh57sparkn1:10000/d> delete from new123 where foo='foo';
Error: Error while compiling statement: FAILED: SemanticException [Error 10297]: Attempt to do update or delete on table default.new123 that does not use an AcidOutputFormat or is not bucketed (state=42000,code=10297)

[#9]

I decided to try with a bucketed table and ended up with this error;

0: jdbc:hive2://svqxbdcn6cdh57sparkn1:10000/d> delete from floridacities where id='30';
Error: Error while compiling statement: FAILED: SemanticException [Error 10122]: Bucketized tables do not support INSERT INTO: Table: default.floridacities (state=42000,code=10122)

[#10]

I was also facing same issue like you. Then I had followed these steps  and it worked for me :

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

Then I changed added hive.in.test property=true in the hive-site.xml file in /usr/lib/hive location.

After that I restarted the Hive from HUE and then ran the update command and it worked for me.

[#11]

Turning on ACID properties in the Hive configs worked for me. I had to restart Oozie and Hive after saving the changes.

[#12]

Turn on ACID properties in the Hive config, save changes, and restart the affected services. This worked for me.

[#13]

/etc/hive/conf and update property in hive-site.xml  

not using these ser use xml tag .

SET hive.txn.manager =org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
SET hive.compactor.initiator.on = true;
SET hive.compactor.worker.threads = 1;

it worked for me.

[#14]

<property>
<name>hive.support.concurrency</name>
<value>true</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>

[#15]

Hi Harsha,

I am also facing same error..

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;

1	102	syam
2	101	syri
3	101	syri

delete from testTableNew where id = '101';

• Error while compiling statement: FAILED: SemanticException [Error 10294]: Attempt to do updateor 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 updateor 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.

[#16]

It's problem with hive server. Please run UPDATE or DELETE commands on a Client machine or update the below set commands on Hive-stie.xml file.

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

[#17]

Hello,

 the following will work:

hive -e "set hive.auto.convert.join.noconditionaltask.size = 10000000; set hive.support.concurrency = true; set hive.enforce.bucketing = true; set hive.exec.dynamic.partition.mode = nonstrict; set hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; set hive.compactor.initiator.on = true;
set hive.compactor.worker.threads = 1 ; update abc1 SET empwork_name = "Raj" where empwork_key = 70;"

Cherif T

[#18]

I get the following:

insert into rhtest_db.val_test_2 values(15, 'testing');

Error while compiling statement: FAILED: SemanticException [Error 10293]: Unable to create temp file for insert values User [hive] is not authorized to perform [DECRYPT_EEK] on key with ACL name [hdfskey]!!

Any Ideas?