企业官网建设流程全解析

说实话，我在这个“数仓混大米”学习群里混了几天，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，表名默认大写；到了MySQL，Linux下区分大小写。我在迁移过程中吃过一次亏：源表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类型的映射表，大家可以直接参考：

类型映射里最常见的错误是隐式转换。比如用户ID在MySQL里是varchar(20)，到了Hive里建表时建成了bigint，ETL同步的时候Hive会自动把字符串转成数字，如果源表里有“A001”这种非纯数字ID，同步任务要么报错，要么静默置NULL。这种问题到了数据质量校验环节才会暴露，等到查原因的时候已经浪费了大半天。

所以我现在做字段映射时，都会额外加一列“转换SQL”，明确写清楚是用cast(x as type)还是concat、substr这类函数来转换，绝不依赖隐式转换。你永远想象不到源系统里能存出什么奇葩数据，显式转换至少报错报得明明白白。

2.3 字段映射文档长什么样

很多人觉得字段映射文档就是个Excel，列个源字段和目标字段就完事了。但Day02课程里给了一个更实用的模板，我后来在项目里沿用，确实能省很多沟通成本。模板大概是这样的：

这张表看起来简单，但真正写起来很耗时，因为要逐字段跟业务方确认含义。不过这个时间花得非常值。我见过太多项目，代码写了一堆，最后因为没有字段映射文档，新来的同事根本不敢动那块SQL；而有了这份文档，下游做指标开发的人自己就能看懂逻辑，不需要每次跑来问“这个字段是啥意思”。

3. SQL语句转换：方言转换与逻辑迁移

3.1 从MySQL到Hive：常用函数对比

SQL语句之间的转换，最常见的就是从MySQL搬到Hive，或者从Oracle搬到Hive。不同数据库的SQL方言差异很大，直接复制粘贴跑不通是常态。以下是我在实操中总结的一些高频函数对照，基本覆盖了日常开发的大部分场景：

这里特别提醒一下日期格式化的坑：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，一般按下面这套顺序来，基本不会乱：

1. 先用EXPLAIN看执行计划，确认Hive到底走了什么步骤，是Map Only还是MapReduce，有没有Reduce阶段产生了巨大的数据倾斜。
2. 看扫描的分区数量。如果一条SQL扫描了全部分区，但实际只需要最近7天，优先改过滤条件，把分区裁剪做对。
3. 看STAGE之间的数据量，哪个STAGE的Number of Rows异常大，就盯着哪个STAGE去优化。
4. 分析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是string，Hive在JOIN时会做隐式类型转换。表面上能跑，但实际执行时会发生大量无谓的序列化和反序列化。最气人的是，如果某张表的user_id字段有几个极端的脏数据（大量NULL或者一个默认值“0”），这些脏数据在reduce阶段全部跑到同一个节点上去处理，直接导致数据倾斜。

排查技巧很简单：先看EXPLAIN里最后几个Stage的数据分布，如果哪个KEY的Record Count特别大，把这个KEY值拿出来查一下原始数据，基本都是脏数据或者类型不匹配导致的空值。解决方式有两个，要么统一类型，从源头解决；要么先用nvl(user_id, rand())打散空值，让NULL随机分布，减少单节点压力。

5.2 空值处理在转换中被忽略

SQL转换时，NULL的处理是最容易被忽略但影响最大的问题。MySQL里count(字段)不会统计NULL，Hive里也一样，但很多人转换时会把注意：整行数据里某些字段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怎么转Hive？collect_list和collect_set有什么区别？
• 一个NOT IN查询在大数据量下为什么慢？怎么重写成LEFT JOIN IS NULL？
• varchar(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迁一下”，别傻傻地复制粘贴改个函数名，先用这张文章的思路把表、字段、逻辑、执行计划都过一遍，你再动手。