分享一个 HIVE SQL 性能优化点-使用公共表表达式 CTE 替换临时表
hive 作业的性能优化是一个永恒的话题,其优化方法也有很多,在此分享一个优化点,即编写 SQL 时使用公共表表达式 CTE 替换临时表,经测试优化效果还不错,尤其是涉及到当量IO的场景。
1. CTE 优化点概述
使用公共表表达式CTE (Common Table Expression) 替换临时表(create temporary table temp1 as xx),以提高 SQL 作业的运行效率,并提升代码的可阅读性和易维护性;
2. CTE 性能优化效果
- 某SQL,使用一个160万数据的临时表的情况下,代码优化前 73秒,优化后47秒,性能提升大概有35%;
- 某客户现场某 SQL 脚本优化前43分钟,优化后11分钟;(代码较长故不在此提供,核心要点是代码优化前使用了5个临时表,优化后使用了5个CTE);
- 具体的性能提升情况,跟临时表的个数,临时表的数据量,以及集群的网络和磁盘IO性能有关;
3. CTE 性能优化原理
CTE 在功能上类似于一个没有持久化元数据到 hms 也没有持久化数据到 HDFS 的临时表或视图,CTE 通过把查询结果集保存在内存/本地磁盘中,避免了使用临时表的一系列开销,从而达到了性能优化的目的:
- 使用临时表的系列开销,包括创建和删除 HMS中元数据的开销ÿ