hive测试题_hive考试题

第一题：分区分桶测试

以下数据的每一列分别表示：

sid  name  gender  age  academy  dt  chinese math english 
95001	李勇	男	20	CS	2017-08-31	56	28	62
95002	刘晨	女	19	IS	2017-08-31	84	22	38
95003	王敏	女	22	MA	2017-08-31	38	80	82
95004	张立	男	19	IS	2017-08-31	39	65	4
95005	刘刚	男	18	MA	2018-08-31	18	28	79
95006	孙庆	男	23	CS	2018-08-31	12	67	16
95007	易思玲	女	19	MA	2018-08-31	27	60	84
95008	李娜	女	18	CS	2018-08-31	12	12	82
95009	梦圆圆	女	18	MA	2018-08-31	62	35	94
95010	孔小涛	男	19	CS	2017-08-31	82	97	4
95011	包小柏	男	18	MA	2019-08-31	41	53	71
95012	孙花	女	20	CS	2017-08-31	90	51	79
95013	冯伟	男	21	CS	2019-08-31	20	69	86
95014	王小丽	女	19	CS	2017-08-31	83	64	60
95015	王君	男	18	MA	2019-08-31	39	48	29
95016	钱国	男	21	MA	2019-08-31	94	7	69
95017	王风娟	女	18	IS	2019-08-31	87	56	54
95018	王一啊	女	19	IS	2019-08-31	54	36	24
95019	邢小丽	女	19	IS	2018-08-31	78	9	82
95020	赵钱	男	21	IS	2019-08-31	4	48	87
95021	周二	男	17	MA	2018-08-31	84	61	16
95022	郑明	男	20	MA	2018-08-31	64	70	90
95023	李小勇	男	20	CS	2017-08-31	52	21	19
95024	刘小晨	女	19	IS	2017-08-31	1	16	76
95025	王小敏	女	22	MA	2017-08-31	13	89	98
95026	张小立	男	19	IS	2017-08-31	85	84	80
95027	刘小刚	男	18	MA	2018-08-31	7	17	48
95028	孙小庆	男	23	CS	2018-08-31	17	94	83
95029	易小思玲	女	19	MA	2018-08-31	69	84	23
95030	李小娜	女	18	CS	2018-08-31	16	70	70
95031	梦小圆	女	18	MA	2018-08-31	82	48	37
95032	孔涛	男	19	CS	2017-08-31	83	80	12
95033	包柏	男	18	MA	2019-08-31	18	16	71
95034	孙小花	女	20	CS	2017-08-31	36	95	93
95035	冯小伟	男	21	CS	2019-08-31	31	19	13
95036	王丽	女	19	CS	2017-08-31	16	42	9
95037	王小君	男	18	MA	2019-08-31	91	3	67
95038	钱小国	男	21	MA	2019-08-31	89	59	18
95039	王娟	女	18	IS	2019-08-31	7	17	67
95040	王小一	女	19	IS	2019-08-31	49	32	45
95041	邢丽	女	19	IS	2018-08-31	30	40	40
95042	赵小钱	男	21	IS	2019-08-31	71	69	57
95043	周小二	男	17	MA	2018-08-31	18	9	1
95044	郑一明	男	20	MA	2018-08-31	64	3	44
95045	张一勇	男	20	CS	2017-08-31	10	65	97
95046	刘一丽	女	19	IS	2017-08-31	56	8	45
95047	张一敏	女	22	MA	2017-08-31	19	8	98
95048	张一立	男	19	IS	2017-08-31	49	39	92
95049	刘一刚	男	18	MA	2018-08-31	4	79	91
95050	孙一庆	男	23	CS	2018-08-31	99	71	61
95051	易一玲	女	19	MA	2018-08-31	49	53	71
95052	李一	女	18	CS	2018-08-31	22	87	79
95053	小一	女	18	MA	2018-08-31	53	58	35
95054	孔一	男	19	CS	2017-08-31	30	62	5
95055	包一	男	18	MA	2019-08-31	73	68	28
95056	孙一庆	女	20	CS	2017-08-31	68	22	33
95057	冯一	男	21	CS	2019-08-31	91	26	46
95058	王一二	女	19	CS	2017-08-31	49	81	51
95059	王一三	男	18	MA	2019-08-31	56	27	49
95060	钱一	男	21	MA	2019-08-31	97	40	24
95061	王小娟	女	18	IS	2019-08-31	96	79	34
95062	王小一	女	19	IS	2019-08-31	87	6	55
95063	邢丽	女	19	IS	2018-08-31	56	19	33
95064	赵小钱	男	21	IS	2019-08-31	70	17	85
95065	周小二	男	17	MA	2018-08-31	66	19	14
95066	郑小明	男	20	MA	2018-08-31	99	95	14
95067	李勇	男	19	CS	2017-08-31	48	40	13
95068	刘晨	女	19	IS	2017-08-31	86	28	46
95069	王敏	女	21	MA	2017-08-31	91	59	0
95070	张立	男	17	IS	2017-08-31	99	39	64
95071	刘刚	男	20	MA	2018-08-31	73	2	64
95072	孙庆	男	19	CS	2018-08-31	90	71	5
95073	易思玲	女	19	MA	2018-08-31	72	87	25
95074	李娜	女	21	CS	2018-08-31	92	23	65
95075	梦圆圆	女	17	MA	2018-08-31	37	34	5
95076	孔小涛	男	20	CS	2017-08-31	15	37	40
95077	包小柏	男	19	MA	2019-08-31	29	82	30
95078	孙花	女	19	CS	2017-08-31	6	27	75
95079	冯伟	男	21	CS	2019-08-31	4	10	29
95080	王小丽	女	17	CS	2017-08-31	70	70	36
95081	王君	男	20	MA	2019-08-31	20	74	31
95082	钱国	男	19	MA	2019-08-31	59	85	39
95083	王风娟	女	19	IS	2019-08-31	6	98	95
95084	王一	女	21	IS	2019-08-31	16	26	27
95085	邢小丽	女	17	IS	2018-08-31	73	28	4
95086	赵钱	男	20	IS	2019-08-31	69	52	59
95087	周二	男	19	MA	2018-08-31	29	40	10
95088	郑明	男	18	MA	2018-08-31	77	13	91
95089	李勇	男	23	CS	2017-08-31	49	22	8
95090	刘晨	女	19	IS	2017-08-31	12	18	25
95091	王敏	女	18	MA	2017-08-31	44	94	47
95092	张立	男	18	IS	2017-08-31	71	45	17
95093	刘刚	男	19	MA	2018-08-31	27	90	50
95094	孙庆	男	18	CS	2018-08-31	92	34	75
95095	易思玲	女	20	MA	2018-08-31	38	63	24
95096	李娜	女	21	CS	2018-08-31	94	66	37
95097	梦圆圆	女	19	MA	2018-08-31	91	35	45
95098	孔小涛	男	18	CS	2017-08-31	9	61	0
95099	包小柏	男	19	MA	2019-08-31	93	66	25
95100	孙花	女	18	CS	2017-08-31	47	22	27
95101	冯伟	男	23	CS	2019-08-31	23	99	62
95102	王小丽	女	19	CS	2017-08-31	6	34	3
95103	王君	男	18	MA	2019-08-31	0	7	8
95104	钱国	男	18	MA	2019-08-31	93	2	35
95105	王风	女	19	IS	2019-08-31	12	84	50
95106	王一	女	18	IS	2019-08-31	29	60	86
95107	邢小	女	20	IS	2018-08-31	50	51	6
95108	赵钱	男	21	IS	2019-08-31	93	54	86
95109	周二	男	19	MA	2018-08-31	57	39	93
95110	郑明	男	18	MA	2018-08-31	30	78	62
95111	张勇	男	19	CS	2017-08-31	25	5	19
95112	刘丽	女	18	IS	2017-08-31	74	96	44
95113	张敏	女	23	MA	2017-08-31	64	17	82
95114	张小	男	19	IS	2017-08-31	14	57	69
95115	刘小	男	18	MA	2018-08-31	37	84	29
95116	孙小	男	18	CS	2018-08-31	7	99	26
95117	易小	女	19	MA	2018-08-31	60	97	26
95118	李小	女	18	CS	2018-08-31	57	8	88
95119	小小	女	20	MA	2018-08-31	7	97	64
95120	孔大	男	21	CS	2017-08-31	33	27	85
95121	包大	男	19	MA	2019-08-31	96	2	38
95122	孙小	女	18	CS	2017-08-31	54	94	23
95123	冯小伟	男	19	CS	2019-08-31	26	45	1
95124	王小丽	女	18	CS	2017-08-31	65	77	43
95125	王小君	男	23	MA	2019-08-31	24	22	34
95126	钱小国	男	19	MA	2019-08-31	98	98	2
95127	王小娟	女	18	IS	2019-08-31	36	66	30
95128	王小一	女	18	IS	2019-08-31	98	78	15
95129	邢丽	女	19	IS	2018-08-31	41	41	52
95130	赵小钱	男	18	IS	2019-08-31	87	65	93
95131	周小二	男	20	MA	2018-08-31	26	56	48
95132	郑小明	男	21	MA	2018-08-31	31	96	60
95133	李勇	男	19	CS	2017-08-31	26	53	78
95134	刘晨	女	18	IS	2017-08-31	60	27	11
95135	王敏	女	19	MA	2017-08-31	63	25	60
95136	张立	男	18	IS	2017-08-31	46	86	77
95137	刘刚	男	23	MA	2018-08-31	74	6	48
95138	孙庆	男	19	CS	2018-08-31	18	66	20
95139	易思玲	女	18	MA	2018-08-31	72	87	44
95140	李娜	女	18	CS	2018-08-31	12	68	55
95141	梦圆圆	女	19	MA	2018-08-31	63	70	29
95142	孔小涛	男	18	CS	2017-08-31	14	0	15
95143	包小柏	男	20	MA	2019-08-31	29	78	3
95144	孙花	女	21	CS	2017-08-31	1	21	86
95145	冯d伟	男	19	CS	2019-08-31	51	86	85
95146	王3丽	女	18	CS	2017-08-31	88	33	11
95147	王d君	男	23	MA	2019-08-31	61	98	29
95148	钱1国	男	19	MA	2019-08-31	88	65	12
95149	王2娟	女	18	IS	2019-08-31	30	64	41
95150	王B一	女	18	IS	2019-08-31	16	93	10
95151	邢A丽	女	19	IS	2018-08-31	58	20	24
95152	赵钱D	男	18	IS	2019-08-31	77	48	92
95153	周二C	男	20	MA	2018-08-31	89	7	60
95154	郑明B	男	21	MA	2018-08-31	18	76	59
95155	李勇A	男	19	CS	2017-08-31	53	35	49
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156

将上面的数据存储到一个分区分桶表中。要求：分区按照学院和入学日期分区，按照sid分四个桶。

1、创建一个普通的外部表student_emp，并加载以上数据

drop table student_emp;
create external table student_emp(
sid int,
name string,
gender string,
age int,
academy string,
dt date,
chinese int,
math int,
english int
)
row format delimited fields terminated by '\t';

load data local inpath './data/student_emp.txt' into table student_emp;


select * from student_emp;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

2、分区分桶表student的建表语句

create table student_part(
sid int,
name string,
gender string,
age int,
chinese int,
math int,
english int
)
partitioned by (academy string,dt date)
clustered by (sid) sorted by (sid) into 4 buckets
row format delimited fields terminated by '\t';
1
2
3
4
5
6
7
8
9
10
11
12

3、动态导入语句

insert into student_part partition(acdemy,dt) select sid,name,gender,age,chinese,math,english,
academy,dt
from student_emp;
1
2
3

4、查询每个学院的每年入学总成绩排名的前五名的学生信息：连续，重复效果

-- 总分
select sid,name,academy,chinese+math+english sum from student_emp;

-- 排名
select *,dense_rank() over(distribute by academy sort by sum desc) rk from
(select sid,name,academy,chinese+math+english sum from student_emp) A;

-- 前5 
select * from
(select *,dense_rank() over(distribute by academy sort by sum desc) rk from
(select sid,name,academy,chinese+math+english sum from student_emp) A) B
where B.rk<=5;
1
2
3
4
5
6
7
8
9
10
11
12

第二题：三表交集

a_test、b_test、c_test三个hive表 每个表中都只有一列int类型且列名相同，求三个表中互不重复的数
a.txt	b.txt	c.txt
1		2		1
2		3		2
3		11		3
4		12		11
5		14		5
6		15		6
7		16		7
8		18		8
9		35		20
		6		30
		7		40
		8
   
a.txt
1
2
3
4
5
6
7
8
9

b.txt
3
11
12
14
5
16
18
35
6
7
8

c.txt
1
2
3
11
5
6
7
8
20
30
40

drop table a_test;
create table a_test(
id int
);
load data local inpath './data/a.txt' into table a_test;

drop table b_test;
create table b_test(
id int
);
load data local inpath './data/b.txt' into table b_test;

drop table c_test;
create table c_test(
id int
);
load data local inpath './data/c.txt' into table c_test;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69

答案粘贴处：

-- 用 union all 来去重连接三张表，统计出现次数为1的数 
select A.id
from 
(select id from a_test union all
select id from b_test union all
select id from c_test) A
group by A.id having count(A.id)=1;
1
2
3
4
5
6
7

运行结果

OK
id
4
9
12
14
16
18
20
30
35
40
Time taken: 15.319 seconds, Fetched: 10 row(s)
1
2
3
4
5
6
7
8
9
10
11
12
13

第三题：hql 行转列

数据： 
content_test表： 
uid contents
1 i|love|china
2 china|is|good|i|i|like

需求：词频统计。统计结果如下,如果出现次数一样，则按照content名称排序： 
content cnt
i 3
china 2
good 1
like 1
love 1
is 1

create table content_test(
uid int,
contents string
)
row format delimited fields terminated by ' ';
load data local inpath './data/content_test' into table content_test;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

答案粘贴处：

-- 1) 展开
select content from content_test lateral view explode(split(contents,"\\|")) t as content;

-- 2) 计数排序
select content,count(content) cnt from
(select content from content_test lateral view explode(split(contents,"\\|")) t as content) A
group by content
order by cnt desc;
1
2
3
4
5
6
7
8

运行结果

-- 1) 展开
OK
content
i
love
china
china
is
good
i
i
like
Time taken: 0.085 seconds, Fetched: 9 row(s)

-- 2) 计数排序
OK
content cnt
i       3
china   2
love    1
like    1
is      1
good    1
Time taken: 16.155 seconds, Fetched: 6 row(s)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

第四题：求出连续七天登陆的总人数

需求：求出连续七天登陆的总人数

数据: login_test表
uid  dt  login_status(1登录成功,0异常)
1	2019-07-11	1
1	2019-07-12	1
1	2019-07-13	1
1	2019-07-14	1
1	2019-07-15	1
1	2019-07-16	1
1	2019-07-17	1
1	2019-07-18	1
2	2019-07-11	1
2	2019-07-12	1
2	2019-07-13	0
2	2019-07-14	1
2	2019-07-15	1
2	2019-07-16	0
2	2019-07-17	1
2	2019-07-18	0
3	2019-07-11	1
3	2019-07-12	1
3	2019-07-13	1
3	2019-07-14	1
3	2019-07-15	1
3	2019-07-16	1
3	2019-07-17	1
3	2019-07-18	1

create table login_test(
uid int,
dt string,
login_status int
) 
row format delimited fields terminated by '\t';
load data local inpath './data/login_test' into table login_test;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

答案粘贴处：

-- 1) 筛选登录成功的日期
select uid,dt,row_number() over(partition by uid order by dt) from login_test
where login_status=1;

-- 2) date_sub日期函数 当前日期减去num天数
select t1.uid,date_sub(t1.dt,t1.num) dt from
(select uid,dt,row_number() over(distribute by uid sort by dt) num
from login_test where login_status=1) t1;

-- 3) dt分组计数 count() 条件大于7的用户数
select uid,dt from
(select t1.uid,date_sub(t1.dt,t1.num) dt from
(select uid,dt,row_number() over(distribute by uid sort by dt) num
from login_test where login_status=1) t1) t2
group by uid,dt
having count(uid)>7;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

运行结果

-- 1) 筛选登录成功的日期
OK
uid     dt      c2
1       2019-07-11      1
1       2019-07-12      2
1       2019-07-13      3
1       2019-07-14      4
1       2019-07-15      5
1       2019-07-16      6
1       2019-07-17      7
1       2019-07-18      8
2       2019-07-11      1
2       2019-07-12      2
2       2019-07-14      3
2       2019-07-15      4
2       2019-07-17      5
3       2019-07-11      1
3       2019-07-12      2
3       2019-07-13      3
3       2019-07-14      4
3       2019-07-15      5
3       2019-07-16      6
3       2019-07-17      7
3       2019-07-18      8
Time taken: 11.333 seconds, Fetched: 21 row(s)

-- 2) date_sub日期函数 当前日期减去num天数
OK
uid     dt
1       2019-07-10
1       2019-07-10
1       2019-07-10
1       2019-07-10
1       2019-07-10
1       2019-07-10
1       2019-07-10
1       2019-07-10
2       2019-07-10
2       2019-07-10
2       2019-07-11
2       2019-07-11
2       2019-07-12
3       2019-07-10
3       2019-07-10
3       2019-07-10
3       2019-07-10
3       2019-07-10
3       2019-07-10
3       2019-07-10
3       2019-07-10
Time taken: 6.713 seconds, Fetched: 21 row(s)

-- 3) dt分组计数 count() 条件大于7的用户数
OK
uid     dt
1       2019-07-10
3       2019-07-10
Time taken: 13.285 seconds, Fetched: 2 row(s)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58