Tech Tips

  1. プログラミング
  2. 714 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. Run Amazon FreeRTOS on M5Stack Core2 for AWS …

  2. Udacity Self-Driving Car Engineer Nanodegree …

  3. How to draw circle/line/text, show tooltip an…

  4. I read Software Systems Architecture 2nd Edit…

  5. How to define my environment with TF-Agents

関連記事

Comment

  1. No comments yet.

  1. No trackbacks yet.

PAGE TOP