分享一个 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；

• 使用 CTE 后，SQL 代码不再冗长且结构清晰，从而也提高了代码的可阅读性和易维护性；

4. CTE 语法

WITH cte_name1 AS (select_statement1), cte_name2 AS (select statment2) sql_containing_cte_name:

• cte_name 是公共表表达式的名字;

• select_statement 是一个完整的 SELECT 语句;

• sql_containing_cte_name 是包含了刚刚定义的公共表表达式的 SQL 语句;

• CTE 的 scope 生命周期是 with 子句后的单条 select/insert 语句，所以定义了一个 CTE 以后只能在紧跟着的单条 SQL 中使用，后续的 SQL 语句中该 CTE 的定义是失效的；

5. CTE 优化点实施细节

使用公共表表达式 CTE 替换临时表，比如：

• 优化前 SQL: “create temporary table liming_temp1 stored as orc as select * from lv_test.dws_cdt_person where part_date=20220526 and branch_no = xxx; insert overwrite table lv_test.dws_cdt_person_liming partition(part_date) select * from liming_temp1;”

• 优化后 SQL：“with liming_cte1 as (select * from lv_test.dws_cdt_person where part_date=20220526 and branch_no = xxx) insert overwrite table lv_test.dws_cdt_person_liming partition(part_date) select * from liming_cte1;”

6. CTE 跨平台跨引擎适用性

经测试，CDH 和 TDH 中的 HIVE SQL 都支持 CTE，SPARK SQL 也支持 CTE.

7. CTE 其它说明

• 由于 CTE 的 scope 有效空间，只局限于紧跟 with 语句的单一一个 SQL 语句（只在当前一个 SQL 语句的执行期有效），所以当 CTE 需要被多个 SQL 语句引用时，尤其是 CTE 的对应结果需要通过复杂的计算逻辑查询获得时，为避免每次都重复计算降低所有作业的整体性能，且还是推荐使用临时表；

• A Common Table Expression (CTE) is a temporary result set derived from a simple query specified in a WITH clause, which immediately precedes a SELECT or INSERT keyword, you can use CTE to keep your hive queries as readable and performant as possible;

• The CTE is defined only within the execution scope of a single statement and not stored in the metastore, so you can refer a CTE only within the execution scope of the statement that immediately follows the WITH clause；

• One or more CTEs can be used in a Hive SELECT, INSERT, CREATE TABLE AS SELECT, or CREATE VIEW AS SELECT statement;

• 利用 CTE 优化作业性能，一般不需要修改任何默认参数，但如果要细粒度控制 CTE 底层是否物化/持久化，需要关注以下两个参数 hive.optimize.cte.materialize.threshold/hive.optimize.cte.materialize.full.aggregate.only：

• hive.optimize.cte.materialize.threshold:default 3, If the number of references to a CTE clause exceeds this threshold, Hive will materialize it before executing the main query block, -1 will disable this feature;

• hive.optimize.cte.materialize.full.aggregate.only: default true, If enabled only CTEs with aggregate output will be pre-materialized. All CTEs otherwise. Also the number of references to a CTE clause must exceeds the value of hive.optimize.cte.materialize.threshold;

• https://issues.apache.org/jira/browse/HIVE-1180

• https://cwiki.apache.org/confluence/display/Hive/Common+Table+Expression