Hive小技巧和调优

作为Hadoop生态圈中的重要组件，Hive在数据分析、处理方面扮演着异常重要的角色。另外，Hive作为大数据组件，处理的数据量往往很大，合适的优化技巧在运行效率方面往往可以起到非常好的效果。

1、筛选重复记录

这是在业务中经常遇到的一个问题，主要场景往往是，同一条记录被多次插入，或者同一个id对应多条记录，但是只需要其中一条就足矣。

（1）对于重复记录，如果是数据去重，自然是可以使用distinct关键字处理，如果记录不同，而对于同一id任意一条记录字段都是有效的话，可以使用group by + max/min这种组合方式处理：

select id,max(c1) as c1,max(c2) as c2 from test_table group by id

（2）另外还有一种就是可以取任意一条记录，但是必须是同一条记录的所有字段，这时候可以借助row_number+join方式实现：

create table test_table2 as 
select *, row_number() over(order by id) as c1 from test_table1;

select* from
(select id,max(c1) as c1 from test_table2 group by id) t1 inner join test_table1 t2
on t1.id=t2.id and t1.c1=t2.c1;

（3）还有一种比较特殊的处理方式，就是利用Hive的行转列集合函数处理。当然这种方法用的比较少，也不建议这么用。

select id,collect_list(c1)[0] as c1,collect_list(c2)[0] as c2 from test_table group by id

2、多表合并

多表合并也是一个非常常见的场景，例如，有三张学生三门课成绩表，分别是学号和成绩，现在合并为一张表，四列分别是id和三门课成绩。对于上面的场景，通常的做法就是一个三表join即可，但是join对于资源的消耗和执行效率也很可观，其实在不利用join的情况下，利用union all + group by也可以完成这个任务：

select id,sum(score_1) as score_1,sum(score_2) as score_2,sum(score_3) as score_3 from
(select id,score_1,0 as score2,0 as score3 from test_table1
union all
select id,0 as score_1,score2,0 as score3 from test_table2
union all
select id,0 as score_1,0 as score2,score3 from test_table3) t1
group by id;

当然这里介绍的方法并不是为上述业务场景服务的，只是提供一种处理思路，另外在采用这种方法的时候需要注意对于NULL的处理。

3、join

数据库join是性能调优一个永恒的话题，常见的注意事项如下：

• 优先过滤后再进行连接操作，最大限度的减少参与连接的数据量。
• 小表连接大表，将小表放在左边，建议启动MapJoin方法。
• 内连接建议使用t1 inner join t2 on而非from t1, t2。

4、row_number排序

row_number常用来添加排序字段，对于分区排序建议使用 row_number() over(distribute by c1 sort by c2 desc)，而不是row_number() over(partition by cl1 order by c2)。

• distribute by + sort by：保证同一字段值只存在一个结果文件当中，结合sort by保证每个reduce任务结果有序，并发性控制好，推荐使用。
• order by：适用全局排序，缺陷是只能使用一个reduce任务。

5、压缩存储

综合网络I\O 、磁盘I\O、CPU开销以及压缩率，选择压缩比78%的Orc格式和压缩速度快的Snappy格式，进行Hive底层数据压缩存储。Snappy需在执行Hive ql时通过设置参数指定，Orc需在创建表时指定，具体语法如下：

Snappy格式设置参数

set hive.intermediate.compression.codec=org.apache.hadoop.io.compress.SnappyCodec;
set mapred.output.compression.codec=org.apache.hadoop.io.compress.SnappyCodec;
set mapred.map.output.compression.codec=org.apache.hadoop.io.compress.SnappyCodec;
set mapred.output.compression.codec=org.apache.hadoop.io.compress.SnappyCodec;

创建表时设置Orc格式

create table table_name(
  id bigint,
  ...)
…
row format serde
  'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
stored as inputformat
  'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
outputformat
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat';

6、并行执行

Hive默认情况下是不开启并行执行模式的，例如，如果两个不相关的子查询A join  B或者A union all B，既然A、B不相关，所以在资源允许情况下，最好是A、B两个子查询并行执行效率最高，但是默认情况下Hive是先执行完一个，再执行另一个，造成效率低下，因此可以开启并行模式：

set hive.exec.parallel=true;
set hive.exec.parallel.thread.number=12;

7、count去重统计

count去重统计常见方法就是 count(distinct column_a)，但是这种方法计算效率低，建议使用子查询group by加外层count来提升计算效率。另外，在统计数据的时候推荐count(1)而不是使用count(*)进行统计。

8、with

在hive查询中，经常会遇到一个子查询多次使用的情况，这时候第一可以使用子查询嵌套加别名，第二可以写两遍子查询。这两种方法不管哪一种相对with而言都比较麻烦。with可以预定义一段执行语句（相当于变量）在下面调用。这样既可以简化sql，而且因为with语句只执行一遍，还可以优化性能。

WITH t1 AS (
		SELECT *
		FROM carinfo
	), 
	t2 AS (
		SELECT *
		FROM car_blacklist
	)
SELECT * FROM t1 inner join t2 on t1.id=t2.id;

 

参考资料

https://www.cnblogs.com/raymoc/p/5323824.html