Hive SQL连接查询全解析：join与union的用法、坑与优化

做数据开发这行写 Hive SQL 基本每天都要跟表连接打交道。不管是数仓分层加工、用户标签计算还是临时取数最绕不开的语法就是inner join、left join、full join以及union all、union。很多人刚开始学的时候觉得不就是几个关键字嘛真到了线上跑任务发现结果多了一倍、少了数据、任务还一直卡住基本都是因为这些连接的细节没吃透。这篇东西不聊理论摆谱就按我实际写 HiveQL 的常用套路把连接查询的语法、场景、坑和优化一次讲清楚适合刚入门的朋友也适合写过不少但偶尔被连接搞懵的同学。1. 连接查询的整体思路先想清楚要“拼”还是“叠”1.1 连接join和集合union的本质区别我习惯把 Hive 里的数据操作分成两类一类是“横向拼字段”一类是“纵向加行”。连接查询join是横向拼字段。比如订单表里只有user_id没有用户姓名那我就用user_id去用户表里把姓名、年龄、城市这些字段带过来。拼完之后列变多了行数一般是根据关联关系变化的。集合操作union all / union是纵向加行。比如一张表存了昨天的订单另一张表存了今天的订单两个表的结构一模一样我想把它们上下合并成一个全量订单表。合并之后行变多了列数不变。这个本质区别如果没搞清楚后面写出来的 SQL 会非常别扭。有人把union all当成“另一种 join”来用结果列怎么都对不上其实一开始方向就错了。1.2 关联键的选择决定结果正确性做连接查询时关联键join key就是表与表之间的“匹配依据”。比如a.user_id b.uid这里的user_id和uid就是关联键。选择关联键时我通常先问自己三个问题这个键能不能唯一标识一行如果两边都不唯一结果就会膨胀。这个键两边类型是否一致string和bigint做比较有时隐式转换会杀掉索引或导致结果异常。这个键是否有空值空值在 join 里通常匹配不上会导致丢数据。很多人写 SQL 从不看数据分布上来就join结果跑出来行数对不上。其实在写之前跑几条group by看看关联键的重复情况可以省出大量排查时间。1.3 Hive 连接查询与传统关系型数据库的差异如果你之前玩的是 MySQL、PostgreSQL 这类数据库刚写 Hive SQL 时会觉得语法很像但底层逻辑差别很大。Hive 本质上是一个数据仓库工具它把 SQL 翻译成分布式计算任务数据是按文件分块存在 HDFS 上的。因此join 的时候不是数据库那种索引驱动的小规模匹配而是会触发 Shuffle数据会按照关联键重新分区发送到不同节点上再匹配。这也是为什么连接查询在 Hive 里更容易遇到数据倾斜——某个关联键的数据量特别大所有数据都往一个节点上跑这个节点就会变成瓶颈。另外Hive 对不等值连接比如、的支持不如传统数据库那么优雅能不用尽量不用。实际开发中我用得最多的是等值连接也就是on a.id b.id。明白了这些差异再来逐个看各种连接方式思路会顺很多。2. inner join只留两边都有的数据2.1 基本语法和运行逻辑inner join翻译成“内连接”或者“等值连接”逻辑很简单取两个表的交集只保留关联键在两个表中都能匹配上的行。select a.order_id, a.user_id, b.user_name, b.city from temp.orders a inner join temp.users b on a.user_id b.user_id;这段 SQL 的结果是订单表中所有能在用户表里找到对应用户的订单记录才会出现在结果里。用户表里没有那些用户订单数据不会显示用户表里多出来的用户因为没有任何订单也不会显示。inner关键字可写可不写直接写join默认就是 inner join。但我建议在正式代码里写上inner join明确表达意图别人看你的代码时不用猜。2.2 典型场景与示例我常用inner join的场景是“过滤加补字段一起做”。比如统计有效订单的用户分布前提是订单必须能匹配到用户。如果匹配不到说明用户数据可能有问题或者订单是测试单这类数据你不想留在结果里。这时候inner join是最合适的因为在连接过程中就把没匹配上的行过滤掉了。再比如数仓里常见的“拉宽表”事实表关联维度表。事实表是订单流水维度表是商品信息我只需要保留商品维度存在的数据因为商品信息都不存在的订单后续统计分析没有意义。此时用 inner join 即可。2.3 使用 inner join 时最容易踩的坑关联键重复导致行数爆炸。这是最常见的坑。假设订单表里同一个user_id有 3 条订单用户表里同一个user_id有 2 条记录那么 join 之后会产生 3×26 条结果。你说离谱不离谱所以在 join 之前最好先确认关联键在两边是否唯一或者想清楚重复之后结果是否还能接受。关联键为 null。Hive 中两个 null 做等值比较结果是“未知”不是相等。所以订单表里user_id为 null 的记录永远匹配不上用户表里的 null。这会导致“明明两张表都有 null但 join 之后就是没有”。如果你确实想用 null 做关联需要特殊处理比如用nvl(user_id, 未知)把 null 替换成一个普通字符串。先过滤再接还是先接再过滤。理论上可以先用子查询把两边都过滤干净再做 join能减少 Shuffle 的数据量。实际我一般在内层 where 过滤再 join效率会好不少。3. left join左表为尊右表只负责补字段3.1 基本语法与数据保留规则left join应该是日常用得最多的连接方式。它的核心规则是左表的所有行都要出现在结果里右表只负责补充字段。如果左表的行在右表中找不到匹配右表字段就用 null 填充。select a.order_id, a.user_id, b.user_name, b.city from temp.orders a left join temp.users b on a.user_id b.user_id;这条 SQL 的结果是订单表里所有订单都保留不管有没有匹配到用户。用户表里有人没订单没关系结果里看不到他们。这就是 left join 和 inner join 最大的区别left join 不会因为右表缺数据而丢左表行。我写取数 SQL 时特别喜欢 left join因为主表是哪个哪些数据要全保留一目了然。如果业务上“订单必须全部列出来用户信息能补就补”那必然选 left join。3.2 on 条件里过滤右表和在 where 里过滤的差异这个点太关键了很多线上事故就是这么出的。看这两段 SQL-- 写法一在 on 里过滤右表 select a.order_id, b.user_name from temp.orders a left join temp.users b on a.user_id b.user_id and b.city 上海; -- 写法二在 where 里过滤右表 select a.order_id, b.user_name from temp.orders a left join temp.users b on a.user_id b.user_id where b.city 上海;写法一的意思是我拿订单关联用户但关联的时候只关联“上海”的用户。不在上海的用户相当于匹配不到b.user_name会是 null但订单行仍然保留。写法二的意思是先做 left join得到所有订单和其用户信息然后再用 where 过滤只保留用户城市为“上海”的行。这样一来非上海用户的订单会被整个过滤掉左表不再是“全保留”了。菜鸟经常在这上面栽跟头。如果你想让左表全部保留右表的过滤条件必须放在 on 里如果你确实只想要满足条件的明细那放在 where 里也没问题但你得意识到它其实是先 join 后过滤效果已经接近 inner join。3.3 实战案例如何用 left join 做“无则补零”统计每天每个用户的订单金额但要求没有订单的用户也要出现金额置为 0。这种需求很常见比如用户活跃度报表。select a.user_id, nvl(b.total_amount, 0) as total_amount from temp.users a left join ( select user_id, sum(amount) as total_amount from temp.orders where dt 2025-01-01 group by user_id ) b on a.user_id b.user_id;这里用户表为主表所有用户都保留。有订单的用户能关联到聚合后的金额没订单的用户total_amount是 null再用nvl转成 0。这种“以一侧全量为主另一侧做统计后补充”的模式在数仓里非常普遍。需要注意右表先做了group by再 join能避免一对多导致行数爆炸。经验之谈能用子查询预先聚合的不要直接 join 明细表。3.4 左表重复和右表重复的连锁反应left join 同样会遇到重复问题。左表重复结果会跟着重复右表重复左表每一行会和右表多个匹配行分别组合结果行数膨胀。举个例子订单表左关联用户表订单表其实每个订单只对应一个用户但用户表因为某种原因有两条相同user_id的记录那么每个订单就会出两条结果。这比 inner join 的重复更隐蔽因为左表数据一条不少只是莫名其妙变多了。所以你在跑完 left join 后建议用count(*)和左表行数对比一下。如果结果行数比左表多很多八成是右表关联键重复。排查方法很简单对右表关联键做group by having count(*) 1看看重复项有多少。4. full join左右都想留谁也不丢4.1 语法与实现原理full join在 Hive 里也写成full outer join它的规则是左表和右表的行都完整保留。左表在右表没有匹配右表字段为 null右表在左表没有匹配左表字段为 null。两边都对不上结果里会分别出现两行一边是“左表有右表无”另一边是“右表有左表无”。select a.user_id, a.order_cnt as left_cnt, b.user_id, b.browse_cnt as right_cnt from temp.orders a full join temp.user_browse b on a.user_id b.user_id;实现上full join 通常会把两端数据都做一次分发然后做连接最后把两边未匹配的行补全。所以在分布式环境里full join 的 Shuffle 数据量一般比 left join 大对资源消耗也更高。能用 left join 解决问题时我不会优先选 full join。4.2 适用场景两张表互为补集我习惯用 full join 做“数据对账”和“补数”。比如订单主题表和用户行为表都是从不同源头加工出来的可能存在部分用户只出现在其中一张表里。这时候想看看两张表各自哪些用户缺失直接 full join 一下再根据两边字段是否为 null 判断归属即可。select coalesce(a.user_id, b.user_id) as user_id, case when a.user_id is not null and b.user_id is null then only_in_orders when a.user_id is null and b.user_id is not null then only_in_browse else both end as tag from temp.orders a full join temp.user_browse b on a.user_id b.user_id;运行结果会清楚地把数据分成“只在订单表”“只在行为表”“两边都有”三类。这种写法在临时排查数据质量问题时非常管用。另外做“补数”任务时会用 full join两套并行计算的数据各自有部分缺失把两边的数据都合起来缺失的字段互相补上。比如 A 表有 1 号、2 号的用户数据B 表有 2 号、3 号的用户数据full join 后就能得到 1、2、3 号的全量数据。4.3 和 left join union all 的方案对比full join 很有用但它不是不可替代的。当你想保留两边全量但又想分别控制两边的过滤逻辑时也可以拆成left join union all来做效果等价。-- left join 部分保留左表全部 select a.user_id, a.field_a, b.field_b from temp.tb_a a left join temp.tb_b b on a.user_id b.user_id union all -- 右表有但左表没有的部分 select b.user_id, cast(null as string) as field_a, b.field_b from temp.tb_b b left join temp.tb_a a on b.user_id a.user_id where a.user_id is null;这种做法比较繁琐但好处是可以对每一半的过滤条件单独控制也更容易在不同引擎上优化。如果 Hive 的 full join 出现资源问题或者你的 Hive 版本对 full join 支持不够稳定可以退而求其次用这个方案。4.4 使用 full join 的注意事项full join 结果里可能会有“行为主键为 null”的情况。最好用coalesce(a.id, b.id)重新生成一个统一主键方便下游使用。两边关联键都重复时full join 的膨胀效果是乘法级的写之前一定要测一下重复度。full join 和 where 配合时也要警惕你在 where 里加了对单侧字段的过滤很可能把另一侧补出来的行删掉导致 full join 失效。比如你写where a.field 0那右表独有的行因为 a 字段是 null 就被过滤了结果不再“全量”。Hive 的 full join 在部分执行引擎下可能出现分发不均匀的情况我一般会在跑大表前先小范围验证一把再跑全量。5. union 与 union all纵向拼接的正确姿势5.1 集合操作和连接操作的本质区别再强调一遍union 系列是“上下拼行”不是“左右拼列”。你需要合并的几张表列数、列顺序和列类型要一致否则跑起来不是报错就是数据错位。比如select col1, col2 from temp.tb_a union all select col1, col2 from temp.tb_b;这里只是把 tb_a 和 tb_b 的行依次放到一起。如果第一个 select 查了两列第二个 select 查了三列Hive 会直接报错。这样设计的原因不难理解纵向合并相当于把一个一个文件里的结构相同的记录追加进同一个结果集。列不一样就无法定义每一列到底该放什么。5.2 两者的区别与性能差异union all和union的区别只有一个去重。union all把两个结果集直接拼接不去重有多少行就返回多少行。union拼接之后对全部结果做一次去重逻辑上相当于union distinct。性能差异也很明显。union all只是文件的简单追加基本不需要额外的全局去重操作union则要把所有列都当作去重键做一次全局 Shuffle。所以绝大多数场景能用union all就不要用union。你要是没有去重需求却写了 union等于白白增加了一轮分布式运行时间。还有一种情况你其实想去重但关联键里包含很多字段、数据量又大用 union 会很慢。我一般会先union all合并再在合并结果上做group by或者row_number()去重虽然多了一步但可控性更好。5.3 实战多分区表汇总与业务表合并最典型的场景是“多天分区合并”。比如数仓里误删了某个分区需要从备份表把多天的数据恢复到一张表里insert overwrite table temp.orders_full partition (dt) select order_id, user_id, dt from temp.orders_20241201 where dt 20241201 union all select order_id, user_id, dt from temp.orders_20241202 where dt 20241202 union all select order_id, user_id, dt from temp.orders_20241203 where dt 20241203;这里必须用union all因为是不同分区的数据理论上不会有重复主键没必要去重用 union 反而会白白跑一次全局去重浪费时间。另一个场景是“多业务模块统一出口”。比如好几个业务线要汇总成一张用户标签大表每张表的用户可能重复但标签口径不同。如果你只想要用户集合可以考虑 union 去重如果后面还要知道每个用户来自哪条业务线就用 union all并且人工加一个来源字段。5.4 常见坑列顺序、类型转换、排序失效写 union 系列时我栽过几个跟头这里列一下列顺序不一致。第一段 select 的 a 列是 user_id第二段 select 的 a 列变成了 user_name语法不报错但结果完全错位。解决办法是每一段 select 都显式列出字段最好保持完全一致的顺序。数据类型不一致。一个表里该字段是bigint另一个表里是stringHive 有时会隐式转换有时会报错。稳妥做法是在 select 里手动cast。对 union 结果做 order by。很多人写成select col1 from temp.tb_a union all select col1 from temp.tb_b order by col1;这样排序是会对最终结果生效的。但要注意order by必须放在最后一个 select 之后不能放在每段 select 里否则每段分别排序会被 union 打乱。两边字段里有临时别名。比如第一段select order_id as id第二段select order_idHive 看字段名不同可能会把结果列名改成第一个 select 的别名。建议每一段都保持相同别名避免下游取数时列名对不上。union 不能保证最终数据顺序。哪怕每段都 order byunion all 之后顺序也可能变化要用外部查询再排序。6. 连接查询的调优与故障排查6.1 数据倾斜关联键集中导致的任务跑不动写 join 最头疼的就是跑着跑着卡住某个 Reduce 任务要跑几个小时其他 Reduce 早就结束了。这大概率是数据倾斜。数据倾斜的典型场景是关联键里有“热点值”。比如订单表里大量订单的user_id是空字符串或者一个特殊的测试值用户表里根本没有这些用户但在 join 时Hive 还是会把这些空值分到同一个节点去匹配导致这个节点负载巨大。我一般这么做先查关联键分布select user_id, count(*) from temp.orders group by user_id order by count(*) desc limit 20。看看是不是有几个键特别多。如果热点值是 null 或空串干脆在 join 前过滤掉或者把 null 替换成随机值分摊到不同节点。如果是正常业务热点比如“超级大用户”的数据量特别大可以把它拆出来单独处理再用union all合回结果。6.2 MapJoin小表与大表的连接优化Hive 里有一个很实用的优化MapJoin。当 join 的右边是一张小表时可以让它在 Map 阶段直接加载进内存在 map 端完成匹配这样就不需要 Shuffle速度会快很多。一般 Hive 会自动判断是否启用 MapJoin但如果小表比较大或某个参数设置不对自动优化可能没触发。我遇到小表关联大表时会显式加 hintselect /* mapjoin(b) */ a.col1, b.col2 from temp.big_table a left join temp.small_table b on a.id b.id;注意MapJoin 要求小表能放进内存我通常控制在几十 MB 到几百 MB 级别。如果小表有几 GB硬塞会导致 OOM。这种时候还是老老实实跑普通 join或者把小表先过滤聚合得足够小再说。6.3 null 关联键导致的丢数据前面提到 Hive 等值比较时 null 不相等。如果你用 null 关联结果两边都出不来。实际业务里user_id有可能是 null或者被清洗成了空字符串。应对方法看需求如果 null 用户不需要参与统计就在 join 前过滤掉where user_id is not null。如果 null 用户需要保留在左表left join 时右表匹配不上就行左表自动保留右表字段为 null。如果两张表的 null 你希望彼此“视作相等”需要手动转换比如on nvl(a.user_id, -1) nvl(b.user_id, -1)。但要注意转换之后 null 可能集中到‘-1’上容易造成数据倾斜需要配合随机打散技巧。6.4 常见问题速查表现象可能原因处理建议join 后行数变多关联键在某一侧重复先 group by 验证重复度或先聚合再 joinleft join 后左表行数少于原表where 过滤了右表字段把过滤条件移到 on 中full join 后右表独有数据丢失where 里写了左表过滤条件检查过滤条件避免干掉 null 行union 结果顺序不稳定对 union 结果排序失效union 外层包 select 再 order by任务卡在某个 reduce关联键热点值倾斜过滤或打散 null、空值、热点值两侧 null 关联不上等值比较中 null 不相等用 nvl 或 coalesce 替换后关联union 报列数不一致各段 select 列数不同每段统一列数和列顺序MapJoin 没生效小表过大或自动优化未开启显式 mapjoin hint或先压缩小表这张表我写 SQL 时会时常回看排查一次胜读文档十遍。最后再分享一点个人体会连接查询用多了之后你会发现真正难的不是语法而是对数据形态的判断。我每次写 join 前会先问主表是谁关联键有没有重复右表会不会带过来多行空值怎么处理这几个问题想清楚了SQL 一般不会有太大问题。很多新手上来就喜欢用union distinct或者 full join觉得“全要”更安全。但实际运行下来union all和 left join 往往更快、更可控。线上任务稳定第一能少一次大 Shuffle 就少一次。如果你正在做数仓报表或者临时取数建议把本文里的例子都自己跑一遍不要只看不练。特别是on和where的过滤位置差异自己造点数据跑一跑印象绝对比看十篇文章都深。数据开发这条路靠的就是一个个坑踩出来然后把坑变成自己的经验。