Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

How to activate ACID transactions in Hive within HDP 2.4 ?

Solved Go to solution
Highlighted

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

Accepted Solutions

Re: How to activate ACID transactions in Hive within HDP 2.4 ?

@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

3 REPLIES 3

Re: How to activate ACID transactions in Hive within HDP 2.4 ?

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

Re: How to activate ACID transactions in Hive within HDP 2.4 ?

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 !

Re: How to activate ACID transactions in Hive within HDP 2.4 ?

@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

Don't have an account?
Coming from Hortonworks? Activate your account here