Hiveday08调优

1&＃xff1a;EXPLAIN查看执行计划

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

2&＃xff1a;FETCH抓取

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

1&＃xff1a;把 hive.fetch.task.conversion设置成none&＃xff0c;然后执行查询语句&＃xff0c;都会执行mapreduce程序。hive (default)> set hive.fetch.task.conversion&＃61;none;
hive (d efault)> select * from emp;
hive (default)> select ename from emp;
hive (default)> select ename from emp limit 3;2&＃xff1a;把hive.fetch.task.conversion设置成more然后执行查询语句如下查询方式都不会执行mr程序。hive (default)> set hive.fetch.task.conversion&＃61;more;
hive (default)> select * from emp;
hive (default)> select ename from emp;
hive (default)> select ename from emp limit 3;

3&＃xff1a;本地模式

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

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

set hive.exec.mode.local.auto&＃61;true; 开启本地 mr设置local mr的最大输入数据量&＃xff0c;当输入数据量小于这个值时采用local mr的方式&＃xff0c;默认为134217728&＃xff0c;即 128M
set hive.exec.mode.local.auto.inputbytes.max&＃61;500000000设置 local mr的最大输入文件个数&＃xff0c;当输入文件个数小于这个值时采用local mr的方式&＃xff0c;默认为4
set hive.exec.mode.local.auto.input.files.max&＃61;10;

4&＃xff1a;小表大表Join&＃xff08;MapJoin&＃xff09;

1&＃xff1a;设置自动选择MapJoinset hive.auto.convert.join &＃61; true; 默认为true2&＃xff1a;大表小标的阈值设置set hive.mapjoin.smalltable.filesize &＃61; 25000000&＃xff1b;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 &＃61; 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 &＃61; b.id;

5&＃xff1a;大表Join大表

        1&＃xff1a;空key过滤

        条件&＃xff08;1&＃xff1a;非inner join。2&＃xff1a;不需要字段为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 &＃61; o.id;

        2&＃xff1a;空key转换

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

insert overwrite table jointable select n.* from nullidtable n left join bigtable b on n.id &＃61; b.id;insert overwrite table jointable select n.* from nullidtable n full join bigtable o on
nvl(n.id,rand()) &＃61; o.id;

        3&＃xff1a;SMB

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

SMB&＃xff0c;分桶之前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 &＃39;\t&＃39;;create table bigtable 2&＃xff08;id bigint,t bigint,uid string,keyword string,url_rank int,
click_num int,click_url string) row format delimited fields terminated by &＃39;\t&＃39;;加载数据
load data local inpath &＃39;/opt/module/data/bigtable&＃39; into table bigtable2;load data local inpath &＃39;/opt/module/data/bigtable&＃39; 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 &＃61; 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 &＃39;\t&＃39;;
load data local inpath &＃39;/opt/module/data/bigtable&＃39; 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 &＃39;\t&＃39;;
load data local inpath &＃39;/opt/module/data/bigtable&＃39; into table
bigtable_buck2;设置参数
set hive.optimize .bucketmapjoin &＃61;
set hive.optimize.bucketmapjoin.sortedmerge &＃61; true;
set hive.input.format&＃61;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 &＃61; s.id;测试时间为49s


6&＃xff1a;group by

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

开启Mao端聚合参数设置1&＃xff1a;是否在Map端进行聚合&＃xff0c;默认为True
set hive.map.aggr &＃61; true2&＃xff1a;在Map端进行聚合操作的条目数目
set hive.groupby.mapaggr.checkinterval &＃61; 1000003&＃xff1a;有数据倾斜的时候进行负载均衡&＃xff0c;默认为false
set hive.groupby.skewindata &＃61; true当选项为true&＃xff0c;查询计划会有两个mr job&＃xff0c;第一个mr job中&＃xff0c;map的输出结果会随机分布到reduce中&＃xff0c;每个reduce 做部分聚合&＃xff0c;并输出结果。这样是为了相同的group by key可能被分发到不同的reduce中&＃xff0c;从而达到负载均衡的目的。第二个mr job&＃xff0c;再根据预处理的数据按照group by key分布到reduce中&＃xff0c;最后完成聚合。

7&＃xff1a;Count&＃xff08;Distinct&＃xff09;去重统计

        只涉及到一个Reduce&＃xff0c;所以如果数据量太大&＃xff0c;可能整个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&＃xff1a;合理设置Map和Reduce数

9&＃xff1a;并行执行

Hive会将一个查询转化成一个或者多个阶段。这样的阶段可以是MapReduce阶段、抽样阶段、合并阶段、 limit 阶段。或者 Hive 执行过程中可能需要的其他阶段。默认情况下&＃xff0c;Hive 一次只会执行一个阶段。set hive.exec.parallel&＃61;true; 打开任务并行执行
set hive.exec.parallel.thread.number&＃61;16; // 同一个 sql 允许最大并行度&＃xff0c;默认为8 。

10&＃xff1a;严格模式

危险操作
1&＃xff1a;分区表不使用分区过滤hive.strict.checks.no.partition.filter设置为 true时&＃xff0c;对于分区表&＃xff0c;除非 where语句中含有分区字段过滤条件来限制范围 否则不允许执行。 换句话说&＃xff0c;就是用户不允许扫描所有分区。2&＃xff1a;使用order by没有limithive.strict.checks.orderby.no.limit设置为 true时&＃xff0c;对于使用了order by语句的查询&＃xff0c;要求必须使用 limit语句。因为 order by为了执行排序过程会将所有的结果数据分发到同一个Reducer中进行处理&＃xff0c;强制要求用户增加这个 LIMIT 语句可以防止 Reducer 额外执行很长一段时间。3&＃xff1a;笛卡尔积hive.strict.checks.cartesian.product 设置为true时&＃xff0c;会限制笛卡尔积的查询。