做大数据分析的人，十有八九都遇到过这种场景：数仓里同一张订单表，日期字段同一天既有2024-06-01，又有20240601，还有2024/06/01的写法；同一个用户ID，接口侧存的是userId_123，数据库侧存的是123，两边 join 永远对不上；月底对账时业务报表和财务系统差了38块5，排查半天发现不是 SQL 算错，而是金额字段从 decimal(18,2) 被转成了 string，精度丢了一位。
这些问题的源头都不在分析环节，而在数据进入数仓之后、进入计算之前的清洗环节。今天想聊的，就是我在日常处理 Hive 数据清洗任务时真正会用到、也反复踩过坑的一些技巧，适合刚开始接触大数据分析、在数仓里提数开发的人参考。看完之后你会发现，数据清洗绝对不是“写几个 SQL 把空值填掉”这么简单，它是在给整条数据链路安装过滤器。
如果只是把清洗理解为“去重 + 填空”，大概率会在第二步翻车。清洗的本质，是让数据从“业务系统里能跑”变成“分析口径下能用”，中间涉及对数据来源的理解、对业务规则的确认、对异常值的判断，以及对跑批性能和结果可追溯性的控制。下面直接进入正题。
1. 数据清洗前得先分清：哪些脏该洗，哪些脏不能洗
1.1 按来源把脏数据分成三类，才能对症下药
我习惯把脏数据分成三类：技术脏、业务脏、环境脏。分类不是为了学术，而是为了决定清洗动作是“转换”“过滤”还是“保留报错”。
技术脏，指的是数据在传输、存储、接口对接过程中产生的格式类问题。最常见的有：时间字段格式不统一、小数精度丢失、字符编码错乱、时区不统一。比如两个服务端接口，一个返回 Unix 秒级时间戳，一个返回yyyy-MM-dd HH:mm:ss字符串，落库到同一张表之后，dt 字段就成了两套标准。这类脏数据清洗方式很明确：制定统一规范，写转换函数，能把所有值规整成目标格式。
业务脏，指的是数据本身符合技术规范，但不符合业务语义。典型例子是性别字段出现男、male、0、1、M五种写法；年龄字段出现 0、-1、空值；订单状态枚举里混入了不存在的编码。这类脏数据最危险，因为它表面上“格式正常”，实际却会把聚合结果带偏。处理这类数据，必须先找业务方确认枚举字典和默认值含义，不能凭经验猜。
环境脏，指的是同步链路本身造成的数据重复或不完整。比如离线任务重跑导致 ODS 层出现重复分区、日志采集进程崩溃导致某几小时数据丢失、上游任务先写后删导致读到半成品。这类脏数据往往通过“时间戳字段 + 主键去重 + 分区校验”来处理，清洗重点不是转换，而是识别哪些记录属于无效写入。
1.2 开洗之前，先做三件基础事，别急着写 SQL
很多同学拿到表就开始SELECT * FROM ... LIMIT 100，看几行就动手写清洗脚本，这是最容易翻车的。我的习惯是动手之前先做三件事。
第一，确认分区策略。清洗任务必须知道每次处理哪个时间范围，是全量重建还是增量清洗，这决定了后面所有 SQL 的 where 条件和写入方式。假如一张表要按天增量清洗，但你没做分区裁剪，直接把历史 3 年数据重跑了，跑批时间从 20 分钟变成 6 小时，还没跑完就撞上调度窗口。
第二，抽样做数据体检。用TABLESAMPLE(BUCKET x OUT OF y)或者随机抽样取一小部分数据，按字段统计空值率、枚举值分布、最大最小值，先做到“心里有数”。这一步不花多少时间，但能帮你判断哪些字段值得洗、哪些字段压根没脏。
第三，确认清洗任务的可回滚性。写清洗 SQL 时会用到INSERT OVERWRITE，如果逻辑写错，覆盖之后原数据就没了。所以正式清洗前，要么先把原始数据备份到单独的表或分区，要么确保 ODS 层的原始表还在且可以用上游日志重放，否则排查问题时连对比样本都没有。
我自己踩过的一个坑是：某次清洗任务把手机号字段里的+86前缀去掉后，直接覆盖了原始手机号字段，结果后来发现有些渠道的号码本来就该带国家码，业务统计需要区分国内外用户，但原始信息已经被抹掉了。从那以后，所有清洗字段我都保留一份原始值或者清洗标记，这个习惯帮了大忙。
2. Hive SQL 清洗手段：从去重、空值到异常值的标准动作
2.1 去重不是SELECT DISTINCT，而是按业务主键取代表记录
这是数据清洗里被误解最深的一个点。SELECT DISTINCT确实能去重，但它去的是整行完全重复的记录，一旦两条记录的主键相同、其他字段稍有差异（比如更新时间不同、状态字段不同），DISTINCT 就无能为力了。更关键的是，DISTINCT 会触发全字段的 shuffle，数据量大时性能非常难看。
实际业务中，重复往往是“同一业务主键出现了多条记录”。比如订单表里同一个 order_id 对应两条状态，一条是“已支付”，一条是“已退款”，那分析时应该取哪条？我的标准写法是窗口函数按主键分区、按业务时间倒序、取 rn = 1：
INSERT OVERWRITE TABLE dwd_orders PARTITION (dt = '2024-06-27') SELECT order_id, user_id, amount, status, create_time, update_time FROM ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY order_id ORDER BY update_time DESC ) AS rn FROM ods_orders WHERE dt = '2024-06-27' ) t WHERE rn = 1;这里几个细节需要注意。PARTITION BY的字段是业务主键，不是把所有字段都放进去，否则又变回全字段去重了；ORDER BY的字段要选择能代表“哪条记录才是最新有效记录”的业务时间，一般用 update_time 或 create_time，如果系统里还有操作流水号，可以用流水号做二级排序。
如果你只想快速去重，不关心保留哪一条，也可以不排，直接ROW_NUMBER() OVER (PARTITION BY order_id)，然后取 rn = 1，这样能省掉排序带来的开销。还有一点，窗口函数结果集里不能直接取原始*，要显式列出字段并把 rn 过滤掉，避免最后表里多了一列无业务含义的行号。
2.2 NULL、空字符串与业务默认值：三种情况必须分开处理
很多清洗脚本一上来就是COALESCE(user_name, '未知')，把所有空值一刀切替换掉。这样做的问题在于：NULL、空字符串、业务默认值表示三种完全不同的事情。
NULL 代表“未知”，空字符串代表“有采集但没填”，业务默认值比如 0、-1、'N/A'代表系统在异常情况下写入的占位符。它们对聚合计算的影响不同：SUM(amount)会跳过 NULL，但如果空字符串被隐式转成 0，AVG(amount)的结果就会偏低；如果默认值是 -1，SUM直接就把总额拉没了。
我常用的处理逻辑是按字段语义分三层：
SELECT user_id, -- 手机号：NULL 和空字符串统一处理为未知，方便后续判断 CASE WHEN user_phone IS NULL OR user_phone = '' THEN 'UNKNOWN' ELSE user_phone END AS user_phone_std, -- 年龄：先处理非法值，再处理 NULL CASE WHEN user_age IS NULL THEN 0 WHEN user_age < 0 OR user_age > 120 THEN 9999 ELSE user_age END AS user_age_std, -- 金额：NULL 业务上视为 0，但负数是异常值，需要标记 CASE WHEN amount IS NULL THEN 0 WHEN amount < 0 THEN -1 ELSE amount END AS amount_flag FROM ods_users WHERE dt = '2024-06-27';这里你会发现，我并没有把所有空值都替换成同一个值，而是针对不同字段定义不同的“清洗结果”。具体怎么处理，取决于这个字段在后续分析里怎么用。比如年龄字段，NULL 用 0 代替只是权宜之计，后续统计最好把 0 排除；金额字段的负数，我建议单独打标记，不要直接求和，否则会污染 GMV。
还有一个容易忽略的点：Hive 里NULL = NULL得到的是 NULL，不是 true。判断空值一定要用IS NULL，不能用= NULL。字符串空判断用col = ''或者length(col) = 0，但要注意 trim，有些系统写入时会携带空格。
2.3 用统计量把异常值从“肉眼看不出”变成“一眼查出”
异常值清洗不是靠人看，而是靠统计函数。对于数值字段，我习惯先用一小段 SQL 看分布，把超过合理阈值的记录揪出来：
SELECT COUNT(*) AS total_cnt, SUM(CASE WHEN amount IS NULL THEN 1 ELSE 0 END) AS null_cnt, SUM(CASE WHEN amount < 0 THEN 1 ELSE 0 END) AS neg_cnt, SUM(CASE WHEN amount > 100000 THEN 1 ELSE 0 END) AS exceed_cnt, PERCENTILE_APPROX(amount, 0.99) AS p99, PERCENTILE_APPROX(amount, 0.5) AS median FROM ods_orders WHERE dt = '2024-06-27';看这几个数字就能快速定位问题：如果负金额占比超过 0.1%，可能是退款单被重复同步；如果 99 分位值远高于中位数，说明存在少数超大金额订单，需要判断是真实业务（比如大额合同），还是脏数据（比如金额单位不统一，有的元、有的分）。这种“先看分布、再定阈值”的方法，比一上来拍脑袋写WHERE amount > 10000要科学得多。
判断完分布之后，真正清洗异常值有两种思路：一种是直接过滤，比如订单金额超过 100 万且没有备注的，大概率是错误数据，可以在 DWD 层排除；另一种是打标记，把异常值留在原表里，但增加一个amount_flag字段供下游分析时使用。我通常倾向后者，因为过滤掉的数据可能藏着真实业务价值，打标记更稳。
2.4 格式规整：日期、小数与编码一次搞定
格式规整是所有清洗动作里最机械但最不能出错的。日期字段的处理逻辑，我一般用一套兼容性较强的 CASE WHEN：
SELECT dt_orig, -- 将多种格式统一为 'yyyy-MM-dd' CASE WHEN dt_orig RLIKE '^[0-9]{8}$' THEN CONCAT(SUBSTRING(dt_orig, 1, 4), '-', SUBSTRING(dt_orig, 5, 2), '-', SUBSTRING(dt_orig, 7, 2)) WHEN dt_orig RLIKE '^[0-9]{4}/[0-9]{2}/[0-9]{2}' THEN REGEXP_REPLACE(dt_orig, '/', '-') WHEN dt_orig RLIKE '^[0-9]{4}-[0-9]{2}-[0-9]{2}' THEN dt_orig ELSE NULL END AS dt_std FROM ods_table;数值精度问题，尤其是金额字段，我强烈建议在清洗阶段就转成 DECIMAL 类型：
CAST(amount AS DECIMAL(18, 2))这是因为浮点数在 Hive 里存成 DOUBLE 会有精度误差，0.1 + 0.2 可能结果是 0.30000000000000004，做 SUM 的时候误差会被放大。等到用的时候再转，远不如清洗阶段就转成定点数干净。
编码统一也是格式规整里的大头。比如性别字段有男/女/male/female/1/0等乱七八糟的写法，最简做法是 UDF 或者 CASE WHEN 映射成统一枚举。如果你的团队有现成的 UDF，比如统一手机号格式、统一身份证号校验，尽量复用，不要反复造轮子。
3. 窗口函数在清洗场景里是怎么“以小博大”的
3.1 同一业务主键下的“最新有效记录”，窗口函数是唯一解
前面去重部分已经用到了ROW_NUMBER()，但它的能力远不止去重。真正复杂的业务场景是：一个主键下有多条记录，每条记录反映不同状态，我们既要取最新一条，又要保留历史路径。比如用户会员等级变更表，一个用户可能一个月内从普通会员变成黄金会员又变成铂金会员，分析当日用户等级分布时只能用当天最后一次变更后的等级。
写法也很直观：
SELECT user_id, member_level FROM ( SELECT user_id, member_level, ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY change_time DESC ) AS rn FROM dwd_member_change WHERE dt = '2024-06-27' ) t WHERE rn = 1;这种场景如果用 GROUP BY，你得先求每个用户最大的 change_time，再 join 回原表，逻辑绕、性能差，还要处理两个用户 change_time 相同的情况。窗口函数一步到位，代码也容易读。
3.2 用 LAG/LEAD 做相邻行为判断：连续重复提交、异常高频操作一览无余
LAG 和 LEAD 函数能访问同一分区内前一行和后一行的数据。它们特别适合清洗“连续行为”类数据。我举一个真实的例子：某个埋点表里，同一用户同一订单在几十秒内重复上报了三次“支付成功”，但实际只支付了一笔。单纯看每一行都是合法记录，用主键也去不掉，因为它们没有唯一业务键。
这时就可以用 LAG 计算相邻两次上报的时间间隔，把时间差过短的记录标记为疑似重复：
SELECT order_id, user_id, action_time, LAG(action_time) OVER ( PARTITION BY order_id ORDER BY action_time ) AS prev_action_time, (UNIX_TIMESTAMP(action_time, 'yyyy-MM-dd HH:mm:ss') - UNIX_TIMESTAMP(LAG(action_time) OVER ( PARTITION BY order_id ORDER BY action_time ), 'yyyy-MM-dd HH:mm:ss')) AS gap_seconds FROM ods_action_log WHERE dt = '2024-06-27';拿到gap_seconds之后，再在外层过滤掉小于某个阈值的记录。阈值怎么定？一般是结合业务确认，比如支付事件重复上报通常发生在 30 秒内，那就把gap_seconds < 30的记录打标或过滤。这里要提醒一句，做这种判断前一定要先按PARTITION BY的字段确认记录本身是有序的，如果上游日志本身就乱序，洗出来都是假的，等于白洗。
3.3 多表合并时用窗口函数替代多次自连接，既省时间又少 bug
数据清洗里还有一个常见需求：把两张明细表按业务主键合并成一张宽表，但同一个主键在两侧都可能有多条记录。最笨的自连接写法会造成行数膨胀，多了一条就多出一倍，最后 COUNT 出来数字怎么看都不对。
更好的做法是把“取主键下的某条代表记录”提前到连接动作之前，先把两侧表各自去重降维度，再 join。比如订单明细表和支付流水表关联时，先对支付流水按订单号取最近一条，再 join 订单表。这个思路看起来简单，实际能省掉大量因多对多连接导致的脏数据，清洗质量会显著提升。
4. 容易翻车的跑批环节：性能、小文件和幂等性
4.1 分区裁剪是第一原则，别让清洗任务拖垮整个集群
清洗任务最常见的性能问题，就是没写分区条件或写得太宽，导致 Hive 扫描了全表。我见过最夸张的一次，某任务可以按天增量清洗，但脚本里没有WHERE dt = '${bizdate}'，结果每天早上把过去 3 年的 ODS 数据全部重洗一遍，集群资源被吃满，下游依赖任务全部延迟。
正确写法是任务参数化，每天只处理当天分区：
INSERT OVERWRITE TABLE dwd_orders PARTITION (dt = '${bizdate}') SELECT ... FROM ods_orders WHERE dt = '${bizdate}';如果某天需要回溯历史数据，单独跑一个“回溯模式”，而不是每天默认全量跑。还有一个细节是：在清洗任务里对日期分区字段做主键过滤时，尽量避免在 ON 条件或子查询里使用函数套函数，比如WHERE dt = FROM_UNIXTIME(${timestamp}, 'yyyy-MM-dd')，如果提前把变量算好再传进去，扫描效率会好很多。
4.2 动态分区写入的小文件问题：不处理会让下游越跑越慢
清洗任务写出的目标表往往是分区表，而源表数据可能来自多个分区或者多个渠道，写入时很容易用动态分区。动态分区本身没问题，但如果没有控制分发逻辑，Hive 会为每个分区生成大量小文件。一个只有几 MB 的分区，文件数却上百个，下游读的时候就要启动上百个 mapper，效率可想而知。
控制办法很简单，写入时用DISTRIBUTE BY把相同分区的数据分发给同一个 reducer：
INSERT OVERWRITE TABLE dwd_orders PARTITION (dt) SELECT order_id, user_id, dt FROM ods_orders WHERE dt = '${bizdate}' DISTRIBUTE BY dt;如果目标表有多个分区字段，就按实际分区字段组合 distribute。另外，还可以在跑批前设置 Hive 的参数来开启小文件合并：
SET hive.merge.mapfiles = true; SET hive.merge.mapredfiles = true; SET hive.merge.size.per.task = 256000000; SET hive.merge.smallfiles.avgsize = 128000000;这几个参数的含义分别是：map-only 任务结束时合并小文件、map-reduce 任务结束时合并输出、合并后单个文件的目标大小、小文件的平均大小阈值。设好之后，清洗任务的输出文件数会稳定很多。
4.3 数据倾斜：同一个 key 的数据量特别大时，先打散再聚合
清洗任务也逃不过数据倾斜。最常见的是按用户 ID 或店铺 ID 做 GROUP BY 时，某个头部用户的数据量占了一大半，单个 reducer 要处理几千万条，其他 reducer 却闲得很，跑批直接卡死。
常用的加盐法是：将大 key 先用随机数拆散成多个临时 key，各自聚合成中间结果，再去掉盐做二次聚合。以统计每个用户的订单金额为例：
SELECT user_id, SUM(amount) AS total_amount FROM ( SELECT -- 加盐：把同一个 user_id 的数据分散到 10 个分组 user_id, amount, CASE WHEN is_hot_user = 1 THEN CONCAT(CAST(user_id AS STRING), '_', CAST(rand() * 10 AS INT)) ELSE CAST(user_id AS STRING) END AS salted_key FROM ( SELECT user_id, amount, -- 先用一个子查询标记热点用户，防止加盐影响主键聚合正确性 CASE WHEN user_id IN ('hot_user_1', 'hot_user_2') THEN 1 ELSE 0 END AS is_hot_user FROM ods_orders WHERE dt = '${bizdate}' ) inner_data ) salted_data GROUP BY user_id, salted_key;这段 SQL 建议现场按实际场景调整，核心思路是“热点数据加随机盐分散压力，非热点不加盐，第二次聚合再归拢”。实际生产里，我通常会把热点用户名单事先算出来，而不是在清洗 SQL 里硬编码，这样更灵活。
4.4 清洗任务的幂等性：重复执行不能留下重复数据
调度系统不稳定，某个清洗任务跑了一半挂了，恢复后重跑是常态。如果清洗逻辑是INSERT INTO，重跑一次就会在同一个分区里追加一份数据，下游直接翻倍。解决办法很粗暴：清洗任务的写入统一使用INSERT OVERWRITE，并且指定具体分区，保证同一个分区无论执行几次，写入结果都是一份。
还有一个细节是“先写后删”问题。有的任务为了性能，先往目标分区写入临时数据，全部写完后再删掉原始调度标记表，如果中途失败，临时分区会残留。我常用的做法是，把当次任务的数据先写入一个临时表或临时分区，写入成功后通过一个原子性的ALTER TABLE ... DROP PARTITION+ALTER TABLE ... ADD PARTITION完成切换，虽然没有事务那么完美，但能明显降低残留脏数据的概率。
5. 清洗做完了怎么验收，以及如何把清洗能力沉淀下来
5.1 清洗前后统计对比：数字不对，说明清洗逻辑有问题
清洗任务写完，第一件事不是看任务成没成功，而是看清洗前后的统计指标。我会把同一份数据在清洗前后的行数、唯一主键数、空值数、金额合计放到一张表里对比：
SELECT 'before' AS stage, COUNT(*) AS row_cnt, COUNT(DISTINCT order_id) AS uniq_cnt, COUNT(DISTINCT user_id) AS user_cnt, SUM(amount) AS amount_sum FROM ods_orders WHERE dt = '${bizdate}' UNION ALL SELECT 'after' AS stage, COUNT(*) AS row_cnt, COUNT(DISTINCT order_id) AS uniq_cnt, COUNT(DISTINCT user_id) AS user_cnt, SUM(amount) AS amount_sum FROM dwd_orders WHERE dt = '${bizdate}';比如 before 的row_cnt是 100 万，after 降到 98 万，那说明有 2 万条被去重或过滤掉了，你得确认这 2 万条到底是什么，而不是看着任务跑完就收工。金额合计如果 before 和 after 差距过大，可能是过滤条件把有效订单误杀了，也可能是金额字段还在字符串类型下做着隐式转换。只有把这些数字逐一解释清楚，清洗才算真正完成。
5.2 数据抽样检查清单：配置化规则表，让检查不靠人肉
统计对比是宏观层面，抽样检查是微观层面。我会在清洗后的表里按用户维度或按渠道维度随机抽样几百到几千行，人工确认关键字段是否符合预期。抽样 SQL 可以这样写：
SELECT * FROM dwd_orders WHERE dt = '${bizdate}' AND rand() < 0.001 LIMIT 1000;更系统一点的做法是把检查规则做成一张配置表：每条规则包含字段名、检查类型（非空、取值枚举、范围、正则）、阈值、处理动作。清洗任务跑完后，自动执行一遍规则检查，把不满足规则的记录数输出。这样做的好处是积累知识库，同样的脏数据下次出现时能自动识别，不用每次从零开发。
5.3 保留原始字段、反向通知上游，清洗不是终点而是反馈
清洗逻辑本身也会演化：业务枚举变了、上游接口换了、新的默认值出现了，固定写死的判断条件很快就会过时。所以清洗脚本里，凡是做过转换的字段，我习惯保留一个原始字段备份和清洗标记字段，比如phone_raw、phone_std、phone_clean_flag = 1。这样万一清洗规则被业务否定，还能快速回滚重建，不用重新拉取原始数据。
更重要的一点是：清洗任务发现了高频脏数据，一定要反向反馈给上游数据生产方。比如连续一周发现某个渠道的订单时间字段都是yyyy-MM-dd'T'HH:mm:ss格式，这属于源头质量问题，单靠清洗手段每次都能处理，但每次也都在消耗跑批时间和维护成本。直接在数据质量周报里反馈给对应负责人，推动源头修复，清洗任务的负担会越来越小。
我个人的体会是，数据清洗的重点从来不是“SQL 技巧有多花哨”，而是你对业务数据的理解有多深、你是否愿意在清洗前做体检、清洗后做验收、清洗过程中保留回滚能力。整套流程走下来，Hive 数据清洗才算真正融入大数据分析链路，而不只是临时抱佛脚地在每次查询前手工拼一段去重条件。
