hive - 由于 : Partition already exists 失败，无法执行 Hive 交换分区

Question

在将增量数据与现有数据合并后，我正在尝试从暂存数据库交换分区，如下所示:

1. 创建了带分区的暂存表:

   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) 行格式SERDE'org.apache.hadoop.hive.ql.io.orc.OrcSerde' 存储为输入格式'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' 输出格式'org.apache.hadoop.hive.ql。 io.orc.OrcOutputFormat' Location('/apps/hive/warehouse/stg.db/customers_testcontrol_staging'

2. 与基表数据合并后向上表插入数据

   插入覆盖表 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 其中 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;

我正在执行连接的表的主键是:customer_id 和 source_name

1. 交换分区步骤:

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

但是交换分区步骤失败，异常:

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

我采用了 Hive Confluence page 中的语法

EXCHANGE 分区语句中是否遗漏了任何内容？谁能告诉我我在这里犯了什么错误，我该如何解决？

Answer 1

要成功运行 exchange partition，您的目标表 base.customers 不能包含您正在交换的分区。

• 但是 base.customers 表已经有您正在交换的 partition(source_name=ORACLE)。

解决方案:

• 删除目标表中现有的分区，然后再次运行交换分区命令。

(或)

• 交换目标中不存在的分区表。

• 如果您只想将数据附加到 Destination 表，然后运行 ​​insert into base.customers 表，方法是从stg.customers_testcontrol_staging 表。

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 eitheras source or destination. Alternatively, use LOAD DATA or INSERTOVERWRITE commands to move partitions across transactional tables.

4. This command requires both the source and destination table names tohave the same table schema. If the schemas are different, thefollowing exception is thrown:

5. The tables have different schemas. Their partitions cannot beexchanged