Production Level Knowledge & Tips

  1. プログラミング
  2. 309 view

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.

プログラミング recent post

  1. How did I pass GCP Professional Cloud Archite…

  2. How to bring columns not GROUP BY key from ne…

  3. How to drop Hive’s External Table

  4. Install sbt 1.0.0 and run sample template

  5. Visualization of Neural Network and its Train…

関連記事

Comment

  1. No comments yet.

  1. No trackbacks yet.

PAGE TOP