How to activate ACID transactions in Hive within HDP 2.4 ?

[#1]

I would like to be able to update a table within Hive, but I am not sure how to do that from the actual version of Ambari.

I found that ACID transactions is off on my ambari but I don't now how to change it.

Any help would be appreciated.

[#2]

you can not turn on acid on the existing table, you need to specify TBLPROPERTIES ('transactional'='true') at the time of table creation(DDL). further on this you can follow the apache documentation https://cwiki.apache.org/confluence/display/Hive/Hive+Transactions

[#3]

Hi,

I am using HDP 2.5. I want to perform a delete operation from a Hive table. I have created a table with the following command:

hive> create table test (x int, y string) clustered by (x) into 2 buckets stored as ORC tblproperties ("transactional" = "true"); OK Time taken: 0.148 seconds

Further, I have set the following Hive 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.optimize.sort.dynamic.partition=false;

Then on, I am doing a test insert into the hive table leveraging the following command:

INSERT INTO TEST VALUES (1,a);

Unfortunately my Hive CLI shell is getting hung and I have to issue Ctrl + C command to get out of the shell. May I know why is this happening please? Is it due to the fact that my Hive schema / database contains a mixture of ACID and non-ACID tables ? Any suggestion to resolve this problem will be very helpful.

Thanks in advance !

[#4 · Accepted solution]

@Juan lowee

For using ACID transactions in Hive, following properties needs to added under Hive:

• hive.support.concurrency – true
• hive.enforce.bucketing – true (Not required as of Hive 2.0)
• hive.exec.dynamic.partition.mode – nonstrict
• hive.txn.manager – org.apache.hadoop.hive.ql.lockmgr.DbTxnManager
• hive.compactor.initiator.on – true (for exactly one instance of the Thrift metastore service)
• hive.compactor.worker.threads – a positive number on at least one instance of the Thrift metastore service

If a Hive table is to be used in ACID writes (insert, update, delete) then the table property "transactional=true" must be set on that table. Sample create table is:

create table test_partition(col1 int, col2 string) clustered by (col1) into 5 buckets stored as orc tbl_properties("transactional=true");

For details, you can refer to below link:

https://cwiki.apache.org/confluence/display/Hive/Hive+Transactions

Hope this helps.

Thanks and Regards,

Sindhu