Hive参数配置以及HQL编写技巧

来源：互联网发布：人工智能物联网龙头股编辑：程序博客网时间：2024/04/20 11:04

前言

本文的意图在于根据常见hive作业运行场景，介绍hive以及hadoop的参数设置方法。

Group by数据倾斜：

如果需要group by的字段有一个值或一些值出现的频率很高：

可以用hive.groupby.skewindata参数平衡数据。

Map,Reduce数控制：

如果需要减小Map的数量：

~~_MRv1.0：~~

set mapred.min.split.size=<Long>

^MRv2.0:

set mapreduce.input.fileinputformat.split.minsize=<Long>

（相反，如果把min换成max就是增大map的数量,建议尽量减少map数）

如果需要限制map的数量：

~~^MRv1.0：~~

set mapred.map.tasks=<number>

^MRv2.0:

set mapreduce.job.maps=<number>

如果需要限制reduce的数量：

~~^MRv1.0：~~

set mapred.reduce.tasks=<number>

^MRv2.0:

set mapreduce.job.reduces=<number>

对于某些有数据倾斜的字段：

如A表与B表左外连接时：

A left outer join B on A.col1=B.col1;

如果A.col1有大量null值会照成倾斜，可以把A.col1为null的值拿出来单独处理，如：

A left outer join B on A.col1=B.col1 and A.col1 != null

union all

select * from A where A.col1 = null;

如果A表与B表内连接时：

A join B on A.col1=B.col1

同样，如果Acol1有大量的null值可以这么处理：

A join B on case when A.col1 is NULL then concat('NULL',rand()) else A.col1 end = B.col1

反正NULL不参与连接，所以这么处理后随机值也连接不上不仅分散了数据而且不影响最后结果。

减少不必要的操作：

select * from
    (select * from t1
        Group by c1,c2,c3
    Union all
    Select * from t2
        Group by c1,c2,c3
    ) t3
Group by c1,c2,c3;

可以明显看出t1,t2里面的group by c1,c2,c3是不必要的。这样写会生成3个Job但实际上一个Job就够了。

如果需要mapjoin的小表很大，可以先做一下处理：

Select * from log a
    Left outer join members b
    On a.memberid = b.memberid.
比如这种场景，常常需要在事实数据中附上一些用户信息，这个时候就需要和已有的用户信息表连接，但是
Members有600w+的记录，把members分发到所有的map上也是个不小的开销，而且map join不支持这么大的小表。如果用普通的join，又会碰到数据倾斜的问题。
解决方法就是先根据log取所有的memberid，然后mapjoin 关联members取今天有日志的members的信息，然后在和log做mapjoin
Select /*+mapjoin(b)*/* from log a
    Left outer join 
   (select  /*+mapjoin(c)*/d.*
        From 
            (select  distinct memberid from log ) c
            Join members d
            On c.memberid = d.memberid
   )b
   On a.memberid = b.memberid

一些控制Map,reduce的参数：

设置reduce所占用的内存：
mapreduce.reduce.memory.mb=10240;
设置reduce的JVM堆内存使用上限：
mapreduce.reduce.java.opts=-Xmx8196m;
设置reduce可用于shuffle的内存占堆内存的占比：
mapreduce.reduce.shuffle.input.buffer.percent=0.8
设置reduce何时进行in-mem的文件合并：
mapreduce.reduce.shuffle.merge.percent=0.7

用left semi join代替IN/EXISTS：

用left semi join能比IN/EXISTS少用一个JOB，如：

SELECT t1.key, t1.value
    FROM a  t1
left outer join 
(SELECT distinctkey from b) t2 
on t1.id = t2.id
where t2.id is not null;
与下面相比：
SELECT a.key, a.val
FROM a 
LEFT SEMI JOIN 
b 
on (a.key = b.key);
后者会少一个job.    

多组group by合为一个Job:

set hive.multigroupby.singlereducer=true;

尽量使用 RCFile 或者 SequenceFile 的表格式
尽量避免 load /user 目录下的用户数据
使用分区字段来提高查询性能
对于分区表，查询的时候，带上过滤条件，严禁对基础表 dt0 表的全表扫描
创建表的字段类型和 java 类型是对应的。不支持日期类型，提供转换为字符串类型的函数
查询语句中，不支持 having，可写嵌套的 select 来解决；group by 后只能是表的定义列名，不能像 mysql 那样可以为查询语句为逻辑处理结果声明的别名，但可为逻辑处理过程语句
hive 中分托管表和外部表，不同的主要是在 drop 时，托管表，从 hive 中删除元数据和表数据；外部表，只能删除元数据
hive 中 join 关联查询，只能通过 from 表 1 join 表 2 on (等值的关联条件) ,不支持像 mysql 或者 oracle 中，可以 from 表 1，表 2 where 表 1.列 = 表 2.列的形式
hive 中不支持 in (子查询语句)，比如： in (select id from 表名) .可以通过内连接或者半连接 from 表 1 left semi join 表 2 on （表 1.列名 = 表 2.列名），表 2只能在 on 中出现，不能在 select 中引用
通过 explain 查看 hive sql 执行计划及解分成后的 mapreduce 作业数等信息
hive 中在不需要全局排序的情况下，写排序语句时，最好用 distribute by 表名.字段名 sort by 表名.字段名 asc | desc 的形式，尽量不用 order by 形式

0 0