hive小练习_联合查询
1 创建2张表 用户表User和会员表 vip
2 user表中字段如下
3 uid 身份证号 uid int comment 'UserCard'
4 name 姓名
5 phone 电话
6 sex 性别
7 salary 工薪
8 address 地址
9
10 create table user(
11 uid bigint comment 'UserCard',
12 name string comment 'UserName',
13 phone bigint comment 'PhoneNum',
14 sex string comment 'UserSex',
15 salary float comment 'UserSalary',
16 address string comment 'UserAddress'
17 )row format delimited fields terminated by ' ';
18
19 数据
20 41303116465465564 张三 15838544449 男 30000.0 北京
21 41303116213215564 李四 158383213149 男 2000.0 北京
22 41303152323365564 王五 15838544449 男 50000.0 北京
23 41305555544465564 赵六 15838544449 男 4000.0 北京
24 41306666666666564 田七 15838544449 男 8000.0 北京
25 41305555544465587 韩梅梅 15838555449 女 6000.0 郑州
26 41306666666666589 田七 1583854466 女 7000.0 洛阳
27
28
29
30 vip表中字段如下
31 vid vip编号
32 vname vip名
33 uid 身份证号
34
35 create table vip(
36 vid int,
37 vname string,
38 uid string
39 )row format delimited fields terminated by ' ';
40
41
42 1001 老张 41305555544465564
43 1002 Mvp刘 41306666666666564
44 1003 MZ 41303152323365564
45 1004 皮皮狗 41303116465465564
46 1005 朝花夕拾 654651313215646
47 1006 血色残阳 165465554465131x
48
49
50 1.向两张表中插入5行数据
51 hadoop fs -put user.txt /user/hive/warehouse/db_1904.db/user
52 hadoop fs -put vip.txt /user/hive/warehouse/db_1904.db/vip
53
54 2.查询办理会员的用户所有信息
55 select * from user u inner join vip v on u.uid=v.uid;
56
57 3.查询办理会员的用户姓名 电话 地址和vip名称
58 select u.name,u.phone,u.address,v.vname from user u inner join vip v on u.uid=v.uid;
59
60 4.查询出左表中所有数据再与右表相匹配(左外连接)
61 select * from user u left outer join vip v on u.uid=v.uid;
62
63 5.右外连接（右表数据完全显示，让左表与其连接没有匹配项使用null）
64 select * from user u right outer join vip v on u.uid=v.uid;
65
66 6.全外连接
67 select * from user u full join vip v on u.uid=v.uid;
68
69 7.左半连接(只获取与右边等值连接条件匹配的数据，不显示右表 如果重复会去除重复相当于map中的key)
70 select * from user u left semi join vip v on u.uid=v.uid;
71
72 8.order by 排序 （全局排序，整个过程由一个reduce来完成）
73 select * from user order by sex,salary;
74
75 9.sort by排序 （局部排序，整个过程中可以是多个reduce来完成 输出结果可能会有重合）
76 select * from user sort by sex,salary;
77
78 班中分为男女生 按照性别排 再按照各自的成绩排序
79
80 10.distribute by 和 sort by 语句 （distribute by +分区列 sort by +排序列A，排序列B 先后有影响）
81 可以实现2次排序，类似与MR中partition
82 select name,sex,salary from user distribute by sex sort by sex,salary desc;
83
84 11.cluster by 可以代替distribute by 和 sort by 语句
85 select name,sex,salary from user cluster by sex,salary;
86
87 需求：
88 1.获取User表中的总人数
89 select count(uid) from user;
90
91 2.获取User表中工资最高的人信息
92 select * from user order by salary desc limit 1;
93
94 3.获取User表中工资最低的人信息
95 select * from user order by salary asc limit 1;
96
97 4.获取平均工资
98 select round(avg(salary),2) from user;
99
100 5.获取当月需要发放的总工资
101 select sum(salary) from user;
102
103 将10的结果导出到mysql表中（hive导出数据到mysql）
104
105 1.将10的结果复制到新表
106 create table user_sex_salary row format delimited fields terminated by '\t' as
107 select name,sex,salary from user distribute by sex sort by sex,salary desc;
108
109 2.mysql创建表接受数据
110 create table user(
111 name varchar(50),
112 sex varchar(50),
113 salary float
114 );
115
116 3.使用sqoop导出
117 sqoop export \
118 --connect jdbc:mysql://localhost:3306/lpy"?useUnicode=true&characterEncoding=utf-8" \
119 --username root \
120 --password 123456 \
121 --table user \
122 --export-dir '/user/hive/warehouse/db_1904.db/user_sex_salary/000000_0' \
123 --input-fields-terminated-by '\t' \
124 -m 1;
总结：
1.联合查询
内连接
语法： select * from 表A inner join 表B on 等值连接条件;
查询结果为条件相等的左表和右表中的数据
左外连接
语法： select * from 表A left join 表B on 等值连接条件;
查询左表的所有数据跟右表匹配,匹配不到的数据用NUll占用
右外连接
语法： select * from 表A right join 表B on 等值连接条件;
查询右表的所有数据跟左表匹配,匹配不到的数据用NUll占用
全外连接
语法： select * from 表A full join 表B on 等值连接条件;
结果集会包含左表和右表的所有记录
左半连接
语法： select * from 表A left semi join 表B on 等值连接条件;
只获取与右边等值连接条件匹配的数据，不显示右表 如果重复会去除重复相当于map中的key
