hive|hive 查询性能优化总结 hive查询性能优化总结

一、join优化 ** Join查找操作的基本原则：** 应该将条目少的表/子查询放在 Join 操作符的左边。
原因是在 Join 操作的 Reduce 阶段，位于 Join 操作符左边的表的内容会被加载进内存，将条目少的表放在左边，可以有效减少发生内存溢出错误的几率。
Join查找操作中如果存在多个join，且所有参与join的表中其参与join的key都相同，则会将所有的join合并到一个mapred程序中。
案例：

//在一个mapre程序中执行join SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)

//在两个mapred程序中执行join SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

Map join的关键在于join操作中的某个表的数据量很小，案例：

SELECT /*+ MAPJOIN(b) */ a.key, a.value FROM a join b on a.key = b.key

Mapjoin 的限制是无法执行a FULL/RIGHT OUTER JOIN b，和map join相关的hive参数：

hive.join.emit.interval hive.mapjoin.size.key hive.mapjoin.cache.numrows

** 在进行 join 操作的条件过滤的时候，应该将过滤条件放在 on 关键词里面，提高查询的效率。**
由于join操作是在where操作之前执行，所以当你在执行join时，where条件并不能起到减少join数据的作用；案例：

SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key) WHERE a.ds='2009-07-07' AND b.ds='2009-07-07'

最好修改为：

SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key AND b.ds='2009-07-07' AND a.ds='2009-07-07')

在join操作的每一个mapred程序中，hive都会把出现在join语句中相对靠后的表的数据stream化，相对靠前的变的数据缓存在内存中。当然，也可以手动指定stream化的表：

SELECT /*+ STREAMTABLE(a) */ a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)

二、group by 优化 Map端聚合，首先在map端进行初步聚合，最后在reduce端得出最终结果，相关参数：

//是否在 Map 端进行聚合，默认为 True hive.map.aggr = true

//在 Map 端进行聚合操作的条目数目 hive.groupby.mapaggr.checkinterval = 100000

** 数据倾斜的聚合优化**
对数据进行聚合优化，可以进行如下的参数设置

hive.groupby.skewindata = https://www.it610.com/article/true

当此项设定为 true，生成的查询计划会有两个 MR Job。第一个 MR Job 中，Map 的输出结果集合会随机分布到 Reduce 中，每个 Reduce 做部分聚合操作，并输出结果，这样处理的结果是相同的 Group By Key 有可能被分发到不同的 Reduce 中，从而达到负载均衡的目的；第二个 MR Job 再根据预处理的数据结果按照 Group By Key 分布到 Reduce 中（这个过程可以保证相同的 Group By Key 被分布到同一个 Reduce 中），最后完成最终的聚合操作。
三、合并小文件文件数目过多，会给 HDFS 带来压力，并且会影响处理效率，可以通过合并 Map 和 Reduce 的结果文件来消除这样的影响：

// 是否和并 Map 输出文件，默认为 True hive.merge.mapfiles = true // 是否合并 Reduce 输出文件，默认为 False hive.merge.mapredfiles = false // 合并文件的大小 hive.merge.size.per.task = 256*1000*1000

四、Hive实现(not) in 通过left outer join进行查询实现 not in,（假设B表中包含另外的一个字段 key1 不在 a 表中）

select a.key from a left outer join b on a.key=b.key where b.key1 is null

【hive|hive 查询性能优化总结】通过left semi join 实现 in

SELECT a.key, a.val FROM a LEFT SEMI JOIN b on (a.key = b.key)

Left semi join 的限制：join条件中右边的表只能出现在join条件的 on 关键词中，不可以出现在 where 等关键词中，也无法查询查询其相应的字段内容。
五、排序优化 ** Order by： ** 实现全局排序，一个reduce实现，效率低
Sort by：实现部分有序，如果用sort by进行排序，并且设置mapred.reduce.tasks > 1，则sort by只保证每个reducer的输出有序，不保证全局有序。单个reduce输出的结果是有序的，效率高。
通常和DISTRIBUTE BY关键字一起使用（DISTRIBUTE BY关键字可以指定map 到 reduce端的分发key）

CLUSTER BY col1 等价于 DISTRIBUTE BY col1 SORT BY col1

使用sort by 你可以指定执行的reduce 个数（ set mapred.reduce.tasks=）,对输出的数据再执行归并排序，即可以得到全部结果。
** distribute by: **按照指定的字段对数据进行划分到不同的输出reduce / 文件中。

insert overwrite local directory '/home/hadoop/out' select * from test order by name distribute by length(name);

此方法会根据name的长度划分到不同的reduce中，最终输出到不同的文件中。
length 是内建函数，也可以指定其他的函数或这使用自定义函数。
** Cluster By: ** cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。但是排序只能是倒序排序，不能指定排序规则为asc 或者desc。
六、使用分区
Hive中的每个分区都对应hdfs上的一个目录，分区列也不是表中的一个实际的字段，而是一个或者多个伪列，在表的数据文件中实际上并不保存分区列的信息与数据。Partition关键字中排在前面的为主分区（只有一个），后面的为副分区
静态分区：静态分区在加载数据和使用时都需要在sql语句中指定
案例：(stat_date='20120625',province='hunan')
动态分区：使用动态分区需要设置 hive.exec.dynamic.partition参数值为true，默认值为false，在默认情况下，hive会假设主分区时静态分区，副分区使用动态分区；如果想都使用动态分区，需要设置
set hive.exec.dynamic.partition.mode=nostrick，默认为strick
案例：(stat_date='20120625',province)
必须在表定义时创建partition

//单分区建表语句： // 单分区表，按天分区，在表结构中存在id，content，dt三列,以dt为文件夹区分 create table day_table (id int, content string) partitioned by (dt string);

// 双分区建表语句： //双分区表，按天和小时分区，在表结构中新增加了dt和hour两列。先以dt为文件夹，再以hour子文件夹区分 create table day_hour_table (id int, content string) partitioned by (dt string, hour string);

添加分区表语法（表已创建，在此基础上添加分区）：

ALTER TABLE table_name ADD partition_spec [ LOCATION 'location1' ] partition_spec [ LOCATION 'location2' ] ... ALTER TABLE day_table ADD PARTITION (dt='2008-08-08', hour='08') location '/path/pv1.txt'

删除分区语法：

// ALTER TABLE table_name DROP partition_spec, partition_spec,...

用户可以用 ALTER TABLE DROP PARTITION 来删除分区。分区的元数据和数据将被一并删除。例：

ALTER TABLE day_hour_table DROP PARTITION (dt='2008-08-08', hour='09');

数据加载进分区表中语法：

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]// 例： LOAD DATA INPATH '/user/pv.txt' INTO TABLE day_hour_table PARTITION(dt='2008-08- 08', hour='08'); LOAD DATA local INPATH '/user/hua/*' INTO TABLE day_hour partition(dt='2010-07- 07');

当数据被加载至表中时，不会对数据进行任何转换。Load操作只是将数据复制至Hive表对应的位置。数据加载时在表下自动创建一个目录
基于分区的查询的语句：

SELECT day_table.* FROM day_table WHERE day_table.dt>= '2008-08-08';

查看分区语句：

hive> show partitions day_hour_table; OK dt=2008-08-08/hour=08 dt=2008-08-08/hour=09 dt=2008-08-09/hour=09

七、Distinct 使用 Hive支持在group by时对同一列进行多次distinct操作，却不支持在同一个语句中对多个列进行distinct操作。
八、Hql使用自定义的mapred脚本注意事项：在使用自定义的mapred脚本时，关键字MAP REDUCE 是语句SELECT TRANSFORM ( ... )的语法转换，并不意味着使用MAP关键字时会强制产生一个新的map过程，使用REDUCE关键字时会产生一个red过程。
自定义的mapred脚本可以是hql语句完成更为复杂的功能，但是性能比hql语句差了一些，应该尽量避免使用，如有可能，使用UDTF函数来替换自定义的mapred脚本
九、UDTF
UDTF将单一输入行转化为多个输出行，并且在使用UDTF时，select语句中不能包含其他的列，UDTF不支持嵌套，也不支持group by 、sort by等语句。如果想避免上述限制，需要使用lateral view语法，案例：

select a.timestamp, get_json_object(a.appevents, '$.eventid'), get_json_object(a.appenvets, '$.eventname') from log a;

select a.timestamp, b.* from log a lateral view json_tuple(a.appevent, 'eventid', 'eventname') b as f1, f2;

其中，get_json_object为UDF函数，json_tuple为UDTF函数。
UDTF函数在某些应用场景下可以大大提高hql语句的性能，如需要多次解析json或者xml数据的应用场景。
十、聚合函数count和sum Count和sum函数可能是在hql语句中使用的最为频繁的两个聚合函数了，但是在hive中count函数在计算distinct value时支持加入条件过滤。