hive 之行拆列explode

本文详细介绍了Hive中的两种UDTF(用户定义表函数):explode和lateral view的使用方法。包括如何通过explode将集合类型数据展开成多行记录,以及使用lateral view解决explode的一些限制问题。此外,还解释了如何使用outer lateral view处理空数组的情况。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

1、explode

explode(ARRAY)  列表中的每个元素生成一行

explode(MAP) map中每个key-value对,生成一行,key为一列,value为一列

限制:
1、No other expressions are allowed in SELECT
        SELECT pageid, explode(adid_list) AS myCol... is not supported
2、UDTF's can't be nested
        SELECT explode(explode(adid_list)) AS myCol... is not supported
3、GROUP BY / CLUSTER BY / DISTRIBUTE BY / SORT BY is not supported
        SELECT explode(adid_list) AS myCol ... GROUP BY myCol is not supported

 

2、lateral view

可使用lateral view解除以上限制,语法:

lateralView: LATERAL VIEW explode(expression) tableAlias AS columnAlias (',' columnAlias)*
fromClause: FROM baseTable (lateralView)*

案例:

table名称为pageAds

> SELECT pageid, adid
FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid;

输出结果:

3、多个lateral view

from语句后面可以带多个lateral view语句

案例:

表名:baseTable

from后只有一个lateral view:

SELECT myCol1, col2 FROM baseTable

LATERAL VIEW explode(col1) myTable1 AS myCol1;

结果:

多个lateral view:

SELECT myCol1, myCol2 FROM baseTable

LATERAL VIEW explode(col1) myTable1 AS myCol1

LATERAL VIEW explode(col2) myTable2 AS myCol2;

结果:

 

4、Outer Lateral Views

如果array类型的字段为空,但依然需返回记录,可使用outer关键词。

比如:select * from src LATERAL VIEW explode(array()) C AS a limit 10;

这条语句中的array字段是个空列表,这条语句不管src表中是否有记录,结果都是空的。

而:select * from src LATERAL VIEW OUTER explode(array()) C AS a limit 10;

结果中的记录数为src表的记录数,只是a字段为NULL。

比如:

238 val_238 NULL
86 val_86 NULL
311 val_311 NULL
27 val_27 NULL
165 val_165 NULL
409 val_409 NULL
255 val_255 NULL
278 val_278 NULL
98 val_98 NULL

<think>嗯,用户让我介绍一下HiveSQL中的行转。首先,我得确定他们所说的行转具体指的是哪种操作。行转在数据处理中通常有两种情况:一种是常见的将多行数据合并成一行,比如使用collect_list或者concat_ws函数;另一种是更复杂的行转换,比如将多个行的某个字段值转换为多个,这时候可能会用到case when或者更高级的函数。 首先,我需要回忆一下Hive中处理行转的常用方法。比如,当需要把同一分组下的多个行数据合并到一个表中,可以使用collect_list或者collect_set,前者保留顺序和重复,后者去重。然后配合concat_ws来拼接成字符串。例如,用户可能有多个订单记录,需要按用户ID分组,将所有订单合并成一个字段。 另外,如果是需要将不同的行中的值转换为不同的,可能需要使用case when结合聚合函数。比如,每个学生的不同科目的成绩分布在多行,需要转换为每个学生一行,每对应一个科目。这时候可能需要用max(case when subject='数学' then score end) as math_score这样的结构,结合group by学生ID来实现。 用户可能在实际工作中遇到了数据需要展示为更紧凑的形式,或者需要满足某些报表的需求,所以需要行转。需要确保他们理解这两种不同的场景,以及对应的解决方法。此外,Hive可能不支持像pivot这样的语法,所以得用传统的方法来实现。 还要注意性能问题,比如collect_list处理大量数据时的内存消耗,或者使用多个case when可能导致的查询复杂度增加。可能需要提醒用户根据数据量选择合适的方法,或者考虑使用其他工具如Spark进行更高效的处理。 最后,可能需要给出具体的例子来说明这两种情况,帮助用户更好地理解。比如,给出一个学生成绩表的例子,演示如何将多行成绩转换为。或者一个用户标签的例子,展示如何将多个标签合并成一个字段。</think>在 HiveSQL 中实现行转主要有两种典型场景,以下是具体方法和示例说明: 1. **多行合并为单(行聚合)** ```sql -- 原始数据示例 (学生选课表) student_id course 1001 Math 1001 English 1002 Physics -- 使用 collect_list + concat_ws 实现 SELECT student_id, concat_ws(',', collect_list(course)) AS courses FROM student_courses GROUP BY student_id; -- 结果 student_id courses 1001 Math,English 1002 Physics ``` 2. **多行值转为多(经典行转换)** ```sql -- 原始数据示例 (学生成绩表) student_id subject score 1001 Math 85 1001 English 92 1002 Math 78 1002 Physics 88 -- 使用 CASE WHEN + 聚合函数实现 SELECT student_id, MAX(CASE WHEN subject = 'Math' THEN score END) AS math_score, MAX(CASE WHEN subject = 'English' THEN score END) AS english_score, MAX(CASE WHEN subject = 'Physics' THEN score END) AS physics_score FROM student_scores GROUP BY student_id; -- 结果 student_id math_score english_score physics_score 1001 85 92 NULL 1002 78 NULL 88 ``` **进阶用法:** - 动态处理(需结合预处理或UDF): ```sql -- 使用预先生成的表 SET hivevar:subjects=Math,English,Physics; SELECT student_id, ${hivevar:subjects} FROM ( SELECT student_id, MAP(subject, score) AS score_map FROM student_scores ) t LATERAL VIEW explode(score_map) s AS subject,score; ``` **注意事项:** 1. `collect_list` 保留所有元素(包括重复),`collect_set` 会去重 2. CASE WHEN 方案需预先知道所有名,适用于维度有限的情况 3. 动态生成通常需要结合 shell/python 预处理生成 SQL 4. 大数据量时优先考虑存储格式(ORC/Parquet)提升性能 实际业务中,行转常用于: - 用户标签聚合(合并多个标签) - 时间序数据转宽表 - 多维度指标横向展示 - 日志特征合并等场景
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值