Hive中如何进行数据去重操作？代码举例讲解

发表于 作者 IT之美

在Hive中,我们可以使用以下方式进行数据去重操作:

1. CREATE TABLE AS SELECT with DISTINCT:

  • 使用CTAS语句创建表时,可以通过DISTINCT关键词对查询结果进行去重。
  • 这会在创建表时直接过滤掉重复数据。
    例如:
CREATE TABLE dept_unique 
AS 
SELECT DISTINCT dept_id, dept_name 
FROM department;

这会创建dept_unique表,其中dept_id和dept_name两列的数据都是唯一的。

2. INSERT OVERWRITE with DISTINCT:

  • 使用INSERT OVERWRITE向表中插入数据时,可以通过DISTINCT对查询结果进行去重。
  • 这会在写入表时过滤掉重复数据。
    例如:
INSERT OVERWRITE TABLE dept_unique
SELECT DISTINCT dept_id, dept_name
FROM department;

这会清空dept_unique表,然后插入dept_id和dept_name唯一的数据。

3. HAVING COUNT(*) > 1:

  • 我们可以通过GROUP BY对数据进行分组,然后使用HAVING COUNT(*) > 1过滤掉GROUP BY后只有一行的数据。
  • 这等效于去重,但是会保留最后一行数据。
    例如:
SELECT * 
FROM employees
GROUP BY dept_id, name
HAVING COUNT(*) > 1;

这会查询员工表,对部门和姓名分组,并仅保留分组后有多行的组。

4. ROW_NUMBER() OVER():

  • 我们可以使用 Window Function ROW_NUMBER()对数据进行排序和编号。
  • 然后使用ROW_NUMBER()的结果在WHERE子句进行过滤,实现去重。
    例如:
SELECT *
FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY dept_id ORDER BY name) AS rn 
FROM employees) t
WHERE rn = 1;

这会对员工表按部门和姓名排序,给每个组中的每行编号,然后过滤rn=1的行,实现去重。