zuqqhi2 Tech Memo

How to drop Hive’s External Table

Gerd AltmannによるPixabayからの画像

We cannot drop Hive’s External Table with Drop Table query if we don’t have write permission to an original table. And if you have such permission, Drop Table query removes an original table as well even if we don’t want to. I googled some times but I couldn’t find what I’ll share in this article in another site before. So, I’ll leave some detail queries before I forget.

 ALTER TABLE old_dummy_table SET TBLPROPERTIES ( 'EXTERNAL'='FALSE');

 ALTER TABLE old_dummy_table SET LOCATION
 "hdfs:///user/zuqqhi2/tmp/dummy";

 ALTER TABLE old_dummy_table PARTITION (service_id = "1", dt
 >= "2019-01-01") SET LOCATION
 "hdfs:///user/zuqqhi2/tmp/dummy";

 ALTER TABLE old_dummy_table DROP PARTITION (service_id = "1",
 dt >= "2019-01-01");

 DROP TABLE old_dummy_table;

Above queries are for a Hive table which has Partition. So, Partition’s Location is also changed. But of course we don’t need it for non Partition table. When we select Partition in Alter Table, we can use not only “=” but also “>=”, “<>” and etc. Those are very useful for many Partition table.

zuqqhi2