大数据从业者必知必会的Hive SQL调优技巧 | 京东云技术团队

摘要：在大数据领域中，Hive SQL被广泛应用于数据仓库的数据查询和分析。然而，由于数据量庞大和复杂的查询需求，Hive SQL查询的性能往往不尽人意。本文针对Hive SQL的性能优化进行深入研究，提出了一系列可行的调优方案，并给出了相应的优化案例和优化前后的SQL代码。通过合理的优化策略和技巧，能够显著提升Hive SQL的执行效率和响应速度。

**关键词：**Hive SQL；性能优化；调优方案；优化案例

1. 引言

随着大数据时代的到来，数据分析和挖掘变得越来越重要。Hive作为Hadoop生态系统中的数据仓库工具，扮演着重要的角色。然而，由于数据量庞大和查询复杂性，Hive SQL查询的执行效率往往较低。因此，深入了解Hive SQL调优技巧对于数据工程师和数据分析师来说至关重要。

2. 先做个自我反思

很多时候， Hive SQL 运行得慢是由开发人员对于使用的数据了解不够以及一些不良的使用习惯引起的。

•真的需要扫描这么多分区吗？

比如，对于销售明细事务表来说，扫描一年的分区和扫描一周的分区所带来的计算、 IO 开销完全是两个量级，所耗费的时间肯定也是不同的。作为开发人员，我们需要仔细考虑业务的需求，尽量不要浪费计算和存储资源。

•习惯使用select *这样的方式，而不是用到哪些列就指定哪些列吗？

比如，select coll, col2 from your_table ，另外， where 条件中也尽量添加过滤条件，以去掉无关的数据行，从而减少整个 MapReduce 任务中需要处理、分发的数据量。

•需要计算的指标真的需要从数据仓库的公共明细层来自行汇总吗？

是不是数仓团队开发的公共汇总层已经可以满足你的需求？对于通用的、管理者驾驶舱相关的指标等通常设计良好的数据仓库公共层肯定已经包含了，直接使用即可。

3 查询优化

3.1 尽量原子化操作

尽量避免一个SQL包含复杂逻辑，可以使用中间表来完成复杂的逻辑。建议对作业进行合理拆分，降低作业出问题重跑时资源的浪费和下游时效的影响。

3.2 使用合适的数据类型

选择合适的数据类型可以减小存储空间和提高查询效率。例如，将字符串类型转换为整型类型可以节省存储空间并加快查询速度。

优化案例

优化前：

SELECT * FROM table WHERE age = '30'; 

优化后：

SELECT * FROM table WHERE age = 30;

3.3 避免全表扫描

尽量避免全表扫描，可以通过WHERE子句筛选出需要的数据行，或者使用LIMIT子句限制返回结果的数量。

反面案例

天天全表扫描计算所有历史数据。 map数超20万。

Select * from table where dt<=’{TX_DATE}’

优化案例1

优化前：

–优化前副表的过滤条件写在where后面，会导致先全表关联再过滤分区。

select a.* from test1 a left join test2 b on a.uid = b.uid where a.ds='2020-08-10' and b.ds='2020-08-10' 

优化后：

select a.* from test1 a left join test2 b on (b.uid is not null and a.uid = b.uid and b.ds='2020-08-10') where a.ds='2020-08-10'

优化案例2

利用max函数取表最大分区，造成全表扫描。

优化前：

Select max(dt) from table

优化后：

使用自定义(show partition 或 hdfs dfs –ls )的方式替代max(dt)

3.4 使用分区

数据分区是一种将数据按照某个字段进行分组存储的技术，可以有效减少查询时的数据扫描量。通过分区字段进行数据过滤，可以只对目标分区进行查询，加快查询速度。

优化案例

优化前：

SELECT * FROM table WHERE date = '2021-01-01' AND region = 'A';

优化后：

SELECT * FROM table WHERE partition_date = '2021-01-01' AND partition_region = 'A';

反面案例

代码写死日期，一次性不合理扫描2年+日志数据。map数超20万，而且会越来越大，直到跑不出来。

Select * from table where src_mark=’2