咱们先从一个非常常见的需求说起。你手里有一张表其中某个字段存了一串ID用逗号分隔或者用户画像字段里存了“体育,数码,娱乐”这种多值标签。现在要把这些值拆开一行变成多行每个值单独一行同时还得带着原表其他字段继续参与统计。这个需求在离线数仓里几乎是每天都要碰到的。很多同学刚开始上手Hive时都会在这里卡住因为光用split只拆成一个数组光用explode又会丢掉其他字段三个函数拆开讲谁都能看懂但真正组合起来用对还是有不少门道。这篇是“Hive实践”系列的第5篇前面几篇如果看过可以直接接着往下看没看过也没关系今天的内容相对独立只要你会基础的Hive SQL就可以直接“服用”。文章核心围绕split、explode、lateral view这三个函数展开讲清楚每个函数的原理和坑再通过完整的实战案例把它们串起来目标是让你看完之后遇到“一列变多行”的需求时能直接照着写SQL且心里清楚每一步为什么这么写。1. 内容拆解与场景定位1.1 三个函数分别承担什么角色这一节先把概念理顺。很多同学把这三个函数混在一起记其实它们的角色完全不同split一个普通字符串函数输入字符串和正则分隔符输出一个数组。它本身不改变行数只是把“一个长字符串”变成“一个数组字段”。explode一个UDTF用户自定义表生成函数输入数组或Map输出多行。它能把一行“炸”成多行是“一列变多行”的核心引擎。lateral view一种语法结构不是函数。它的作用是把explode产生的虚拟表与原表的每一行做侧向关联相当于一座桥让原表其他字段在explode之后还能保留。打个比方帮助理解。假设你手里有一串葡萄一个字符串split是先把这串葡萄一颗一颗摘下来放进果盘数组explode是把果盘里的葡萄倒到桌面上每一颗单独摆开多行而lateral view是保证每一颗葡萄掉在桌面后你仍然知道它是从哪一串葡萄上掉下来的——即原表其他字段仍然可见。这个理解很关键因为它直接决定了你写SQL时是否记得带lateral view。1.2 这些函数到底用在哪里结合日常离线数仓工作这类操作的使用场景非常集中。最常见的有以下几类日志解析埋点日志里一个用户可能触发多个事件ID事件ID以逗号分隔存在一个字段里要展开后统计每个事件的发生次数。用户标签展开用户表里有一个标签字段保存了“体育,数码,娱乐”这类多值信息需要拆开单独统计每个标签的覆盖用户数。订单与商品明细一条订单记录里包含多个商品ID需要把订单商品拆成明细行。上游多值字段下钻业务库为了省事经常把多个值塞进一个字段数仓接入后第一步往往就是“拆”。这些场景的共同特征就是物理表里一行数据的某个字段逻辑上是多值。Hive的结构化查询能力天然不适合直接处理这种“表中有表”的数据所以需要上述三件套来完成从“多值”到“明细”的转换。1.3 一个容易混淆的概念行转列和列转行在搜索热词里能看到不少同学在查“hive行转列和列转行”以及“hive里面列转行”。这里要提醒一下严格意义上的“行转列”和“列转行”指的是二维表形状的变换。比如经典的“行转列”是用SUMCASE WHEN把同一用户的多个分类值转成多列“列转行”通常是用UNION ALL合并多列。而我们今天讲的explode准确说应该叫“一列拆多行”或“集合展开”它是把一个集合值字段展开成多行记录。把explode叫“列转行”虽然听起来差不多但它容易误导人。列转行的重点是“多列归一列”而explode的重点是“单值变多行”。理解了这个区别你在搜索资料和写SQL时才能更精准不至于把概念用混。2. split函数正则转义是重灾区2.1 基础语法与返回类型split函数本身很基础语法如下split(str, regex) -- 返回类型arraystring -- 示例 SELECT split(a,b,c, ,); -- 结果[a,b,c]它接收两个参数第一个是待分割的字符串表达式第二个是正则表达式分隔符。这里有一个非常容易踩的坑第二个参数不是普通的字符串匹配而是Java风格的正则表达式。这意味着很多在直觉上“我写个字符就行”的分隔符都必须要考虑正则语义。2.2 正则转义实战举几个高频例子说明。处理IP点号分隔假设要按点号分割IP地址-- 错误写法点号是正则里的“任意字符” SELECT split(192.168.1.1, .); -- 结果[,,,,,,] 之类根本不是想要的 -- 正确写法对点号做转义 SELECT split(192.168.1.1, \\.); -- 结果[192,168,1,1]为什么写成\\.因为Hive SQL里的字符串字面量本身要处理一层转义\\在字符串中表示一个普通反斜杠这个反斜杠传给正则引擎后再和.组合变成正则里的“匹配点号”。如果你是在Shell命令行里执行Hive SQL整体还要再考虑Shell的转义那就会更复杂建议直接用hive -e执行时先本地测试SQL再跑数据。处理竖线分隔CSV或者类CSV数据里经常用竖线做分隔符比如a|b|c-- 错误写法| 在正则里表示“或” SELECT split(a|b|c, |); -- 结果[a,b,c]这里碰巧是对的但换个数据就乱套 SELECT split(a|b||c, |); -- 结果[a,b,,c]看起来好像也还行 -- 但如果是这样 SELECT split(abc|def, |); -- 结果[a,b,c,,d,e,f]全拆坏了 -- 正确写法 SELECT split(a|b|c, \\|); -- 结果[a,b,c]竖线这个坑非常阴险因为你用a|b|c测的时候结果恰好是对的容易让人放松警惕。一旦数据里出现稍微复杂的内容就会全线崩溃。经验是遇到竖线分隔符一律写\\|不要心存侥幸。处理反斜杠本身如果数据本身用反斜杠分隔例如a\b\c在SQL里需要写成SELECT split(a\\b\\c, \\\\); -- 结果[a,b,c]这里涉及三层转义SQL字符串字面量、正则引擎的反斜杠语义、以及要匹配的目标字符本身。实际工作中反斜杠做分隔符的场景比较少但如果碰上了记住“多写几个反斜杠”然后用一个能查看中间结果的工具验证。2.3 数组的常用后续操作split返回的是数组类型Array所以它天然可以和很多数组函数无缝衔接。比如-- 取第一个元素 SELECT split(a,b,c, ,)[0]; -- a -- 取最后一个元素 SELECT split(a,b,c, ,)[2]; -- c -- 判断数组大小 SELECT size(split(a,b,c, ,)); -- 3 -- 判断是否包含某个值 SELECT array_contains(split(a,b,c, ,), b); -- true另外一个实用技巧是如果你只是想在split后取倒数第一个元素可以用reverse反向处理SELECT reverse(split(reverse(a,b,c), ,)[0]); -- 先反转成c,b,a取第一个是c再反转回来还是c这个写法在只想取最后一个元素、又不想多算一遍size时非常方便。2.4 split的边界情况split的另一个坑是当字符串前后有空格时分割出来会带空串。例如a, b, c按英文逗号分隔结果是[a, b, c]注意“ b”前面是有空格的。如果后续要跟你自己的字典做匹配这个空格会搞出很多莫名的脏数据。解决办法是在split前先trim或replaceSELECT split(regexp_replace(a, b, c, , ), ,); -- 结果[a,b,c]另外如果分隔符匹配不到split会返回一个只包含原字符串的数组例如split(abc, ,)会返回[abc]。这与很多其他语言直接报错的行为不一样需要注意。3. explode函数UDTF的威力与限制3.1 UDTF的本质explode是Hive内置最常用的UDTF。普通函数比如concat是“一行进一行出”聚合函数比如sum是“多行进一行出”而UDTF是“一行进多行出”。这种函数在SQL标准里并不常见Hive对其支持也是有条件的。explode支持两种输入类型数组Array把数组中的每个元素输出为一行默认列名是col。Map把Map的每个键值对输出为两列默认列名是key和value。它的基本写法非常简洁-- 数组展开 SELECT explode(array(a,b,c)); -- 输出三行 -- a -- b -- c -- Map展开 SELECT explode(map(k1,v1,k2,v2)); -- 输出两行两列 -- k1 v1 -- k2 v23.2 和split组合的经典用法单独用explode的场景不多因为它通常跟在split后面。这里先演示一个最基础的组合-- 拆分为一行一个词 SELECT explode(split(a,b,c, ,)); -- 输出三行 -- a -- b -- c这里需要理解Hive SQL的执行顺序先对每个输入行调用split产生一个数组然后explode对数组中的每个元素输出一行。这个组合本身是没问题的但它有一个致命缺陷——它不能带出原表其他字段。3.3 不能带其他字段的关键限制以下是几乎所有初学者都会犯的错误-- 这段SQL会报错 SELECT id, explode(split(tag_str, ,)) AS tag FROM user_tag_table;报错信息通常是UDTFs are not supported in the SELECT clause这背后是Hive对UDTF的一个硬性限制当你使用UDTF时SELECT中不能再出现其他列或表达式。原因Hive内部还没有实现UDTF输出与其他字段的自动关联这个能力由lateral view来补充。另外还有两个相关限制UDTF不能嵌套比如explode(explode(...))会直接报Nested UDTF错误。UDTF不能用在WHERE、GROUP BY等位置只能在SELECT或LATERAL VIEW中使用。这些限制并非Hive不愿意放开而是因为UDTF产生的数据量不确定需要一种显式的语法来告诉引擎“如何把展开结果和原表数据关联”这个显式语法就是lateral view。3.4 explode的额外注意事项explode在输入为NULL时不会有任何输出这一点经常被人忽略。比如一个用户标签字段是NULLexplode后这行就没了。如果你的后续统计依赖于所有用户都出现一次就必须用后面要讲的LATERAL VIEW OUTER来规避。另外explode一个空数组array()同样没有任何输出。在Hive 0.12版本之前这种行为甚至可能导致任务报错新版已经正常为空了但语义上仍然要注意“空集合会丢行”的特性。4. lateral view被忽略的桥梁4.1 为什么叫“侧视图”lateral view直译是“侧视图”。我自己的理解是它是在原表的每一行旁边动态拉起一个“侧向的虚拟表”这个虚拟表由UDTF产生并通过AS子句给这个虚拟表的字段起名字。然后原表当前行的其他字段和虚拟表展开出来的每一行一一拼接形成新的一行。在SQL文法和执行逻辑上LATERAL VIEW必须紧跟在表名之后、WHERE之前它不是一个独立子句更像是表引用的一部分。这一点在写复杂SQL时经常被忽略一旦顺序写错Hive会报语法错误。4.2 标准语法和执行原理标准写法如下SELECT t.id, tag -- 这里是展开出来的字段 FROM user_tag_table t LATERAL VIEW explode(split(t.tag_str, ,)) tv AS tag;拆解一下user_tag_table t原表起别名t。LATERAL VIEW explode(...)对当前行的tag_str字段先split再explode生成一个虚拟表。tv虚拟表的别名这个别名可以省略但在复杂查询里建议保留方便定位问题。AS tag给虚拟表展开出来的那一列起名叫tagSELECT里就可以直接引用。执行逻辑用一句话概括对原表的每一行计算一次explode把生成的多个虚拟行与该行其他字段拼接形成最终输出。展开前是一行展开后是“一行变多行”其他列原样复制。理解这个执行逻辑非常重要。因为它意味着lateral view后面的explode表达式里可以用原表的任意字段这就是它比“SELECT里直接explode”灵活得多的原因。同时它也有一个副作用如果explode结果为空当前行会整体消失这正是很多“查着查着少了数据”的元凶。4.3 多级lateral view笛卡尔积展开当一行中有多个集合字段需要同时展开时可以使用多个lateral view。比如订单表有item_ids商品ID列表和tag_ids商品标签列表要同时展开SELECT t.order_id, item_id, tag_id FROM order_table t LATERAL VIEW explode(split(t.item_ids, ,)) item_tv AS item_id LATERAL VIEW explode(split(t.tag_ids, ,)) tag_tv AS tag_id;这里要特别强调多个lateral view之间是笛卡尔积关系。假设第一个explode炸出3个商品第二个explode炸出2个标签最终结果就是3×26行。这在某些场景比如商品和标签的关联分析是符合预期的但在其他场景可能造成无意义的数据膨胀。写多级lateral view之前务必先估算一下每个集合的基数避免结果集失控。4.4 LATERAL VIEW OUTER保留空集合行从Hive 0.12开始支持LATERAL VIEW OUTER。它的作用和LEFT OUTER JOIN类似——当explode结果为空时保留原表的当前行展开字段置为NULL。-- 普通lateral view标签为空的用户会整行消失 SELECT t.user_id, tag FROM user_tag_table t LATERAL VIEW explode(split(t.tag_str, ,)) tv AS tag; -- 使用outer标签为空的用户仍保留tag为NULL SELECT t.user_id, tag FROM user_tag_table t LATERAL VIEW OUTER explode(split(t.tag_str, ,)) tv AS tag;这个区别在日常统计中影响巨大。比如你要统计“所有用户的标签覆盖情况”如果有一个用户没有标签普通lateral view直接把他删掉了原本“该用户在结果中出现0次”的记录就消失了统计用户总数就会偏小。用OUTER则会把tag置为NULL用户总数保持完整。5. 综合实战从标签表到统计报表5.1 场景设定假设我们有这样一张用户标签表user_tag_tableuser_idtag_strregister_date1001体育,数码,娱乐2024-01-011002数码,阅读2024-01-021003NULL2024-01-031004娱乐,娱乐,体育2024-01-04注意几个细节1003这个用户的标签是NULL。1004用户的标签里有重复值“娱乐”业务上可能有含义统计时要注意是否去重。数据量假设很大大约几百万用户每个用户标签数从0到几十个不等。需求分三步第一步把标签拆开每个用户每个标签一行第二步统计每个标签下的用户数第三步看看重复标签怎么处理更合理。5.2 第一步基础展开先做最基础的展开同时保留用户ID和注册日期SELECT user_id, tag, register_date FROM user_tag_table LATERAL VIEW explode(split(tag_str, ,)) tv AS tag;执行之后1001会变成3行1002变成2行。1003因为是NULL直接消失。1004变成3行“娱乐,娱乐,体育”。如果业务上“娱乐”出现两次有特殊含义比如同一标签打两次那保留重复没问题如果只是想去重需要在后面的统计中用COUNT(DISTINCT user_id)或GROUP BY tag时配合DISTINCT处理。5.3 第二步标签用户数统计统计每个标签有多少用户SELECT tag, COUNT(DISTINCT user_id) AS user_cnt FROM user_tag_table LATERAL VIEW explode(split(tag_str, ,)) tv AS tag GROUP BY tag;注意这里用的是COUNT(DISTINCT user_id)不是COUNT(*)因为同一个用户可能在一个标签下出现多次1004的“娱乐”出现了两次。用COUNT(DISTINCT user_id)可以避免把同一个用户的重复标签当成两个用户。5.4 第三步空用户保留问题现在回到1003这个用户。假如老板要的是“所有用户的标签分布”NULL标签用户也想保留在结果里至少能查出“有多少用户没有标签”。这时候必须用LATERAL VIEW OUTERSELECT tag, COUNT(DISTINCT user_id) AS user_cnt FROM user_tag_table LATERAL VIEW OUTER explode(split(tag_str, ,)) tv AS tag GROUP BY tag;此时1003会以tagNULL的形式出现在结果中在最终报表里用IF(tag IS NULL, 未知, tag)归类即可。5.5 第四步扩展到真实日志场景再举一个更接近于真实数仓任务的场景。假设有一张埋点日志表ods_event_logevent_iduser_iditem_idsevent_timee001u0011001,1002,10032024-03-01 10:00:00e002u00210012024-03-01 10:05:00e003u0011002,10032024-03-01 10:10:00需求统计每个商品被多少个不同用户浏览过即item维度的UV。SELECT item_id, COUNT(DISTINCT user_id) AS uv FROM ods_event_log LATERAL VIEW explode(split(item_ids, ,)) item_tv AS item_id GROUP BY item_id;这个SQL看起来很简单但实际跑数仓任务时还要考虑几个点item_ids字段是否可能为NULL或空字符串如果存在NULL是否需要LATERAL VIEW OUTER保留这里其实不需要因为我们只关心发生过的曝光NULL行可以直接丢弃。item_ids是否可能有脏数据比如包含空格或分隔符不一致建议先做质量探查比如统计最大展开行数、检查是否有其他分隔符混入。数据量很大时GROUP BY item_id会不会发生数据倾斜下面会讲。5.6 数据倾斜问题的现实应对在实际跑上述SQL时最常遇到的性能问题就是数据倾斜。比如某个热门商品“1001”的曝光量占了总曝光量的80%那么在GROUP BY阶段所有“1001”的数据都集中在一个Reduce上任务会非常慢甚至OOM。这里给出一种加盐两阶段聚合思路。第一阶段在key上加随机后缀把热点拆开并行聚合第二阶段去掉后缀再聚合。-- 第一阶段加盐拆散热点 SELECT concat(item_id, _, floor(rand() * 10)) AS salted_item_id, COUNT(DISTINCT user_id) AS uv FROM ods_event_log LATERAL VIEW explode(split(item_ids, ,)) item_tv AS item_id GROUP BY concat(item_id, _, floor(rand() * 10)); -- 第二阶段去掉盐再做一次汇总 SELECT split(salted_item_id, _)[0] AS item_id, SUM(uv) AS uv FROM ( SELECT concat(item_id, _, floor(rand() * 10)) AS salted_item_id, COUNT(DISTINCT user_id) AS uv FROM ods_event_log LATERAL VIEW explode(split(item_ids, ,)) item_tv AS item_id GROUP BY concat(item_id, _, floor(rand() * 10)) ) t GROUP BY split(salted_item_id, _)[0];注意这里COUNT(DISTINCT user_id)在加盐后分两级第一级仍然是COUNT(DISTINCT user_id)第二级用SUM(uv)会有去重逻辑被破坏的风险。严格的做法是第一级用DISTINCT user_id先做一次但为了讲解清晰这里不展开说。实际场景建议先通过SELECT item_id, COUNT(*) FROM ... GROUP BY item_id摸底倾斜情况再决定使用哪种优化策略。另外Hive有一个参数hive.groupby.skewindatatrue开启后会自动做负载均衡对某些倾斜场景有帮助但它不保证所有场景都有效最好还是先从数据层面理解倾斜原因。5.7 多字段同时展开时的行数评估前面提到多个lateral view会产生笛卡尔积。在真实场景中假设订单表一行里既有商品列表item_ids又有商品数量列表item_cnts两个字段一一对应。如果用两个lateral view分别展开再做笛卡尔积行数就可能从N行变成N×M行。正确做法是在explode前把两个列表合并成一个数组或者使用posexplode带序号来处理。如果你的Hive版本支持posexplode推荐优先考虑SELECT t.order_id, item_id, item_cnt FROM order_table t LATERAL VIEW posexplode(split(t.item_ids, ,)) idx_tv AS pos, item_id LATERARY VIEW posexplode(split(t.item_cnts, ,)) cnt_tv AS cnt_pos, item_cnt WHERE pos cnt_pos;这段SQL用pos cnt_pos做过滤把一一对应的商品和数量配对避免笛卡尔积。不过实际开发中我更建议在ETL入仓阶段就把这种“双列表”改成明细行不要每次查询都现场配对既浪费计算资源又容易被过滤条件坑。6. 常见问题与排查技巧6.1 高频报错与解决方案报错信息原因解决方案UDTFs are not supported in the SELECT clause在SELECT里直接explode并同时选其他字段改用LATERAL VIEWNested UDTF在explode里又套用了explode拆成多个LATERAL VIEW或先处理成数组Cannot recognize input near lateral viewLATERAL VIEW位置不对或Hive版本过老确认LATERAL VIEW紧跟表名后并检查语法Neither LATERAL VIEW explode nor LATERAL VIEW explodeAS别名或虚拟表别名问题确认虚拟表别名和AS列别名都写对Failed to breakup cbo rules部分版本CBO优化和UDTF冲突尝试关闭CBO优化set hive.cbo.enablefalse; 或升级版本6.2 展开后数据比预期多很多如果跑完发现结果行数暴增先检查以下几点字符串里是否有隐藏分隔符。比如看上去是英文逗号实际是中文逗号“”或者全角逗号split会匹配不到导致整串不拆或者拆错。分隔符转义是否准确。点号和竖线是最容易出问题的。多级lateral view是否造成了笛卡尔积。如果只是想把多个集合对齐展开要注意用posexplode或数据预处理。6.3 排查步骤建议遇到“展开结果不对”时我建议按这个顺序排查第一步先单独测试splitSELECT split(tag_str, ,) FROM user_tag_table WHERE user_id 1001;确认split产生的数组是你想要的。第二步单独测试explodeSELECT explode(split(tag_str, ,)) FROM user_tag_table WHERE user_id 1001;确认展开后每个值都正确。第三步再加上lateral view并选择一个有代表性的样本数据检查SELECT user_id, tag FROM user_tag_table LATERAL VIEW explode(split(tag_str, ,)) tv AS tag WHERE user_id IN (1001,1003);这样一段一段拆开验证很容易定位问题到底出在哪一层。6.4 空值、空串、重复值这三类数据问题在展开场景中特别常见NULLexplode(NULL)没有输出整行消失。需要保留原行时使用LATERAL VIEW OUTER。空字符串split(, ,)返回[]explode后会输出一行空字符串。这种“伪数据”往往很隐蔽建议展开后统一过滤WHERE tag ! .重复值explode不会自动去重。如果后续统计用户数务必用COUNT(DISTINCT user_id)否则同一个用户的重复标签会被重复计数。6.5 性能层面的提示最后提一个性能问题。如果某个字段特别长包含几千个待拆分的值explode之后一行会变成几千行这种膨胀率很高。在跑大批量任务之前建议先用一个子查询统计一下最大split长度SELECT MAX(size(split(item_ids, ,))) FROM ods_event_log;如果最大值达到几千几万就要评估这个任务是否适合直接用explode或者需要调整并行度、内存参数。展开操作本身不复杂但它带来的数据膨胀对下游计算资源影响很大提前摸底永远值得做。我在实际项目中就踩过一次这样的坑某张大宽表的标签字段平均只有几个标签结果极个别的用户被打了上万个标签explode之后这行数据拖垮了整个reduce阶段任务跑了几个小时超时失败。后来先做了样本探查发现极端值后在ETL层把这种超长标签用户单独过滤出来单独处理才彻底解决。从那以后我养成了习惯凡是线上跑爆炸类SQL之前都会先看一眼数据分布尤其是最大值和偏度。所以最后再分享一个小技巧当你遇到splitexplodelateral view这组合一起上线的任务时先在一个小分区里跑一遍重点看结束之后的“输出行数/输入行数”膨胀倍数。这个倍数如果超出你的预估多半不是数据有问题就是业务规则没理解透先查清楚再全量跑能省下不少时间和计算资源。