Unable to perform hive exchange partition due to failure of: Partition already exists

[#1]

I am trying to exchange a partition from staging db after merging the incremental data with the existing one as below:

1. Created staging table with partition:

CREATE TABLE stg.customers_testcontrol_staging(customer_id bigint,customer_name string, customer_number string,status string,attribute_category string,attribute1 string, attribute2 string, attribute3 string, attribute4 string, attribute5 string) PARTITIONED BY (source_name string) ROW FORMAT SERDE'org.apache.hadoop.hive.ql.io.orc.OrcSerde' STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat' Location('/apps/hive/warehouse/stg.db/customers_testcontrol_staging'

2. Inserted data into above table after merging with the base table data

INSERT OVERWRITE TABLE finstg.customers_testcontrol_staging PARTITION 
(source_name) SELECT t1.* FROM (SELECT * FROM base.customers where 
source_name='ORACLE' UNION ALL SELECT * FROM 
external.customers_incremental_data) t1 JOIN (SELECT 
customer_id,source_name, max(updated_date) max_modified FROM (SELECT * 
FROM base.customers where source_name='ORACLE' UNION ALL SELECT * FROM 
external.customers_incremental_data) t2 GROUP BY 
customer_id,source_name) s ON t1.customer_id=s.customer_id AND 
t1.source_name=s.source_name;

Primary Keys of the table I am performing the join are: customer_id & source_name

3. Exchange partition step.

ALTER TABLE base.customers EXCHANGE PARTITION (source_name = 'ORACLE') WITH TABLE stg.customers_testcontrol_staging;

But the exchange partition step fails with the exception:

Error: Error while compiling statement: FAILED: SemanticException [Error 10118]: Partition already exists [customers(source_name=ORACLE)]

I took the syntax from Hive Confluence page

Is there anything I missed to include in the EXCHANGE partition statement ? Could anyone let me what is the mistake I am doing here & how can I fix it ?

[#2]

@Sidhartha Bobby

To run exchange partition successfully, Your destination table base.customers cannot contain the partition that you are exchanging.

• But base.customers table is already have the partition(source_name=ORACLE) that you are exchanging.

Resolution:

• Delete the existing partition in destination table and run the exchange partition command again.

(or)

• Exchange the partition that is already not exists in destination table.

• If you want to just append the data to Destination table then run insert into base.customerstable by selecting from stg.customers_testcontrol_staging table.

From HiveDocs: Constraints for Hive Exchange partitions:

1. The destination table cannot contain the partition to be exchanged.

2. The operation fails in the presence of an index.

3. 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.

4. 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:

5. The tables have different schemas. Their partitions cannot be exchanged