Hive：LATERAL VIEW 使用总结
LATERAL VIEW 使用总结
-
-
- 使用案例一（单个LATERAL VIEW）：split + explode + LATERAL VIEW
- 使用案例二（多个LATERAL VIEW）：explode + LATERAL VIEW
-
lateral view用于和split, explode等UDTF一起使用，它能够将一行数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。lateral view首先为原始表的每行调用UDTF，UTDF会把一行拆分成一或者多行，lateral view再把结果组合，产生一个支持别名表的虚拟表。The LATERAL VIEW clause is used in conjunction with generator functions such as EXPLODE, which will generate a virtual table containing one or more rows. LATERAL VIEW will apply the rows to each original output row.
LATERAL VIEW Clause - Spark 3.2.0 Documentation (apache.org)
使用案例一（单个LATERAL VIEW）：split + explode + LATERAL VIEW
求出每个技能对应的最大的用户的年龄
表和数据
| user_id | user_name | age | skills |
|---|---|---|---|
| 1356 | kyle | 23 | Hadoop-Hive-Spark |
| 1357 | Jack | 22 | Hadoop-Hive |
| 1358 | Sam | 26 | Mysql-Oracle |
| 1359 | Lucy | 28 | Redis-Mysql |
| 1360 | Rose | 32 | Hadoop-Hive-Spark-Flink-Hbase |
| 1361 | Herry | 25 | Flink-Hbase-ClickHouse-Kafka |
| 1362 | Kelly | 27 | Spark-Flink-Hbase |
cache table user_info
select '1356' user_id, 'kyle' user_name, 23 age, 'Hadoop-Hive-Spark' skills
union
select '1357' user_id, 'Jack' user_name, 22 age, 'Hadoop-Hive' skills
union
select '1358' user_id, 'Sam' user_name, 26 age, 'Mysql-Oracle' skills
union
select '1359' user_id, 'Luc' user_name, 28 age, 'Redis-Mysql' skills
union
select '1360' user_id, 'Rose' user_name, 32 age, 'Hadoop-Hive-Spark-Flink-Hbase' skills
union
select '1361' user_id, 'Harry' user_name, 25 age, 'Flink-Hbase-ClickHouse-Kafka' skills
union
select '1362' user_id, 'Kelly' user_name, 27 age, 'Spark-Flink-Hbase' skills;
需求分析
先从 skills 字段把每个技能分割出来，然后按照 user_id 和 skills 字段分组，求出最大的年龄
with t1 as (-- 对 skills 字段进行切割并实现列转行select user_id,user_name,age,skillfrom user_infolateral view explode(split(skills,'-')) skill_table as skill
),t2 as (-- 按照 skill 分组 age 排序，为了标记每个技能对应的最大的用户信息select *,row_number() over(partition by skill order by age desc) rnfrom t1
)selectuser_id,user_name,age,skill
from t2
where rn = 1;
使用案例二（多个LATERAL VIEW）：explode + LATERAL VIEW
将 skills 和 mark 字段全部转为列
表和数据
| user_id | user_name | age | skills | mark |
|---|---|---|---|---|
| 1356 | kyle | 23 | [“Hadoop”,“Hive”,“Spark”] | [“A”, “B”, “C”] |
| 1357 | Jack | 22 | [“Hadoop”,“Hive”] | [“A”, “D”, “E”] |
| 1358 | Sam | 26 | [“Mysql”,“Oracle”] | [“B”, “C”] |
| 1359 | Lucy | 28 | [“Redis”,“Mysql”] | [“D”, “E”] |
需求分析
由于 skills 和 mark 字段全部都是 Array 类型，所以可以直接使用 explode 函数处理
select user_id,user_name,age,skill,mark
FROM baseTable
LATERAL VIEW explode(skills) view1 AS skill
LATERAL VIEW explode(mark) view2 AS mark;
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！