Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

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

Solved Go to solution
Highlighted

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

Contributor

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 ?

1 ACCEPTED SOLUTION

Accepted Solutions

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

Guru
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
1 REPLY 1

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

Guru
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