how can we rename multiple partitions in Hive?

Question

If have two partitioned columns for eg. school name and class How can I rename a specific class partition which is present inside all school partitions

so,

/school=ABC/class=1/

/school=PQR/class=1/

.

class = 1 should be transformed to class = 2

/school=ABC/class=2/

/school=PQR/class=2/

.

Edit: In this example there are only two schools but it is variable there could be thousands of schools.

Possible duplicate of [How to rename partition value in Hive?](https://stackoverflow.com/questions/41124538/how-to-rename-partition-value-in-hive) — Gaurang Shah, Dec 05 '18 at 16:09
My query is not renaming a specific partition. It is about renaming hundreds to thousands of them. — rishabh, Dec 06 '18 at 05:43

score 0 · Answer 1 · answered Dec 05 '18 at 16:08

If table is managed table, you can simply use below command to rename the partition,

alter table tbl_name PARTITION (school='ABC', class=1) RENAME TO PARTITION (school='ABC', class=2);

alter table tbl_name PARTITION (school='PQR', class=1) RENAME TO PARTITION (school='PQR', class=2);

Below is execution I tried in hive,

hive> create table tbl_name (

name string, age int) partitioned by (school string, class int);

hive> alter table tbl_name ADD PARTITION (school='ABC', class=1); OK Time taken: 0.157 seconds

hive> alter table tbl_name ADD PARTITION (school='PQR', class=1); OK Time taken: 0.128 seconds

hive> show partitions tbl_name; OK school=ABC/class=1 school=PQR/class=1

hive> alter table tbl_name PARTITION (school='ABC', class=1) RENAME TO PARTITION (school='ABC', class=2); OK Time taken: 0.468 seconds

hive> alter table tbl_name PARTITION (school='PQR', class=1) RENAME TO PARTITION (school='PQR', class=2); OK Time taken: 0.432 seconds

hive> show partitions tbl_name; OK school=ABC/class=2 school=PQR/class=2

Hope this will help.

In this example there are only two schools but it is variable there could be thousands of schools. — rishabh, Dec 06 '18 at 05:42

Vijiy · Answer 2 · 2018-12-06T09:48:31.247

You can try getting the partition information from metadata.

1> get the metadata information from hive-site.xml file (location: /hive/installation/location/hive/hive-2.1/conf)

2> Get the env and credentials

 <property>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>[hostname]</value>
    <description>JDBC connect string for a JDBC metastore</description>
</property>


 <property>
    <name>javax.jdo.option.ConnectionUserName</name>
    <value>UserName</value>
    <description>username to use against metastore database</description>
 </property>

 <property>
    <name>javax.jdo.option.ConnectionPassword</name>
    <value>PWD</value>
    <description>password to use against metastore database</description>
 </property>

3> connect to metastore, and below is the query to get partition information.

select D.NAME, P.PART_NAME,  T.TBL_NAME from PARTITIONS P INNER JOIN TBLS T ON P.TBL_ID=T.TBL_ID INNER JOIN DBS D ON T.DB_ID=D.DB_ID WHERE D.NAME=<DBNAME> AND T.TBL_NAME=<TBLNAME> AND P.PART_NAME LIKE '%class=2%';

Once you have partition information, then you can make use of replace and concat function to derive alter statement.

Hope this helps.

how can we rename multiple partitions in Hive?

2 Answers2