changing hive external table to internal table in the same database also drops the data from the another table

Question

I have been trying to drop a hive external table using the statement

alter table $tableName set tblproperties('EXTERNAL' = 'FALSE');

after I pass these properties and do a

DROP TABLE TABLENAME;

this command also drops the data from the other hive tables in that database.

Please can anyone suggest me why is it doing so? also if there is any way where we can only delete one external table which is mentioned without loosing the other data.

ALTER TABLE TABLENAME set tblproperties('EXTERNAL' = 'FALSE');
 drop table tablename;

This is possible if there are other tables with location pointing to the same location. — leftjoin, Jan 30 '19 at 07:47
yeah I figured out but thanks for confirming appreciate that leftjoin — Aditya Verma, Jan 30 '19 at 08:28
See this answer with tests please: https://stackoverflow.com/a/54038932/2700344 — leftjoin, Jan 30 '19 at 09:05

score 1 · Answer 1 · answered Jan 31 '19 at 10:26

When you drop managed table in Hive, its location with data files also gets dropped. And technically possible to create many tables, both managed and external on top of the same location, see this answer So, such scenario is quite possible. Some other table created with the same location will be emptied as well. Actually, data and table in Hive are loosely connected things. Table in Hive is a metastore information about location, schema, SerDe, statistics, access privileges, etc. And data is being stored in HDFS/S3 or other compatible filesystem. You can use some other means to drop data or load data, like hadoop fs -rm command, not only managed table in Hive.

changing hive external table to internal table in the same database also drops the data from the another table

1 Answers1