Versions Compared

Old Version 16

changes.mady.by.user Lefty Leverenz

Saved on

compared with

New Version Current

changes.mady.by.user Lefty Leverenz

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: edit Constraints section, add links to DML wiki

...

  • 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

...

Code Block
languagesql
ALTER TABLE <source<dest_table> EXCHANGE PARTITION (<[partial] partition spec>) WITH TABLE <dest<src_table>

Example Usage – Basic

Code Block
languagesql
--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) PARTITIONED BY (ds string);
ALTER TABLE T1 ADD PARTITION (ds='1');

--ExchangeMove partition data from T1 to T2(ds=1)
ALTER TABLE T1T2 EXCHANGE PARTITION (ds='1') WITH TABLE T2

...

T1;


Example Usage – Partial Partition Spec (Exchanging Multiple Partitions)

Code Block
languagesql
--Create two tables with multiple partition partitionscolumns.
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 addADD partitionPARTITION (ds = '1', hr = '00');
ALTER TABLE T1 addADD partitionPARTITION (ds = '1', hr = '01');
ALTER TABLE T1 addADD partitionPARTITION (ds = '1', hr = '03');

--Alter the table, moving partition all the three partitions data where ds='1' from table T1 to table T2 (ds=1) 
ALTER TABLE T1T2 EXCHANGE PARTITION (ds='1') WITH TABLE T2 T1;

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 – 

...

Partition Spec With Multiple Partition Columns

Code Block
languagesql
-- Create two tables, both with multiple partition partitionscolumns.
CREATE TABLE t1T1 (a int) PARTITIONED BY (d1 int, d2 int);
CREATE TABLE t2T2 (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 t1T2 EXCHANGE PARTITION (d1 = 1, d2 = 12) WITH TABLE t2T1;