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 · Accepted solution]

Hi @Sidhartha,

The error means that the destination table base.customers already has the partition source_name=ORACLE, as the error indicated:

Partition already exists [customers(source_name=ORACLE)]

Can you run :

SHOW PARTITIONS base.customers;

to confirm? If yes, try to drop it and then run EXCHANGE PARTITION again.

Cheers
Eric