Hive：优化Reduce，查询过程；判断数据倾斜，MAPJOIN

文章目录

• • 1、SQL reduce过程 参数理解
  • 2、数据存储格式
  • • 2.1 减少查询数据量
    • 2.2 map端优化（不常用，了解即可）
  • 3、Reduce的优化
  • 4、统计订单数据和历史订单周一的下单数？
  • • 4.1 常规思想查询
    • 4.2 改良方式一：
    • 4.3 改良方式二：（推荐，先算条件再联表）
  • 5、创建 aisles，products 表
  • 6、MAPJION
  • 7、针对周一 周二 周三的用户 进行订单数据合并？
  • 8、数据倾斜
  • • 8.1 数据倾斜原因
    • 8.2 数据倾斜特点
    • 8.3 数据倾斜万能方法
    • 8.4 如何定位数据倾斜？
  • 9、一个MR，与多个MR

---

① Hive 数据管理、内外表、安装模式操作

② Hive：用SQL对数据进行操作，导入数据、清洗脏数据、统计数据订单

③ Hive：多种方式建表，需求操作

④ Hive：分区原因、创建分区、静态分区 、动态分区

⑤ Hive：分桶的简介、原理、应用、创建

⑥ Hive：优化 Reduce，查询过程；判断数据倾斜，MAPJOIN

---

1、SQL reduce过程 参数理解

use badou;
-- 1、查看订单的星期几0-6代表星期一-星期天
select distinct order_dow
from orders
-- 2、查看订单的星期几
select order_dow from orders
group by order_dow

可以从结果看到，两种查询方法是一样的结果，但是第二种方法查询效率更好。也就是说：

• 在工作中针对数据去重 能使用group by 就不使用distinct ，使用group by 多个reduce进行处理，而 distinct 所有数据在一个reduce中进行处理。

In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=
In order to set a constant number of reducers:
  set mapreduce.job.reduces=

在执行过程中会显示上面的语句。

-- 1、表示每个reduce处理的数据量
set hive.exec.reducers.bytes.per.reducer= 
-- 例如，设置处理数据20000个
set hive.exec.reducers.bytes.per.reducer=20000; 
select user_id, count(1) cnt from orders
group by user_id
limit 10; 
-- reduce会自动把拆成1009份进行操作
-- number of mappers: 1; number of reducers: 1009

-- 1、表示reduce最大的上限个数
set hive.exec.reducers.max=  
-- 2、再执行会发现reduce=10
select user_id, count(1) cnt from orders
group by user_id
limit 10; 

-- 1、表示指定reduce个数
set mapreduce.job.reduces= 
-- 2、设置 reduce=15 看看reduce上线是否受到影响？
set mapreduce.job.reduces=15; 

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

可以发现，对参数的设置，优先级 set mapreduce.job.reduces=15 最高。

---

2、数据存储格式

选择好的数据存储格式，能有效较少存储空间的占用。Hive常用orc存储格式；

-- 创建一个orc表
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
--查看表结构
show create table udata_orc;

日常中常用的存储格式是，Textfile 和 orc 格式。

---

2.1 减少查询数据量

分区表优化、桶表优化、压缩数据。

2.2 map端优化（不常用，了解即可）

set hive.map.aggr=true;  --类似于combiner
set mapred.map.tasks=10; --设置map数量

---

3、Reduce的优化

（1）调整reduce的个数：

• 设置reduce处理的数据量；(上面1、步骤)
• set mapred.reduce.tasks=10 。(设置reduce工作数)

不需要 gruop by 的情况，== reduce：1

-- 查星期一的总订单数
select count(1) order_cnt from orders
where order_dow='0'

(2) order by排序user_id数据默认升序

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

---

4、统计订单数据和历史订单周一的下单数？ 4.1 常规思想查询

 --count 统计
select count(*) order_cnt from orders as od
-- 连接两个表
inner join trains as tr
-- 都有order_id 
on od.order_id=tr.order_id
-- 都有order_id 
where od.order_dow='0'
limit 10;

这种思路是先把表连接，再查找，明显查找时间花费多。

我们可以用其他方式统计周一订单数

---

4.2 改良方式一：

select count(*) order_cnt from orders ord 
inner join trains tr
-- 连接条件同时满足 order_id和周一 
on (ord.order_id=tr.order_id and order_dow='0')
limit 10;

4.3 改良方式二：（推荐，先算条件再联表）

-- 先把订单周一的结果存到 ord表，再与trains表连接
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;

可以知道，用方式二的思路查找周一订单会省时间。

---

5、创建 aisles，products 表

--创建 aisles
create table aisles(
aisle_id string
,aisle string
)
row format delimited fields terminated by ','
lines terminated by 'n';
--加载本地数据  overwrite 覆盖 into 追加
load data local inpath 'day3/aisles.csv'
overwrite into table aisles;
-- 删除脏数据
alter table aisles set tblproperties ('skip.header.line.count' = '1');


--创建 products
create table products(
product_id string,
product_name string,
aisle_id string,
department_id string
)
row format delimited fields terminated by ','
lines terminated by 'n'
--加载本地数据  overwrite 覆盖 into 追加
load data local inpath 'day3/products.csv'
overwrite into table products
-- 删除脏数据
alter table aisles set tblproperties ('skip.header.line.count' = '1');

---

6、MAPJION

MAPJION会把小表全部加载到内存中，在map阶段直接拿另外一个表的数据和内存中表数据做匹配，由于在map端是进行了join操作，省去了reduce运行的时间，是hive中的一种优化。

-- aisles 是小表
select * from aisles limit 10

select 
 a.aisle as aisle_name, b.product_name
from aisles a 
inner join products b
on a.aisle_id=b.aisle_id
limit 10;

---

7、针对周一 周二 周三的用户 进行订单数据合并？

合并数据用union all（不去重），union(去重)。

select count(*) from (
select distinct * from -- 用distinct 去重
(
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

----
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

---

8、数据倾斜

在数据中，会出现数据倾斜的一般是脏数据（’-’,’’, NULL, null)，再执行任务后卡住，就有可能是数据倾斜。

数据倾斜：reduce卡住不动
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;）。

8.1 数据倾斜原因

• （1）key分布不均导致的；
• （2）人为的建表疏忽 ；
• （3） 业务数据特点 。

8.2 数据倾斜特点

1. 任务进度长时间维持在99%（或100%），查看任务监控页面，发现只有少量（1个或几个）reduce子任 务未完成。
2. 查看未完成的子任务，可以看到本地读写数据量积累非常大，通常超过10GB可以认定为发生数据倾斜。

8.3 数据倾斜万能方法

set hive.groupby.skewindata=true

8.4 如何定位数据倾斜？

① 任务运行时间过长；
② 看日志信息；
③ 数据抽样，进行剔除，(剔除数据前必须提前和业务方周知)

---

shell ： 查看文件
du -sh order_products__prior.csv 查看文件的大小
wc -l order_products__prior.csv 查看文件条数

---

9、一个MR，与多个MR

join on 条件的key左边一致是一个MR，左边不一致是多个MR。

-- 左边的key是一致的
select a.val b.val c.val from a
join b on (a.key = b.key1)
join c on (a.key = c.key1)

-- 一个MR Launching Job 1 out of 1
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;

-----
select a.val b.val c.val from a
join b on (a.key = b.key1)
join c on (c.key = b.key2)

-- 多个MR Total jobs = 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=pro.product_id
limit 10;