For information on how to truncate whole data or partitioned data in internal tables, check the previous blog. According to Hive documentation, the target table must be a native/managed table in order to truncate complete data / partitioned data; otherwise, an exception “FAILED: SemanticException [Error 10146]: Cannot truncate non-managed table <table-name>” will be thrown. However, in this essay, we’ll look at a solution for achieving that.
The following steps to be performed. Please note that this isn’t recommended approach however it is considered as a work-around.
Step-1:
Convert the external table to internal (aka. a managed table):
ALTER TABLE <table-name> SET TBLPROPERTIES(‘EXTERNAL’=’FALSE’);
Step-2:
The table partition should then be truncated:
TRUNCATE TABLE <table-name> PARTITION (PartitionColumn=’PartitionValue’)
Step-3:
Finally, you can convert it back to External table:
ALTER TABLE <table-name> SET TBLPROPERTIES(‘EXTERNAL’=’TRUE’);
Please note that the approach is applicable to truncate the entire data from a table or specific partitioned data.
Hope you find this article helpful.
Please follow us for more interesting updates.
Big Data and SQL 