How to rename a hive table without changing location?

26
votes

Based on the Hive doc below:

Rename Table

ALTER TABLE table_name RENAME TO new_table_name;

This statement lets you change the name of a table to a different name.

As of version 0.6, a rename on a managed table moves its HDFS location as well. (Older Hive versions just renamed the table in the metastore without moving the HDFS location.)

Is there any way to rename a table without changing the location?

4
hadoophivehiveql
Have you tried using external tables instead of hive managed tables: cwiki.apache.org/confluence/display/Hive/… – Stefan Papp
Even with a "managed" table, you should be able to set a non-default location => RENAME, then change location back to the original default, then move all HDFS files back... At this point you should ask yourself: why do I need to force the location? Does it make sense?? You give no reason for that, so I assume it's a bad idea. – Samson Scharfrichter

4 Answers

27
votes

Yeah we can do that. You just need to follow below three commands in sequence.

  1. Lets say you have a external table test_1 in hive. And you want to rename it test_2 which should point test_2 location not test_1. Then you need to convert this table into Managed table using below command. test_1 -> pointing to test_1 location

    ALTER TABLE db_name.test_1 SET TBLPROPERTIES('EXTERNAL'='FALSE');

  2. Rename the table name.

    ALTER TABLE db_name.test_1 RENAME TO db_name.test_2;

  3. Again convert the managed table after renaming to external table.

    ALTER TABLE db_name.test_2 SET TBLPROPERTIES('EXTERNAL'='TRUE');

db_name.test_2 table will point the test_2 location. If we do it without making the managed table it will point the test_1 location.

5
votes

As of Hive 2.2.0 a managed table's HDFS location is moved only if the table is created without a LOCATION clause and under its database directory.Link

5
votes

ALTER TABLE does not follow the databasename.tablename syntax in Hive like it does in CREATE or SELECT. Mention the databasename first and then run alter table statement.

syntax as below

USE databasename;
ALTER TABLE old_tablename RENAME TO new_tablename;
1
votes

Here is the command executed

ALTER TABLE old_ratings RENAME TO ratings;