说实话我在这个“数仓混大米”学习群里混了几天Day01讲数仓分层的时候我还觉得挺简单结果Day02一上来就是SQL表映射、字段映射、SQL语句之间的转换直接把我从“我会写SQL”打回“我只会跑SQL”的原型。这个主题乍一看像是文档翻译工作实际上是把一个业务库的表结构、字段语义、SQL逻辑完整搬运到数仓模型里的过程。你如果没搞清楚这三层转换后面写ETL、搭指标、做宽表每一步都会踩坑。这篇文章就把Day02的核心内容完整复盘一遍。我会从表映射怎么拆、字段映射怎么对、SQL方言怎么转、转了之后怎么排查慢SQL这四个方面展开中间穿插实际案例和容易翻车的细节。适合刚接触数仓开发、正在准备数据开发面试、或者被领导丢了一个“把业务库迁到数仓”任务的同学参考。1. 表映射先把“表”这层关系理清楚1.1 表映射到底在映射什么表映射不是简单地把源表A改名为目标表B而是要把一张表的完整“身份信息”搬到目标环境里。我在Day02课程里学到的第一件事就是表映射至少要看四个要素表名、主键、分区策略、同步策略全量还是增量。这四个要素缺一个后面写同步任务的时候就只能靠猜猜错了就是数据重复或者丢数。举个例子业务库MySQL里有一张用户订单表t_order到了数仓贴源层ODS要建成ods_t_order到了明细层DWD要变成dwd_order_detail再到汇总层DWS可能变成dws_user_order_sum。从t_order到dwd_order_detail表名变了主键从业务主键变成了“业务主键分区字段”分区策略新增了dt日期分区同步策略从增量同步变成了“每日全量快照加增量更新”。这就是一个完整的表映射过程不是改个名字那么轻松。还要注意同义不同名和同名不同义两种极端情况。我在实际项目里遇到过业务库有user_info和user_profile两张表里面的字段几乎一样但一个是给C端App用的一个是给运营后台用的数据口径完全不同。如果只看表名就想当然地合并后续所有指标都会算错。所以做表映射之前最好先跟业务方对一遍“这张表到底是什么、谁在用、主键是什么、数据怎么变”。1.2 分层架构下的表映射规则数仓分层是表映射的大前提。Day01学的那套ODS - DWD - DWS - ADS分层在表映射里体现得非常具体。ODS层直接对应源业务系统表名一般加前缀ods_结构上尽量跟源表保持一致分区按日期或小时通常做增量同步。DWD层做清洗、规范化、维度退化后形成明细表加前缀dwd_这时候表结构已经和源表不一样了很多字段被拆开、重命名、补全。DWS层按主题汇总粒度更粗前缀dws_或ads_一般按天/周/月分区字段里大量出现汇总值、累计值、去重值。表映射在不同层之间是逐层推导的。从ODS到DWD你要回答“这张明细表由哪几张ODS表关联而来”“关联键和关联类型是什么”“过滤条件是什么”。从DWD到DWS你要回答“统计维度是什么”“度量字段有哪些”“去重口径是什么”。这些问题在写SQL之前就应该用表格梳理清楚而不是打开编辑器直接写create table as select。1.3 表映射落地的三个坑第一个坑是表名大小写混乱。如果用Hive库表名统一小写如果用Oracle表名默认大写到了MySQLLinux下区分大小写。我在迁移过程中吃过一次亏源表OrderInfo在Hive里建表时写成了orderinfo跑同步任务时一直报“表不存在”最后排查了半小时发现是大小写问题。建议所有映射文档里统一用全小写加下划线。第二个坑是主键不唯一。很多业务表在MySQL里虽然有索引但不是真正的主键数据是支持重复的。到了数仓里如果直接拿这个字段去做关联、去重出来的数据量非常吓人。Day02课程里专门强调了每次做表映射都要验证“候选主键”的唯一性用count和count(distinct)对比一下两个数不一致就说明这个“主键”是假的需要换一个或者组合多个字段。第三个坑是分区策略设错导致全量扫描。明明是一张每天新增几十万条数据的业务表如果表映射时没写清楚增量字段ETL就只能每天全量同步几天之后ODS表的数据量直接爆炸。我建议在表映射表格里单独加一列“增量字段”写清楚是create_time、update_time还是id自增避免后续维护的人一脸懵。2. 字段映射字段名、类型、默认值一个都不能少2.1 字段命名映射的四种套路字段映射比表映射更细碎但也更体现经验。源系统里的字段命名风格千奇百怪有驼峰式createTime有全小写createtime还有带特殊字符的。到了数仓里最常见的是统一成小写加下划线create_time、user_id、order_amount。字段命名映射我总结有四种套路。第一种叫直接对应源字段名本身就很规范user_id到user_id不用改。第二种叫语义重命名name这种太宽泛的字段要根据业务含义改成user_name或product_name。第三种叫驼峰转下划线createTime改成create_time这种事情在从Java团队维护的业务库取数时特别常见。第四种叫拆分与合并比如源表里有一个location字段存的是“省市区”拼接字符串到DWD层要拆成province、city、district三个字段。字段映射还有一个容易忽略的地方默认值和枚举值。我遇到过一张源表的status字段里面存的值是1、2、3但没有任何文档说明1代表什么、2代表什么。映射文档里如果不把枚举含义写清楚下游写报表的人只能靠猜猜错了就是上线事故。所以字段映射表里最好留一列“枚举含义”或者“字段说明”把1、2、3都写明白。2.2 类型映射与隐式转换的坑数据类型映射是字段映射里的重灾区。我自己就踩过decimal精度被截断的坑也见过同事因为datetime和string比较导致的数据漏数。这里列一张常见数据库类型到数仓Hive类型的映射表大家可以直接参考源数据库类型Hive类型注意事项varchar(n)string长度约束丢失下游使用注意截断intint/bigint看长度超过10位用bigintdecimal(10,2)decimal(10,2)精度必须保留否则金额误差datetimetimestamp注意时区问题建议统一UTC或东八区datedate可用string替代但查询效率打折tinyint(1)boolean/int布尔类型在数仓里一般用int(0/1)textstring大字段单独管理避免频繁扫描类型映射里最常见的错误是隐式转换。比如用户ID在MySQL里是varchar(20)到了Hive里建表时建成了bigintETL同步的时候Hive会自动把字符串转成数字如果源表里有“A001”这种非纯数字ID同步任务要么报错要么静默置NULL。这种问题到了数据质量校验环节才会暴露等到查原因的时候已经浪费了大半天。所以我现在做字段映射时都会额外加一列“转换SQL”明确写清楚是用cast(x as type)还是concat、substr这类函数来转换绝不依赖隐式转换。你永远想象不到源系统里能存出什么奇葩数据显式转换至少报错报得明明白白。2.3 字段映射文档长什么样很多人觉得字段映射文档就是个Excel列个源字段和目标字段就完事了。但Day02课程里给了一个更实用的模板我后来在项目里沿用确实能省很多沟通成本。模板大概是这样的序号源表.源字段目标表.目标字段字段类型(源-目标)转换逻辑字段说明/枚举值1t_order.order_iddwd_order_detail.order_idvarchar(32)-string直接映射订单唯一编号2t_order.user_iddwd_order_detail.user_idint-bigintcast(user_id as bigint)用户ID3t_order.pay_statusdwd_order_detail.pay_statustinyint-intif(pay_status2,1,0)1已支付0未支付4t_order.create_timedwd_order_detail.order_create_timedatetime-timestampcast(create_time as timestamp)下单时间按东八区处理这张表看起来简单但真正写起来很耗时因为要逐字段跟业务方确认含义。不过这个时间花得非常值。我见过太多项目代码写了一堆最后因为没有字段映射文档新来的同事根本不敢动那块SQL而有了这份文档下游做指标开发的人自己就能看懂逻辑不需要每次跑来问“这个字段是啥意思”。3. SQL语句转换方言转换与逻辑迁移3.1 从MySQL到Hive常用函数对比SQL语句之间的转换最常见的就是从MySQL搬到Hive或者从Oracle搬到Hive。不同数据库的SQL方言差异很大直接复制粘贴跑不通是常态。以下是我在实操中总结的一些高频函数对照基本覆盖了日常开发的大部分场景功能MySQL写法Hive写法空值替换ifnull(a, 0)nvl(a, 0)或coalesce(a, 0)条件判断if(a1, 大, 小)写法一致多条件分支case when ... end写法一致字符串拼接concat(a, b)concat(a, b)多参数时用concat_ws日期格式化date_format(now(), %Y-%m-%d)date_format(current_date, yyyy-MM-dd)日期加减date_add(date, interval 1 day)date_add(date, 1)取子串substr(a, 1, 3)写法一致去重select distinct a写法一致但更推荐row_number()行转列group_concat(a)concat_ws(,, collect_list(a))获取当前时间now()from_unixtime(unix_timestamp())正则匹配regexprlike这里特别提醒一下日期格式化的坑MySQL里%Y-%m-%d %H:%i:%s到了Hive里要改成yyyy-MM-dd HH:mm:ss年的大小写含义不一样%y是两位年份yyyy是四位年份写错直接返回NULL。我见过不止一个人在这里栽跟头跑出来的分区全是NULL导致数据落到默认分区。3.2 一条真实SQL的完整转换过程只看函数对照表还不够最好看一条完整的SQL怎么从MySQL转换成Hive。我拿一条比较典型的业务查询举例这条SQL是从订单明细里统计每个用户每天的支付金额和支付单量-- 原始MySQL写法 SELECT u.user_id, date_format(o.pay_time, %Y-%m-%d) AS pay_date, count(DISTINCT o.order_id) AS pay_cnt, sum(ifnull(o.pay_amount, 0)) AS pay_amount FROM t_user u LEFT JOIN t_order o ON u.user_id o.user_id WHERE o.pay_status 2 AND o.pay_time date_sub(now(), interval 30 day) GROUP BY u.user_id, date_format(o.pay_time, %Y-%m-%d);这条SQL在Hive里直接跑至少有三个地方报错或者逻辑不对。第一date_format的格式串要变成yyyy-MM-dd第二date_sub(now(), interval 30 day)这种写法Hive不认要改成date_add(current_date, -30)或者date_sub(current_date, 30)第三也是最重要的Hive不鼓励在JOIN之后再用WHERE过滤左表的驱动条件这种写法在MySQL里可能没问题在Hive里容易触发全表扫描。转换之后的Hive SQL我写成这样-- 转换后的Hive写法 SELECT u.user_id, date_format(o.pay_time, yyyy-MM-dd) AS pay_date, count(DISTINCT o.order_id) AS pay_cnt, sum(nvl(o.pay_amount, 0)) AS pay_amount FROM dwd_user_info u LEFT JOIN dwd_order_detail o ON u.user_id o.user_id AND o.pay_time date_sub(current_date, 30) AND o.pay_status 2 GROUP BY u.user_id, date_format(o.pay_time, yyyy-MM-dd);注意我把pay_status 2和pay_time的过滤条件从WHERE挪到了JOIN的ON里面因为Hive对先过滤再关联的执行顺序更友好尤其是左表很大、右表需要裁剪的时候这样能显著减少shuffle的数据量。虽然SQL语义上略有差别LEFT JOIN的右表过滤放在ON里不会把左表数据过滤掉但这正是数仓转换时要重点思考的地方同样是“LEFT JOIN”过滤条件放哪里结果完全不同。3.3 子查询、Join与去重的转换细节SQL语句转换最难的不是函数而是逻辑结构的重写。比如MySQL里很多人喜欢写IN子查询数据量小的时候没有问题但到了Hive里子查询会被改写成Join如果子查询里有重复数据会导致结果集膨胀。我在Day02课程里学到一个典型的重写案例。原来MySQL里统计“在最近30天有支付行为但没有下首单的用户”写法可能是SELECT user_id FROM t_user WHERE user_id NOT IN ( SELECT user_id FROM t_order WHERE pay_time date_sub(now(), interval 30 day) );这种SQL在MySQL里跑小数据量没问题但到了Hive里酸爽就来了。第一NOT IN遇到子查询结果里有NULL时整条查询会变成空结果这个坑在MySQL和Hive里都存在只是Hive更敏感。第二NOT IN在Hive里被改写成左外连接后过滤NULL性能往往非常差。正确的写法是用LEFT JOIN ... IS NULL来做-- 转换后的Hive写法 SELECT u.user_id FROM dwd_user_info u LEFT JOIN ( SELECT DISTINCT user_id FROM dwd_order_detail WHERE pay_time date_sub(current_date, 30) ) o ON u.user_id o.user_id WHERE o.user_id IS NULL;这里有两个转换细节。一是子查询里加DISTINCT把重复用户去掉防止一对多导致数据膨胀。二是把NOT IN改写成LEFT JOIN加IS NULL判断这在Hive里是目前公认最稳、最不容易出错的反连接写法。我自己在后面做“未转化用户”“流失用户”这类指标时基本都沿用这个模板。去重逻辑在SQL转换里也要格外小心。MySQL里用的count(DISTINCT xxx)在Hive小数据量下可以直接保留但数据量大了之后建议改成两层结构先按去重字段group by一层再在外层做count。这个不算转换算优化但两者经常一起做。面试官问“SQL去重有哪些写法”的时候你如果能答出distinct、group by、row_number() over(partition by ... order by ...)三种并且说清楚各自适用场景这一题基本就稳了。4. 慢SQL与执行计划转换完之后必须做的检查4.1 SQL转换后的性能隐患很多同学做完了SQL语句转换看到能跑出结果就以为大功告成其实这是最容易翻车的地方。同一个业务逻辑在MySQL里的执行计划和在Hive里的执行计划可能天差地别。我在实践中总结了三个高频性能隐患第一个是函数包裹分区字段导致分区裁剪失效。比如一张订单表按dt分区但你写的是date_format(pay_time, yyyy-MM-dd) 2024-01-01如果pay_time不是分区字段那么这条SQL为了算出结果必须扫描所有分区。这就像你去图书馆找一本书管理员问你“书名是什么”你却回答“封面的颜色是蓝色”管理员只能把全馆的书都翻一遍才知道有哪些是蓝色封面。第二个是大表关联时没有过滤条件。两个几亿行的表直接JOIN在MySQL里可能跑几分钟出结果在Hive里可能导致几十GB的shuffle数据。转换SQL时一定要学会“先缩小再关联”能过滤的都提前过滤能用子查询先聚合的就先聚合。第三个是小表没有用MapJoin。Hive默认优化开关下小表小于25MB可以自动转为MapJoin但如果你写的SQL把“小表”的关键过滤条件写歪了优化器可能判断不出来依然走Reduce Join。正确做法是在关联前先看执行计划确认小表是否走MapJoin如果没走就显式加/* MAPJOIN(小表别名) */或者调大hive.auto.convert.join相关参数。4.2 慢SQL排查的基本思路我排查一条慢SQL一般按下面这套顺序来基本不会乱先用EXPLAIN看执行计划确认Hive到底走了什么步骤是Map Only还是MapReduce有没有Reduce阶段产生了巨大的数据倾斜。看扫描的分区数量。如果一条SQL扫描了全部分区但实际只需要最近7天优先改过滤条件把分区裁剪做对。看STAGE之间的数据量哪个STAGE的Number of Rows异常大就盯着哪个STAGE去优化。分析Join的顺序是不是把大结果集的表放在了左侧导致Reduce端数据集中到一个节点。分享一个我刚处理过的案例。有一张累计用户流水表dwd_user_flow按dt分区我写了一条统计“最近30天每个用户的活跃天数”的SQL结果跑了40分钟不出结果。用EXPLAIN一看问题出在我对flow_time字段用了函数date_format和分区字段dt做了隐式关联判断导致分区裁剪完全没生效。后来我把过滤条件改成直接写在dt上预先算好起始分区和结束分区改成dt 2024-01-01 and dt 2024-01-30同时把date_format从WHERE里去掉放在SELECT里做格式化这条SQL直接从40分钟跑到了3分钟。这个案例给我的启发是SQL转换不光是语法兼容更要关注执行计划层面的兼容。同一个逻辑在MySQL里怎么写都能跑在Hive里写错位置就是灾难。转换完之后一定要花时间看执行计划不要相信“能跑出结果就是对的”。5. 常见问题与排查技巧实录5.1 字段类型对不上导致的数据倾斜我遇到过一个非常隐蔽的问题两张表关联时一张表user_id是bigint另一张表user_id是stringHive在JOIN时会做隐式类型转换。表面上能跑但实际执行时会发生大量无谓的序列化和反序列化。最气人的是如果某张表的user_id字段有几个极端的脏数据大量NULL或者一个默认值“0”这些脏数据在reduce阶段全部跑到同一个节点上去处理直接导致数据倾斜。排查技巧很简单先看EXPLAIN里最后几个Stage的数据分布如果哪个KEY的Record Count特别大把这个KEY值拿出来查一下原始数据基本都是脏数据或者类型不匹配导致的空值。解决方式有两个要么统一类型从源头解决要么先用nvl(user_id, rand())打散空值让NULL随机分布减少单节点压力。5.2 空值处理在转换中被忽略SQL转换时NULL的处理是最容易被忽略但影响最大的问题。MySQL里count(字段)不会统计NULLHive里也一样但很多人转换时会把注意整行数据里某些字段NULL很常见如果不提前想清楚出来的报表数据就是错的。我见过一个统计案例源单表order_amount存在大量NULL业务方认为NULL代表“未支付”金额按0处理。原始MySQL查询使用了ifnull(order_amount, 0)转换到Hive时同事直接写成了sum(order_amount)结果金额少了一大截数据比对发现差异追查半天才发现是空值没处理。现在我在编写SQL转换时每次sum、avg、count之前都会先问一句这个字段的NULL是应该跳过、置0还是报错不同的业务含义对应完全不同的写法。5.3 面试里常考的转换题刷SQL面试题的时候题型其实和这一天的内容高度重合。我整理了几个高频问题你们可以自测一下ifnull(a,0)和nvl(a,0)和coalesce(a,0)有什么区别row_number() over(partition by user_id order by dt desc)和distinct去重的区别一条MySQL的group_concat怎么转Hivecollect_list和collect_set有什么区别一个NOT IN查询在大数据量下为什么慢怎么重写成LEFT JOIN IS NULLvarchar(32)转成string后会导致哪些问题要不要保留长度限制date_format日期格式串%Y-%m-%d和yyyy-MM-dd混用会怎样面试官问这些问题其实不是真的在考你会不会写函数而是在考你有没有真正理解“不同SQL引擎的底层执行逻辑差异”。MySQL是单机数据库Hive是分布式批处理NOT IN、JOIN、DISTINCT、子查询这四个东西在两套引擎里的执行策略完全不同。能把这个差异讲明白SQL转换的题基本就过关了。5.4 转换前必做的数据探查工作在做任何SQL转换之前我强烈建议大家先做一轮数据探查。不要拿到源表结构就直接开始写映射文档和SQL一定要先跑几条探查SQL确认数据的真实情况。我常用的探查包括-- 探查关键字段的去重值和重复率 SELECT count(1) AS total_cnt, count(DISTINCT user_id) AS user_cnt, count(1) - count(DISTINCT user_id) AS dup_cnt FROM ods_t_order; -- 探查空值和脏数据 SELECT sum(if(user_id IS NULL, 1, 0)) AS null_user_cnt, sum(if(order_amount 0 OR order_amount IS NULL, 1, 0)) AS bad_amount_cnt FROM ods_t_order; -- 探查日期字段的格式情况 SELECT dt, substr(pay_time, 1, 7) AS month, count(1) FROM ods_t_order GROUP BY dt, substr(pay_time, 1, 7) ORDER BY dt DESC LIMIT 20;这些探查看上去很基础但真的能救命。我做过一个项目源表主键看着是order_id探查之后发现同一个order_id对应多行原因是业务系统允许多次修改。如果没有提前探查直接拿order_id做主键做同步数据就会丢。把这些探查SQL沉淀成一套模板每次接到新表都跑一遍效率和稳定性能提高不少。6. 写在最后的实操建议做了这么多数仓转换的活我自己最大的体会是表映射、字段映射、SQL语句转换这三件事本质上是在跟业务的“不确定性”作斗争。你以为你在转换SQL其实你是在转换业务逻辑你以为你在做字段映射其实你是在把不同系统对同一个业务概念的不同理解对齐。所以最后给大家几个非常朴素的建议。第一接任何任务先花20%的时间做数据探查和文档梳理这会帮你省后面80%的返工时间。第二不要过分相信自己的记忆所有映射关系老老实实落到表格里哪怕只是一张简单的Excel也能救命。第三SQL转换完了一定要记得做数据对比验证用“源表探测SQL”和“数仓结果SQL”分别统计总量和关键指标两个对不上就先别急着上线。这套Day02的内容看起来有点枯燥但它贯穿了后面所有数仓开发工作。把这套东西吃透了后面写ETL、做指标、解决慢SQL的时候你会回来感谢这一天的。下次再有人让你“把这个SQL迁一下”别傻傻地复制粘贴改个函数名先用这张文章的思路把表、字段、逻辑、执行计划都过一遍你再动手。