hive 创建多表关联

1，新建数据库

,1 

2，建表

create table country(cid int,cname string)  row format delimited fields terminated by ",";
create table position(pid int,pname string)  row format delimited fields terminated by ",";
create table Gender(gid int,gname string)  row format delimited fields terminated by ",";

create table students(
 sid int,
 sname string,
 country string,
 position string,
 Gender string,
 countryId int,
 positionId int,
 GenderId int,
 Capability int
 ) row format delimited fields terminated by ",";  

3，上传数据

students
1,曹操,魏,top,male,1,1,1,90
2,刘备,蜀,jungle,male,2,2,1,89
3,孙策,吴,top,male,3,1,1,93
4,孙尚香,吴,adc,female,3,4,2,92
5,诸葛亮,蜀,mid,male,2,3,1,94
6,周瑜,吴,mid,male,3,3,1,90
7,黄忠,蜀,adc,male,2,4,1,93
8,赵云,蜀,jungle,male,2,2,1,90
9,关羽,蜀,top,male,2,1,1,95
10,张飞,蜀,sup,male,2,5,1,88
11,大乔,吴,sup,female,3,5,2,85
12,小乔,吴,mid,female,3,3,2,90
13,貂蝉,魏,mid,female,1,3,2,92
14,吕布,魏,top,male,1,1,1,94
15,甄姬,魏,mid,female,1,3,2,90
16,典韦,魏,jungle,male,1,2,1,95
17,马超,蜀,jungle,male,2,2,1,95
18,蔡文姬,魏,sup,female,1,5,2,84
19,夏侯惇,魏,top,male,1,1,1,87
20,甄姬,魏,mid,female,1,3,2,90
 
 country
1,魏
2,蜀
3,吴
 
 position
1,top
2,jungle
3,mid
4,adc
5,sup
 
 Gender
1,male
2,female

准备数据： 

 

上传： 

 

在hive里面主键的概念明显是模糊了的甚至说是去掉的，更没有想传统数据仓库的时候，事实表和维表的键可以连在一起的情况一般都是直接在hive-ql的时候用jion的，即在条件判断的时候加上a.id=b.id