hive 的函数 lateral view 用法详解

select good_bey_2023,hello_2024
 
from newyear 
lateral view posexplode(split('hive, spark,flink ,line,line,so,easy',',')) t as lan_id_index, good_bey_2023
 
lateral view posexplode(split('date,todate,firstday,day,day,no,bug',',')) t as day_index, hello_2024
where lan_id_index = day_index;

预览结果应该是

这里用到了hive 的 lateral view 功能，这篇文章只要介绍一下这个函数。

lateral view 简介

hive函数 lateral view 主要功能是将原本汇总在一条（行）的数据拆分成多条（行）成虚拟表，再与原表进行笛卡尔积，从而得到明细表。配合UDTF函数使用，一般情况下经常与explode函数搭配，explode的操作对象（列值）是 ARRAY 或者 MAP ,可以通过 split 函数将 String 类型的列值转成 ARRAY 来处理。

select col_A,col_B,tmp_table.tmp_col 
from table_name 
lateral view explode(split(col,'分隔符')) tmp_table as tmp_col
 

使用实例

转成多行

我朋友圈的代码就是栗子：

select good_bey_2023,hello_2024
 
from newyear 
lateral view posexplode(split('hive, spark,flink ,line,line,so,easy',',')) t as lan_id_index, good_bey_2023
 
lateral view posexplode(split('date,todate,firstday,day,day,no,bug',',')) t as day_index, hello_2024
where lan_id_index = day_index;

预览结果应该是

汇总求和

select good_bey_2023,count(hello_2024) hello_2024
 
from newyear 
lateral view posexplode(split('hive, spark,flink ,line,line,so,easy',',')) t as lan_id_index, good_bey_2023
 
lateral view posexplode(split('date,todate,firstday,day,day,no,bug',',')) t as day_index, hello_2024
where lan_id_index = day_index
group by good_bey_2023;

explode(x)和posexplode()

explode(x)和posexplode() 均为炸裂函数，区别在于explode炸出一个值，posexplode不仅炸出一个值还附带索引号；

如何产生1-100的连续的数字？

方法一：结合space函数与split函数，posexplode函数，lateral view函数获得

select
id_start+pos as id
from(
    select
    1 as id_start,
    100 as id_end
) m  lateral view posexplode(split(space(id_end-id_start), '')) t as pos, val
 

方法二：

select
  row_number() over() as id
from  
  (select split(space(99), ' ') as x) t
lateral view
explode(x) ex;
 

如何产生开始日期到结束日期的连续的日期？

 SELECT 
 DATE_ADD(START_DATE, pos)
FROM (
 SELECT DISTINCT
  "2023-03-13" AS START_DATE,
  "2023-03-22" AS END_DATE
   from order_detail
) s1 lateral VIEW posexplode(split(SPACE(DATEDIFF(END_DATE, START_DATE)), " ")) s2 AS pos, null_ele
 

lateral view json_tuple(转成多列)

lateral view json_tuple 函数解析非结构化的json数据类型

工作中遇到一个数据表的存储形式，如下：

其中col1，col2都是string类型，存放的是JSON格式的数据，JSON的key分别是：

使用lateral view json_tuple函数 从两列中分别选出part3,part4, to_part3,to_part4的key对应的数据值：

--使用lateral VIEW json_tuple函数解析数据
SELECT
    id,
    to_part3,
    to_part4,
    IF(part3=0,0.0, to_part3/part3) as ratio3,
    IF(part4=0,0.0, to_part4/part4) as ratio4
FROM
 {table_name}
lateral VIEW json_tuple(col1, 'part3', 'part4') json1 AS part3,part4 
lateral VIEW json_tuple(col2, 'to_part3', 'to_part4') json2 AS to_part3,to_part4
WHERE
 ...

lateral view json_tuple VS lateral view explode

之前的文章lateral view explode函数解析非结构化的map数据类型 介绍了使用explode ,  lateral view explode 函数来解析Map类型数据的key, value的应用。

初看下这两个例子很像，那么为什么这里使用later view json_tuple 而不是使用later view explode函数呢？

如果使用later view explode函数能不能达成想要的效果呢？

这里的关键点就是数据结构了。

本文中的例子，col1,col2数据类型是JSON，key是固定的，每条数据都有相同的key，即使这个key对应的值是0，也会有记录。

而上文中的例子，业务场景不一样，col1 、 col2的key不是固定的，数据类型是MAP。

这是跟随实际应用场景而选择的数据存储类型。

比如本文中，场景类型有限，就是(part1, part2, part3, part4,to_part2, to_part3, to_part4, to_part34 )这几类，所以使用JSON的形式，穷举key来保存数据是合适的。

在电商业务中，广告触点类型非常的多(多到成百上千)，而一个用户进入电商网站，实际接触到的广告触点类型却是很少的(几个到几十个)，这时候如果还用JSON类型穷举所有广告触点的key，就会发现大量key的值是0，这是一个稀疏数据，这是很浪费空间的。所以，这种情况下一般采用MAP数据类型，只保留有实际意义的key和对应的值。

所以，使用later view explode函数能通过将每条数据拆分成key、value的形式来使用。

而如果使用later view json_tuple函数的话，如果在一条数据中没有指定想要的key，那么就会报错失败了。

outer lateral view

later view 前面还可以加上一个 outer 关键字，这是为了避免 当udtf 没有得到任何结果时最终虚拟结果表里丢失原数据行的问题。具体来将，由于later view 的工作原理是将原表与 udtf 产生的虚拟表做 inner join 操作，所以如果 udtf 不产生任何结果时，那么对应原表的那一行也会在 inner join 操作后消失。outer关键字就是来解决这个问题的，加上这个关键字之后执行的就是 outer join 操作了，因此原表数据会被完全保留下来。