How to rename a table in Hive?

Alter table statement in Hive

Alter table statement helps to change the structure of the table in Hive. It allows us to rename the table,add columns/partitions,rename columns/partitions and so on in Hive table.Hive versions prior to 0.6 just renamed the table in the metastore without moving the HDFS location. But the later version moves its HDFS location if you rename on a managed table.

Rename table syntax in Hive

As mentioned in the syntax, mention your current table name after the ALTER TABLE command and specify the new table name after the RENAME TO command.

Example for Rename table in Hive

Let’s look at an example that uses the ALTER TABLE statement to rename the table. In this example, we will rename the prime_customer table to prime_customer_bkup in Hive.

Current table details in Hive

The prime_customer table has the below customer details in the test_db database.

Select records from the Hive table
Select records from the Hive table

This table is created as managed table in Hive. Lets see the structure of the table and its HDFS location before renaming the table.

From the output of describe formatted command, we can see that the HDFS location of the table is hdfs://test_env/apps/hive/warehouse/test_db.db/prime_customer.

Renaming the Hive table

Rename the table using Alter statement in Hive
Rename the table using Alter statement in Hive

The Alter table statement has successfully renamed the table from prime_customer to prime_customer_bkup in Hive. Also it changed the HDFS location of the file from hdfs://test_env/apps/hive/warehouse/test_db.db/prime_customer to hdfs://test_env/apps/hive/warehouse/test_db.db/prime_customer_bkup.

The old table prime_customer doesn’t exist in Hive and renamed table prime_cusomer_bkup has the records as shown below.

Renamed table in Hive with example
Renamed table in Hive with example