THIS IS A TEST INSTANCE. ALL YOUR CHANGES WILL BE LOST!!!!
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 Jira server ASF JIRA serverId 5aa69414-a9e9-3523-82ec-879b028fb15b key of HIVE-4095.
Exchanging multiple partitions is supported in Hive versions 1versions 1.2.2, 1. 1.3.0, 2and 2.0.0+ as part of Jira server ASF JIRA serverId 5aa69414-a9e9-3523-82ec-879b028fb15b key of HIVE-11745 See Language Manual DDL for additional information on the Exchange Partition feature.
Exchanging multiple partitions is supported in Hive versions 1versions 1.2.2, 1. 1.3.0, 2and 2.0.0+ as part of
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 spec specification can be fully or partially specified.
Constraints
- The destination table cannot contain the partition to be exchanged.
- The operation fails in the presence of an index.
- 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 <source_table> EXCHANGE PARTITION (<([partial)?] partition spec>) WITH TABLE <dest_table> |
Example
...
Usage – Basic
Code Block | ||
---|---|---|
| ||
--Create two tables, one partitioned by ds, one not partitioned CREATE TABLE T1(a string, b string) PARTITIONED BY (ds string); CREATE TABLE T2(a string, b string); --Exchange partition data from T1 to T2(ds=1) ALTER TABLE T1 EXCHANGE PARTITION (ds='1') WITH TABLE T2 |
Note that the schema for T1 is being used for the newly created partition T2(ds=1).
Example
...
Usage – Partial Partition Spec
Code Block | ||
---|---|---|
| ||
--Create two tables with multiple partitions. CREATE TABLE T1(a string, b string) PARTITIONED BY (ds string, hr string); CREATE TABLE T2(a string, b string) PARTITIONED BY (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 data where ds='1' from table T1 to table T2 (ds=1) ALTER TABLE T1 EXCHANGE PARTITION (ds='1') WITH TABLE T2 |
Note that the schema for T1 is being used for the newly created partition T2(ds=1). Either all the partitions of T1 will get created or the whole operation will fail. All partitions of T1 are dropped.
Example Usage
...
– Exchanging Multiple
...
Partitions
Code Block | ||
---|---|---|
| ||
-- Create two tables, both with multiple partitions CREATE TABLE t1 (a int) PARTITIONED BY (d1 int, d2 int); CREATE TABLE t2 (a int) PARTITIONED BY (d1 int, d2 int); -- Alter the table, moving partition data d1=1, d2=2 from table T1 to table T2 ALTER TABLE t1 EXCHANGE PARTITION (d1 = 1, d2 = 1) WITH TABLE t2; |