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

简介: 分享一个 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;
  • 使用 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.
相关文章
|
3月前
|
SQL 存储 HIVE
Hive中的表是如何定义的?请解释表的结构和数据类型。
Hive中的表是如何定义的?请解释表的结构和数据类型。
34 0
|
4月前
|
SQL HIVE
Hive sql 执行原理
Hive sql 执行原理
43 0
|
3月前
|
消息中间件 SQL RocketMQ
RocketMQ-初体验RocketMQ(10)-过滤消息_SQL92表达式筛选消息
RocketMQ-初体验RocketMQ(10)-过滤消息_SQL92表达式筛选消息
63 0
|
8天前
|
SQL XML 前端开发
sql 性能优化基于explain调优(二)
sql 性能优化基于explain调优(二)
14 0
|
8天前
|
SQL Oracle 关系型数据库
Oracle的PL/SQL表达式:数据的魔法公式
【4月更文挑战第19天】探索Oracle PL/SQL表达式,体验数据的魔法公式。表达式结合常量、变量、运算符和函数,用于数据运算与转换。算术运算符处理数值计算,比较运算符执行数据比较,内置函数如TO_CHAR、ROUND和SUBSTR提供多样化操作。条件表达式如CASE和NULLIF实现灵活逻辑判断。广泛应用于SQL查询和PL/SQL程序,助你驾驭数据,揭示其背后的规律与秘密,成为数据魔法师。
|
10天前
|
SQL 数据库 HIVE
Hive【基础知识 05】常用DDL操作(数据库操作+创建表+修改表+清空删除表+其他命令)
【4月更文挑战第8天】Hive【基础知识 05】常用DDL操作(数据库操作+创建表+修改表+清空删除表+其他命令)
21 0
|
1月前
|
SQL 数据可视化 Apache
阿里云数据库内核 Apache Doris 兼容 Presto、Trino、ClickHouse、Hive 等近十种 SQL 方言,助力业务平滑迁移
阿里云数据库 SelectDB 内核 Doris 的 SQL 方言转换工具, Doris SQL Convertor 致力于提供高效、稳定的 SQL 迁移解决方案,满足用户多样化的业务需求。兼容 Presto、Trino、ClickHouse、Hive 等近十种 SQL 方言,助力业务平滑迁移。
阿里云数据库内核 Apache Doris 兼容 Presto、Trino、ClickHouse、Hive 等近十种 SQL 方言,助力业务平滑迁移
|
1月前
|
SQL Java 关系型数据库
MyBatis的动态SQL之OGNL(Object-Graph Navigation Language)表达式以及各种标签的用法
MyBatis的动态SQL之OGNL(Object-Graph Navigation Language)表达式以及各种标签的用法
18 0
|
1月前
|
SQL 关系型数据库 MySQL
干货!SQL性能优化,书写高质量SQL语句
干货!SQL性能优化,书写高质量SQL语句
31 2
|
2月前
|
SQL 消息中间件 Kafka
Flink部署问题之hive表没有数据如何解决
Apache Flink是由Apache软件基金会开发的开源流处理框架,其核心是用Java和Scala编写的分布式流数据流引擎。本合集提供有关Apache Flink相关技术、使用技巧和最佳实践的资源。

热门文章

最新文章