1

Assuming we have 2 hive tables created under the same HDFS file path.

I want to be able to drop a table WITH the HDFS files path, without corrupting the other table that's in the same shared path.

By doing the following:

drop table test;

Then:

hadoop fs -rm -r hdfs/file/path/folder/*

I delete both tables files, not just the one I've dropped.

In another post I found this solution:

--changing the tbl properties to to make the table as internal
ALTER TABLE <table-name> SET TBLPROPERTIES('EXTERNAL'='False'); 
--now the table is internal if you drop the table data will be dropped automatically
drop table <table-name>;

But I couldn't get passed the ALTER statement as I got a permission denied error (User does not have [ALTER] privilege on table)

Any other solution?

leftjoin
  • 36,950
  • 8
  • 57
  • 116
GeoSal
  • 333
  • 1
  • 2
  • 15

1 Answers1

1

If you have two tables using the same location, then all files in this location belongs to both tables, does not matter how they were created.

Say if you have table1 with location hdfs/file/path/folder and table2 with the same location hdfs/file/path/folder and you inserted some data into table1, files are created and they are being read if you select from table2, and vice-versa: if you insert into table2, new files will be accessible from table1. This is because table data is being stored in the location, no matter how you put the files inside that location. You can insert data into table using SQL, put files into location manually, etc.

Each table or partition has it's location, you cannot specify files separately.

For better understanding, read also this answer with examples about multiple tables on top of the same location: https://stackoverflow.com/a/54038932/2700344

leftjoin
  • 36,950
  • 8
  • 57
  • 116