Hive-day08-调优

1：EXPLAIN查看执行计划

hive (default)> explain extended select * from emp;
hive (default)> explain extended select deptno, avg(sal) avg_sal from emp group by deptno;

2：FETCH抓取

Fetch抓取是指 Hive中对某些情况的查询可以不必使用 MapReduce计算 。例如 SELECT * FROM employees;在这种情况下 Hive可以简单地读取 employee对应的存储目录下的文件，然后输出查询结果到控制台。在hive-default.xml.template文件中 hive.fetch.task.conversion默认是 more 老版本 hive默认是 minimal 该属性修改为 more以后 在全局查找、字段查找、 limit查找等都不走mr。

1：把 hive.fetch.task.conversion设置成none，然后执行查询语句，都会执行mapreduce程序。

hive (default)> set hive.fetch.task.conversion=none;
hive (d efault)> select * from emp;
hive (default)> select ename from emp;
hive (default)> select ename from emp limit 3;

2：把hive.fetch.task.conversion设置成more然后执行查询语句如下查询方式都不会执行mr程序。

hive (default)> set hive.fetch.task.conversion=more;
hive (default)> select * from emp;
hive (default)> select ename from emp;
hive (default)> select ename from emp limit 3;

3：本地模式

如果数据量比较小，在这种情况下，为查询触发执行任务消耗的时间可能会比实际job的执行时间要多得多。所以，这时，Hive可以通过本地模式在单台机器上处理所有的任务，对于小数据集，执行时间可以明显被缩短。

如下是关于是否开启本地模式，以及开启本地模式的触发条件。

set hive.exec.mode.local.auto=true; 开启本地 mr

设置local mr的最大输入数据量，当输入数据量小于这个值时采用local mr的方式，默认为134217728，即 128M
set hive.exec.mode.local.auto.inputbytes.max=500000000

设置 local mr的最大输入文件个数，当输入文件个数小于这个值时采用local mr的方式，默认为4
set hive.exec.mode.local.auto.input.files.max=10;

4：小表大表Join（MapJoin）

1：设置自动选择MapJoin

set hive.auto.convert.join = true; 默认为true

2：大表小标的阈值设置

set hive.mapjoin.smalltable.filesize = 25000000；

insert overwrite table jointable select b.id, b.t, b.uid, b.keyword, b.url_rank,b.click_num, b.click_url from smalltable s join bigtable b on b.id = s.id;

insert overwrite table jointable select b.id, b.t, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url from bigtable b join smalltable s on s.id = b.id;

5：大表Join大表

        1：空key过滤

        条件（1：非inner join。2：不需要字段为null的

hive (default)> insert overwrite table jointable select n.* from (select * from nullidtable where id is not null) n left join bigtable o on n.id = o.id;

        2：空key转换

        有时虽然某个key 为空对应的数据很多，但是相应的数据不是异常数据，必须要包含在join 的结果中，此时我们可以表 a 中 key 为空的字段赋一个随机的值，使得数据随机均匀地分不到不同的 reducer 上

insert overwrite table jointable select n.* from nullidtable n left join bigtable b on n.id = b.id;

insert overwrite table jointable select n.* from nullidtable n full join bigtable o on
nvl(n.id,rand()) = o.id;

        3：SMB

        先通过key值进行hash，将两个大表分成相同个数的桶，这样的话，A表的第一个桶就是B表的第一个桶相对应，依次对应，然后join，最后再拼接。

SMB，分桶之前

create table bigtable(id bigint, t bigint, uid string, keyword string,url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t';

create table bigtable 2（id bigint,t bigint,uid string,keyword string,url_rank int,
click_num int,click_url string) row format delimited fields terminated by '\t';

加载数据
load data local inpath '/opt/module/data/bigtable' into table bigtable2;

load data local inpath '/opt/module/data/bigtable' into table bigtable;

insert overwrite table jointable select b.id, b.t, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url from bigtable s join bigtable 2 b on b.id = s.id;

测试时间为88s

分桶方法

create table bigtable_buck1(
id bigint,
t bigint,
uid string,
keyword string,
url_rank int,
click_num int,
click_url string)
clustered by(id)
sorted by(id)
into 6 buckets   桶的个数不要超过cpu的个数
row format delimited fields terminated by '\t';
load data local inpath '/opt/module/data/bigtable' into table
bigtable_buck1;

create table bigtable_buck2(
id bigint,
t bigint,
uid string,
keyword string,
url_rank int,
click_num int,
click_url string)
clustered by(id)
sorted by(id)
into 6 buckets
row format delimited fields terminated by '\t';
load data local inpath '/opt/module/data/bigtable' into table
bigtable_buck2;

设置参数
set hive.optimize .bucketmapjoin =
set hive.optimize.bucketmapjoin.sortedmerge = true;
set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;

测试

insert overwrite table jointable
select b.id, b.t, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url
from bigtable_buck1 s
join bigtable_buck2 b
on b.id = s.id;

测试时间为49s

6：group by

        默认情况下，Map阶段同一key分发给一个reduce，当一个key数据过大时就倾斜了。并不是所有的聚合操作都需要在Reduce 端完成，很多聚合操作都可以先在 Map 端进行部分聚合，最后在 Reduce 端得出最终结果。

         

开启Mao端聚合参数设置

1：是否在Map端进行聚合，默认为True
set hive.map.aggr = true

2：在Map端进行聚合操作的条目数目
set hive.groupby.mapaggr.checkinterval = 100000

3：有数据倾斜的时候进行负载均衡，默认为false
set hive.groupby.skewindata = true

当选项为true，查询计划会有两个mr job，第一个mr job中，map的输出结果会随机分布到reduce中，每个reduce 做部分聚合，并输出结果。这样是为了相同的group by key可能被分发到不同的reduce中，从而达到负载均衡的目的。第二个mr job，再根据预处理的数据按照group by key分布到reduce中，最后完成聚合。

7：Count（Distinct）去重统计

        只涉及到一个Reduce，所以如果数据量太大，可能整个job很难完成。一般count distinct使用先group by再count的方式替换。

hive (default)> select count(distinct id) from bigtable;

hive (default)> select count(id) from (select id from bigtable group by id) a;

8：合理设置Map和Reduce数

9：并行执行

Hive会将一个查询转化成一个或者多个阶段。这样的阶段可以是MapReduce阶段、抽样阶段、合并阶段、 limit 阶段。或者 Hive 执行过程中可能需要的其他阶段。默认情况下，Hive 一次只会执行一个阶段。

set hive.exec.parallel=true; 打开任务并行执行
set hive.exec.parallel.thread.number=16; // 同一个 sql 允许最大并行度，默认为8 。

10：严格模式

危险操作
1：分区表不使用分区过滤

hive.strict.checks.no.partition.filter设置为 true时，对于分区表，除非 where语句中含有分区字段过滤条件来限制范围 否则不允许执行。 换句话说，就是用户不允许扫描所有分区。

2：使用order by没有limit

hive.strict.checks.orderby.no.limit设置为 true时，对于使用了order by语句的查询，要求必须使用 limit语句。因为 order by为了执行排序过程会将所有的结果数据分发到同一个Reducer中进行处理，强制要求用户增加这个 LIMIT 语句可以防止 Reducer 额外执行很长一段时间。

3：笛卡尔积

hive.strict.checks.cartesian.product 设置为true时，会限制笛卡尔积的查询。