Jira | ||||||
---|---|---|---|---|---|---|
|
Exchanging multiple partitions is supported in Hive versions 1.2.2. 1.3.0, 2.0.0+ as part of
Jira | ||||||
---|---|---|---|---|---|---|
|
- 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
- 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:
Example usage - Basic
Code Block |
---|
--Create two tables, one partitioned by ds, one not partitioned
|
Code Block |
create table T1(a string, b string) partitioned by (ds string); create table T2(a string, b string); |
The operation
Code Block |
---|
--Exchange partition data from T2 to T1(ds=1) alter table T1 exchange partition (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@dsT1(ds=1).
Example usage - Rolling Hourly to Daily Partitions
Code Block |
---|
--Create two tables, one with multiple partitions, one with a single partition.
create table T1(a string, b string) partitioned by (ds string, hr string);
create table T2(a string, b string) partitioned by (hr string);
|
The operation
Code Block |
---|
--Alter the table, moving partition data where ds='1' from table T2 to table T1 (ds=1) alter table T1 exchange partition (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@dsT1(ds=1). Either all the partitions of T1 will get created or the whole operation will fail. All partitions of T2 are dropped.
Example Usage - Exchanging Multiple partitions
Code Block | ||
---|---|---|
| ||
-- Create two tables, both with multiple partitions
CREATE TABLE t3 (a int) PARTITIONED BY (d1 int, d2 int);
CREATE TABLE t4 (a int) PARTITIONED BY (d1 int, d2 int);
-- Alter the table, moving partition data where d1=1, d2=2 from table T4 to table T3
ALTER TABLE t4 EXCHANGE PARTITION (d1 = 1, d2 = 1) WITH TABLE t3;
|