HQL语句的调优

1、去重语句(用group by 来代替distinct)

Group by 也有去重的功能，具体内容如下

select distinct customer_id
from test_join_order;
=>
select customer_id
from test_join_order
group by customer_id;


在极大的数据量(且很多重复值)时，可以先group by去重，在count()计数，效率高于count(distinct col)



create table if not exists test1(
id int
)
row format delimited
fields terminated by ',';

insert into test1 values(1);
insert into test1 values(2);
insert into test1 values(1);
insert into test1 values(3);
insert into test1 values(1);
insert into test1 values(2);
insert into test1 values(4);

select count(distinct(id))
from test1;

select count(t1.id)
from(
select id 
from test1
group by id) t1;

2、聚合技巧——利用窗口函数grouping sets、cube、rollup

这个窗口函数是都是上一个的完善与封装

group by quantity,sex,age
grouping sets(quantity,sex,age)

group by quantity,sex,age
with cube;

group by quantity,sex
with rollup;

-- 数量分布
select quantity,count(*) 
from test2
group by quantity;
2       1
3       2
4       1
5       1
7       2

-- 性别分布
select sex,count(*) 
from test2
group by sex;
0       3
1       4

-- 年龄分布
select age,count(*) 
from test2
group by age;
16      2
19      1
21      1
23      1
34      2

缺点要分别写三次SQL，需要执行三次，重复工作，且费事

-- 优化方法(聚合结果均在同一列，分类字段用不同列来进行分区)

select quantity,sex,age,count(*)
from test2
group by quantity,sex,age
grouping sets(quantity,sex,age)

NULL    NULL    16      2
NULL    NULL    19      1
NULL    NULL    21      1
NULL    NULL    23      1
NULL    NULL    34      2
NULL    0       NULL    3
NULL    1       NULL    4
2       NULL    NULL    1
3       NULL    NULL    2
4       NULL    NULL    1
5       NULL    NULL    1
7       NULL    NULL    2

就是把三列的结果放在一起

以下是测试数据
create table if not exists test2(
quantity int,
sex int,
age int
)
row format delimited
fields terminated by ',';

insert into test2 values(5,1,16);
insert into test2 values(3,0,23);
insert into test2 values(4,1,16);
insert into test2 values(7,0,34);
insert into test2 values(2,0,19);
insert into test2 values(7,1,21);
insert into test2 values(3,1,34);

cube:根据group by 维度的(所有)组合进行聚合

select quantity,sex,age,count(*)
from test2
group by quantity,sex,age
grouping sets(quantity,sex,age,(sex,age),(quantity,sex),(quantity,age),(quantity,sex,age));

(sex,age)是相同sex和age的数量有多少个

以上的写法可以写成
select quantity,sex,age,count(*)
from test2
group by quantity,sex,age
with cube;

cube
英 [kjuːb]   美 [kjuːb]  
n.
立方体;立方形;立方形的东西(尤指食物);立方;三次幂
vt.
求…的立方;把(食物)切成小方块

rollup主要用于分层统计，相当于是达到每层汇总的效果

select quantity ,sex ,sum(age)
from test2
group by quantity,sex
with rollup;

2       NULL    19
2       0       19
3       NULL    57
3       0       23
3       1       34
4       NULL    16
4       1       16
5       NULL    16
5       1       16
7       NULL    55
7       0       34
7       1       21

3、表的连接优化

1、小表在前，大表在后
	hive假定查询中最后一个是大表，它会将其他表缓存起来，然后扫描最后那个表

2、使用相同的连接建
	当对3个或者多个表进行join连接时，如果每个on子句都使用相同的连接键的话，那么只会产生一个MR job
	
3、尽早的过滤数据
	减少每个阶段的数据量，对于分区表要加分区，同时只选择需要使用到的字段
	当逻辑过于复杂时，引入中间表

4、如何解决数据倾斜

1、数据倾斜的表现：
	任务进度长时间维持在99%（100%），查看任务监控页面，发现只要少量（1个或几个）reduce子任务未完成。因为其处理的数据量和其他reduce差异过大
	
2、数据倾斜的原因与解决方法：
	1、空值产生的数据倾斜
	解决：如果两个表连接时，使用的连接条件有很多空值，建议在连接条件中增加过滤
	eg:on a.user_id = b.user_id and a.user_id is not null
	2、大小表连接（其中一张表很大，另一张表非常小）
	解决：将小表放到内存里，在map端做join
	select /*+mapjoin(a)*/ a.user_id,a.user_name
	 from user_info a
	 join join_order b
	 where b.customer_id = a.customer_id;
	 
	 把数据提前放到内存中的方法
	 /*+mapjoin(a)*/
	 
	 3、两个表连接条件的字段数据类型不一致
	 解决：将连接条件的字段数据类型转换成一致的
	 
	 on a.user_id = cast(b.user_id as string)