Hive: Metastore doesn't gets updated once table is renamed and altered

0
votes

I have an external table name MIAC and I renamed it to MIAC_BKUP. Also, I renamed the location of external table from MIAC to MIAC_bkup using Alter statement.

Now I create a new external table with name MIAC again to populate new values but I see MIAC_BKUP is also getting updated with new values only and no bckup values.

I suspect metastore not getting updated. I ran MSCK repair but no go.

both are partitioned tables

1
hadoophive

1 Answers

1
votes

So there is 3 thing you have change in your process.

  1. Rename table from MIAC to MIAC_bkup (you did that)
  2. Rename table location hdfs:///<some-lcoation>/MIAC to hdfs:///<some-lcoation>/MIAC_bkup (which you also did)
  3. Letting the table know about your new location with below query. 
ALTER TABLE MIAC_bkup SET LOCATION "hdfs:///<some-lcoation>/MIAC_bkup"

So when you added data into new MIAC, both backup and new tables are pointing to hdfs:///<some-lcoation>/MIAC. This is the reason you were seeing data in the backup table added via new table.

Untill here everything should work for you. Even you don’t have to do msck repair.

I believe you don’t have partition, because in that case your issue would be different.
But if the table has partition please follow these additional steps.

In the metadata, hive also stores partition location. So your existing partition metadata doesn’t know about the location change. You need to refresh the partition metadata also.

MSCK REPAIR alone wouldn’t do this job. You have to 1st drop all partition info from metadata then do the MSCK to sync older partition which are in backup location.

-- drop all partiotion
ALTER TABLE MIAC_bkup DROP PARTITION(partition_name <> ''); --refer this to understand 

-- repair table
MSCK REPAIR TABLE MIAC_bkup;

NB: MSCK repair doesn’t delete partition, it only adds or checks inconsistency