Update and delete in hive

[#1]

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

[#2 · Accepted solution]

@soumyabrata kole

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

[#3]

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

[#4]

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

[#5]

You can use ambari to enable ACID.

[#6]

@soumyabrata kole Update worked using beeline

[#7]

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

[#8]

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

[#9]

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/

[#10]

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