hive如何提高查询性能

本文会通过四个方面介绍Hive性能调优，主要包括：

√性能调优工具

√设计优化策略

√数据存储优化

√作业优化技巧

1.合并中间表

一个日志文件中，每一行记录，会有很多很多字段，四五十个字段很正常。实际分析中，常常使用少数几个字段将原始的表中数据，依据业务需求提取出要分析的字段，数据放入到对应的业务表（子表）中，实际的业务针对业务表进行分析。

在实际中，我们会发现，有些业务处理，会有共同数据集用户表、订单表、商品表，三个表需要进行join的操作，join 会产生一个结果集，会有很多的业务是针对此jion结果集进行分析。

优化：将众多的业务中相同的中间结果集，抽取到一个Hive中的表中去。

2.合理使用分区表

外部表、分区表，结合使用，采用多级分区。数据采用存储格式（textfile、orcfile、parquet）或者数据压缩(snappy)。

明细数据我们一般采用按天分区，对于特别大的表，可以采用子分区，每个分区其实对应到HDFS上就是一个目录。数据存储方式我们可以采用parquet列式存储，同时具有很好的压缩性能；同时可以减少大量的表扫描和反序列化的时间。在OLAP查询场景下，我们选择需要的列信息进行查询，而不是直接select * 查询所有字段。

3.优化查询

Hive调优及优化的12种方式
Hive性能调优
如何优化查询速度
hive 查询性能优化总结
数据倾斜产生，原因及其解决方案
什么是数据倾斜?如何解决数据倾斜?
深入浅出Hive数据倾斜

通过谓词下推，压缩等技术实现极佳的速度提升？？？
使用sortby和使用order by 的去区别？？
数据倾斜？？

where条件优化
优化前（关系数据库不用考虑会自动优化）：

select m.cid,u.id from order m join customer u on( m.cid =u.id )where m.dt='20180808';
优化后(where条件在map端执行而不是在reduce端执行）：

select m.cid,u.id from （select * from order where dt='20180818'） m join customer u on( m.cid =u.id);
union优化
尽量不要使用union （union 去掉重复的记录）而是使用 union all 然后在用group by 去重

count distinct优化
不要使用count (distinct cloumn) ,使用子查询。

select count(1) from (select id from tablename group by id) tmp;
用in 来代替join
如果需要根据一个表的字段来约束另为一个表，尽量用in来代替join 。

select id,name from tb1 a join tb2 b on(a.id = b.id);

select id,name from tb1 where id in(select id from tb2);
in 要比join 快

消灭子查询内的 group by 、 COUNT(DISTINCT)，MAX，MIN。可以减少job的数量。

join 优化：
Common/shuffle/Reduce JOIN：连接发生的阶段，发生在reduce 阶段，适用于大表连接大表(默认的方式)

Map join ：连接发生在map阶段，适用于小表连接大表大表的数据从文件中读取；小表的数据存放在内存中（hive中已经自动进行了优化，自动判断小表，然后进行缓存）。

set hive.auto.convert.join=true;
SMB join：Sort -Merge -Bucket Join 对大表连接大表的优化，用桶表的概念来进行优化。在一个桶内发送生笛卡尔积连接（需要是两个桶表进行join）

set hive.auto.convert.sortmerge.join=true;
set hive.optimize.bucketmapjoin = true;
set hive.optimize.bucketmapjoin.sortedmerge = true;

set hive.auto.convert.sortmerge.join.noconditionaltask=true;

关于with as 的使用

https://blog.csdn.net/qq_40640228/article/details/118083226

在关系型数据库中，对于UNION ALL，使用WITH AS定义了一个UNION ALL语句，当该片断被调用2次以上，优化器会自动将该WITH AS短语所获取的数据放入一个Temp表中
在Hive内 WITH AS语法默认并不会将执行结果作为临时表存入内存，而是每一次引用都会执行一次WITH AS内的计算逻辑，而MySQL和SqlServer这种关系型数据库执行WITH AS时会将计算结果作为临时表。

在hive中有一个参数

hive.optimize.cte.materialize.threshold

1
  这个参数在默认情况下是-1（关闭的）；当开启（大于0），比如设置为2，则如果with…as语句被引用2次及以上时，会把with…as语句生成的table物化，从而做到with…as语句只执行一次，来提高效率。

将hive.optimize.cte.materialize.threshold设置为1