共计 2794 个字符，预计需要花费 7 分钟才能阅读完成。
分享一个 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/jir…
- https://cwiki.apache.org/conf…