Drop and overwrite external table in hive

Question

I need to create an external table in hiveql with the output from a SELECT clause. Every time when the HiveQL is ran the table should be dropped and recreated . When we drop an external table only the table structure is getting dropped but not the data files from HDFS location. How to achieve this?

Answer 1

Create Table As Select (CTAS) has restrictions. One of them is that target table cannot be External.

You have these options:

1. Create external table once, then INSERT OVERWRITE

   INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) select_statement1 FROM from_statement;

2. Use managed table, then you can DROP TABLE, then CREATE TABLE ... as SELECT

See also answer about skipTrash and auto.purge property.