分享一个 HIVE SQL 性能优化点-使用公共表表达式 CTE 替换临时表

分享一个 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中元数据的开销，也包括三副本模式创建和删除HDFS上的数据的开销；(使用临时表时会创建 hms中的元数据和 hdfs中的数据，session会话结束时会删除临时表在hms中的元数据和 hdfs中的数据)；
• 当数据量比较大时，Hdfs文件的创建和销毁，涉及到大量网络IO和磁盘IO，一般开销都是比较大的，对我们的大部分大数据应用来说，性能瓶颈一般是在IO而不是CPU；
• 使用