question Re: Hive update , delete and insert ERROR in cdh 5.4.2 in Support Questions

Hive update , delete and insert ERROR in cdh 5.4.2

Jais — Thu, 02 Jan 2020 15:25:39 GMT

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

Re: Hive update , delete and insert ERROR in cdh 5.4.2

Harsh J — Fri, 17 Jul 2015 12:01:48 GMT

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.

Re: Hive update , delete and insert ERROR in cdh 5.4.2

ctifrani — Mon, 14 Sep 2015 13:19:48 GMT

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

Re: Hive update , delete and insert ERROR in cdh 5.4.2

surneni — Thu, 01 Oct 2015 17:06:42 GMT

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...

Re: Hive update , delete and insert ERROR in cdh 5.4.2

Harsh J — Fri, 02 Oct 2015 06:11:25 GMT

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

Re: Hive update , delete and insert ERROR in cdh 5.4.2

Matthias1987 — Fri, 04 Dec 2015 16:57:37 GMT

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

Re: Hive update , delete and insert ERROR in cdh 5.4.2

PK12 — Fri, 26 Feb 2016 22:50:18 GMT

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

Re: Hive update , delete and insert ERROR in cdh 5.4.2

rdub — Thu, 04 Aug 2016 17:58:43 GMT

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.

Re: Hive update , delete and insert ERROR in cdh 5.4.2

rdub — Thu, 04 Aug 2016 18:24:33 GMT

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)

Re: Hive update , delete and insert ERROR in cdh 5.4.2

rdub — Thu, 04 Aug 2016 18:49:14 GMT

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)

Re: Hive update , delete and insert ERROR in cdh 5.4.2

syamsri — Thu, 13 Jul 2017 18:52:55 GMT

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.

Re: Hive update , delete and insert ERROR in cdh 5.4.2

Hagedorn — Wed, 25 Apr 2018 15:52:57 GMT

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?

Re: Hive update , delete and insert ERROR in cdh 5.4.2

vratmuri — Wed, 13 Jun 2018 20:05:40 GMT

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;

Re: Hive update , delete and insert ERROR in cdh 5.4.2

georgey — Fri, 05 Apr 2019 19:45:44 GMT

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

Re: Hive update , delete and insert ERROR in cdh 5.4.2

georgey — Fri, 05 Apr 2019 19:51:24 GMT

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

Re: Hive update , delete and insert ERROR in cdh 5.4.2

toSarvesh — Tue, 09 Apr 2019 16:43:24 GMT

/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.

Re: Hive update , delete and insert ERROR in cdh 5.4.2

toSarvesh — Tue, 09 Apr 2019 16:48:00 GMT

<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>

Re: Hive update , delete and insert ERROR in cdh 5.4.2

satyam29 — Wed, 01 Jan 2020 12:24:31 GMT

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

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.