hive复合数据类型查表使用 以及控制语句 case when、if

–hive中的复合数据类型
–数组
–有如下数据
战狼2,吴京:吴刚:龙母,2017-08-16
三生三世十里桃花,刘亦菲:杨洋,2017-08-20
普罗米修斯,苍老师:小泽老师:波多老师,2017-09-17
–建表映射：

create table t_movie(movie_name string,actors array<string>,first_show date)
row format delimited fields terminated by ','
collection items terminated by ':';

–查询元素
select movie_name,actors[1],first_show from t_movie;
±------------±------±------------+
| movie_name | _c1 | first_show |
±------------±------±------------+
| 战狼2 | 吴刚 | 2017-08-16 |
| 三生三世十里桃花 | 杨洋 | 2017-08-20 |
| 普罗米修斯 | 小泽老师 | 2017-09-17 |

–查询包含字段

select movie_name,actors,first_show
from t_movie 
where array_contains(actors,'吴刚');

–查询每部电影有多少个演员

select movie_name,actors,first_show,size(actors) as actor_number
from t_movie;

–电影名字 主演数量 首映时间

select movie_name,size(actors) as actor_number,first_show
from t_movie;

二、–有如下数据

1,zhangsan,father:xiaoming#mother:xiaohuang#brother:xiaoxu,28
2,lisi,father:mayun#mother:huangyi#brother:guanyu,22
3,wangwu,father:wangjianlin#mother:ruhua#brother:jingtian,29
4,mayun,father:mayongzhen#mother:angelababy,26

建表映射

create table t_family(id int,name string,family_members map<string,string>,age int)
row format delimited fields terminated by ','
collection items terminated by '#'
map keys terminated by ':';

±-------------±---------------±---------------------------------------------------±------------- -+
| t_family.id | t_family.name | t_family.family_members | t_family.age |
±-------------±---------------±---------------------------------------------------±------------- -+
| 1 | zhangsan | {“father”:“xiaoming”,“mother”:“xiaohuang”,“brother”:“xiaoxu”} |