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.
The operation fails in the presence of an index.
Exchange partition is not allowed with transactional tables either as source or destination. Alternatively, use LOAD DATA or INSERT OVERWRITE commands to move partitions across transactional tables.
This command requires both the source and destination table names to have the same table schema.
If the schemas are different, the following exception is thrown:The tables have different schemas. Their partitions cannot be exchanged
Syntax
Code Block | ||
---|---|---|
| ||
ALTER TABLE <dest_table> EXCHANGE PARTITION (<[partial] partition spec>) WITH TABLE <src_table>
|
Example Usage – Basic
Code Block | ||
---|---|---|
| ||
--Create two tables, partitioned by ds
CREATE TABLE |
- Exchange Partition
It is proposed as part of https://issues.apache.org/jira/browse/HIVE-4095. Once this is implemented, it should be part of the Language Manual DDL.
The idea is similar to this.
The high level idea is as follows:
Code Block |
---|
alter table <tablename> exchange partition (<(partial)?partition spec>) with table <tablename>
|
The semantics of the above statement is that the data is moved from the target table to the source table. Both the tables should have the same schema. The operation fails in the presence of an index. The source table should not have that partition. Consider the following examples:
Code Block |
---|
create table T1(a string, b string) partitionedPARTITIONED byBY (ds string); createCREATE tableTABLE T2(a string, b string) PARTITIONED BY (ds string); |
The operation
Code Block |
---|
alter tableALTER TABLE T1 exchangeADD partitionPARTITION (ds='1') with table T2 |
moves the data from T2 to T1@ds=1. The operation fails if T1@ds=1 already exists or T1 and T2 have different schemas. Note that the schema for T2 is being used for the newly created partition T1@ds=1.
;
--Move partition from T1 to T2
ALTER TABLE T2 EXCHANGE PARTITION (ds='1') WITH TABLE T1; |
Example Usage – Partial Partition Spec (Exchanging Multiple Partitions)
Code Block | ||
---|---|---|
| ||
--Create two tables with multiple partition columns.
CREATE TABLE T1 | ||
Code Block | ||
create table T1(a string, b string) partitionedPARTITIONED byBY (ds string, hr string); createCREATE tableTABLE T2 (a string, b string) partitionedPARTITIONED byBY (ds string, hr string); |
The operation
Code Block |
---|
alter table T1 exchange partition (ds='1') with table T2 |
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 all the three partitions data where ds='1' from table T1 to table T2 (ds=1)
ALTER TABLE T2 EXCHANGE PARTITION (ds='1') WITH TABLE T1; |
Note that the schema for T1 moves the data from T2 to T1@ds=1. The operation fails if T1@ds=1 already exists or T1 and T2 have different schemas. Note that the schema for T2 is being used for the newly created partition T1@dsT2(ds=1). Either all the partitions of T1 will get created or the whole operation will fail. All partitions of T2 are dropped.T1 are dropped.
Example Usage – Partition Spec With Multiple Partition Columns
Code Block | ||
---|---|---|
| ||
-- Create two tables with multiple partition columns.
CREATE TABLE T1 (a int) PARTITIONED BY (d1 int, d2 int);
CREATE TABLE T2 (a int) PARTITIONED BY (d1 int, d2 int);
ALTER TABLE T1 ADD PARTITION (d1=1, d2=2);
-- Alter the table, moving partition data d1=1, d2=2 from table T1 to table T2
ALTER TABLE T2 EXCHANGE PARTITION (d1 = 1, d2 = 2) WITH TABLE T1;
|