Hive调优

1.      设置hive.map.aggr=true，提高HiveQL聚合的执行性能。

这个设置可以将顶层的聚合操作放在Map阶段执行，从而减轻清洗阶段数据传输和Reduce阶段的执行时间，提升总体性能。

缺点：该设置会消耗更多的内存。

注：顶层的聚合操作（top-levelaggregation operation）,是指在group by语句之前执行的聚合操作。例如，

hive> SET hive.map.aggr=true;

hive> SELECTcount(*), avg(salary)FROM employees group by ** having max()>1

 

2.      显示数据时，使用“local mode”可避免启动MapReduce，显著提升性能。

当执行“select * from table”时，Hive会简单地仅仅从表中读取数据并将格式化的数据输出到控制端，这个过程不会生成MapReduce程序。而对于其它类型的查询，比如指定字段或者包含表关联的查询等，Hive会使用MapReduce执行这些查询。若想避免这些查询的MapReduce执行，可以设置hive.exec.mode.local.auto=true; 这样的话，Hive会尝试在本地执行相关查询并将数据显示出来。

注：这种情况下，Hive查询的数据可能不全，只是一个结点上的数据，可供测试查询使用。

 

3.      （陷阱）浮点数的比较

hive> SELECT name, salary, deductions['Federal Taxes']

> FROM employeesWHERE deductions['Federal Taxes'] >0.2;

其中deductions['FederalTaxes']为Double类型数据。

查出的数据实际上是>=0.2的记录。

原因：IEEE的浮点数表示，它影响几乎所有的程序！

简单来说就是，对于0.2这个数值的在计算机中的Float表示为0.2000001，Double值为0.200000000001,都比0.2略大。

解决方法：使用cast函数。WHEREdeductions['Federal Taxes'] > cast(0.2 AS FLOAT);

 

4.      Hive中进行表的关联查询时，尽可能将较大的表放在Join之后。

Hive在处理表的关联查询时，会默认对最后一张表执行streaming操作，也就是将其它的表缓存起来，将最后一张表与其它的表进行关联，这个操作只会读一遍这张最大的表，反之，该表会读取多次，特别是包含多张表时。另外，由于Join之前的表会默认缓存，如果大表放在前面的位置，也会造成内存的消耗。

但可以通过指令改变这种默认行为：

SELECT /*+ STREAMTABLE(s) */ s.ymd, s.symbol, s.price_close,d.dividend

FROM stocks s JOIN dividends d ON s.ymd = d.ymd AND s.symbol =d.symbol

WHERE s.symbol ='AAPL';

STREAMTABLE会告诉Hive的查询优化器将制定的表作为最大的表来处理。

 

5.      在where语句中添加分区过滤条件可加速查询的执行。

 

6.      避免笛卡尔积！

SELECTS * FROM stocks JOIN dividends; //没有指定ON条件，Hive会对两张表执行笛卡尔积连结！

SELECT * FROM stocks JOIN dividends

 WHERE stock.symbol =dividends.symbol and stock.symbol='AAPL';// 在Hive中，连结操作会在where条件之前执行，所以这条语句与上一条语句执行时间相当！

SELECT * FROM stocks JOIN dividends ON stock.symbol =dividends.symbol;//这样才能真正执行Inner Join而不是笛卡尔积！

 

7.      Map-side Join：Map端连结

SELECT /*+ MAPJOIN(d) */ s.ymd, s.symbol, s.price_close, d.dividend

FROM stocks s JOIN dividends d ON s.ymd = d.ymd AND s.symbol =d.symbol

WHERE s.symbol ='AAPL';

在Hive v0.7之前，MAPJOIN()会将指定的表，一般是较小的表，加载到内存中，这样整个连结过程会在Map段完成。这样可以避免产生冗余的中间数据（连结产生的中间表）同时也可以免除相应的Reduce操作，进而提高整体性能。

在Hive v0.7之后，需要设置hive.auto.convert.join=true，开启MapJoin功能。

注：另外也可以进行bucketMapJoin的优化，具体理解，待调研。

 

8.      Order by 和 Sort by 和 Distribute By 和Cluster By

这四个语句都和排序相关，但底层的执行细节不同。

Order By：会将所有的数据在一个reducer上执行，得到的结果是整体有序的。但是由于不能并发执行，所以效率比较低。

Sort By：排序操作在多个reducer上执行，得到的结果是局部有序（一个reducer内）的，但是整体数据不一定是严格有序的。另外，这个语句还可能造成数据的重叠和丢失。由于MapReduce是采用Hash的方式来组织数据的，所以当使用Sort By时，一个reducer的输出会覆盖另一个reducer的数据。

Distribute By：为Sort By而生！它可以修正SortBy带来的负面作用，避免数据的覆盖和丢失。Distribute By将保证具有相同的指定关键字的记录进入到同一个reducer进行处理，这样就可以避免reducer在输出数据时将不同reducer的记录放到同一个位置，从而造成数据的覆盖！

SELECT s.ymd, s.symbol, s.price_close

FROM stocks s

DISTRIBUTE BY s.symbol

SORT BY  s.symbol ASC, s.ymd ASC;

  

  Cluster By = Distribute By + Sort By！

  但是除了Order By之外产生的所有排序结果默认情况下（除非修改mapred.reduce.tasks的值）都不能做到结果的整体有序。

 

9.      抽样查询

对于大量的数据，有时会需要查看数据的状态，比如是否有记录，其中的某个字段是否有值，但是若对整张表查询可能会比较耗时，另外得出的结果也不具有随机性。

Hive支持抽样查询èTableSample

例如，有一张表numbers,其中包含一个列number

hive> SELECT * from numbers TABLESAMPLE(BUCKET 3 OUT OF 10 ONrand()) s;

2

4

rand()返回一个随机值，这里对应结果的条数。

hive> SELECT * from numbersTABLESAMPLE(BUCKET 3 OUT OF 10 ON number) s;

2

如果不用rand()而是特定的列名，那么在多次运行中，返回的结果是确定的。

 

         在Bucket语句中，分母(eg.10)代表数据会被散列到的桶的数目，分子代表被选中的桶的编号。

 

除了按桶抽样，也可以按块进行抽样：

SELECT * FROM numbersflatTABLESAMPLE(0.1 PERCENT) s;

注：按块抽样的最小抽样单元是一个HDFS的块，默认情况下，如果表的大小小于128MB，那么所有的列将会被抽出，无论设置的百分比是多少！

 

10.  UNION ALL è 同时返回多张表的查询结果

SELECT log.ymd, log.level, log.message

FROM (

SELECT l1.ymd, l1.level,

l1.message, 'Log1' AS source

FROM log1 l1

UNION ALL

SELECT l2.ymd, l2.level,

l2.message, 'Log2' AS source

FROM log1 l2

) log

SORT BY log.ymd ASC;

 

注：要求两个表查询结果的字段的个数和类型必须一致!

 

11.  （技巧）explain/explain extended è展开查询计划树，可以作为优化查询的工具。

12.  Limit 优化 è hive.limit.optimize.enable=true

可以避免查询中对整张表的query，它受一下两个条件的约束：

<property>

<name>hive.limit.row.max.size</name>

<value>100000</value>

<description>When trying a smaller subset of data for simpleLIMIT,

how much size we need to guarantee each row to have at least.

</description>

</property>

<property>

<name>hive.limit.optimize.limit.file</name>

<value>10</value>

<description>When trying a smaller subset of data for simpleLIMIT,

maximum number of files we can sample.</description>

</property> 

14.  并行执行è hive.exec.parallel=true

Hive将一个query语句转换成多阶段任务来执行，每次执行一个阶段的任务。当被解析成的多个阶段之间不存在依赖的时候，可以让多个阶段的任务并行执行，这可以大大加快任务执行的速度，但同时也许需要更多的集群资源。

15.  Mapper与Reducer数量的优化

折衷：数量太大，会导致任务的启动、调度和运行过程的开销太大；数量太小，无法很好地利用集群的并发特性。

Hive会在接收到查询任务后，根据输入数据的大小评估所需要的reducer数量，但这个过程需要时间开销。默认的hive.exec.reducers.bytes.per.reducer是1GB，也可以改变这个值。

如何自己评估输入数据的大小？

[edward@etl02 ~]$ hadoop dfs -count /user/media6/fracture/ins/* |tail -4

1 82614608737 hdfs://.../user/media6/fracture/ins/hit_date=20120118

1 72742992546 hdfs://.../user/media6/fracture/ins/hit_date=20120119

1 172656878252 hdfs://.../user/media6/fracture/ins/hit_date=20120120

1 2 362657644hdfs://.../user/media6/fracture/ins/hit_date=20120121

注：当在执行Hadoop任务时，特别是hadoop-streaming脚本，如果只有mapper而没有reducer的话，可以将reducer数量设为0，这可以作为解决数据倾斜的一种方法！

 

16.  JVM 复用 è 在一个JVM实例上运行多个mapreduce任务，减少创建jvm实例的开销

         <property>

<name>mapred.job.reuse.jvm.num.tasks</name>

<value>10</value>

<description>How many tasks torun per jvm. If set to -1, there is no limit.

JVM Reuse | 139

</description>

</property>

缺点：会造成潜在的集群资源的浪费。

 

17.  探测性执行（Speculative Execution）

所谓探测性执行，是指Hadoop会启动同一个任务的多个副本在集群上执行，但它会丢弃该阶段的产生的多个副本的数据。

这个阶段会消耗更多的集群资源，目的是为了探测执行较慢的TaskTrackers，并将它们列入黑名单，进而提升整体工作流程的性能。

<property>

<name>mapred.map.tasks.speculative.execution</name>

<value>true</value>

<description>If true, then multiple instances of some maptasks

may be executed in parallel.</description>

</property>

<property>

<name>mapred.reduce.tasks.speculative.execution</name>

<value>true</value>

<description>If true, then multiple instances of some reducetasks

may be executed in parallel.</description>

</property>

 

18.  虚拟列

Hive提供了三个虚拟列：INPUT__FILE__NAME,BLOCK__OFFSET__INSIDE__FILE和ROW__OFFSET__INSIDE__BLOCK。但ROW__OFFSET__INSIDE__BLOCK默认是不可用的，需要设置hive.exec.rowoffset为true才可以。可以用来排查有问题的输入数据。

hive> SELECT INPUT__FILE__NAME, BLOCK__OFFSET__INSIDE__FILE, line

> FROM hive_text WHERE line LIKE '%hive%' LIMIT 2;

har://file/user/hive/warehouse/hive_text/folder=docs/

data.har/user/hive/warehouse/hive_text/folder=docs/README.txt  2243

har://file/user/hive/warehouse/hive_text/folder=docs/

data.har/user/hive/warehouse/hive_text/folder=docs/README.txt  3646

 

hive> set hive.exec.rowoffset=true;

hive> SELECT INPUT__FILE__NAME, BLOCK__OFFSET__INSIDE__FILE,

> ROW__OFFSET__INSIDE__BLOCK

> FROM hive_text WHERE line LIKE '%hive%' limit 2;

file:/user/hive/warehouse/hive_text/folder=docs/README.txt 2243 0

file:/user/hive/warehouse/hive_text/folder=docs/README.txt3646 0

 

 

hive.optimize.cp=true：列裁剪

hive.optimize.prunner：分区裁剪

hive.limit.optimize.enable=true：优化LIMIT n语句

hive.limit.row.max.size=1000000：

hive.limit.optimize.limit.file=10：最大文件数

 

1. 本地模式(小任务)：

需要满足以下条件：

　　1.job的输入数据大小必须小于参数：hive.exec.mode.local.auto.inputbytes.max(默认128MB)

　　2.job的map数必须小于参数：hive.exec.mode.local.auto.tasks.max(默认4)

　　3.job的reduce数必须为0或者1

hive.exec.mode.local.auto.inputbytes.max=134217728

hive.exec.mode.local.auto.tasks.max=4

hive.exec.mode.local.auto=true

hive.mapred.local.mem：本地模式启动的JVM内存大小

 

2. 并发执行：

hive.exec.parallel=true ，默认为false

hive.exec.parallel.thread.number=8

 

3.Strict Mode：

hive.mapred.mode=true，严格模式不允许执行以下查询：

分区表上没有指定了分区

没有limit限制的order by语句

笛卡尔积：JOIN时没有ON语句

 

4.动态分区：

hive.exec.dynamic.partition.mode=strict：该模式下必须指定一个静态分区

hive.exec.max.dynamic.partitions=1000

hive.exec.max.dynamic.partitions.pernode=100：在每一个mapper/reducer节点允许创建的最大分区数

DATANODE：dfs.datanode.max.xceivers=8192：允许DATANODE打开多少个文件

 

5.推测执行：

mapred.map.tasks.speculative.execution=true

mapred.reduce.tasks.speculative.execution=true

hive.mapred.reduce.tasks.speculative.execution=true;

 

6.Single MapReduce MultiGROUP BY

hive.multigroupby.singlemar=true：当多个GROUP BY语句有相同的分组列，则会优化为一个MR任务

 

7. hive.exec.rowoffset：是否提供虚拟列

 

8. 分组

两个聚集函数不能有不同的DISTINCT列，以下表达式是错误的：

INSERT OVERWRITE TABLE pv_gender_agg SELECT pv_users.gender, count(DISTINCT pv_users.userid), count(DISTINCT pv_users.ip) FROM pv_users GROUP BY pv_users.gender;

SELECT语句中只能有GROUP BY的列或者聚集函数。

 

9.

hive.map.aggr=true;在map中会做部分聚集操作，效率更高但需要更多的内存。

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

 

10.

hive.groupby.skewindata=true：数据倾斜时负载均衡，当选项设定为true，生成的查询计划会有两个MRJob。第一个MRJob 中，

Map的输出结果集合会随机分布到Reduce中，每个Reduce做部分聚合操作，并输出结果，这样处理的结果是相同的GroupBy Key

有可能被分发到不同的Reduce中，从而达到负载均衡的目的；第二个MRJob再根据预处理的数据结果按照GroupBy Key分布到

Reduce中（这个过程可以保证相同的GroupBy Key被分布到同一个Reduce中），最后完成最终的聚合操作。

 

11.Multi-Group-By Inserts：

FROM test

INSERT OVERWRITE TABLE count1

SELECT count(DISTINCT test.dqcode)

GROUP BY test.zipcode

INSERT OVERWRITE TABLE count2

SELECT count(DISTINCT test.dqcode)

GROUP BY test.sfcode;

 

12.排序

ORDER BY colName ASC/DESC

hive.mapred.mode=strict时需要跟limit子句

hive.mapred.mode=nonstrict时使用单个reduce完成排序

SORT BY colName ASC/DESC ：每个reduce内排序

DISTRIBUTE BY(子查询情况下使用 )：控制特定行应该到哪个reducer，并不保证reduce内数据的顺序

CLUSTER BY ：当SORT BY 、DISTRIBUTE BY使用相同的列时。

 

 

 

13.合并小文件

hive.merg.mapfiles=true：合并map输出

hive.merge.mapredfiles=false：合并reduce输出

hive.merge.size.per.task=256*1000*1000：合并文件的大小

hive.mergejob.maponly=true：如果支持CombineHiveInputFormat则生成只有Map的任务执行merge

hive.merge.smallfiles.avgsize=16000000：文件的平均大小小于该值时，会启动一个MR任务执行merge。

 

 

 

14.map/reduce数目

减少map数目：

　　set mapred.max.split.size

　　set mapred.min.split.size

　　set mapred.min.split.size.per.node

　　set mapred.min.split.size.per.rack

　　set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat

增加map数目：

当input的文件都很大，任务逻辑复杂，map执行非常慢的时候，可以考虑增加Map数，来使得每个map处理的数据量减少，从而提高任务的执行效率。

假设有这样一个任务：

select data_desc, count(1), count(distinct id),sum(case when …),sum(case 

when ...),sum(…) from a group by data_desc

如果表a只有一个文件，大小为120M，但包含几千万的记录，如果用1个map去完成这个任务，肯定是比较耗时的，这种情况下，我们要考虑将这一个文件合理的拆分成多个，这样就可以用多个map任务去完成。

　　set mapred.reduce.tasks=10;

　　create table a_1 as select * from a distribute by rand(123);

这样会将a表的记录，随机的分散到包含10个文件的a_1表中，再用a_1代替上面sql中的a表，则会用10个map任务去完成。每个map任务处理大于12M（几百万记录）的数据，效率肯定会好很多。

 

reduce数目设置：

　参数1：hive.exec.reducers.bytes.per.reducer=1G：每个reduce任务处理的数据量

　参数2：hive.exec.reducers.max=999(0.95*TaskTracker数)：每个任务最大的reduce数目

　reducer数=min(参数2,总输入数据量/参数1)

　set mapred.reduce.tasks：每个任务默认的reduce数目。典型为0.99*reduce槽数，hive将其设置为-1，自动确定reduce数目。