hadoop - delete hive partitioned external table but retain partitions -

when using external hive tables, there way can delete data within directory retain partitions via query. please note dont want drop table , recreate it. want empty underlying folder , start process on again. tables large, partitions year, month, date , hour , takes lot of time recreate partitions manually.

thanks

truncate table ... delete data.
truncate table partition (...) delete specific partitions' data.

the directories structure kept.

---

external table should first converted manged, .e.g

alter table t set tblproperties('external'='false'); 

when done, can convert back

alter table t set tblproperties('external'='true'); 

---

demo

create table t (i int) partitioned (x char(1)); set hive.exec.dynamic.partition.mode=nonstrict; insert t partition (x) values (1,'a'),(2,'b'),(3,'c'); alter table t set tblproperties('external'='true'); 

---

select * t; 

---

+-----+-----+ | t.i | t.x | +-----+-----+ |   1 |   | |   2 | b   | |   3 | c   | +-----+-----+ 

---

dfs -ls -r /user/hive/warehouse/t; 

---

drwxrwxrwx   - cloudera supergroup          0 2017-03-28 11:40 /user/hive/warehouse/t/x=a -rwxrwxrwx   1 cloudera supergroup          2 2017-03-28 11:40 /user/hive/warehouse/t/x=a/000000_0 drwxrwxrwx   - cloudera supergroup          0 2017-03-28 11:40 /user/hive/warehouse/t/x=b -rwxrwxrwx   1 cloudera supergroup          2 2017-03-28 11:40 /user/hive/warehouse/t/x=b/000000_0 drwxrwxrwx   - cloudera supergroup          0 2017-03-28 11:40 /user/hive/warehouse/t/x=c -rwxrwxrwx   1 cloudera supergroup          2 2017-03-28 11:40 /user/hive/warehouse/t/x=c/000000_0 

---

truncate table t partition (x='b'); 

failed: semanticexception [error 10146]: cannot truncate non-managed table t.

alter table t set tblproperties('external'='false'); 

---

truncate table t partition (x='b'); 

---

select * t; 

---

+-----+-----+ | t.i | t.x | +-----+-----+ |   1 |   | |   3 | c   | +-----+-----+ 

---

dfs -ls -r /user/hive/warehouse/t; 

---

drwxrwxrwx   - cloudera supergroup          0 2017-03-28 11:40 /user/hive/warehouse/t/x=a -rwxrwxrwx   1 cloudera supergroup          2 2017-03-28 11:40 /user/hive/warehouse/t/x=a/000000_0 drwxrwxrwx   - cloudera supergroup          0 2017-03-28 11:42 /user/hive/warehouse/t/x=b drwxrwxrwx   - cloudera supergroup          0 2017-03-28 11:40 /user/hive/warehouse/t/x=c -rwxrwxrwx   1 cloudera supergroup          2 2017-03-28 11:40 /user/hive/warehouse/t/x=c/000000_0 

---

truncate table t; 

---

+-----+-----+ | t.i | t.x | +-----+-----+ 

---

dfs -ls -r /user/hive/warehouse/t; 

---

drwxrwxrwx   - cloudera supergroup          0 2017-03-28 11:43 /user/hive/warehouse/t/x=a drwxrwxrwx   - cloudera supergroup          0 2017-03-28 11:43 /user/hive/warehouse/t/x=b drwxrwxrwx   - cloudera supergroup          0 2017-03-28 11:43 /user/hive/warehouse/t/x=c 

---

alter table t set tblproperties('external'='true');