Hive常见语句区别

1、hive中的order by，sort by，distribut by，cluster by及其区别

转载博客：https://www.cnblogs.com/dcx-1993/p/11041228.html

hive 查询语法

1
2
3
4
5
6
7
select [all | distinct] select_ condition, select_ condition
from table_name a
[join table_other b on a.id=b.id]
[where wehre_condition]
[group by col_list [having condition]]
[cluster by col_list | [distribute by col_list] [sort by col_list | order by col_list]]
[limit number]

准备数据：

1
2
3
create table if not exists stu_test(id int,name string,sex string,age int)
row format delimited fields terminated by ','
;　
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
insert into stu_test values
(1,'zs','m',18)
,(2,'ls','m',19)
,(3,'ww','m',20)
,(4,'zq','f',18)
,(5,'ll','f',21)
,(6,'hl','f',19)
,(7,'xh','f',20)
,(8,'cl','f',22)
,(9,'fj','m',19)
,(10,'wb','m',23)
,(11,'wf','f',24)
,(12,'jj','m',21)
,(13,'yy','m',20)
,(14,'ld','f',18)
,(15,'ch','f',22)
;

1.order by col_list:

　　排序 全局排序 默认为升序asc ,因此只有一个reducer,只有一个reduce task的结果，比如文件名是000000_0,会导致当输入规模较大时，需要较长的计算时间。
　　如果指定了hive.mapred.mode=strict（默认值是nonstrict）,这时就必须指定limit来限制输出条数，原因是：所有的数据都会在同一个reducer端进行，数据量大的情况下可能不能出结果，那么在这样的严格模式下，必须指定输出的条数。

例如：stu:按照年龄排序

1
select * from stu_test order by age desc;

结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
id name sex age
11 wf f 24
10 wb m 23
15 ch f 22
8  cl f 22
5  ll f 21
12 jj m 21
13 yy m 20
7  xh f 20
3  ww m 20
9  fj m 19
6  hl f 19
2  ls m 19
14 ld f 18
1  zs m 18
4  zq f 18

2.sort by col_list :

　　局部排序，其在数据进入reducer前完成排序。因此，如果用sort by 进行排序，并且设置mapred.reduce.tasks>1,则sort by 只保证每个reducer的输出有序，不保证全局排序。在每一个reducetask中，每一个小的输出结果排序，但是当reducetask的个数为1的话和order by 的排序结果一致。

注意：sort by 指定的字段仅仅是用于排序的字段，不用于分reducetask输出结果，最终的输出文件中的结果是随机生成的

1
select * from stu_test sort by age desc;

结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
id name sex age
11 wf f 24
10 wb m 23
15 ch f 22
8  cl f 22
5  ll f 21
12 jj m 21
13 yy m 20
7  xh f 20
3  ww m 20
9  fj m 19
6  hl f 19
2  ls m 19
14 ld f 18
1  zs m 18
4  zq f 18

正常（set mapreduce.job.reduces=1）结果和sort by 的结果一致
如果设置reduce task 个数为3的话： set mapreduce.job.reduces=3，此时不一致

1
2
set mapreduce.job.reduces=3
select * from stu_test sort by age desc;

结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
id name sex age
10 wb m 23
15 ch f 22
8 cl f 22
5 ll f 21
7 xh f 20
9 fj m 19
6 hl f 19
 
11 wf f 24
12 jj m 21
3 ww m 20
2 ls m 19
14 ld f 18
4 zq f 18
 
13 yy m 20
1 zs m 18

随机生成3个文件，然后在每个文件中进行排序。

3.distribute by col_list：

根据指定的字段将数据分到不同的reducer,且分发算法是hash散列，按照reducer输出结果。
类似于分桶的概念 按照指定的distribute by字段 和 设置的reducetask的个数进行取余分组，但是并没有排序，只是分，没有排序。
select * from stu_test distribute by age desc; 错误，不能使用desc,因为它不是排序的意思。

1
2
set mapreduce.job.reduces=3
select * from stu_test distribute by age；

结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
id name sex age
000000_0 age%3=0
14 ld f 18
1  zs m 18
4  zq f 18
12 jj m 21
11 wf f 24
5  ll f 21
000000_1 age%3=1
15 ch f 22
9  fj m 19
6  hl f 19
2  ls m 19
8  cl f 22
000000_1 age%3=2
13 yy m 20
7  xh f 20
3  ww m 20
10 wb m 23

 

1
2
3
set mapreduce.job.reduces=2
 
select * from stu_test distribute by age；　　

 

结果：

000000_0 age%2=0
15 ch f 22
14 ld f 18
13 yy m 20
11 wf f 24
8 cl f 22
7 xh f 20
4 zq f 18
3 ww m 20
1 zs m 18
000000_0 age%2=1
12 jj m 21
6 hl f 19
10 wb m 23
9 fj m 19
5 ll f 21
2 ls m 19

4.cluster by col_list ：

除了具有distribute by 的功能外，还会对该字段进行排序
cluster by = distribute by+sort by
cluster by id = distribute by id +sort by id
注意：（1）cluster by 和sort by 不可以同时使用，cluster by是按照reduce task 输出结果，sort by只是随机排序，不按照reduce task输出结果。

（2）当分组字段和排序字段是同一个字段的时候 cluster by id = distribute by id +sort by id
不是同一个字段的时候 请不要使用 cluster by id

（3）排序只能是升序排序，不能指定排序规则为ASC或者DESC，所以cluster by = distribute by +sort by 

1
select * from stu_test cluster by age；　　

结果：

id name sex age
000000_0 age%3=0
14 ld f 18
4  zq f 18
1  zs m 18
12 jj m 21
5  ll f 21
11 wf f 24
000000_1 age%3=1
6  hl f 19
2  ls m 19
9  fj m 19
15 ch f 22
8  cl f 22
000000_1 age%3=2
3  ww m 20
13 yy m 20
7  xh f 20
10 wb m 23

2、union和union all的区别

在hive中，union和union all都是对两个子查询的结果合并，不过还是有区别的，union会对两个子查询的结果去重合并，而union all不会对子查询结果去重处理。

接下来验证通过实战进行验证，下面是两张表的数据。
注意：
Hive 1.2.0之前的版本仅支持UNION ALL，其中重复的行不会被删除。
Hive 1.2.0和更高版本中，UNION的默认行为是从结果中删除重复的行。

select * from temp.temp_mobile_two_tb t;

t.mobile
+8615613526666
8613598985656
18955996677
+8615613526666
8613598985656
18955996677
select * from temp.temp_mobile_tb t;

t.mobile
+8615613526666
8613598985656
18955996677

使用union查看结果

select mobile col from temp.temp_mobile_tb t1
 union 
select mobile from temp.temp_mobile_two_tb t2;

_u1.mobile
+8615613526666
18955996677
8613598985656

由此可以看出，已经去重了。

使用union all查看结果

select mobile from temp.temp_mobile_tb t1
 union all
select mobile from temp.temp_mobile_two_tb t2;

_u1.mobile
+8615613526666
8613598985656
18955996677
+8615613526666
8613598985656
18955996677
+8615613526666
8613598985656
18955996677

可以看出，union all只是对子查询的结果进行合并而已，并不会对结果去重。

3、Avg、sum、count 函数，在某列有空值的情况下，结果会有哪些不同

https://blog.csdn.net/datai3890/article/details/101587833?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-2.nonecase&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-2.nonecase

 

。。。

posted @ 2020-03-10 18:01  guoyu1  阅读(280)  评论(0)  编辑  收藏  举报