基于条件删除Hive表中的重复记录

核心思路 #

针对表中存在的版本冗余和重复记录，通过窗口函数筛选每个(Comp_id, comp_isin, date)唯一组合下的最优记录：

• 优先保留v2版本（若存在），自动剔除同组合下的v1版本
• 同时过滤完全重复的记录，确保每个唯一组合仅存一行数据

实现方案 #

1. 创建清理后的新表（推荐，避免误操作原数据）

CREATE TABLE cleaned_company AS
WITH ranked_data AS (
    SELECT 
        *,
        -- 按版本优先级排序，v2优先；重复记录按插入时间取最新
        ROW_NUMBER() OVER (
            PARTITION BY Comp_id, comp_isin, date 
            ORDER BY CASE version WHEN 'v2' THEN 1 WHEN 'v1' THEN 2 END, insert_tms DESC
        ) AS rn
    FROM company
)
SELECT Comp_id, comp_isin, date, value, version, insert_tms
FROM ranked_data
WHERE rn = 1;

2. 直接覆盖原表（需先备份数据）

如果确认可以直接修改原表，执行前务必先备份：

-- 备份原表
CREATE TABLE company_backup AS SELECT * FROM company;

-- 覆盖原表
INSERT OVERWRITE TABLE company
WITH ranked_data AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY Comp_id, comp_isin, date 
            ORDER BY CASE version WHEN 'v2' THEN 1 WHEN 'v1' THEN 2 END, insert_tms DESC
        ) AS rn
    FROM company
)
SELECT Comp_id, comp_isin, date, value, version, insert_tms
FROM ranked_data
WHERE rn = 1;

逻辑说明 #

• PARTITION BY Comp_id, comp_isin, date：按唯一标识分组，确保每组内处理该组合的所有记录
• ORDER BY CASE ...：定义版本优先级，v2排第一，v1排第二；如果同版本有重复记录，取insert_tms最新的一条
• WHERE rn = 1：只保留每组中排序后的第一条记录，既实现版本筛选，又完成去重

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