文档地址：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会自动优化最合适的逻辑执行。无论先后，最终的结果都是一致的。

# hive # hadoop
Logo
开放原子开发者工作坊

开放原子开发者工作坊旨在鼓励更多人参与开源活动，与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动，如meetup、训练营等，主打技术交流，干货满满，真诚地邀请各位开发者共同参与！

更多推荐

新华网：开源盛会在江城——2024开放原子开发者大会侧记

开源盛会在江城——2024开放原子开发者大会侧记

avatar 开放原子开发者工作坊

新华社：释放开源潜能，加快构筑软件创新“朋友圈”

释放开源潜能，加快构筑软件创新“朋友圈”

avatar 开放原子开发者工作坊

开源鸿蒙：引领万物智联，加速生态崛起

开源鸿蒙：引领万物智联，加速生态崛起

avatar 开放原子开发者工作坊