Hive优化

Hive 优化

1、数据调研：定义枚举值

查询是否为 0-6
方式一：

select distinct order_dow
from orders

方式二：

select order_dow
from orders
group by order_dow

注意：在工作中针对数据去重，能使用group by 就不使用distinct

使用group by 多个reduce进行处理
distinct 所有数据在一个reduce中进行处理

测试一下参数代码：
//group by 需要reduce操作，所以用一下代码进行测试

select 
user_id, count(1) cnt
from orders
group by user_id
limit 10;

2、参数优化

①set hive.exec.reducers.bytes.per.reducer= 每个reduce处理的数据量

Number of reduce tasks not specified. Estimated from input data size: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>   表示每个reduce处理的数据量
  如果大于number ,就会多生成一个reduce 
  <number> =1024, 如果等于或者小于1k,一个 reduce 
  1M   10个reduce
测试：
设置reduce处理最大的量： set hive.exec.reducers.bytes.per.reducer=20000；


结果：number of mappers: 1; number of reducers: 1009

②set hive.exec.reducers.max= 表示reduce最大的上限个数

In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>  表示reduce最大的上限个数
测试：设置reduce最大上限为10
  set hive.exec.reducers.max=10
结果：number of mappers: 1; number of reducers: 10

③set mapreduce.job.reduces= 表示指定reduce个数

 set hive.exec.reducers.max=10
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>  表示指定reduce个数
  set mapreduce.job.reduces=5;  设置 reduce 5 看看最大值(10)set hive.exec.reducers.max=10是否受到影响？
结果：  number of mappers: 1; number of reducers: 5
  set mapreduce.job.reduces=15; 设置 reduce 15 看看最大值(10)set hive.exec.reducers.max=10是否受到影响？
结果：number of mappers: 1; number of reducers: 15

总结：针对参数的设置，知道优先级 set mapreduce.job.reduces 最高

3、数据存储格式：（stored as orc）

数据压缩：文件存储优化

工作中常用：orc
TextFIle：默认使用

CREATE TABLE `udata_orc`(
  `user_id` string, 
  `item_id` string, 
  `rating` string, 
  `timestamp` string)
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY '\t' 
  LINES TERMINATED BY '\n' 
stored as orc

4、减少查询数据量：

分区分表优化
桶表优化
分桶规则：对分桶字段值进行哈希，哈希值除以桶的个数求余，余数决定了该条记录在哪个桶中，也就是余数相同的在一个桶中。
优点：1、提高join查询效率 2、提高抽样效率

5、map端优化（工作中 map是不怎么进行优化的）

重点优化reduce端

①set hive.map.aggr=true;  类似于combiner 谨慎使用，计算中位数时可能会不准确
②set mapred.map.tasks=10; 增加map端的数量 

6、Reduce优化

– hive.exec.reducers.bytes.per.reducer；reduce任务处理的数据量
– 调整reduce的个数：
• 设置reduce处理的数据量
• set mapred.reduce.tasks=10

7、什么情况下只有一个reduce；

很多时候你会发现任务中不管数据量多大，不管你有没有设置调整reduce个数的参数，任务中一直都只有一个reduce任务；其实只有一个reduce任务的情况，除了数据量小于hive.exec.reducers.bytes.per.reducer参数值的情况外，还有以下原因：（不推荐）

(1)没有group by

只有一个reduce
number of mappers: 1; number of reducers: 1
获取星期一的数据量级

select
count(*) order_cnt
from orders 
where order_dow='0'
完成时间：
Time taken: 258.719 seconds, Fetched: 1 row(s)

（推荐）利用 group by 进行操作：对于大量的数据查询，建议使用group by 进行聚合，可以得到优化

select 
count(*) order_cnt
from orders
where order_dow='0'
group by order_dow
完成时间：
Time taken: 187.819 seconds, Fetched: 1 row(s)

(2)order by 排序

number of mappers: 1; number of reducers: 1

select 
user_id, order_dow
from orders
where order_dow='0'
order by user_id 
limit 10;

(3)笛卡尔积

抽取建表：
提取user_id中的order_number

方式一：select order_number from orders limit 4;（但是字段名为order_number）
方式二：select order_number as user_id from orders limit 4;(字段名为user_id)
create table tmp_d as 
select order_number as user_id from orders limit 4;
user_id
4
3
2
1	

select a.*
from tmp_d a
join tmp_d b
将我们所有产生的结果进行枚举出来
1	1
2	1
3	1
4	1
1	2
2	2
3	2
4	2
......

8、Reduce优化

(1)分区裁剪

需求：统计周一的下单数？
将 order_dow 对应到我们的分区中

不推荐这样写：不规范
在所有数据关联之后，再进行过滤（数据量很大）

select count(*) order_cnt
from orders ord
inner join trains tr
on ord.order_id= tr.order_id
where order_dow='0'
limit 10;

推荐：
方式一：关联时候提前进行过滤

select
from orders ord
inner join trains 
on (ord.order_id=tr.order_id and order_dow='0')
limit 10;

方式二：在orders表中提前进行过滤，然后在进行关联

select count(*) order_cnt
from (select  * from orders where order_dow='0') ord
inner join trains tr
on ord.order_id=tr.order_id
limit 10;

(2)避免笛卡尔积

方式一：通过on进行关联

select a.*
from tmp_d a
join tmp_d b
on a.user_id=b.user_id

number of mappers: 1; number of reducers: 0

方式二：通过where

select a.*
from tmp_d a
join tmp_d b
where a.user_id=b.user_id

(3)Map join
场景：针对小表，不要超过1G，或者50万条记录

建立aisles表：
create table badou.aisles(
aisle_id string   
,aisle string
)
row format delimited fields terminated by ','
lines terminated by '\n';
--（SQL注释）加载本地数据 overwrite 覆盖 into 追加
load data local inpath '/home/badou/hive/data/aisles.csv'
overwrite into table aisles
--脏数据的处理
insert overwrite table badou.aisles
select * from aisles where aisle_id!='aisle_id'

建立products表：

create table badou.products(
product_id string
,product_name string
,aisle_id string
,department_id string
)
row format delimited fields terminated by ','
lines terminated by '\n';

load data local inpath '/home/badou/hive/data/products.csv'
overwrite into table products

insert overwrite table badou.products
select * from products where product_id!='product_id'

代码：优先将小表加载进去

select 
/*+ MAPJOIN(aisles) */ a.aisle as aisle_name, b.product_name
from aisles a
inner join products b
on a.aisles_id = b.aisles_id
limit 10;

(4) union all / distinct

– 先做union all再做join或group by等操作可以有效减少MR过程，尽管是多个Select，最终只有一个mr
需求：针对周一 周二 周三的用户进行订单合并？
方式一：间接去重

Total jobs = 2
Time taken: 94.833 seconds, Fetched: 1 row(s)

select count(*)
from
(select distinct *
from
(
select user_id,order_id from orders where order_dow='0'
union all
select user_id,order_id from orders where order_dow='1'
union all
select user_id,order_id from orders where order_dow='2'
) t
) t1

方式二：直接去重(速度特别慢)

Total jobs = 3
Time taken: 231.336 seconds, Fetched: 1 row(s)

select count(*)
from
(
select user_id,order_id from orders where order_dow='0'
union
select user_id,order_id from orders where order_dow='1'
union 
select user_id,order_id from orders where order_dow='2'
) t1

(5) 数据失衡（倾斜）set hive.groupby.skewindata=true;

将map reduce进行拆分，为两个map rededuce
1.随机分发到不同的reduce节点，进行聚合 （count）
2.最终的一个reduce做最终结果的聚合（200w条记录 求和 => 1条）

设置reduce个数： set mapreduce.job.reduces=30;
这里数据条数 1亿 条： 比如异常数据，脏数据（’-’,’’, null）落到一个reduce进行处理
异常数据，脏数据（’-’,’’, NULL, null的字符串) 5000W
其他数据 5000W
1个reduce 5000W 200w条记录 求和 50%
29个reduce 剩余 50%

select 
add_to_cart_order, count(1) cnt
from priors
group by add_to_cart_order
limit 10;

发生数据倾斜的状态（情况）：
map = 100%,  reduce = 56%
map = 100%,  reduce = 56%
map = 100%,  reduce = 56%
map = 100%,  reduce = 56%

map=100%，reduce=1%
map=100%，reduce=1%
map=100%，reduce=1%

总结： 如何出现数据倾斜，任务直接失败，然后调度到另一个reduce处理，但是还是失败，尝试调度，直接导致任务一直出问题，运行时间很长，直接导致整个任务运行过长， 进行变量设置
万能方法一：

set hive.groupby.skewindata=true;

方法二：

on case when (x.uid ='-' or x.uid= '0' or x.uid is null) then concat('-',rand())
  else x.uid end = f.user_id;

方法三：
列裁剪： 结果中使用的字段，我们在子查询的时候，才select 出来

如何定位数据倾斜：
① 任务运行时间过长
② 看日志信息（一直卡主，不动）
③ 数据抽样， 进行剔除 必须提前和业务方周知 ！！！

9、MR job的数量

1个MR

Launching Job 1 out of 1(启动一个MR任务)
select ord.order_id
from orders ord
inner join trains tr
on ord.order_id=tr.order_id
inner join priors pri
on ord.order_id=pri.order_id
limit 10;

多个MR

Total jobs = 3(产生3个)

select ord.order_id
from orders ord
inner join trains tr
on ord.order_id=tr.order_id
inner join products pro
on tr.product_id=product_id
limit 10;

10、并行执行

同步执行hive的多个阶段，hive在执行过程，将一个查询转换成一个或多个阶段。某个特定的job可能包含众多的阶段
，而这些阶段可能并非完全相互依赖的，也就是说可以并行执行的，这样可能使得整个job的执行时间缩短。
参数设置：set hive.exec.parallel=true;