Page History

The

...

EXCHANGE PARTITION command will move a partition from a source table to target table and alter each table's metadata.  The Exchange Partition feature is implemented as part

...

of HIVE-4095

...

. 

...

Exchanging multiple partitions is supported in Hive

...

versions 1.2.2, 1.

...

3.0,

...

and 2.0.0+ as part

...

of HIVE-11745

...

.

When the command is executed, the source table's partition folder in HDFS will be renamed to move it to the destination table's partition folder.  The Hive metastore will be updated to change the metadata of the source and destination tables accordingly.

The partition specification can be fully or partially specified.

See Language Manual DDL for additional information on the Exchange Partition feature.

Constraints

• The

...

•  destination table cannot contain the partition to be exchanged.

...

...

...

...

...

...

...

...

...

...

Syntax

...

Code Block
language sql
ALTER TABLE <dest_table> EXCHANGE PARTITIONalter table <tablename> exchange partition (<([partial)?] partition spec>) withWITH tableTABLE <tablename><src_table>

Example

...

Usage – Basic

--Create two tables, one partitioned by ds, one not partitioned
create table
CREATE TABLE T1(a string, b string) partitionedPARTITIONED byBY (ds string);
createCREATE tableTABLE T2(a string, b string) PARTITIONED BY (ds string);

 ALTER TABLE T1 ADD PARTITION (ds='1');

--ExchangeMove partition data from T2T1 to T1(ds=1)
alter table T1 exchange partitionT2
ALTER TABLE T2 EXCHANGE PARTITION (ds='1') withWITH table T2 

Note that the schema for T2 is being used for the newly created partition T1(ds=1).

...

TABLE T1;

Example Usage – Partial Partition Spec (Exchanging Multiple Partitions)

...

--Create two tables, one with multiple partitions, one with a single partitionpartition columns.
createCREATE tableTABLE T1 (a string, b string) partitionedPARTITIONED byBY (ds string, hr string);
createCREATE tableTABLE T2 (a string, b string) partitionedPARTITIONED byBY (ds string, hr string);
ALTER TABLE T1 ADD PARTITION (ds = '1', hr = '00');
ALTER TABLE T1 ADD PARTITION (ds = '1', hr = '01');
ALTER TABLE T1 ADD PARTITION (ds = '1', hr = '03');

--Alter the table, moving partition all the three partitions data where ds='1' from table T2T1 to table T1T2 (ds=1) 
alterALTER tableTABLE T1T2 exchangeEXCHANGE partitionPARTITION (ds='1') withWITH tableTABLE T2 T1;

Note that the schema for T2 T1 is being used for the newly created partition T1T2(ds=1). Either all the partitions of T1 will get created or the whole operation will fail. All partitions of T2 T1 are dropped.

Example Usage

...

 – Partition Spec With Multiple Partition Columns

-- Create two tables, both with multiple partition partitionscolumns.
CREATE TABLE t3T1 (a int) PARTITIONED BY (d1 int, d2 int);
CREATE TABLE t4T2 (a int) PARTITIONED BY (d1 int, d2 int);
ALTER TABLE T1 ADD PARTITION (d1=1, d2=2);

-- Alter the table, moving partition data where d1=1, d2=2 from table T4T1 to table T3T2
ALTER TABLE t4T2 EXCHANGE PARTITION (d1 = 1, d2 = 12) WITH TABLE t3T1;