How can I load data into HIVE table avoiding lock problem while some queries are running on the same table

user_7826910 0
Viewed 252

I am a newbie in HIVE. Now I have a .csv file containing the data that I want to append to a table in the HIVE database. But when I run the command: load data inpath /path/to/file into table T1 and wait for a long time, an exception is thrown: "Error in acquiring locks: Locks on the underlying objects cannot be acquired. retry after some time". Those SQL queries all need a very long time (maybe 1~2 hours) to return the results.

I check the locks in this table T1 via show locks T1 extended, and I find some queries are running on the same table:

T1: SHARED
LOCK_QUERYID:hive_20211203..... (omitted)
LOCK_TIME:1638512103766
LOCK_MODE:IMPLICIT
LOCK_QUERYSTRING: ... (a SQL query involved in table T1)

... (other queries like above)

I look up the configurations of HIVE:

SELECT VERSION()    ->    2.1.1-cdh6.2.1 r0a45... (omitted)
SET hive.lock.numretries    ->    100
SET hive.unlock.numretries    ->    10
SET hive.lock.sleep.between.retries     ->    60s
SET hive.txn.manager     ->    org.apache.hadoop.hive.ql.lockmgr.DummyTxnManager
SET hive.support.concurrency     ->    true

The table is an internal table (MANAGED_TABLE), and not a partition table.

My question is how can I successfully load data into the table while the queries are running on the same table.

hive
1 Answers

If the table is not in transactional mode, you can put files into table location using hdfs dfs -put command:

hdfs dfs -put localfilepath/* /your/table/location/

It can affect running queries, so, do it on your own risk.

user_27003440
Related