Hive drop table not working - Hanging

Question

I tried dropping a HIVE table. Screen hangs. I tried in both HIVE CLI and beeline. Neither the table getting dropped nor the data getting inserted into the table. Need help!

Kinswoman

Does your Hive has successfully installed? – Steerageway

Follow this thread grokbase.com/t/hive/user/1116f9ybk8/cant-drop-table – Lebanon

Yes. It is CDH. – Kinswoman

@JyothiBabuAraja. it didn't help. for the past two days, I have tried many steps, but no success – Kinswoman

Have you debugged as in that thread? – Lebanon

Answer 1

Actually the table was locked with some queries. After unlocking the table, I am able to drop the table now.

=>Query to unlock the table

unlock table <tablename>

=>We can find the tables which are locked in the Hive CLI /Beeline:

show locks extended;

Kinswoman

I got org.apache.hadoop.hive.ql.exec.DDLTask. show Locks LockManager not specified and found it needs to be set up in hive-site.xml – Heaume

Answer 2

I had simular issue but I was not able even to drop hive database which had this table inside.

I had to manually delete from metastoredb coruppted record.

Before do that of course do mysqldump just in case :)

In table TBLS record which was coresponding to the table. Record atributes SD_ID, VIEW_EXPANDED_TEXT,VIEW_ORIGINAL_TEXT were null.

To find it:

SELECT DB.DB_ID,
       DB.NAME AS DB_NAME,
       TB.SD_ID,
       TB.TBL_ID,
       TB.TBL_NAME,
       SD.SD_ID,
       SD.INPUT_FORMAT,
       CD.*
FROM TBLS AS TB
     left JOIN DBS AS DB ON(DB.DB_ID = TB.DB_ID)
     left JOIN SDS AS SD ON(TB.SD_ID = SD.SD_ID)
     left JOIN COLUMNS_V2 AS CD ON(SD.CD_ID = CD.CD_ID)
WHERE 
TB.TBL_NAME='your_name';

Then I had to delete all FK of this record inside two other tables:

 delete from TBL_COL_PRIVS where TBL_ID=xxxx ;
 delete from TBL_PRIVS where TBL_ID=xxxx ;
 delete from TBLS where TBL_ID=xxxx ;

Dirk

Answer 3

Another possible reason that drop table can seem to hang is that you have lots of partitions (for example, hourly partitions for several years). If this is the case, be gentle when you drop partitions, you can use something like:

alter table your_table drop if exists partition(year=2021,month<=6);

To drop only some of the partitions at a time.

Heaume

Answer 4

use this code for manual remove:

SELECT TB."TBL_ID"
FROM "TBLS" AS TB
     left JOIN "DBS" AS db ON(db."DB_ID" = TB."DB_ID")
     left JOIN "SDS" AS SD ON(TB."SD_ID" = SD."SD_ID")
     left JOIN "COLUMNS_V2" AS CD ON(SD."CD_ID" = CD."CD_ID")
WHERE 
TB."TBL_NAME"='$table_name';


delete from "TBL_COL_PRIVS" where "TBL_ID"=$id;
delete from "TBL_PRIVS" where "TBL_ID"=$id;
delete from "PARTITION_KEY_VALS" where "PART_ID" in (select "PART_ID" from "PARTITIONS" where "TBL_ID"=$id);
delete from "PARTITION_PARAMS" where "PART_ID" in (select "PART_ID" from "PARTITIONS" where "TBL_ID"=$id);
delete from "PARTITIONS" where "TBL_ID"=$id;
delete from "PARTITION_KEYS" where "TBL_ID"=$id;
delete from "TABLE_PARAMS" where "TBL_ID"=$id;
delete from "TBLS" where "TBL_ID"=$id;

Conah