hive优化（三）

一． join 优化

Join 查找操作的基本原则： 应该将条目少的表/子查询放在 Join 操作符的左边。原因是在 Join 操作的 Reduce 阶段，位于 Join 操作符左边的表的内容会被加载进内存，将条目少的表放在左边，可以有效减少发生内存溢出错误的几率。Join 查找操作中如果存在多个 join，且所有参与 join 的表中其参与 join 的 key 都相同，则会将所有的 join 合并到一个 mapred 程序中。案例：SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1) 在一个 mapre 程序中执行 joinSELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2) 在两个 mapred 程序中执行 joinMap join 的关键在于 join 操作中的某个表的数据量很小，案例：SELECT /*+ MAPJOIN(b) */ a.key, a.valueFROM a join b on a.key = b.keyMapjoin 的限制是无法执行 a FULL/RIGHT OUTER JOIN b，和 map join 相关的 hive参数： hive.join.emit.interval hive.mapjoin.size.key hive.mapjoin.cache.numrows由于 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 bON (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 端得出最终结果，相关参数：hive.map.aggr = true 是否在 Map 端进行聚合，默认为 Truehive.groupby.mapaggr.checkinterval = 100000 在 Map 端进行聚合操作的条目数目数据倾斜聚合优化，设置参数 hive.groupby.skewindata = 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 的结果文件来消除这样的影响：hive.merge.mapfiles = true 是否和并 Map 输出文件，默认为 Truehive.merge.mapredfiles = false 是否合并 Reduce 输出文件，默认为 Falsehive.merge.size.per.task = 256*1000*1000 合并文件的大小

四、 Hive 实现(not) in

通过 left outer join 进行查询,（假设 B 表中包含另外的一个字段 key1select a.key from a left outer join b on a.key=b.key where b.key1 is null通过 left semi join 实现 inSELECT a.key, a.val FROM a LEFT SEMI JOIN b on (a.key = b.key)Left semi join 的限制： join 条件中右边的表只能出现在 join 条件中。

五、排序优化

Order by 实现全局排序，一个 reduce 实现，效率低Sort by 实现部分有序，单个 reduce 输出的结果是有序的，效率高，通常和DISTRIBUTE BY 关键字一起使用（DISTRIBUTE BY 关键字 可以指定 map 到 reduce端的分发 key）CLUSTER BY col1 等价于 DISTRIBUTE BY col1 SORT BY col1

六、使用分区

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)

七、 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 数据的应用场景。