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 ?
