hive的lateral view explode 功能

【自取】最近整理的,有需要可以领取学习:

最近遇到了一个神奇的蜂窝功能:侧视图爆炸,感觉与MySQL中的GROUP CONCAT相反,它将原始数据拆分成多行组成一个虚拟表,然后与原始表进行笛卡尔乘积。

[En]

Recently encountered a magical hive function: lateral view explode, which feels contrary to group concat in Mysql, splits the original data into multiple rows to form a virtual table, and then carries on Cartesian product with the original table.

一般模式:select column_A,column_B,tmp_table.tmp_column from db_name.test_tb lateral view explode(column_C) tmp_table as tmp_column;

column_A,column_B,column_C 都是原表 db_name.test_tb的列(字段);

tmp_table:explode形成的新虚拟表,可以不写;

tmp_column:explode形成的列(字段);

注意:分解用于处理数组或映射类型的数据,而不是字符串类型;但您可以通过诸如Split之类的函数将字符串转换为分解可以处理的格式。

[En]

Note: explode is used to handle data of type array or map, not of type string; but you can convert string to a format that explode can handle through functions such as split.

  1. 建表:
create table if not exists db_name.test_tb(id string,content string,comment string) row format delimited fields terminated by '\1' stored as textfile
  1. 插入几行数据:
insert into db_name.test_tb values('1','Tom,Bob,Andy','测试1,测试2,测试3')
insert into db_name.test_tb values('2','Jack,Vicent,Wendy','测试11,测试22,测试33')

hive的lateral view explode 功能
  1. explode:加或者不加as
select explode(split(content,',')) tmp_content from db_name.test_tb

select explode(split(content,',')) as tmp_content from db_name.test_tb

hive的lateral view explode 功能
  1. lateral view + explode:建立笛卡尔积
select id,ex_con,comment from db_name.test_tb lateral view explode(split(content,',')) tmp_content as ex_con

hive的lateral view explode 功能
  1. 通过表名.列名形式
select id,tmp_content.ex_con,comment from db_name.test_tb lateral view explode(split(content,',')) tmp_content as ex_con

hive的lateral view explode 功能
  1. 不添加虚拟表的表名
select id,content,ex_con,comment from db_name.test_tb lateral view explode(split(content,',')) as ex_con

hive的lateral view explode 功能
  1. 多列进行 lateral view
select id,content,ex_con,comment,ex_com from db_name.test_tb lateral view explode(split(content,',')) tmp_content as ex_con lateral view explode(split(comment,',')) tmp_comment as ex_com;

hive的lateral view explode 功能

笛卡尔乘积:每个ID将有9种情况,按上面的顺序列出。

[En]

Cartesian product: there will be 9 situations for each id, listed above in order.

这等效于在步骤6中侧向视图再次分解Comment列,将每个id的结果增加三倍(因为Comment有三个值)。

[En]

This is equivalent to lateral view explode the comment column again in step 6, increasing the result of each id by three times (because comment has three values).

https://zhuanlan.zhihu.com/p/115913870

https://blog.csdn.net/guodong2k/article/details/79459282

Original: https://www.cnblogs.com/qi-yuan-008/p/13584113.html
Author: 落日峡谷
Title: hive的lateral view explode 功能

原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/6867/

转载文章受原作者版权保护。转载请注明原作者出处!

(0)

大家都在看

发表回复

登录后才能评论
免费咨询
免费咨询
扫码关注
扫码关注
联系站长

站长Johngo!

大数据和算法重度研究者!

持续产出大数据、算法、LeetCode干货,以及业界好资源!

2022012703491714

微信来撩,免费咨询:xiaozhu_tec

分享本页
返回顶部