Hive 调优总结

1. 通常，可以通过设置属性hive.map.aggr值为true来提高聚合的性能。

hive.map.aggr=true;这个设置会触发在map阶段的“顶级”聚合过程。（非顶级的聚合过程将会在执行一个GROUP BY后进行）不过这个设置将需要更多的内存。

2. 无需MapReduce

也就是所谓的本地模式。Hive可以简单的读取employees对应目录下的文件。例如：
select * from employees;
或者加where和limit也是可以的。
此外，如果属性hive.exec.mode.local.auto=true的话，Hive会尝试使用本地模式执行其他操作。（非常推荐）

3. 浮点数比较问题FLOAT 0.2>DOUBLE 0.2

由于0.2对于FLOAT类型是0.2000001，而对于DOUBLE类型是0.200000000001，当二者比较是FLOAT会转成DOUBLE 0.200000100000，这个值实际要比0.200000000001大。

规避办法：

1. 对于TEXTFILE文件，读取字符串“0.2”，可以直接在表模式中定义类型为DOUBLE。
2. 显式地指出0.2为FLOAT类型。如：cast(0.2 AS FLOAT)
3. 和金钱相关都避免使用浮点数
   Note: 对于浮点数比较，需保持极端谨慎的态度。

4. LIKE与RLIKE

LIKE模糊查询，RLIKE是一个扩展支持正则匹配

5. JOIN语句，Hive只支持等值连接，即ON a.id = b.id,

原因是由于MapReduce很难实现这种非等值的连接。

大多数情况下，Hive会对每对JOIN连接对象启动一个MapReudce任务。例：a JOIN b ON a.ymd = b.ymd JOIN c ON a.ymd = c.ymd。首先启动MapReduce对a和b进行连接操作，然后再启动一个MapReduce将第一个MapReduce job的输出和c进行连接操作。这是因为Hive总是按照从左到右的顺序执行的。

6. JOIN优化

1. 自动优化：当对3个或者更多的表进行JOIN连接时，如果每个ON子句都是用相同的连接键的话，那么只会产生一个MapReduce job。如上例中都是用ymd作为键,就会自动优化为1个MapReduce job。
2. 在做连接操作时，Hive会将前面对表缓存，然后扫描最后的表进行计算。因此，用户需要保证连续查询中的表的大小从左到右依次增加的（小 JOIN 中 JOIN 大）。
3. 通过“标记”显示地告诉优化器哪张表是大表。SELCET /*+STREAMTABLE(a)*/a.id, a.name from table1 a JOIN table2 b ON a.id = b.id;
4. map-side JOIN（非常推荐）
   完全将小表放到内存，从而省略掉连接的reduce过程。
   使用标记/*+MAPJOIN(d)*/或
   set hive.auto.convert.join=true;
5. OUTER JOIN 优化，尽量不对大量NULL的字段进行WHERE的条件。WHERE在JOIN后执行，应该只作用于过滤那些非NULL字段。
6. LEFT SEMI-JOIN
   Hive不支持... WHERE a.id IN (SELECT d.id from table_d d) 但可以使用左半开连接SELECT a.id, a.name FROM table_a a LEFT SEMI JOIN table_d d ON a.id = d.id;注意，SELECT和WHERE都不能用到右边表的字段。SEMI-JOIN比INNER JOIN更高效，因为：对于左表中一条指定的记录在右表中一旦找到匹配的记录就会停止扫描。
7. 笛卡尔积JOIN（无ON子句）：Hive无法优化

7. ORDER BY, SORT BY, DISTRIBUTE BY

ORDER BY全局排序是非常耗时的，如非必要可以使用SORT BY（reduce中局部排序）替代，但在不同的reducer中数据可能会有重合。DISTRIBUTE BY控制map的输出在reducer中是如何划分的，通常不用关心。Distribute by可以结合SORT BY，将相同的数据发送到同一个reducer中，这样可以避免不同reducer中的数据重合。SELECT s.ymd, s.symbol, s.price FROM stocks s DISTRIBUTE BY s.symbol SORT BY s.symbol ASC, s.ymd ASC;
ASC是可以省略的。Hive要求DISTRIBUTE BY需要写到SORT BY之前。
Cluster B y = 对同一字段的Distribute By + Sort By ASC只能升序

8. 类型转换不合法的情况

cast(value AS FLOAT)如果value不是合法的FLOAT，Hive会返回NULL

9. 分桶的优点

• 分区提供一个隔离数据和优化查询的便利的方式。不过，并非所有的数据集都可以形成合理的分区，特别是确定合适的划分大小。
• 分桶，是将数据集分解成更容易管理的若干部分的另一个技术。通过给定字段（user_id）的哈希值放到同一个桶内。同一个user_id就会存在于同一个桶内。
• 如果没有使用hive.enforce.bucketing属性，就需要自己设置和分桶个数相匹配的reducer个数，例如：使用set mapred.reduce.tasks=96，然后需要在SELECT语句后增加CLUSTER BY
• 因为桶的数量是固定的，所以他没有数据波动，桶对于抽样再适合不过。
• 分桶有利于高效的执行map-side JOIN。

10. 限制调整

LIMIT在很多情况下还是需要执行整个查询语句，然后再返回部分结果。Hive提供一个设置在使用LIMIT语句是，可以对源数据进行抽样：hive.limit.optimize.enable=true，一旦设置为true，那么还有连个参数可以控制这个操作hive.limit.orw.max.size和hive.limit.optimize.limit.file。
唯一的缺点，有可能输入中有用的数据永远不会被处理到。例如：任意的一个需要reudce步骤的查询，JOIN和GROUP BY操作，以及聚合函数的大多数调用，将会产生很不同的结果。也许这个差异在很多情况下是可以接受的，但是重要的是要理解。

11. 并行执行

Hive会将一个查询转换为多个MapReduce阶段，在无必要依赖的阶段之间可以并行执行。hive.exec.parallel=true开启，hive.exec.parallel.number=16设置并行度。

12. 调整mapper和reducer个数

1. Hive是按照输入的数据量大小来确定reducer个数的，我们可以通过dfs -count命令老计算输入量大小。
2. 有些情况map产生比实际多得多的数据，那么根据输入数据量来确定reducer个数就显得有些少了。因此，在map阶段尽量过滤掉不需要的数据就更好的满足reduce的计算。
3. 通常也会通过hive.exec.reducers.max设置reduce个数的上限，防止耗尽共享集群的资源。
4. 小文件合并：
   a) 在map执行前合并小文件较少map数set hive.input.format = org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
   b) 在map-only任务结束是合并小文件，默认true set hive.erge.mapfiles = true;
   c) 合并文件大小，默认256Mset hive.merge.size.per.task = 268435456;
   d) 当输出文件的平均大小小于该值时，启动一个独立的MR Job进行mergeset hive.merge.smallfiles.avgsize = 16777216;
5. 设置reduce个数
   a) 每个reduce处理的数据：hive.exec.reducers.bytes.per.reducer=256000000
   b)每个任务最大的reduce数，默认1009：hive.exec.reducers.max=1009
   c) 计算reducer数的公式：N = min(参数2，总输入数据量/参数1)

13. JVM 重用

由于每一个MapReduce的task都要启动一个JVM，如果是有很多小文件的场景，就会消耗过多的时间在初始化资源上。可以通过mapred.job.reuse.jvm.num.tasks指定重用JVM的个数。缺点：直到所有任务完成后才会释放。一旦出现某个“不平衡”的job一直无法完成工作（倾斜），其他的reduce task需要一直等待。

14. 索引

Hive v0.8.0 版本后增加了bitmap索引实现。索引可以用来加快GROUP BY的查询速度。

15. 推测执行

16. 单个MapReduce中多个GROUP BY

试图将多个GROUP BY操作组装到单个MapReduce。如果想启动这个优化，那么需要一组常用的GROUP BY键：hive.multigroupby.singlemr=false

17. 虚拟列

Hive提供了2种虚拟列便于定位数据和调试：一种用于将要进行划分的输入文件名，另一种用于文件中的块内偏移量。

hive> select INPUT__FILE__NAME, BLOCK__OFFSET__INSIDE__FILE,order_id from orders limit 10;
OK
input__file__name	block__offset__inside__file	order_id
hdfs://node1:9000/user/hive/warehouse/orders/orders.csv	90	2539329
hdfs://node1:9000/user/hive/warehouse/orders/orders.csv	114	2398795
hdfs://node1:9000/user/hive/warehouse/orders/orders.csv	142	473747
hdfs://node1:9000/user/hive/warehouse/orders/orders.csv	169	2254736
hdfs://node1:9000/user/hive/warehouse/orders/orders.csv	197	431534
hdfs://node1:9000/user/hive/warehouse/orders/orders.csv	224	3367565
hdfs://node1:9000/user/hive/warehouse/orders/orders.csv	252	550135
hdfs://node1:9000/user/hive/warehouse/orders/orders.csv	279	3108588
hdfs://node1:9000/user/hive/warehouse/orders/orders.csv	307	2295261
hdfs://node1:9000/user/hive/warehouse/orders/orders.csv	334	2550362
Time taken: 0.068 seconds, Fetched: 10 row(s)

第三种虚拟列提供了文件的行偏移量，需要通过参数显示的启动。

<property>
    <name>hive.exec.rowoffset</name>
    <value>true</value>
</property>

hive> select INPUT__FILE__NAME, BLOCK__OFFSET__INSIDE__FILE, ROW__OFFSET__INSIDE__BLOCK,order_id from orders limit 10;
OK
input__file__name	block__offset__inside__file	row__offset__inside__block	order_id
hdfs://node1:9000/user/hive/warehouse/orders/orders.csv	90	0	2539329
hdfs://node1:9000/user/hive/warehouse/orders/orders.csv	114	0	2398795
hdfs://node1:9000/user/hive/warehouse/orders/orders.csv	142	0	473747
hdfs://node1:9000/user/hive/warehouse/orders/orders.csv	169	0	2254736
hdfs://node1:9000/user/hive/warehouse/orders/orders.csv	197	0	431534
hdfs://node1:9000/user/hive/warehouse/orders/orders.csv	224	0	3367565
hdfs://node1:9000/user/hive/warehouse/orders/orders.csv	252	0	550135
hdfs://node1:9000/user/hive/warehouse/orders/orders.csv	279	0	3108588
hdfs://node1:9000/user/hive/warehouse/orders/orders.csv	307	0	2295261
hdfs://node1:9000/user/hive/warehouse/orders/orders.csv	334	0	2550362
Time taken: 1.191 seconds, Fetched: 10 row(s)
hive> 

18. HiveQL

Hive不支持行级插入操作、更新操作和删除操作。Hive也不支持事务。

19. 关于分区

使用过多的分区可能导致大量的文件。因此，一个理想的分区方案不应该导致产生太多的分区和文件夹目录，并且每个目录下的文件应该足够得大，应该是文件系统中块大小的若干倍。
按照时间分区，随着时间的推移分区数量的增长是“均匀的”。一个分区是一个目录，一个分桶是一个文件。

20. 常用验证结果的方法

将查询结果导入文件中：

insert overwrite local directory
'/opt/module/hive/datas/ortby-result'
select * from emp sort by depton desc;