1.查看简单的执行计划(常用)
explain select xxx from xxx;
2.查看详细的执行计划
explain extended select xxx from xxx;
3.语法
explain 【extended】【dependency】【authorization】 query-sql
2、分区、分桶、指定存储文件格式、压缩格式
常用文件存储格式: orc+snappy\parquet+lzop\orc+lzop
3、动态分区
需要进行一些设置:
set hive.exec.dynamic.partition=true;开启动态分区
set hive.exec.dynamic.partition.mode=nonstrict;设置为非严格模式
set hive.exec.max.dynamic.partitions=1000;所有mr节点的最大动态分区数
set hive.exec.max.dynamic.partitions.pernode=100;单个mr节点的最大动态分区数
set hive.exec.max.created.files=100000;整个mr任务可以创建的最大hdfs文件数
set hive.error.on.empty.partition=false;设置当有空分区生成时是否要报错
4、语法优化
一、针对单表查询的相关优化:
1.分区裁剪、列裁剪
2.group by
默认情况Map阶段的同一个key数据会分发到同一个reduce,当一个key数据过多时发生数据倾斜。
可以在Map端进行预聚合,有以下参数:
set hive.map.aggr=true;是否在map端进行聚合,默认true
set hive.groupby.mapaggr.checkinterval=100000;在map端进行聚合的数据量
set hive.groupby.skewindate=false;是否在数据倾斜时进行负载均衡,默认false
3.vectorization(矢量计算)
在计算scan、filter、aggregation时可以设置批处理(批量读取1-1024行),参数如下:
set hive.vectorized.execution.enabled=true;默认值false
set hive.vectorized.execution.reduce.enabled=true;
4.多重模式
在有多条sql都是对同一张表进行扫描,只是逻辑不同时,可使用多重模式进行优化:
列如:
insert ... select xxx from a where a.xxx = 'xxx';
...
insert ... select xxx from a where a.xxx = 'xxx';
可以修改为:
from a insert xxx select xxx where xxx = 'xxx' insert xxx select xxx where xxx = 'xxx'进而降低a表扫描次数
5.in/exists
在0.8以后开始支持in/exists语法,不推荐使用,可以使用left semi join进行替代,更高效
二、针对多表查询的相关优化:
join的时候,前面的表会被加载到内存中,而后面的表是进行磁盘扫描
1.CBO(成本优化器)
hive1.1.0之后,hive.cbo.enable默认是开启的,可以自动优化多个join的顺序,选择合适的join算法
2.谓词下推
默认hive.optimize.ppd是true,在sql语句中的where谓词逻辑都尽可能的提前
3.MapJoin(大小表join)
在join时将小表的数据分发加载到map端内存中,在map端完成join操作,规避reduce、数据倾斜,提升执行效率,有以下参数:
set hive.auto.convert.join=true;自动选择mapjoin,默认true
set hive.mapjoin.smalltable.filesize=25000000;设置小表阈值,默认小于25M
注意:在left join的时候MapJoin会失效,走的是reduce,commonjoin
4.SMBJoin(大表join大表)
使用分桶join,参数如下:
set hive.optimize.bucketmapjoin=true;
set hive.optimize.bucketmapjoin.sortedmerge=true;
set hive.input.format=org.apache.hadoop.hive.ql.io.bucketizedHiveInputFormat
5.笛卡尔积
默认hive是严格模式,动态分区以及笛卡尔积是不能执行的,如果是非严格模式,将会使用一个reduce完成多个表数据的笛卡尔积,数据量大,系统崩溃,善用笛卡尔积可以很容易解决一些实际的问题(遍历字符串每个字符、生成一个序列)
5、数据倾斜
在绝大多数的任务都完成的情况下,有少数任务执行很慢或者卡在99%甚至导致任务失败,那就是产生了数据倾斜,在一般情况下我们认为当最快的任务和最慢的任务执行时间相差20倍就可以认为是发生了数据倾斜,一般来说,数据倾斜的任务一般是伴随这分组操作,解决方案如下:
1.单表数据倾斜优化:
可以有单个key引起数据倾斜,也可以有多个key引起的数据倾斜,针对单个key引起数据倾斜一般就是开启map端聚合以及发生数据倾斜时自动负载均衡;如果多个key导致数据倾斜,需要我们额外去增加reduce的数量(调整每个reduce处理的数据量,或者在mapred-site.xml文件中设置mapreduce.job.reduces =xxx),是多key,还是单key我们可以采样,或者统计每个key的数据进行判断
set mapreduce.job.reduces = 15;
(1)每个Reduce处理的数据量默认是256MB
hive.exec.reducers.bytes.per.reducer=256000000
(2)每个任务最大的reduce数,默认为1009
hive.exec.reducers.max=1009
(3)计算reducer数的公式
N=min(参数2,总输入数据量/参数1)
2.join产生数据倾斜优化:
set hive.skewjoin.key=100000;指定join时key对应的最大记录条数,超出会拆分
set hive.optimize.skewjoin=false;是否开启join自动负载均衡
set hive.skewjoin.mapjoin.map.tasks=10000;第二个job的mapper数量
几个小bug:
1.在表中的日期类型使用string,而不是date时,我们在查询时的子查询语句中使用date_diff()时会报空指针异常,需要修改字段类型为date,或者更换引擎。
2.开窗函数使用时,over(partition by xxx order by xxx rows between xxx and xxx),rows是失效的,永远都是从分组的起始行到当前行