Skip to main content
xinghx
xinghx
Silver ⭐️⭐️
August 2, 2022
Solved

hive 执行rename后，表的location没有相应发生改变

当前Hive版本：3.1.3000.7.1.7.0-551

 

  1. 创建hive外部表： CREATE TABLE IF NOT EXISTS `alter_test` (a string,b string) STORED AS ORC TBLPROPERTIES("transactional"="false");
  2. 转hive内部表：ALTER TABLE alter_test SET TBLPROPERTIES('external'='false');
  3. 重命名：ALTER TABLE alter_test RENAME TO alter_test_bak;

注：external.table.purge 这个配置是一直为true的

此时，表alter_test_bak的location目录名称依然是alter_test。

同样的操作步骤，我在Hive版本 3.1.3000.7.1.1.0-565是可以同步更改location目录名的，请问这是怎么回事呢

smruti
smrutiAnswer
Gold ⭐️⭐️⭐️
August 3, 2022

@xinghx This is an expected behavior in later version of CDP. Please refer to this Release note.  If yours is a managed table, in the default warehouse location, the HDFS path will be renamed, the way you expect it to.

However, if you plan to rename an External table, you will also need to change the location accordingly:

 

ALTER TABLE <tableName> RENAME TO <newTableName>;
ALTER TABLE <newTableName> set location "hdfs://<location>";

 

View in thread

5 replies

VidyaSargur
Community Manager
VidyaSargur
Community Manager
August 2, 2022

English Translation: 

 

After hive executes rename, the location of the table does not change accordingly

Current Hive version: 3.1.3000.7.1.7.0-551


Create hive external table: CREATE TABLE IF NOT EXISTS `alter_test` (a string,b string) STORED AS ORC TBLPROPERTIES("transactional"="false");
Turn hive internal table: ALTER TABLE alter_test SET TBLPROPERTIES('external'='false');
Rename: ALTER TABLE alter_test RENAME TO alter_test_bak;
Note: the external.table.purge configuration is always true

At this point, the location directory name of the table alter_test_bak is still alter_test.

The same operation steps, I can synchronously change the location directory name in Hive version 3.1.3000.7.1.1.0-565, what is going on?

Translate
Regards, Vidya Sargur, Community Manager Was your question answered? Make sure to mark the answer as the accepted solution. If you find a reply useful, say thanks by clicking on the thumbs up button.
smruti
Gold ⭐️⭐️⭐️
smrutiAnswer
Gold ⭐️⭐️⭐️
August 3, 2022

@xinghx This is an expected behavior in later version of CDP. Please refer to this Release note.  If yours is a managed table, in the default warehouse location, the HDFS path will be renamed, the way you expect it to.

However, if you plan to rename an External table, you will also need to change the location accordingly:

 

ALTER TABLE <tableName> RENAME TO <newTableName>;
ALTER TABLE <newTableName> set location "hdfs://<location>";

 

Translate
    xinghx
    xinghxAuthor
    Silver ⭐️⭐️
    August 4, 2022

    It's unreasonable for me because the table is created without a LOCATION clause and is under its database directory indeed.Besides,the release note topic is after upgrade to cdp,but it's normal in hive version  3.1.3000.7.1.1.0-565 which is also cdp.

    Translate
    smruti
    Gold ⭐️⭐️⭐️
    smruti
    Gold ⭐️⭐️⭐️
    August 5, 2022

    @xinghx The only difference between CDP 7.1.1 and 7.1.7 is HIVE-24920.

    In your test case, the CREATE TABLE statement is creating an External table with "TRANSLATED_TO_EXTERNAL" table property set to "TRUE". Your second query to change the table to a Managed/acid table does not really work, so that query has no impact apart from just adding a table property.

     

    Now coming to the RENAME query, I notice it does not change the location in CDP 7.1.1 either. Please refer to the attachment. In CDP 7.1.7(SP1) it does change the location if we have "TRANSLATED_TO_EXTERNAL" = "TRUE", If we set it to false, we have the same behavior as 7.1.1.

    alter table alter_test set tblproperties("TRANSLATED_TO_EXTERNAL"="FALSE");

    I hope this helps.

    Translate
      Terms & ConditionsAccessibility statement