Hive中基于唯一键合并zhihu_answer_tmp到历史表的实现方案咨询

针对你每日爬取知乎回答并合并到历史表的需求，我整理了几种符合幂等性要求的实现方案——保证重复执行脚本也不会插入重复数据，完全匹配你的需求：仅插入历史表中不存在（answer_id + insert_time 组合唯一）的记录。

场景1：Hive（基于你的DDL结构，大概率是Hive环境） #

因为你的表是按year_month分区的，所以合并时要注意分区字段的处理，这里提供两种常用写法：

写法1：使用NOT EXISTS筛选新记录 #

这种写法逻辑直观，直接过滤掉临时表中已经存在于历史表的记录：

INSERT INTO TABLE zhihu_answer PARTITION(year_month)
SELECT 
    tmp.admin_closed_comment,
    tmp.answer_content,
    tmp.answer_created,
    tmp.answer_id,
    tmp.insert_time,
    tmp.voteup_count,
    -- 如果临时表没有自动生成year_month，可从insert_time提取，示例格式为'yyyy-MM-dd HH:mm:ss'
    -- date_format(from_unixtime(unix_timestamp(tmp.insert_time, 'yyyy-MM-dd HH:mm:ss')), 'yyyy-MM') as year_month
    tmp.year_month
FROM zhihu_answer_tmp tmp
WHERE NOT EXISTS (
    SELECT 1 
    FROM zhihu_answer hist
    WHERE hist.answer_id = tmp.answer_id 
      AND hist.insert_time = tmp.insert_time
);

写法2：使用左连接筛选未匹配的记录 #

通过左连接后过滤历史表字段为NULL的行，同样能得到需要插入的新数据：

INSERT INTO TABLE zhihu_answer PARTITION(year_month)
SELECT 
    tmp.admin_closed_comment,
    tmp.answer_content,
    tmp.answer_created,
    tmp.answer_id,
    tmp.insert_time,
    tmp.voteup_count,
    tmp.year_month
FROM zhihu_answer_tmp tmp
LEFT JOIN zhihu_answer hist
ON hist.answer_id = tmp.answer_id 
   AND hist.insert_time = tmp.insert_time
WHERE hist.answer_id IS NULL;

关键注意事项：

• 确保临时表zhihu_answer_tmp和历史表结构完全一致，包括分区字段year_month；如果临时表没有该字段，一定要从insert_time中提取对应格式的分区值，否则插入会失败。
• 幂等性保障：两种写法都只会插入历史表中没有的记录，重复执行脚本不会产生重复数据，完美避免重复插入的副作用。
• 性能优化：如果数据量较大，建议在WHERE条件中加上分区裁剪，比如tmp.year_month = '2024-05'（匹配当日爬取数据的分区），减少扫描的数据量，提升执行效率。

场景2：MySQL（如果是关系型数据库环境） #

如果你的表是在MySQL中，需要先创建联合唯一索引，再用INSERT IGNORE实现幂等插入：

-- 第一步：创建联合唯一索引，确保answer_id + insert_time的组合唯一
ALTER TABLE zhihu_answer ADD UNIQUE INDEX idx_answer_insert (answer_id, insert_time);

-- 第二步：插入临时表数据，已存在的记录会被自动忽略
INSERT IGNORE INTO zhihu_answer 
SELECT * FROM zhihu_answer_tmp;

内容的提问来源于stack exchange，提问作者DennisLi