Hive hanging while i set ACID property

[#1]

I am using HDP 2.6.1

While i set ACID property hive getting Hang, and i forced to do ctl+c

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);

Hive CLI shell is getting hung and I have to run Ctrl + C command to get out of the shell. I am not able to understand why is this happening ?

Any help to resolve this problem will be very helpful.

[#2]

do you see any error in hiveserver2 log that time ?

[#3]

set hive.compactor.initiator.on=true; set hive.compactor.worker.threads=1;

INSERT INTO TEST VALUES (1,'a');

[#4]

Thank Toy very Much for support. @Vikas Srivastava I have set all the ACID parameter as said above. @Deepak Sharma Where can i see hiveserver2 logs?

Hive shell is hanging after setting below param.

SET hive.txn.manager= org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;

[#5]

SET hive.support.concurrency=false; It should work.

[#6]

I am getting Below error while trying to insert data in ACID property table.

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; CREATE TABLE students (name VARCHAR(64), age INT, gpa DECIMAL(3, 2)) CLUSTERED BY (age) INTO 2 BUCKETS STORED AS ORC TBLPROPERTIES ('transactional'='true');

0: jdbc:hive2://localhost185:10000/> INSERT INTO TABLE students VALUES ('AA', 23, 1.28), ('BB', 32, 2.32);

Error: Error while processing statement: FAILED: Error in acquiring locks: Error communicating with the metastore (state=42000,code=10)

[#7]

hi, iam able to reslove above issue by setting set hive.support.concurrency=false. but the table is not accessible as ACID property must need it true.

i have doubt why it hanging if i make hive.support.concurrency==true. any help or suggestion?

[#8]

Can you post the output of "show locks" command on your table?

[#9]

Thanks. I think something to do with metastore. i am using extrenal metastore which is loacted on RDS instance with postgresql.

hive> show tables;

FAILED: Error in acquiring locks: Error communicating with the metastore

hive> 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;

hive> show locks;

OK

Lock IDDatabaseTablePartitionStateBlocked ByTypeTransaction IDLast HeartbeatAcquired AtUserHostnameAgent Info

1.1nmp_devNULLNULLWAITING SHARED_READNULL1520037557808NULLanonymousip-10-16-42-185hive_20180303003917_80949492-5c2b-4aa6-a905-1093ff2a4bea

2.1defaultNULLNULLWAITING SHARED_READNULL1520038346314NULLanonymousip-10-16-42-185hive_20180303005226_ee4e77bc-f57b-42eb-b477-5af80896dddf

3.1defaultNULLNULLWAITING SHARED_READNULL1520038760316NULLadminip-10-16-42-185hive_20180303005920_82a05407-9848-4a35-9717-f7f495ddfd96

4.1defaultNULLNULLWAITING SHARED_READNULL1520038767616NULLadminip-10-16-42-185hive_20180303005927_11f18092-df0e-4244-a997-4469e81f4fad

5.1defaultNULLNULLWAITING SHARED_READNULL1520038783927NULLadminip-10-16-42-185hive_20180303005943_8725c5da-4cd3-4ca4-b35a-de727ede2f06

6.1nmp_devNULLNULLWAITING SHARED_READNULL1520038948139NULLanonymousip-10-16-42-185hive_20180303010228_b2b9c13f-c2f0-4b67-830b-7a952ed33e73

8.1nmp_devNULLNULLWAITING SHARED_READNULL1520040369997NULLanonymousip-10-16-42-185hive_20180303012609_c1aaf992-9ae4-47d4-a5dd-6dba6a87c20f

9.1defaultNULLNULLWAITING SHARED_READNULL1520040691362NULLanonymousip-10-16-42-185hive_20180303013130_5c4a2821-1361-4447-98c7-8629376665f4

10.1nmp_devNULLNULLWAITING SHARED_READNULL1520040715385NULLanonymousip-10-16-42-185hive_20180303013155_97d1549c-4588-4512-ada7-1015adec8d29

11.1defaultNULLNULLWAITING SHARED_READNULL1520207255470NULLanonymousip-10-16-42-185hive_20180304234735_9763e36c-d54b-45ae-b1d8-3881f2d9abe9

12.1defaultNULLNULLWAITING SHARED_READNULL1520208512712NULLanonymousip-10-16-42-185hive_20180305000831_fcb7ef1e-1936-4dfd-a878-6e09c32cc045

13.1defaultNULLNULLWAITING SHARED_READNULL1520209061214NULLanonymousip-10-16-42-185hive_20180305001740_9814fd52-f1e6-47cb-98a5-7b91ac336170

14.1defaultNULLNULLWAITING SHARED_READNULL1520223616543NULLhdfsip-10-16-40-241hdfs_20180305042015_3ac75d63-13b2-495e-a49b-51affcf48dcb

15.1defaultNULLNULLWAITING SHARED_READNULL1520223629549NULLhdfsip-10-16-40-241hdfs_20180305042028_ecbac190-eda7-42a2-adf9-ec588ed3418e

16.1defaultNULLNULLWAITING SHARED_READNULL1520233942704NULLanonymousip-10-16-42-185hive_20180305071221_4f8265ff-a29c-44a7-abf2-67d3895443b0

7.1nmp_devemb_ipthr_25feb18_02mar18_bucketNULLWAITING EXCLUSIVENULL1520039081989NULLanonymousip-10-16-42-185hive_20180303010441_07bc9b06-26a8-4a6d-a353-6123d5d25592

Time taken: 0.211 seconds, Fetched: 17 row(s)

hive> show tables;

FAILED: Error in acquiring locks: Error communicating with the metastore

[#10]

@Alpesh Virani

Instant Patchy workaround:

SET hive.support.concurrency=false;

Unlock table:

unlock table my_table;

[#11]

Doesnt seem to work. Tried unlock command after setting concurrency=false. The error still says - " FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Current transaction manager does not support explicit lock requests. Transaction manager: org.apache.hadoop.hive.ql.lockmgr.DbTxnManager"