hive 的函式 lateral view 用法詳解 | 附pdf下載

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 操作了，因此原表資料會被完全保留下來。

好了 ，今天的總結就這樣了，以後相似的函式還會總結的，歡迎大家的訂閱。