做大数据分析的人十有八九都遇到过这种场景数仓里同一张订单表日期字段同一天既有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 1INSERT 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但如果空字符串被隐式转成 0AVG(amount)的结果就会偏低如果默认值是 -1SUM直接就把总额拉没了。我常用的处理逻辑是按字段语义分三层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 WHENSELECT 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把相同分区的数据分发给同一个 reducerINSERT 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 PARTITIONALTER 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-ddTHH:mm:ss格式这属于源头质量问题单靠清洗手段每次都能处理但每次也都在消耗跑批时间和维护成本。直接在数据质量周报里反馈给对应负责人推动源头修复清洗任务的负担会越来越小。我个人的体会是数据清洗的重点从来不是“SQL 技巧有多花哨”而是你对业务数据的理解有多深、你是否愿意在清洗前做体检、清洗后做验收、清洗过程中保留回滚能力。整套流程走下来Hive 数据清洗才算真正融入大数据分析链路而不只是临时抱佛脚地在每次查询前手工拼一段去重条件。