hive调优

谈谈你再工作中针对SQL(hive、sparksql...)的优化 ，做的好的3个点？

1.结合场景 + 数据量：

调优前 vs 调优后 的对比情况

2.常见的调优点： groupby join count(distinct ) =》 shuffle =》 数据倾斜

shuffle：根据 map输出的key进行数据分发 各自的reducer上去的

数据倾斜 =》 倾斜在key上

3.没有一劳永逸的调优

1.fetch抓取策略

符合抓取策略 =》 不走mr

hive.fetch.task.conversion：

Some select queries can be converted to a single FETCH task, minimizing latency.

符合fetch 查询就快 =》 不走mr

2.本地化执行

决策 =》hive引擎自己去判断

hive.exec.mode.local.auto =》 开关

hive.exec.mode.local.auto.inputbytes.max =》 按照加载的数据量

hive.exec.mode.local.auto.tasks.max =》按照task

hive.exec.mode.local.auto.input.files.max =》按照文件加载的个数

3.模式限制

hive.mapred.mode

4.推测式执行

1.假设 node1 node2 node3

机器上运行我们的task

2.假设node3机器负载比较高

场景1：

某个时刻、某个节点 负载比较高： cpu使用率比较高、mem被别的作业占用比较多

思考：

task1 和task2 很快运行完成了 ，但由于 node3负载高 ，task3运行比较慢

=》 “木桶效应/短板效应 ”

“桶中能装多少睡是由最短的这块板来决定的 ”

大数据里：一个job跑完需要耗费多长时间 由最慢的一个task来决定的

hadoop{mR}

hive

spark....

内部都有一个机制：推测式执行 去解决这个问题

推测式：

1.node3节点上task 跑了 “一定时间”

会在另外一个节点上页启动一个相同的task

【node5】

这个task3 同时运行在node3节点 node5上 ，谁先跑完 就采用谁的结构 同时kill掉另外一个task

mapreduce：

mapreduce.map.speculative

mapreduce.reduce.speculative

hive：

hive.mapred.reduce.tasks.speculative.execution

5.裁剪 【了解】

1.hive.optimize.cp =》 是否开启列裁剪

eg： table：emp ,columns: a,b,c,d,e

数据存储格式：行式存储

select a,b,c from emp ;

查询只需要3个字段

1. 行式存储 文件无法进行列裁剪 一定是加载全部表中字段

2. 列式存储 ： orc、parquet 列式存储的文件 可以进行列裁剪的

节省 网络io和磁盘io

2.hive.optimize.ppd 谓词下压 =》 节省 网络io和磁盘io 【减少首次加载的数据量】

谓词： 条件相关的东西 where on

下压：取数据从数据源头取数

6.设置 map task数量 & reduce task 数量

1.设置 map task 数量

map task 个数 取决于 切片的个数

切片的个数： 文件的个数、文件大小 、blocksize 、是否可以被切分

FileInputFormat：

getSplits：

computeSplitSize：

mapreduce.input.fileinputformat.split.minsize

mapreduce.input.fileinputformat.split.maxsize

2.设置reduce task个数

reduce 个数 决定了 最终文件的个数

set hive.exec.reducers.bytes.per.reducer=<number>

set hive.exec.reducers.max=<number>

set mapreduce.job.reduces=<number>

hive reduce task个数 如何计算的？

1.用户指定 ：

set mapreduce.job.reduces =》 reduce task个数

2.用户设置参数 hive引擎自己去算

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

set hive.exec.reducers.max =》 reduce task最多有多少个

reduce数据设置？

按照你的作业复杂度 最终输出文件大小来确定 => 小文件问题

hive 小文件合并问题？

Q1: 为什么说hdfs 不适合处理/存储小文件?

1.存储角度：nn压力

2.处理角度：一个小文件对应一个task 处理

Q2： 如何合并小文件 ？

1. hdfs api 来完成 =》 行式存储

github.com

2.hive 里面支持 DDL

Alter Table/Partition Concatenate =》 合并小文件命令

前提：table 存储 orc存储 =》 列式存储

7.并行执行

mr： 可以以 chain方式去运行 job

mr1=》mr2 => mr3 [explain]

hive : sql =>mr

sql的复杂度 不一样 =》 翻译成 mr的个数 以及依赖 都不一样的

EXPLAIN： 查看sql 执行计划

1.The Abstract Syntax Tree for the query 【sql的语法树 不显示了】

2.The dependencies between the different stages of the plan 【一个job 分为多个stage】

3.The description of each of the stages 【每个stage详细信息】

hive : sql =>mr

sql的复杂度 不一样 =》 翻译成 mr的个数 以及依赖 都不一样的

即：

一个sql 可以转换成多个mr job

1.可能是串行执行

2.也可能是并行执行

mr1=》mr2 => mr3

mr1 =>

mr3

mr2 =>

假如sql =》 4个job

1 2 3 job是没有任何依赖关系的

前提：资源够 并行跑 一般性能会好一些的

1.并行执行的参数

hive.exec.parallel =》 false 开关

hive.exec.parallel.thread.number =》 运行并行跑的job数

eg：

emp e join dept d on

加载数据： e d 可以并行跑的

join

8.数据倾斜

1.什么是数据倾斜？

wc：

map输出：(word,1)

shuffle:

按照分区规则 对map端输出的数据进行重新分发

reduce：输入

(word,<1,1,1,1>)

RT2和RT3 处理的数据的时间会很短

RT1 待处理的数据量过多【数据倾斜】，可能会导致什么问题？

1.处理速度慢 能跑完

2.由于数据倾斜 跑不完 【99%】

数据倾斜？

就是由于某个或者某几个 key对应的数据量过大 从而导致对应的task处理非常慢 或者运行报错

导致的原因？

1.只有shuffle才有可能导致数据倾斜

join、groupby 、count(distinct)

解决：

1.group by

map端输出的某些key 数据量过大 =》 skew 【数据倾斜 】

eg：

select deptno from emp group by deptno;

解决思想：

"先打散，再聚合"

hive:

1.参数来解决

hive.groupby.skewindata => sql处于数据倾斜是 会优化 groupby

hive.map.aggr => map端开启一个聚合

2.不通过参数，用户自己定义的 udf函数

1.一个mr作业：

两个mr作业 解决数据倾斜的问题

udf：

add_suffix

pre_suffix

2.count(distinct)

select count(distinct deptno) from emp_tmp ;

distinct => job只有一个 task 只有一个reduce task来完成 =》 必然导致数据倾斜问题

设置reduce 个数 是没有用的

去重： distinct 性能不行

解决：

group by

3.join

1.shuffle join/ mapreduce join 普通join

2.map join =》 不会导致数据倾斜

1.普通join 导致数据倾斜 如何解决？ 工作遇到了 可能low怎么解决

2. 直接抽取 skew数据

sql：

2个sql ：

1sql =》 skew key join

2sql =》 no skew key join

a 表

b 表

得有一个表是小表

1.map join：

1.参数设置

set hive.auto.convert.join=true;

set hive.auto.convert.join=false;

select

a.*,

b.*

from user_click as a left join product_info as b

on a.product_id=b.product_id

limit 2;

2.hints写法： sparksql 也有这种写法 好用的

set hive.auto.convert.join=true;

select /*+ MAPJOIN(product_info) */

a.*,

b.*

from user_click as a left join product_info as b

on a.product_id=b.product_id

limit 2;