【大数据技术】Hive 侧视图lateral view的几种写法分享

发布于：2023-02-09 ⋅ 阅读:(559) ⋅ 点赞:(0)

【大数据技术】Hive 侧视图lateral view的几种写法分享

第一种（单一lateral view）

select a.id 
          ,b.host as host 
          ,b.path as path 
          ,b.query as query 
from url_test a lateral view parse_url_tuple(a.url,'HOST','PATH','QUERY') b as host,path,query   ;

第二种（多重lateral view）

  select a.id 
          ,b.host as host 
          ,b.path as path 
          ,c.query as query 
          ,c.protocol as protocol
from url_test a lateral view parse_url_tuple(a.url,'HOST','PATH') b as host,path   
                lateral view parse_url_tuple(a.url,'PROTOCOL','QUERY') c as protocol ,query ;

在这里插入图片描述

第三种(侧视图为空的情况) !重要!
如下sql中array（）生成了一个空的数组，explode炸裂之后依然是空，故该返回的结果集是空，前面的id，name等字段也失效

select id 
      ,name 
      ,col1
from tb_url
         lateral view explode(array()) tmp as col1 ;

为避免出现上述id,name 等业务字段也连带失效的情况出现，在实际中应使用下述sql

select id 
      ,name 
      ,col1
from tb_url
         lateral view outer explode(array()) tmp as col1 ;