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)

大家都在看

最近整理资源【免费获取】:   👉 程序员最新必读书单  | 👏 互联网各方向面试题下载 | ✌️计算机核心资源汇总