Hive.三表连接

1.数据(g.txt)

1001	g1
1002	g2
1003	g3

2.数据(gu.txt)

1001	10002,10001,10003,10009
1002	10004,10005,10006
1003	10007,10008,10010

3.数据(u.txt)

10001	u1
10002	u2
10003	u3
10004	u4
10005	u5
10006	u6
10007	u7
10008	u8
10009	u9
10010	u10

4.要求
g.txt存储组表
u.txt存储用户表
gu.txt权限表
组表gt中记录了组的信息组id和组名称，用户表记录了用户基本信息用户id和用户名称，gu是组表和用户表的关系，记录了每一个组内与用户对应关系，其中仅记录id信息。题目是根据gt和ut表将gu表中的所有id转换为名称

5.语句

1.create external table g(gid int,gname string) row format delimited fields terminated by '\t' stored as textfile;
2.create external table u(uid int,uname string) row format delimited fields terminated by '\t' stored as textfile;
3.create external table gu(gid int,uid string) row format delimited fields terminated by '\t' stored as textfile;
4.load data local inpath "/home/hadoop/g.txt" into table g;
5.load data local inpath "/home/hadoop/u.txt" into table u;
6.load data local inpath "/home/hadoop/gu.txt" into table gu;
7.create external table temp as select gid,s_uid from gu lateral view explode(split(uid,',')) uid as s_uid;
8.create external table t select g.gname,u.uname from temp,g,u where temp.gid=g.gid and temp.s_uid=u.uid;
9.create external table result select t.gname,concat_ws(',',collect_set(t.uname)) from t group by t.gname;