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?
Asked
Active
Viewed 3,915 times
3
-
After recreating the table, how do you load data? – Bala Oct 11 '18 at 12:26
-
I was referring to create table
as select * from table2; – Dileep Dominic Oct 11 '18 at 17:02 -
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 Oct 11 '18 at 17:53
-
Yes. It is to overwrite the data. – Dileep Dominic Oct 11 '18 at 18:01
-
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 Oct 11 '18 at 18:40
1 Answers
2
Create Table As Select (CTAS) has restrictions. One of them is that target table cannot be External.
You have these options:
Create external table once, then INSERT OVERWRITE
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) select_statement1 FROM from_statement;
Use managed table, then you can
DROP TABLE, thenCREATE TABLE ... as SELECT
See also answer about skipTrash and auto.purge property.
leftjoin
- 36,950
- 8
- 57
- 116