连接查询你真的把每一行数据在干什么想清楚了吗昨天组里一个新人跑数订单量日报比前一天少了30万行排查了半天最后发现问题出在一个right join上。其实很多看似诡异的结果只要把join的执行逻辑在脑子里过一遍立刻就清楚了。这篇我把Hive SQL里最常用的inner join、left join、full join、union all、union放在一起讲明白每个都配好理解的例子和真实的坑适合正在学数仓、刚开始写Hive SQL的数据分析、数据开发同学也适合那些已经写了半年join但偶尔还会被结果吓一跳的人。Hive里的连接查询和MySQL、Presto里的joinSQL写法上长得差不多但一旦涉及数据量、空值、去重、数据膨胀这些场景区别就非常明显了。因为Hive底层跑的是分布式任务你的join逻辑稍微有一点含糊落到集群上可能就要多跑几个Stage甚至导致结果完全不对。下面我按我实际写数仓SQL的习惯一个一个拆开讲。1. 先把join的本质想清楚两张表是怎样“并”在一起的1.1 连接查询不是“合并表”而是“按关联键配对”很多初学者会把join理解为“把两张表拼在一起”这个说法不够精确。join真正做的事情是拿表A里的每一行去表B里找所有满足关联条件的行然后把它们拼成新的一行。如果找到了多行A的那一行就会重复出现多次这就是常说的行数膨胀。举个例子订单表里有两个订单都挂在用户ID为1001的用户名下。用户维表里只有一行是1001对应的用户。用用户ID做inner join最终结果就是两行因为订单表的两个订单分别和维表里那一行用户配对。但如果维表里因为数据问题出现了两条1001的记录最终结果就变成了四行这就是数据膨胀。Hive执行join时会把关联键的hash值相同的记录分到同一个Reducer上去做配对所以关联键的选择、空值率、热点分布直接影响整个任务的运行时间。这也是为什么我一直强调写join之前要先去确认关联键的唯一性而不是上来就一把梭。1.2 Hive连接查询和MySQL里的join有一个思维差异关系型数据库里小表驱动大表、索引命中、join顺序重排这些优化手段很常见但在Hive里join的底层是分布式shuffle优化的思路不太一样。Hive 0.11之后的版本有一个很重要的机制小表自动MapJoin。也就是当一个小表足够小默认阈值是hive.auto.convert.join.noconditionaltask.size一般是10MB或25MBHive会把小表直接加载到每个Map Task的内存里省掉shuffle阶段。这个机制让很多小表join大表的场景跑得飞快但也因为自动转换导致一些意想不到的行为。我自己平时写Hive join的时候会刻意遵循一个习惯先明确哪个表是左表、哪个表是右表然后在大脑里模拟一遍每一行会怎么配对。这个习惯帮我挡掉了至少一半的SQL逻辑错误。你试着也养成这个习惯一旦开始用“逐行配对”的视角看join下面讲的各种join类型就都不会搞混了。2. inner join和left join日常开发里出镜率最高的两位2.1 inner join只要两边都对得上的行inner join返回的是两个表中关联键同时匹配成功的行没匹配上的数据直接丢掉。在数仓场景里它的典型作用是做交集逻辑比如找“既下了单又注册了优惠券的用户”“有库存又有上架的商品”。-- 找出所有下单用户的详细信息 SELECT o.order_id, o.user_id, u.user_name, u.user_level FROM dwd_order_detail_fact o INNER JOIN dim_user_online u ON o.user_id u.user_id;重点说一个优化点inner join在Hive里如果右表是维表而且维表足够小强烈建议让这个维表当右表再开启mapjoin这样整个任务不需要走shuffle。如果维表大那只能接受shuffle但可以想办法把关联键的null过滤掉一部分减少shuffle的数据量。还有一个很容易踩的坑inner join写完如果结果少了很多行大概率不是SQL写错而是因为右表的关联键有null或者右表根本没有对应的维度数据。所以inner join之后可以顺手看一眼左表的总行数对比结果行数就能判断到底丢了多少。2.2 left join左表全保留右表能配上就配上left join返回左表的全部行右表有匹配就补上字段没有就用null填充。它是最常用的join类型在数仓里拼明细、补维度字段、拉宽表基本全是靠left join完成的。SELECT o.order_id, o.pay_amount, u.user_name, u.city FROM dwd_order_detail_fact o LEFT JOIN dim_user_online u ON o.user_id u.user_id;这里有一个非常重要、必须刻在脑子里的结论left join的结果行数 左表行数前提是右表关联键没有重复。写完left join第一个验证动作就是数行数如果结果行数比左表多那就是右表关联键重复了如果比左表少说明on条件里过滤掉了左表数据这个下面专门讲。2.3 left join最容易踩的坑on和where千万别混淆这是我在实际带人时反复强调的一个坑。left join中on条件里的右表过滤只是决定“右表能不能匹配上”而where条件里的右表过滤则直接过滤最终结果里的整行。-- 想查所有订单并且只看用户等级为1的用户的信息 SELECT o.order_id, u.user_name, u.user_level FROM dwd_order_detail_fact o LEFT JOIN dim_user_online u ON o.user_id u.user_id WHERE u.user_level 1;这段SQL看着像“只要等级为1的用户”但因为过滤条件写在where里最终结果只会保留右表匹配上了且等级为1的行。也就是说所有没有匹配到用户维表的订单行因为u.user_level是null不满足where条件被一起过滤掉了。这等价于inner join加过滤根本不是left join。正确写法应该是把过滤条件放进on里或者先过滤维表再joinSELECT o.order_id, u.user_name, u.user_level FROM dwd_order_detail_fact o LEFT JOIN ( -- 先过滤维表再关联 SELECT user_id, user_name, user_level FROM dim_user_online WHERE user_level 1 ) u ON o.user_id u.user_id;为什么先过滤再join更稳因为这样既保留了左表所有订单也只关联符合条件的用户语义清清楚楚。很多人写left join把过滤条件写到where里结果数少了一大截还找不到原因大多数都是这个知识点没吃透。强烈建议你下次写类似SQL的时候先在草稿纸上写下“我会保留哪些行”再落笔。2.4 为什么left join之后行数变多了关联键重复才是罪魁祸首写left join的时候如果右表关联键不唯一左表的某一行会被重复多次。最常见的场景是用日期维度表关联事实表。比如你有一个日期维表理论上一个日期只有一行但如果不小心导入重复了每个date_id出现两遍订单表里的每个订单就会变成两行。这种情况发生之后聚合函数的结果会全面翻倍比如sum(amount)、count(order_id)全错而且左看右看SQL都没问题只有当你去查右表group by关联键的条数时才会发现问题。所以我的习惯是凡是涉及join的SQL如果结果行数异常先跑一句SELECT 关联键, COUNT(1) FROM 右表 GROUP BY 关联键 HAVING COUNT(1) 1排查重复关联键。这句SQL虽然简单但几乎救过我无数次。3. full join两边都要保留的场景以及它和left joinunion all之间的关系3.1 full join到底返回什么full join也叫full outer join它会返回左表和右表的所有行。左表有右表没有的保留左表右表字段为null右表有左表没有的保留右表左表字段为null两边都有的正常配对。在数仓里full join最常见的场景是做数据对账和增量合并。比如你有两套来源不同的用户表一套是CRM系统导出的用户名单一套是订单系统沉淀的用户名单想找出“两边都有哪些用户、哪些用户只在其中一边”用full join非常直观。SELECT COALESCE(a.user_id, b.user_id) AS user_id, a.crm_user_name, b.order_user_name FROM dwd_user_crm a FULL JOIN dwd_user_order b ON a.user_id b.user_id;这段SQL执行完能很清楚地看到crm里和下过单的用户集合之间的差异。对完数之后再根据业务需要把结果落地成一张统一的用户宽表full join的价值就在这儿。3.2 Hive里full join的一个大坑null关联键的匹配行为full join在Hive里有一个特别反直觉的细节就是null关联键的匹配。如果你直接对两个表的null做等值关联也就是ON a.user_id b.user_idHive里null和null是不相等的所以在配对的阶段双方的null行不会互相配对而是各自保留成一行。听起来没什么问题但如果有人为了处理null在on条件里写了ON NVL(a.user_id, ) NVL(b.user_id, )那就完了。两边null会被当成相同的键进行匹配null数据行数多的时候会产生非常夸张的笛卡尔积直接把Reducer跑爆。我曾经见过一个full join任务因为这个写法跑了三个小时没出来最后发现就是NVL把null变成了空串。所以full join时如果你确实需要处理关联键的null正确的做法是先对源数据做过滤或赋值处理再join尽量别在on条件里用NVL。如果把null真的要算作一种有效关联键也一定要先确认双方null的量级确保不会造成爆炸性膨胀。3.3 full join和left joinunion all怎么选full join能做的功能其实也可以拆成left join和right join的union all来实现。比如A表全保留、B表全保留的并集可以拆成A left join B的结果加上B left join A但A为null的结果。但在Hive里直接写full join通常更清晰执行计划也更简洁。我的经验是当两表一对一的概率高、数据量级相当、需要做全量对比时直接用full join。而当逻辑本身就能拆成两个独立的left join、且每个结果后续有不同处理方式时再用union all组合。不要为了显得“高级”而拆也不要因为怕full join的shuffle大而完全不用它只要关联键设计合理full join完全可控。4. 别再搞混union all和union一个是拼行一个是拼行加去重4.1 union all是把两个查询结果竖着拼在一起union all做的事情很简单把两个查询的结果按行上下拼起来。它不会去重也不会排序就是把结果简单堆叠。在Hive SQL里union all要求两个查询的列数和字段类型保持一致。-- 合并两个月的订单明细 SELECT order_id, user_id, pay_amount, 202501 AS month_id FROM dwd_order_detail_202501 UNION ALL SELECT order_id, user_id, pay_amount, 202502 AS month_id FROM dwd_order_detail_202502;数仓里union all的使用频率远高于union因为大多数场景我们都想要“全量数据”而不是去重后的数据。比如上面的月份合并如果某个月的数据内部有重复的order_idunion all会把它们全部保留这往往是正确行为如果你用union重复的订单就被静默吞掉了在数据核对时反而不好复用。4.2 union会自动去重但代价是额外的shuffleunion和union all的唯一区别是union会对结果做distinct去重。这听起来很方便但背后是一个完整的shuffle和reduce过程。当数据量大时union的开销非常大而且它去重的逻辑是对整行全部字段做比较而不是单独对某个主键去重。另外Hive和很多其他SQL引擎一样union的子查询之间如果列名不一致老版本的Hive在返回结果的列名处理上有过坑。现在多数版本以第一个子查询的列名为准但为了保证可读性我建议每个子查询都显式写好列名别用SELECT *。如果你只想对某个字段比如order_id去重应该用GROUP BY或者ROW_NUMBER()而不是依赖union的特性否则会意外丢失字段不同的重复行。4.3 union all在数仓里的典型用法增量合并和全量快照最常见的一个场景就是拉链表新增记录和每日新增记录合并。假设我们有一张用户拉链表每天要把最新的状态和新注册用户追加进去用union all把今天的增量数据和昨日全量拼接再通过窗口函数处理历史有效时间区间这个动作在数仓里每天都在发生。INSERT OVERWRITE TABLE dim_user_scd2 SELECT user_id, user_name, user_level, start_date, end_date FROM ( SELECT user_id, user_name, user_level, 2025-03-01 AS start_date, 9999-12-31 AS end_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY user_level DESC) AS rn FROM ( -- 昨天的有效记录 SELECT user_id, user_name, user_level, start_date, end_date, 0 AS flag FROM dim_user_scd2 WHERE end_date 9999-12-31 UNION ALL -- 今天的新增/变化记录 SELECT user_id, user_name, user_level, 2025-03-01 AS start_date, 9999-12-31 AS end_date, 1 AS flag FROM ods_user_20250301 ) tmp1 ) tmp2 WHERE rn 1;union all在这里就是做数据堆叠的最后通过ROW_NUMBER来做真正的去重挑选这样比union对整个结果集做distinct高效得多而且逻辑更可控。用union all还是union一定要基于“你想对什么去重”来决策而不是随手写。4.4 union和union all选择时的判断标准我做技术方案时会问自己三个问题这两个结果集到底要不要互相排重整行去重会不会有副作用数据量大到什么程度如果数据量大而且业务上只要“全部明细”就选union all。如果两个来源确实有重复但你要的是整行唯一的集合并且数据量可控union可以让你少写一段SQL。不过说句实在话我很少在正式数仓逻辑里直接用union做去重。因为distinct的开销大且语义容易被误解。更稳妥的办法是union all加group by或窗口函数把去重逻辑显式写出来这样别人review代码时也能一眼看懂你在干什么。5. 连接查询写多了你就会遇到的四个典型问题5.1 结果行数突变先查关联键重复不管是left join多出数据还是inner join少出数据第一步永远是排查关联键的唯一性。右表关联键重复left join会膨胀右表关联键缺失inner join会丢数据。把这两个检查动作变成肌肉记忆你会发现很多“玄学问题”其实都有确定的原因。还要提醒一点关联键不要用可空字段更不要用JOIN ON key1 key1 AND key2 key2这种多字段关联时不处理null的写法。Hive里两个null用等号比较结果不是真多字段关联时如果一个字段为null整行匹配不上。稳妥做法是在ETL阶段就把关联键的空值用NVL统一处理成特殊标记比如NVL(user_id, -1)。5.2 join数据倾斜热点key怎么破join发生数据倾斜最常见的特征是Reducer卡在99%跑了很久不动。原因往往是某个关联键的值过多比如一张表里user_id为-1或0的数据特别多导致这些数据全部涌向同一个Reducer。我的处理思路分两步第一步把关联键的null和异常值过滤掉比如WHERE user_id ! -1第二步如果业务上需要保留这些异常值就用加盐拆分给热点key加一个随机数把它拆成多个key再把结果合并回来。-- 加盐拆分的典型思路热点key加随机后缀 SELECT a.user_id, SUM(a.pay_amount) AS pay_amount FROM ( -- 在关联键上加上0-9的随机数 SELECT user_id, pay_amount, CASE WHEN user_id IN (-1, 0) THEN CONCAT(user_id, _, CAST(RAND() * 10 AS INT)) ELSE CAST(user_id AS STRING) END AS join_key FROM dwd_order_detail_fact ) a LEFT JOIN ( SELECT user_id, user_name, -- 维表的关联键也要做同样的处理非热点key原样保留 CASE WHEN user_id IN (-1, 0) THEN CONCAT(user_id, _, suffix) ELSE CAST(user_id AS STRING) END AS join_key FROM ( SELECT user_id, user_name, explode(ARRAY(0,1,2,3,4,5,6,7,8,9)) AS suffix FROM dim_user_online WHERE user_id IN (-1, 0) UNION ALL SELECT user_id, user_name, 0 AS suffix FROM dim_user_online WHERE user_id NOT IN (-1, 0) ) t ) b ON a.join_key b.join_key;这不过是一个简化版思路真正落地的时候还要考虑维表也要做对应加盐展开否则拆出来的key在右表里找不到对应的行数据还是会丢。5.3 关联键里的空值策略三种方式怎么选关联键有空值很多人会纠结。实际上有三种常见方案直接过滤掉空值行、把空值替换成特殊标记参与关联、让空值保持不匹配。判断标准只有一个业务上需不需要保留这些空值行。比如在订单明细拉宽场景里订单一定会有user_id那user_id为空的一定是脏数据直接过滤掉没问题。又比如在用户维表和订单事实表关联时如果订单表里有一些未落库的用户ID需要保留订单行、维表字段为null那就该用left join并且让匹配不上就null千万别强行把null改成-1去关联否则你在-1这个key上可能会聚出来一堆业务上不存在的关系。5.4 聚合前后的表能不能join先聚合再关联还是先关联再聚合这是一个经典问题。比如想算每个用户的支付金额再关联用户维表取用户名。直观的想法是订单表先join用户维表再group by用户但实际上先group by订单表user_id算金额再join维表效率往往高得多因为shuffle的数据量大大减少了。-- 推荐做法先聚合再关联维表 SELECT u.user_name, t.pay_amount FROM ( SELECT user_id, SUM(pay_amount) AS pay_amount FROM dwd_order_detail_fact GROUP BY user_id ) t LEFT JOIN dim_user_online u ON t.user_id u.user_id;很多人习惯先join维表拿到user_name再group by结果就是order明细的所有字段都在join的时候被带进来shuffle量非常大。Hive里shuffle是开销的主要来源我们写SQL时所有优化思路都应该围绕“减少shuffle数据量”展开。先聚合再关联正是这个思路下最简单也最有效的一个操作习惯。结束语其实Hive的连接查询并没有那么复杂关键是把每个join类型背后的“行配对逻辑”真正想明白。我在写这些SQL的时候基本上都会在脑子里跑一遍哪张表是驱动表哪个键是关联键哪些行的字段会带上null哪个地方可能膨胀再落笔写代码。久而久之很多错误在写SQL的当下就会被自己拦截掉而不是等任务跑到一半才发现。最后再分享一个小习惯每次上线一个join逻辑复杂的任务我都会先在测试环境用一条抽样数据跑通然后把结果行数、关键字段的汇总值和预期逐一比对。数仓里的数据质量问题百分之八十都出在join环节把这一关守住你写数的可靠度会提升一大截。
