hive sql 中 distinct 如何优化

在 Hive SQL 中，DISTINCT 是一个常用但容易引发性能问题的操作，因为它通常会触发全局去重（即全量数据 shuffle 到 Reducer 进行 GROUP BY），尤其在大数据场景下容易造成数据倾斜、内存溢出或执行缓慢。以下是针对 DISTINCT 的系统性优化策略：

---

一、基础优化原则

✅ 1. 优先使用 GROUP BY 替代 SELECT DISTINCT

虽然语义等价，但 GROUP BY 更利于 Hive 优化器介入。

-- 不推荐
SELECT DISTINCT col1, col2 FROM table;

-- 推荐
SELECT col1, col2 FROM table GROUP BY col1, col2;

原因：Hive 对 GROUP BY 支持更多优化（如 Map-side 聚合），而 DISTINCT 在某些版本中无法触发这些优化。

---

✅ 2. 启用 Map-side 聚合（预聚合）

在 Map 阶段局部去重，减少传输到 Reduce 的数据量。

SET hive.map.aggr = true;                      -- 启用 Map 端聚合
SET hive.groupby.mapaggr.checkinterval = 100000; -- 检查间隔（默认10万行）
SET hive.groupby.skewindata = true;            -- 处理数据倾斜（自动拆分 job）

⚠️ 注意：Map 端聚合会消耗更多内存，需确保 mapreduce.map.memory.mb 足够。

---

二、高级优化技巧

✅ 3. 提前过滤数据（减少输入规模）

在 DISTINCT 前尽可能通过 WHERE、分区裁剪、列裁剪缩小数据集。

-- 好
SELECT DISTINCT user_id 
FROM user_log 
WHERE dt = '2025-10-31' AND status = 'active';

-- 差（全表扫描）
SELECT DISTINCT user_id FROM user_log;

---

✅ 4. 多列去重时避免笛卡尔爆炸

若对多列组合去重，确保逻辑合理，避免无意义的宽表去重。

-- 若只需去重 user_id，不要带上无关字段
SELECT DISTINCT user_id, event_time  -- ❌ 可能导致结果膨胀
FROM log;

-- 改为只选必要字段
SELECT DISTINCT user_id FROM log;    -- ✅

---

✅ 5. 多个 COUNT(DISTINCT) 的优化

Hive 默认对每个 COUNT(DISTINCT col) 启动独立 job，效率极低。

方案 A：使用 approx_distinct()（推荐）

SELECT 
  approx_distinct(user_id) as uv,
  approx_distinct(session_id) as sv
FROM log;

• 基于 HyperLogLog，误差约 1~2%，性能提升 5~10 倍。
• Hive 2.1+ 支持。

方案 B：手动合并（精确去重）

SELECT
  COUNT(CASE WHEN type = 'user' THEN id END) AS uv,
  COUNT(CASE WHEN type = 'session' THEN id END) AS sv
FROM (
  SELECT user_id AS id, 'user' AS type FROM log GROUP BY user_id
  UNION ALL
  SELECT session_id AS id, 'session' AS type FROM log GROUP BY session_id
) t;

方案 C：开启 Hive 内置优化（Hive 2.2+）

SET hive.optimize.countdistinct = true;
-- 此时多个 COUNT(DISTINCT) 会被合并到一个 job

---

✅ 6. 使用 ROW_NUMBER() 替代部分 DISTINCT 场景

当你只需要“每组一条记录”而非“所有唯一值”时：

-- 获取每个用户最新一条日志
SELECT user_id, event_time, ...
FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time DESC) AS rn
  FROM log
) t
WHERE rn = 1;

比 SELECT DISTINCT user_id, ... 更高效，且可控。

---

三、存储与执行层优化

✅ 7. 使用列式存储（ORC/Parquet） + 谓词下推

CREATE TABLE log STORED AS ORC
TBLPROPERTIES ("orc.compress"="ZLIB");

• 列存减少 I/O
• 谓词下推（Predicate Pushdown）跳过无关数据块

✅ 8. 合理设置 Reducer 数量

SET mapreduce.job.reduces = 200;  -- 根据数据量调整，避免单个 Reducer 过载
-- 或让 Hive 自动估算
SET hive.exec.reducers.bytes.per.reducer = 256000000; -- 默认256MB/reducer

✅ 9. 处理数据倾斜

若某几个 key 占比极高（如空值、默认值）：

-- 过滤异常值
WHERE col IS NOT NULL AND col != 'N/A'

-- 或开启 skew 优化
SET hive.groupby.skewindata = true;

---

四、替代方案：预计算 & 物化

✅ 10. 构建去重汇总表（物化视图思想）

对高频去重查询，每日离线计算：

-- 每日活跃用户表
INSERT OVERWRITE TABLE dau_summary
SELECT dt, COUNT(DISTINCT user_id) AS dau
FROM user_log
GROUP BY dt;

查询时直接读取汇总表，避免实时去重。

---

五、总结：优化路线图

问题类型	推荐方案
单列去重慢	GROUP BY + hive.map.aggr=true + ORC
多 COUNT(DISTINCT)	approx_distinct() 或 UNION 合并
数据倾斜	hive.groupby.skewindata=true + 过滤异常值
只需一条记录	ROW_NUMBER() 替代
高频查询	预聚合表 + 分区
超大表去重	分桶 + 抽样 + 近似算法

---

通过结合 逻辑改写 + 参数调优 + 存储优化 + 近似计算，可以显著提升 Hive 中 DISTINCT 的执行效率，避免资源浪费和任务失败。