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 ...

leftjoin · Accepted Answer · 2021-09-15T11:08:04.597

10

Make your table MANAGED first:

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

truncate table abc;

And finally you can make it external again:

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

edited Sep 15 '21 at 11:08

answered Nov 12 '18 at 07:37

leftjoin

@leftjoin- when we set 'EXTERNAL'='FALSE' for an external table, will it move the file location date to hive warehouse or it just help us to truncate the table. – vikrant rana May 20 '19 at 14:27
2

@vikrantrana truncate works only for managed tables, not external. Look at the docs: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-TruncateTable It says "Currently the target table should be native/managed table or an exception will be thrown. " – leftjoin May 20 '19 at 14:39
I think is much better Rahul's solution. Change the purge property to the external table. It works and it is clean. ALTER TABLE mytable SET TBLPROPERTIES ('external.table.purge'='true' – lauralacarra Apr 07 '22 at 09:55

score 3 · Answer 2 · answered Jan 04 '22 at 19:29

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');

score 2 · Answer 3 · answered Jul 21 '21 at 15:52

2

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.

answered Jul 21 '21 at 15:52

Sooraj Pradeep

Have you tried that with partitioned table? – leftjoin Sep 15 '21 at 11:28
Partitioned table will remain as is. This is misleading answer. – leftjoin Dec 17 '21 at 18:01

score 0 · Answer 4 · answered Dec 08 '22 at 13:16

0

truncate table my_ext_table force;

answered Dec 08 '22 at 13:16

Arnaud

4 Answers4