How to truncate a partitioned external table in hive?

Question

I'm planning to truncate the hive external table which has one partition. So, I have used the following command to truncate the table :

 hive> truncate table abc; 

But, it is throwing me an error stating : Cannot truncate non-managed table abc.

Can anyone please suggest me out regarding the same ...

Answer 1

Make your table MANAGED first:

ALTER TABLE abc SET TBLPROPERTIES('EXTERNAL'='FALSE');

Then truncate:

truncate table abc;

And finally you can make it external again:

ALTER TABLE abc SET TBLPROPERTIES('EXTERNAL'='TRUE');

Answer 2

Look at https://issues.apache.org/jira/browse/HIVE-4367 : use

truncate table my_ext_table force;

Answer 3

There is an even better solution to this, which is basically a one liner.

insert overwrite table table_xyz select * from table_xyz where 1=2;

This code will delete all the files and create a blank file in the external folder location with absolute zero records.

Answer 4

By default, TRUNCATE TABLE is supported only on managed tables. Attempting to truncate an external table results in the following error:

Error: org.apache.spark.sql.AnalysisException: Operation not allowed: TRUNCATE TABLE on external tables

Action Required

Change applications. Do not attempt to run TRUNCATE TABLE on an external table.

Alternatively, change applications to alter a table property to set external.table.purge to true to allow truncation of an external table:

ALTER TABLE mytable SET TBLPROPERTIES ('external.table.purge'='true');