Drop and overwrite external table in hive

2
votes

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?

1
hivehiveqlhiveddl
After recreating the table, how do you load data? - Bala
I was referring to create table <tablename> as select * from table2; - Dileep Dominic
why do you want to drop and re-create the external table? is it to remove data and overwrite it with new data from table as part of select cluase? - Gaurang Shah
Yes. It is to overwrite the data. - Dileep Dominic
if you only want to overwrite data, why should you drop the table? Can you not simply copy and overwrite the data into that external location? or you could use LOAD DATA INPATH...OVERWRITE INTO TABLE... - Bala

1 Answers

2
votes

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.