hive性能调优

小文件过多的问题：

小文件过多会会导致占用过多的内存资源，因为namenode在内存中维护文件系统树和最新的元数据信息，每个小文件都会占用150字节的内存，会影响查询效率
导致小文件过多的原因：
          使用动态分区，会产生很多零碎的小文件，插入分区表时最好指定分区字段的值；
		  reduce过多会产生很多小文件，设置合理的reduce数

减少小文件数量的措施：
1 源头解决：在日增数据中，可以通过设置写入数据的参数，设置map端，reduce端输出时进行文件合并，设置一个输出文件大小均值，当小于这个均值时，会启动一个独立的mapreduce进程进行文件的合并
2 合并hdfs中已有的小文件，有两种方式：
    ①使用hive自带的concatenate工具进行小文件的合并，但是只能对rcFile和ORC格式存储的表，alter table tableName partition(col='xxx') concatenate;  只能针对分区使用
	②创建和原表相同结构的临时表，设置参数，将原表数据写入临时表，此时临时表的小文件就已经减少了，删除原表，然后直接将临时表改名即可，
	设置的参数为
     set mapred.max.split.size=25000000;
     set mapred.min.split.size.per.node=10000000;
     set hive.hadoop.supports.splittable.combineinputformat=true;
     set mapred.min.split.size.per.rack=10000000;
     set hive.exec.compress.output=true;
     set hive.merge.mapfiles=true;
     set hive.merge.mapredfiles=true;
     set hive.merge.size.per.task=256000000;
     set hive.merge.smallfiles.avgsize=16000000;
     set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
     set hive.exec.dynamic.partition.mode=nonstrict;

设置并行任务数

set hive.exec.parallel=true;  开启并行处理
set hive.exec.parallel.thread.number=8;   设置一段sql最大并行度

设置合理的reduce数：

reduce数=min(每个任务最大的reduce数目,任务总的输入数据量/每个reduce处理的数据量)  
每个任务最大的reduce数目：hive.exec.reducers.max=1009 
每个reduce处理的数据量：hive.exec.reducers.bytes.per.reducer=256000000 默认256MB  
设置reduce的数量：set mapreduce.job.reduces=-1  根据上述规则自行计算reduce数目，否则按照指定的reduce执行，除了order by count(distinct col)这种会强制指定一个reduce

hive sql优化
1 对于复杂的计算逻辑，可以使用with as 来组织，
①一方面嵌套的层级变少了，语法看起来更加简单清晰
②一方面使用with as 被用到多次会自动将数据写入临时表，避免这段逻辑被重复计算多次，提高性能
2 清晰逻辑中含有union all时可以开启并行执行

set hive.exec.parallel=true;
set hive.exec.parallel.thread.number=8;

3 慎用distinct ,因为distinct最终会在一个reduce上执行，数据量很大的话就会很慢并且有OOM风险，可以使用group by来实现，另外，count(distinct columnName) 可以先group by ，然后在外层count(字段) 在数据量大的时候，性能优化就越明显

4 排序技巧
使用sort by 代替order by

3 两表关联
小表作为驱动表，大表作为被驱动表

数据倾斜的优化
1 大小表关联的
可以通过hints的方式使用map join

select /*mapjoin(tableName2)*/
xxx
from tableName1 a
left join tableName2 b on a.id=b.id;

2 两张大表关联
如果是空值导数的数据倾斜，可以在关联的时候将空值转化为随机

hivesql实现模糊查找

-- 方法①  使用instr方法返回含北京二字的部门
select department
from dm.dim_department_info_cur
where instr(department_name,'北京')>0;

-- 方法② 使用like返回部门中含“北京”二字的部门
select department
from dm.dim_department_info_cur
where department_name like '%北京%';

sql的执行顺序

from 
join 
on 
group by 
having
select
distinct
order by
limit 

关于hql转化为mapreduce任务，有一个参数

set hive.fetch.task.conversion;  [none,minimal,more]
none 不生效
minimal 对于 SELECT, 对于分区列的FILTER操作, LIMIT操作使用FETCH操作，直接读取HDFS中的表数据进行展示，其余的操作都会转化为mapreduce任务
more 对于 SELECT, FILTER, LIMIT操作进行FETCH操作，其余的会转化为mapreduce任务