Hive练习题之炸裂函数(一)

数据源

Jack,male,eat-play,北京:海淀-上海:浦东
Miso,male,eat-net-play,河北:保定-北京:海淀
小明,male,eat-net,河北:张家口-上海:浦东
小红,male,net-play,河北:保定-北京:海淀
Lily,female,eat-net,河北:保定-北京:海淀
Lucy,female,play-eat,北京:海淀-上海:浦东
Jack,male,eat-net-play,河北:保定-北京:海淀
Miso,male,online-net-eat,河北:保定-北京:海淀

建表

create table test.tb_addr(
name string,
gender string,
likes array<string>,
address map<string,string>
)
row format delimited
fields terminated by ","
collection items terminated by "-"
map keys terminated by ":"
;

导入数据

load data local inpath "/doit16/user_addr.txt" into table test.tb_addr;

+---------------+-----------------+-------------------------+-------------------------+
| tb_addr.name  | tb_addr.gender  |      tb_addr.likes      |     tb_addr.address     |
+---------------+-----------------+-------------------------+-------------------------+
| Jack          | male            | ["eat","play"]          | {"北京":"海淀","上海":"浦东"}   |
| Miso          | male            | ["eat","net","play"]    | {"河北":"保定","北京":"海淀"}   |
| 小明          | male            | ["eat","net"]           | {"河北":"张家口","上海":"浦东"}  |
| 小红          | male            | ["net","play"]          | {"河北":"保定","北京":"海淀"}   |
| Lily          | female          | ["eat","net"]           | {"河北":"保定","北京":"海淀"}   |
| Lucy          | female          | ["play","eat"]          | {"北京":"海淀","上海":"浦东"}   |
| Jack          | male            | ["eat","net","play"]    | {"河北":"保定","北京":"海淀"}   |
| Miso          | male            | ["online","net","eat"]  | {"河北":"保定","北京":"海淀"}   |
+---------------+-----------------+-------------------------+-------------------------+

–对array使用炸裂函数

select
name,
gender,
lks
from tb_addr
lateral view explode(likes) likes as lks;

+-------+---------+---------+
| name  | gender  |   lks   |
+-------+---------+---------+
| Jack  | male    | eat     |
| Jack  | male    | play    |
| Miso  | male    | eat     |
| Miso  | male    | net     |
| Miso  | male    | play    |
| 小明  | male    | eat     |
| 小明  | male    | net     |
| 小红  | male    | net     |
| 小红  | male    | play    |
| Lily  | female  | eat     |
| Lily  | female  | net     |
| Lucy  | female  | play    |
| Lucy  | female  | eat     |
| Jack  | male    | eat     |
| Jack  | male    | net     |
| Jack  | male    | play    |
| Miso  | male    | online  |
| Miso  | male    | net     |
| Miso  | male    | eat     |
+-------+---------+---------+

对map使用炸裂函数

select
name,
gender,
addr1,
addr2
from tb_addr
lateral view explode(address) address as addr1,addr2;

+-------+---------+--------+--------+
| name  | gender  | addr1  | addr2  |
+-------+---------+--------+--------+
| Jack  | male    | 北京     | 海淀     |
| Jack  | male    | 上海     | 浦东     |
| Miso  | male    | 河北     | 保定     |
| Miso  | male    | 北京     | 海淀     |
| 小明  | male    | 河北     | 张家口   |
| 小明  | male    | 上海     | 浦东     |
| 小红  | male    | 河北     | 保定     |
| 小红  | male    | 北京     | 海淀     |
| Lily  | female  | 河北     | 保定     |
| Lily  | female  | 北京     | 海淀     |
| Lucy  | female  | 北京     | 海淀     |
| Lucy  | female  | 上海     | 浦东     |
| Jack  | male    | 河北     | 保定     |
| Jack  | male    | 北京     | 海淀     |
| Miso  | male    | 河北     | 保定     |
| Miso  | male    | 北京     | 海淀     |
+-------+---------+--------+--------+

对map的key值使用炸裂函数

select
name,
gender,
likes,
addr1
from tb_addr
lateral view explode(map_keys(address)) address as addr1;

+-------+---------+-------------------------+--------+
| name  | gender  |          likes          | addr1  |
+-------+---------+-------------------------+--------+
| Jack  | male    | ["eat","play"]          | 北京     |
| Jack  | male    | ["eat","play"]          | 上海     |
| Miso  | male    | ["eat","net","play"]    | 河北     |
| Miso  | male    | ["eat","net","play"]    | 北京     |
| 小明  | male    | ["eat","net"]           | 河北     |
| 小明  | male    | ["eat","net"]           | 上海     |
| 小红  | male    | ["net","play"]          | 河北     |
| 小红  | male    | ["net","play"]          | 北京     |
| Lily  | female  | ["eat","net"]           | 河北     |
| Lily  | female  | ["eat","net"]           | 北京     |
| Lucy  | female  | ["play","eat"]          | 北京     |
| Lucy  | female  | ["play","eat"]          | 上海     |
| Jack  | male    | ["eat","net","play"]    | 河北     |
| Jack  | male    | ["eat","net","play"]    | 北京     |
| Miso  | male    | ["online","net","eat"]  | 河北     |
| Miso  | male    | ["online","net","eat"]  | 北京     |
+-------+---------+-------------------------+--------+