question Update and delete in hive in Archives of Support Questions (Read Only)

Update and delete in hive

soumyabrata_kol — Thu, 18 Feb 2016 23:01:13 GMT

Hi Experts,

I was trying to do insert,update and delete in a Hive table. Though insert worked for me update and delete didn't worked.

I set following properties before executing any DDL/DML :

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;

Then following table created :

CREATE TABLE students (name VARCHAR(64), age INT, gpa DECIMAL(3, 2))
  CLUSTERED BY (age) INTO 2 BUCKETS STORED AS ORC
   TBLPROPERTIES ('transactional'='true');

Following insert worked :

INSERT INTO TABLE students
VALUES ('AA', 23, 1.28), ('BB', 32, 2.32);

Following update/delete are falling :

UPDATE students SET gpa = 3.12 WHERE name='AA';
delete from students WHERE age=32;

Could you please help me to understand the issue ?

Hive version is as below -

[hdfs@sandbox ~]$ hive --version
SLF4J: Class path contains multiple SLF4J bindings. 
SLF4J: Found binding in [jar:file:/usr/hdp/2.3.2.0-2950/hadoop/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class] 
SLF4J: Found binding in [jar:file:/usr/hdp/2.3.2.0-2950/spark/lib/spark-assembly-1.4.1.2.3.2.0-2950-hadoop2.7.1.2.3.2.0-2950.jar!/org/slf4j/impl/StaticLoggerBinder.class] 
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation. 
SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory] 
WARNING: Use "yarn jar" to launch YARN applications. 
SLF4J: Class path contains multiple SLF4J bindings. 
SLF4J: Found binding in [jar:file:/usr/hdp/2.3.2.0-2950/hadoop/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class] 
SLF4J: Found binding in [jar:file:/usr/hdp/2.3.2.0-2950/spark/lib/spark-assembly-1.4.1.2.3.2.0-2950-hadoop2.7.1.2.3.2.0-2950.jar!/org/slf4j/impl/StaticLoggerBinder.class] 
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation. 
SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory] 
Hive 1.2.1.2.3.2.0-2950 
Subversion git://c66-slave-20176e25-6/grid/0/jenkins/workspace/HDP-2.3-maint-centos6/bigtop/build/hive/rpm/BUILD/hive-1.2.1.2.3.2.0 -r c67988138ca472655a6978f50c7423525b71dc27

Compiled by jenkins on Wed Sep 30 19:07:31 UTC 2015

Thanks,

Soumya

Re: Update and delete in hive

nsabharwal — Thu, 18 Feb 2016 23:02:29 GMT

@soumyabrata kole

See this https://community.hortonworks.com/content/kbentry/4321/hive-acid-current-state.html

Re: Update and delete in hive

nsabharwal — Sun, 18 Aug 2019 13:14:37 GMT

@soumyabrata kole

hive> UPDATE students SET gpa = 3.12 WHERE name='AA'; delete from students WHERE age=32;

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

hive>

I enabled ACID transaction and I can run it "It's not recommended in production"

Update: It did not update the gpa value.

Re: Update and delete in hive

soumyabrata_kol — Thu, 18 Feb 2016 23:31:25 GMT

Thanks Neeraj for your answer.

However, I could not find how to enable ACID transactions from the link -https://hortonworks.app.box.com/files/0/f/2070270300/1/f_37967540402

Also other links which are present in the page of above link are not working.

Could you please tell me the steps to enable ACID transactions.

Thanks again !

Soumya

Re: Update and delete in hive

nsabharwal — Sun, 18 Aug 2019 13:14:29 GMT

You can use ambari to enable ACID.

Re: Update and delete in hive

nsabharwal — Sun, 18 Aug 2019 13:14:21 GMT

@soumyabrata kole Update worked using beeline

Re: Update and delete in hive

nsabharwal — Fri, 19 Feb 2016 04:41:01 GMT

@soumyabrata kole It's working though beeline @soumyabrata kole

Re: Update and delete in hive

ekoifman — Wed, 24 Feb 2016 08:25:04 GMT

try "set hive.txn.manager" and see what it prints. This is "sticky" setting so set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager won't always take effect. It's more reliable to set it in hive-site.xml

Re: Update and delete in hive

rsb001happy — Wed, 24 Aug 2016 14:33:41 GMT

hive doesn't support DML queries, if create a ACID property to do this,

set this properties

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;

SET hive.support.concurrency=true;

SET hive.enforce.bucketing=true;

then create a hive table using bucketing and stored as ORC file format, then do the update and delete queries in hive, for more information on hive DML go this blog for step by step process

http://www.geoinsyssoft.com/hive-dml-update-delete-operations/

hive doesn't support DML queries, if create a ACID property to do this, set this properties 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; SET hive.support.concurrency=true; SET hive.enforce.bucketing=true; then create a hive table using bucketing and stored as ORC file format, then do the update and delete queries in hive, for more information on hive DML go this blog for step by step process http://www.geoinsyssoft.com/hive-dml-update-delete-operations/

Re: Update and delete in hive

pooja_shrivasta — Tue, 17 Oct 2017 13:39:38 GMT

For me this setting is disabled .I can not make any changes.Can you please let me know how to on the ACID properties?