【hive】lateral view侧视图

本文介绍了ApacheHive中的lateralview特性,用于处理多值字段,通过UDTF函数进行拆分,模拟列转行操作。重点讲解了语法、示例,以及lateralviewouter处理null值的方法,确保数据完整性。

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

文档地址:https://cwiki.apache.org/confluence/display/Hive/LanguageManual+LateralView

1.介绍

lateral view也叫侧视图,属于hive sql所特有的语法。用来实现类似标准sql中join的操作。但区别在于:

  • join参与运算的往往是两个表,根据指定的关联字段进行横向连接。
  • lateral view参与运算的是一张表,这张表里往往存在某个多值的字段,通过侧视图结合UDTF函数可以将这个多值字段拆分为粒度更小的值,每一个拆分出来的值都会作为新的字段和一条原记录进行拼接。类似于列转行操作,虽然严格意义上不算列转行,因为只是增加了行数,但并未减少列数,只是减少了列中所包含的字段个数。

2.语法

select
	src.*,
	tb_alias.col_alias
from src lateral view UDTF(src.col) tb_alias as col_alias [, col_alias, ...];

以上只是一个基本的语法参考,tb_alias为表别名,这里的表指的是UDTF所返回的虚拟表。as col_alias [, col_alias, ...]是给这个虚拟表的字段指定别名,方便后续引用。返回的虚拟表中有几个字段,就得指定几个别名,业务中一般只返回一个。

关于字段别名,文档里说的是从hive 0.12.0字段别名可以省略,此时它继承自UDTF函数在定义时指定的字段名,但仅做了解即可,按照上面语法通用就不必要做一些非必要的改动。
在这里插入图片描述

重点是理解上述代码的逻辑执行过程,UDTF会根据传入的字段先返回一张虚拟表,此时虚拟表的表名和字段名分别被命名为tb_alias col_alias,然后通过lateral view,将虚拟表的每条记录关联到原来所属的记录上去,类似于join操作,只不过不需要我们显式指定on的字段,hive内部会自己识别原来属于哪条记录并关联。最后再从这张结果表中select我们需要的字段就可以了。

3.code demo

1)单重侧视图

with src as (
    select '张三' as name, '唱;跳;rap'as skills
    union all
    select '李四' as name, '唱;跳'as skills
)
select
    src.*,
    tb.col_name
from src lateral view explode(split(skills,';')) tb as col_name;

output:
在这里插入图片描述

2)多重侧视图

多重侧视图的执行过程是在上一步侧视图结果的基础上,再进行一次lateral view操作,所以对于后面的lateral view,是可以直接引用前面lateral view结果表中的字段的。

下面通过代码对二重lateral view拆开分步演示,可以更好的理解执行逻辑。
step1:

with src as (
    select '张三' as name, map('语文', '71;72;73', '数学', '81;82;83') as col
    union all
    select '李四' as name, map('语文', '90') as col
)
select src.*,
       tb1.subject,
       tb1.score
from src lateral view explode(col) tb1 as subject, score;

output:
在这里插入图片描述

step2,基于step1的结果,对成绩score列的值继续展开:

with src as (
    select '张三' as name, map('语文', '71;72;73', '数学', '81;82;83') as col
    union all
    select '李四' as name, map('语文', '90') as col
)
select src.*,
       tb1.subject,
       tb1.score,
       tb2.score_detail
from src lateral view explode(col) tb1 as subject, score
         lateral view explode(split(tb1.score, ';')) tb2 as score_detail;

output:
在这里插入图片描述
当然,如果需要,可以继续lateral view下去。

3)lateral view outer

有一个点需要注意,就是实际任务中UDTF的返回结果可能存在空值null的情况,对于这种情况,hive会丢失原表中的数据行,因为本身lateral view就类似于join操作,关联不上那就丢失了。

例如:

with src as (
    select '张三' as name, '唱;跳;rap'as skills
    union all
    select '李四' as name, null as skills
)
select
    src.*,
    tb.col_name
from src lateral view explode(split(skills,';')) tb as col_name;

output:
在这里插入图片描述
可以看到原始数据“李四”的信息就丢失了,同时需要注意这里的空值指的是null,而不是空字符串,这是两种不同的概念,比如下面这段sql:

with src as (
    select '张三' as name, '唱;跳;rap'as skills
    union all
    select '李四' as name, '' as skills       -- 这里修改null为空字符串''
)
select
    src.*,
    tb.col_name
from src lateral view explode(split(skills,';')) tb as col_name;

output:
在这里插入图片描述

为了规避这种可能造成数据丢失的情况,hive从0.12.0版本及之后提供了lateral view outer来解决。这种方式可以理解为标准sql中的left join,即使UDTF返回的结果为null,也会保留原表的这条数据。具体见代码:

with src as (
    select '张三' as name, '唱;跳;rap'as skills
    union all
    select '李四' as name, null as skills
)
select
    src.*,
    tb.col_name
from src lateral view outer explode(split(skills,';')) tb as col_name;

output:
在这里插入图片描述

4.tips

1)lateral view + json_tuple

3)中当lateral view遇到explode爆炸函数返回null的时候原表记录也会丢失,但并非lateral view+返回null值的UDTF函数都会丢失原数据,例如json_tuple

with t as (
  select 1 as id, '{
    	"name": "小明",
    	"age": 18,
    	"score": {
    		"math": 100,
    		"english": 90
    	}
    }' as json_str
)

select * from t
lateral view json_tuple(json_str, 'score') t1 as score
lateral view json_tuple(score, 'math1') t2 as math1;

在这里插入图片描述
math1不存在,会以null的形式返回,不影响原数据,和explode处理null值时的方式不同。查了下原因:

这主要是因为explode和json_tuple这两个函数的工作方式和目的不同。

explode函数的目的是将一行变成多行。例如,如果你有一行数据,其中一个列是一个数组,包含三个元素,explode这个数组会生成三行,每行对应数组的一个元素。如果数组是空的或者null,那么explode就没有任何东西可以生成,所以结果集中不包含这一行。

而json_tuple函数的目的是从JSON对象中提取值。如果JSON对象中不存在某个键,json_tuple只能返回NULL,因为它没有找到对应的值。但是,它并不会删除整行数据,因为你可能仍然从JSON对象中提取了其他的键和值。所以,即使某个键在JSON对象中不存在,对应的行也会在结果集中保留,只不过对应的列值为NULL。

总的来说,explode函数是一个“生成”型的函数,它的目标是生成新的行,而json_tuple是一个“提取”型的函数,它的目标是提取现有的值。这就是为什么当它们遇到null或者不存在的值时,它们的行为会有所不同。

供理解参考。

2)lateral view与where的执行顺序

lateral view 本质上类似于sql中的join操作,因此执行顺序为先 lateral view关联,后where筛选。所以lateral view出来的字段可以在where中使用。

where中的字段为lateral view生成的字段:

create temporary view test as
select 1 as id, '{"name": "张三", "age": 18}' as info union all 
select 2 as id, '{"name": "李四", "age": 20}' as info;

select 
    *
from test
lateral view json_tuple(info, 'name', 'age') t as name, age
where name='李四';

在这里插入图片描述
对应的物理执行计划:

== Physical Plan ==
Execute InsertIntoHadoopFsRelationCommand afs://kunpeng.afs.baidu.com:9902/user/g_spark_rdw/rdw/poi_engine/download/20240920/89822ac7d704f2564f/2, false, RCSV, [escape=", delimiter=,, header=true, path=afs://kunpeng.afs.baidu.com:9902/user/g_spark_rdw/rdw/poi_engine/download/20240920/89822ac7d704f2564f/2], Overwrite, [id, info, name, age]
+- WriteFiles
   +- Exchange SinglePartition, REPARTITION_BY_NUM, [plan_id=393057]
      +- *(4) GlobalLimit 10000, 0
         +- Exchange SinglePartition, ENSURE_REQUIREMENTS, [plan_id=393053]
            +- *(3) LocalLimit 10000
               +- *(3) Filter (isnotnull(name#493721) AND (name#493721 = 李四))
                  +- Generate json_tuple(info#493720, name, age), [id#493719, info#493720], false, [name#493721, age#493722]
                     +- Union
                        :- *(1) Project [1 AS id#493719, {"name": "张三", "age": 18} AS info#493720]
                        :  +- *(1) Scan OneRowRelation[]
                        +- *(2) Project [2 AS id#493731, {"name": "李四", "age": 20} AS info#493732]
                           +- *(2) Scan OneRowRelation[]

先Generate json_tuple,后Filter过滤。即先lateral view,后where。

where中的字段为非lateral view生成的字段,即原表字段时:

create temporary view test as
select 1 as id, '{"name": "张三", "age": 18}' as info union all 
select 2 as id, '{"name": "李四", "age": 20}' as info;

select 
    *
from test
lateral view json_tuple(info, 'name', 'age') t as name, age
where id=1;

在这里插入图片描述
物理执行计划:

== Physical Plan ==
Execute InsertIntoHadoopFsRelationCommand afs://kunpeng.afs.baidu.com:9902/user/g_spark_rdw/rdw/poi_engine/download/20240920/7ca72d273e109b9043/2, false, RCSV, [escape=", delimiter=,, header=true, path=afs://kunpeng.afs.baidu.com:9902/user/g_spark_rdw/rdw/poi_engine/download/20240920/7ca72d273e109b9043/2], Overwrite, [id, info, name, age]
+- WriteFiles
   +- Exchange SinglePartition, REPARTITION_BY_NUM, [plan_id=393216]
      +- *(3) GlobalLimit 10000, 0
         +- Exchange SinglePartition, ENSURE_REQUIREMENTS, [plan_id=393212]
            +- *(2) LocalLimit 10000
               +- Generate json_tuple(info#493788, name, age), [id#493787, info#493788], false, [name#493789, age#493790]
                  +- *(1) Project [1 AS id#493787, {"name": "张三", "age": 18} AS info#493788]
                     +- *(1) Scan OneRowRelation[]

在Scan OneRowRelation生成行数据阶段就直接过滤掉id=2的数据不生成,后续只对id=1的数据行Generate json_tuple。

从两段sql的物理执行计划中可以看到,在lateral view、where同时存在时,实际执行中是先执行lateral view还是where,取决于where中的字段,spark会自动优化最合适的逻辑执行。无论先后,最终的结果都是一致的。

<think>嗯,用户想了解在Hive SQL中如何使用LATERAL VIEW和explode函数进行数据处理。我需要先回忆一下这些功能的作用和基本用法。 首先,LATERAL VIEW通常和explode一起使用,用来展开数组或者map类型的字段,将一行数据转换成多行。比如,如果有一个字段是数组,用explode可以把这个数组拆成多行,每行一个元素。这时候需要配合LATERAL VIEW来生成侧视图,从而在查询中引用这些展开后的数据。 然后,用户可能需要具体的示例来理解。比如,假设有一个用户表,其中有一个friends字段是数组类型,存储了用户的好友列表。使用LATERAL VIEW explode(friends)就可以把每个用户的好友拆分成单独的行,每个好友对应一行,同时保留用户的其他信息比如user_id。 接下来,要注意基本语法结构。正确的写法应该是SELECT ... FROM table LATERAL VIEW explode(字段) 别名 AS 列名。这里别名是虚拟表的名称,列名是展开后的列名。用户可能容易混淆别名和列名的位置,需要强调顺序。 另外,用户可能还会遇到需要处理空数组的情况。这时候普通的LATERAL VIEW在数组为空时不会生成任何行,可能导致用户的数据丢失。这时候应该使用LATERAL VIEW OUTER,类似于SQL中的外连接,即使数组为空,主表的记录仍然会保留,相关列显示为NULL。需要给出一个对比示例,说明两者的区别。 还需要提到explode函数的其他变种,比如posexplode,它可以同时返回元素和位置索引,这对需要知道元素在数组中位置的场景很有用。比如,用户可能需要知道好友的顺序,这时候可以用posexplode,并配合侧视图来获取索引值。 另外,多字段展开的情况。如果用户需要同时展开多个数组,需要注意数组的长度必须一致,否则会报错。这时候可能需要结合posexplode来确保元素一一对应,或者进行其他处理,比如过滤掉长度不一致的数据。 用户可能还关心性能方面的注意事项,比如数据倾斜问题。当某个数组特别大时,展开后的数据量会激增,可能导致任务运行缓慢。这时候需要提醒用户注意数据分布,或者考虑其他处理方式。 最后,结合引用中的内容,引用1提到了LATERAL VIEW和OUTER的区别,引用2和3提供了具体的示例,所以要在回答中适当引用这些资料,确保信息准确可靠。需要检查示例SQL的正确性,确保语法正确,比如字段名是否正确,别名是否正确使用等。 总结下来,回答的结构应该是先介绍基本概念,再给出基础示例,然后讨论不同情况下的用法(如空数组处理、posexplode、多字段展开),最后注意事项和常见问题。每个部分都要有对应的示例代码,并正确使用LATERAL VIEW和explode的语法。同时,引用相关的参考资料,帮助用户进一步查阅。</think>### HiveLATERAL VIEW与explode函数使用方法详解 #### 一、核心概念 1. **explode函数**:用于将数组(`array`)或映射(`map`)类型的字段展开为多行。例如,数组`[A,B,C]`会被拆分为三行,每行一个元素。 2. **LATERAL VIEW**:与`explode`配合使用,将展开后的结果作为虚拟表(侧视图)与主表进行关联,保留原始行其他字段[^1]。 --- #### 二、基础语法与示例 ##### 场景:用户好友列表展开 假设有表`user_friends`,字段为`user_id`(用户ID)和`friends`(好友列表数组): ```sql SELECT user_id, friend FROM user_friends LATERAL VIEW explode(friends) friends_table AS friend; ``` **输出结果**: ``` user_id | friend --------|------- 1001 | Alice 1001 | Bob 1002 | Carol ``` *说明*:每个用户的好友数组被展开为独立行,`friends_table`为虚拟表别名,`friend`为展开后的字段名[^3]。 --- #### 三、空数组处理:OUTER关键字 当数组为空时,`LATERAL VIEW`默认丢弃该行。使用`LATERAL VIEW OUTER`可保留主表记录: ```sql SELECT user_id, friend FROM user_friends LATERAL VIEW OUTER explode(friends) friends_table AS friend; ``` **输入数据**: ``` user_id | friends --------|-------- 1003 | [] -- 空数组 ``` **输出结果**: ``` user_id | friend --------|------- 1003 | NULL -- 保留主表行,friend显示为NULL ``` --- #### 四、进阶用法 ##### 1. 展开映射(Map)类型 若字段为`map<string, int>`(如用户技能评分): ```sql SELECT user_id, skill, score FROM user_skills LATERAL VIEW explode(skills_map) skills_table AS skill, score; ``` **输出**: ``` user_id | skill | score --------|---------|------- 2001 | SQL | 90 2001 | Python | 85 ``` ##### 2. 展开多字段数组(需长度一致) 同时展开两个数组`hobbies`和`levels`: ```sql SELECT user_id, hobby, level FROM user_profiles LATERAL VIEW explode(hobbies) hobbies_table AS hobby LATERAL VIEW explode(levels) levels_table AS level; ``` *注意*:若数组长度不同会导致报错,需确保数据一致性[^2]。 ##### 3. 获取数组元素位置(posexplode) 使用`posexplode`同时获取索引和值: ```sql SELECT user_id, pos, friend FROM user_friends LATERAL VIEW posexplode(friends) friends_table AS pos, friend; ``` **输出**: ``` user_id | pos | friend --------|-----|------- 1001 | 0 | Alice 1001 | 1 | Bob ``` --- #### 五、注意事项 1. **性能影响**:展开大型数组可能导致数据量激增,需关注内存和计算资源。 2. **别名冲突**:虚拟表别名和字段名需唯一,避免与主表字段重复。 3. **嵌套数据**:可配合`json_tuple`或`get_json_object`处理JSON字符串。 ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值