Support Questions
Find answers, ask questions, and share your expertise

How to activate ACID transactions in Hive within HDP 2.4 ?

New Contributor

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.

1 ACCEPTED SOLUTION

@Juan lowee

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

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

View solution in original post

3 REPLIES 3

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

New Contributor

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 !

@Juan lowee

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

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

; ;