Hive 调优集锦，让 Hive 调优想法不再碎片化3

1、order by：全局排序，缺陷是只能使用一个reduce
2、sort by：单机排序，单个reduce结果有序
3、cluster by：对同一字段分桶并排序，不能和sort by 连用
4、distribute by + sort by：分桶，保证同一字段值只存在一个结果文件当中，结合 sort by 保证每个 reduceTask 结果有序

create table if not exists student(id int, name string, sex string, age int,department string) row format delimited fields terminated by ",";
load data local inpath "/home/bigdata/students.txt" into table student;

-- 直接使用order by来做。如果结果数据量很大，这个任务的执行效率会非常低
select id,name,age from student order by age desc limit 3;

-- 使用distribute by + sort by 多个reduceTask，每个reduceTask分别有序
set mapreduce.job.reduces=3;
drop table student_orderby_result;
-- 范围分桶 0 < 18 < 1 < 20 < 2
create table student_orderby_result as select * from student distribute by (case
when age > 20 then 0 when age < 18 then 2 else 1 end) sort by (age desc)

-- 先 group by 在 count
select count(1) from (
  select age from student
  where department >= "MA"
  group by age
) t;

-- 优化前（只有一个reduce，先去重再count负担比较大）：
select count(distinct id) from tablename;

-- 优化后（启动两个job，一个job负责子查询(可以有多个reduce)，另一个job负责count(1))：
select count(1) from (select distinct id from tablename) tmp;
select count(1) from (select id from tablename group by id) tmp; // 推荐使用这种

select t.a, count(t.b) , sum(t.c) from t group by t.a;
select t.a, count(distinct t.b， t.c) from t group by t.a;

-- in / exists 实现
select a.id, a.name from a where a.id in (select b.id from b);
select a.id, a.name from a where exists (select id from b where a.id = b.id);

select a.id, a.name from a join b on a.id = b.id;

-- left semi join 实现
select a.id, a.name from a left semi join b on a.id = b.id;

set hive.vectorized.execution.enabled=true ;
set h/ive.vectorized.execution.reduce.enabled=true;

from sale_detail
  insert overwrite table sale_detail_multi partition (sale_date='2019',region='china' )
  select shop_name, customer_id, total_price where .....
  insert overwrite table sale_detail_multi partition (sale_date='2020',region='china' )
  select shop_name, customer_id, total_price where .....;

1、一般情况下，单个SQL中最多可以写128路输出，超过128路，则报语法错误。
2、在一个multi insert中：对于分区表，同一个目标分区不允许出现多次。对于未分区表，该表不能出现多次。
3、对于同一张分区表的不同分区，不能同时有insert overwrite和insert into操作，否则报错返回。

FROM (SELECT a.status, b.school, b.gender FROM status_updates a JOIN profiles b ON (a.userid = b.userid and a.ds='2019-03-20' )) subq1
  INSERT OVERWRITE TABLE gender_summary PARTITION(ds='2019-03-20')
  SELECT subq1.gender, COUNT(1) GROUP BY subq1.gender
  INSERT OVERWRITE TABLE school_summary PARTITION(ds='2019-03-20')
  SELECT subq1.school, COUNT(1) GROUP BY subq1.school;

set mapreduce.map.output.compress=true;
set mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;

set hive.exec.compress.intermediate=true;
set hive.intermediate.compression.codec=org.apache.hadoop.io.compress.SnappyCodec;
set hive.intermediate.compression.type=BLOCK;

set hive.exec.compress.output=true;
set mapreduce.output.fileoutputformat.compress=true;
set mapreduce.output.fileoutputformat.compress.codec=org.apache.hadoop.io.compress.LzoCodec;
set mapreduce.output.fileoutputformat.compress.type=BLOCK;

## 查看
set hive.fetch.task.conversion;

## 默认more
set hive.fetch.task.conversion=more;

<property>
  <name>hive.fetch.task.conversion</name>
  <value>more</value>
<description>
  Expects one of [none, minimal, more].
  Some select queries can be converted to single FETCH task minimizing latency.
  Currently the query should be single sourced not having any subquery and should not have
  any aggregations or distincts (which incurs RS), lateral views and joins.
    0. none : disable hive.fetch.task.conversion
    1. minimal : SELECT STAR, FILTER on partition columns, LIMIT only
    2. more : SELECT, FILTER, LIMIT only (support TABLESAMPLE and virtual columns)
</description>
</property>
<property>
  <name>hive.fetch.task.conversion.threshold</name>
  <value>1073741824</value>
<description>
  Input threshold for applying hive.fetch.task.conversion. If target table is native, 
  input length is calculated by summation of file lengths. 
  If it's not native, storage handler for the table can optionally implement
  org.apache.hadoop.hive.ql.metadata.InputEstimator interface.
</description>
</property>

## 打开hive自动判断是否启动本地模式的开关
set hive.exec.mode.local.auto=true;
## map任务数最大值，不启用本地模式的task最大个数
set hive.exec.mode.local.auto.input.files.max=4;
## map输入文件最大大小，不启动本地模式的最大输入文件大小
set hive.exec.mode.local.auto.inputbytes.max=134217728;

set mapred.job.reuse.jvm.num.tasks=5;

## 可以开启并发执行。
set hive.exec.parallel=true;
## 同一个sql允许最大并行度，默认为8。
set hive.exec.parallel.thread.number=16;

# 启动mapper阶段的推测执行机制
set mapreduce.map.speculative=true;
# 启动reducer阶段的推测执行机制
set mapreduce.reduce.speculative=true;

如果用户对于运行时的偏差非常敏感的话，那么可以将这些功能关闭掉。如果用户因为输入数据量很大而需要执行长时间的MapTask或者ReduceTask的话，那么启动推测执行造成的浪费是非常巨大大。

<property>
  <name>mapreduce.map.speculative</name>
  <value>true</value>
  <description>If true, then multiple instances of some map tasks may be executed in parallel.</description>
</property>
<property>
  <name>mapreduce.reduce.speculative</name>
  <value>true</value>
  <description>If true, then multiple instances of some reduce tasks may be executed in parallel.</description>
</property>

<property>
  <name>hive.mapred.reduce.tasks.speculative.execution</name>
  <value>true</value>
  <description>Whether speculative execution for reducers should be turned on. </description>
</property>

## 设置Hive的严格模式
set hive.mapred.mode=strict;
set hive.exec.dynamic.partition.mode=nostrict;

1、对于分区表，必须添加where对于分区字段的条件过滤
select * from student_ptn where age > 25
2、order by语句必须包含limit输出限制
select * from student order by age limit 100;
3、限制执行笛卡尔积的查询
select a.*, b.* from a, b;
4、在hive的动态分区模式下，如果为严格模式，则必须需要一个分区列式静态分区