with as 语句真的会把查询的数据存内存嘛？

于 2023-02-13 14:50:59 发布
阅读量172 收藏
点赞数 1
分类专栏： spark源码精读分析系列 hive源码阅读 文章标签： hive hadoop 数据仓库 spark 大数据
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/xiaoluobutou/article/details/129008932
版权

面对网上的一些结论，我们要有自己的判断。从源码中找真相~~

最近有好几个朋友都有咨询这个问题，大概有两类：

1、为啥我用了with..as效率没有提高？

2、sql跑不动，改成with..as的写法，会不会更好些？

网上博客几乎都有结论with ... as语句会把数据放在内存：

 

hive-sql

在hive中有一个参数

hive.optimize.cte.materialize.threshold

这个参数在默认情况下是-1（关闭的）；当开启（大于0），比如设置为2，则如果with..as语句被引用2次及以上时，会把with..as语句生成的table物化，从而做到with..as语句只执行一次，来提高效率。

测试

explain
with atable as (
     SELECT  id,source,channel
            FROM  test
​
)
select source from atable WHERE   channel = '直播'
union ALL
select source from atable WHERE   channel = '视频'

不设置该参数时，执行计划：

STAGE DEPENDENCIES:
 Stage-1 is a root stage
 Stage-0 depends on stages: Stage-1
​
STAGE PLANS:
 Stage: Stage-1
  Map Reduce
   Map Operator Tree:
     TableScan
      alias: test
      Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
      Filter Operator
       predicate: (channel = '直播') (type: boolean)
       Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
       Select Operator
        expressions: source (type: string)
        outputColumnNames: _col0
        Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
        Union
         Statistics: Num rows: 2 Data size: 0 Basic stats: PARTIAL Column stats: NONE
         File Output Operator
          compressed: false
          Statistics: Num rows: 2 Data size: 0 Basic stats: PARTIAL Column stats: NONE
          table:
            input format: org.apache.hadoop.mapred.SequenceFileInputFormat
            output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
            serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
     TableScan
      alias: test
      Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
      Filter Operator
       predicate: (channel = '视频') (type: boolean)
       Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
       Select Operator
        expressions: source (type: string)
        outputColumnNames: _col0
        Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
        Union
         Statistics: Num rows: 2 Data size: 0 Basic stats: PARTIAL Column stats: NONE
         File Output Operator
          compressed: false
          Statistics: Num rows: 2 Data size: 0 Basic stats: PARTIAL Column stats: NONE
          table:
            input format: org.apache.hadoop.mapred.SequenceFileInputFormat
            output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
            serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
​
 Stage: Stage-0
  Fetch Operator
   limit: -1
   Processor Tree:
    ListSink

 从执行计划上看，test表被读两次。



设置set hive.optimize.cte.materialize.threshold=1

，执行计划：

STAGE DEPENDENCIES:
 Stage-1 is a root stage
 Stage-6 depends on stages: Stage-1 , consists of Stage-3, Stage-2, Stage-4
 Stage-3
 Stage-0 depends on stages: Stage-3, Stage-2, Stage-5
 Stage-8 depends on stages: Stage-0
 Stage-2
 Stage-4
 Stage-5 depends on stages: Stage-4
 Stage-7 depends on stages: Stage-8
​
STAGE PLANS:
 Stage: Stage-1
  Map Reduce
   Map Operator Tree:
     TableScan
      alias: test
      Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
      Select Operator
       expressions: id (type: int), source (type: string), channel (type: string)
       outputColumnNames: _col0, _col1, _col2
       Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
       File Output Operator
        compressed: false
        Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
        table:
          input format: org.apache.hadoop.mapred.TextInputFormat
          output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
          serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
          name: default.atable
​
 Stage: Stage-6
  Conditional Operator
​
 Stage: Stage-3
  Move Operator
   files:
     hdfs directory: true
     destination: hdfs://localhost:9000/tmp/hive/bytedance/bae441cb-0ef6-4e9c-9f7a-8a5f97d0e560/_tmp_space.db/ce44793b-6eed-4299-b737-f05c66b2281b/.hive-staging_hive_2021-03-24_20-17-38_169_5695913330535939856-1/-ext-10002
​
 Stage: Stage-0
  Move Operator
   files:
     hdfs directory: true
     destination: hdfs://localhost:9000/tmp/hive/bytedance/bae441cb-0ef6-4e9c-9f7a-8a5f97d0e560/_tmp_space.db/ce44793b-6eed-4299-b737-f05c66b2281b
​
 Stage: Stage-8
  Map Reduce
   Map Operator Tree:
     TableScan
      alias: atable
      Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
      Filter Operator
       predicate: (channel = '直播') (type: boolean)
       Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
       Select Operator
        expressions: source (type: string)
        outputColumnNames: _col0
        Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
        Union
         Statistics: Num rows: 2 Data size: 0 Basic stats: PARTIAL Column stats: NONE
         File Output Operator
          compressed: false
          Statistics: Num rows: 2 Data size: 0 Basic stats: PARTIAL Column stats: NONE
          table:
            input format: org.apache.hadoop.mapred.SequenceFileInputFormat
            output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
            serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
     TableScan
      alias: atable
      Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
      Filter Operator
       predicate: (channel = '视频') (type: boolean)
       Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
       Select Operator
        expressions: source (type: string)
        outputColumnNames: _col0
        Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
        Union
         Statistics: Num rows: 2 Data size: 0 Basic stats: PARTIAL Column stats: NONE
         File Output Operator
          compressed: false
          Statistics: Num rows: 2 Data size: 0 Basic stats: PARTIAL Column stats: NONE
          table:
            input format: org.apache.hadoop.mapred.SequenceFileInputFormat
            output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
            serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
​
 Stage: Stage-2
  Map Reduce
   Map Operator Tree:
     TableScan
      File Output Operator
       compressed: false
       table:
         input format: org.apache.hadoop.mapred.TextInputFormat
         output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
         serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
         name: default.atable
​
 Stage: Stage-4
  Map Reduce
   Map Operator Tree:
     TableScan
      File Output Operator
       compressed: false
       table:
         input format: org.apache.hadoop.mapred.TextInputFormat
         output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
         serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
         name: default.atable
​
 Stage: Stage-5
  Move Operator
   files:
     hdfs directory: true
     destination: hdfs://localhost:9000/tmp/hive/bytedance/bae441cb-0ef6-4e9c-9f7a-8a5f97d0e560/_tmp_space.db/ce44793b-6eed-4299-b737-f05c66b2281b/.hive-staging_hive_2021-03-24_20-17-38_169_5695913330535939856-1/-ext-10002
​
 Stage: Stage-7
  Fetch Operator
   limit: -1
   Processor Tree:
    ListSink

​可以看到test表被物化了。

源码：

从源码看，在获取元数据时，会做参数判断，判断参数阈值及cte的引用次数

spark-sql

spark对cte的操作比较少，在spark侧，现在还没发现有相关的优化参数

with atable as (
     SELECT  content_type,
                    channel,
                    channel_note,
                    enter_method,
                    enter_method_note
            FROM    search_dw.dim_ecom_enter_channel_df
            
)
select channel from atable WHERE   content_type = '直播'
union ALL
select channel from atable WHERE   content_type = '视频'

 

Hey!

我是小萝卜算子

 在成为最厉害最厉害最厉害的道路上

很高兴认识你

 推荐阅读

Spark sql 谓词下推之CombineFilters及可以改进的地方

spark sql join情况下谓词下推优化器PushPredicateThroughJoin

Spark sql逻辑执行计划优化器——EliminateOuterJoin【消除outerjoin】

小萝卜算子
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
oracle with as内存,oracle中with as子句的用法小结（转）
weixin_35792271的博客
04-03 590
withas也叫做子查询部分，首先定义一个sql片段，该sql片段会被整个sql语句所用到，为了让sql语句的可读性更高些，作为提供数据的部分，也常常用在union等集合操作中。withas最大的好处就是适当的提高代码可读性，而且如果with子句在后面要多次使用到，这可以大大的简化SQL。关于with as子句的用法，下面两个例子看看就OK了：--例子1WITHdept_costsAS(SE...
with as 语句真的会把数据存内存嘛？（源码剖析）
格子衫
09-24 2060
with as常见问题，大概有两类： 1、为啥我用了with..as效率没有提高？ 2、sql跑不动，改成with..as的写法，会不会更好些？ 网上博客几乎都有结论with ... as语句会把数据放在内存： 前言1： hive可以通过with查询来提高查询性能，因为先通过with语法将数据查询到内存（既然是加到内存中，如果数据量太大，估计会占用大量资源，可能会报内存溢出），然后后面其他查询可以直接使用，这种方法与创建临时表类似但是不需要创建临时表实体表，内存中的子查询结果再会话结束后会自动删除
Hive语法内关于With as 的数据是否会缓存到内存分析
小道的博客
06-21 3407
  WITH AS短语，也叫做子查询部分（subquery factoring），可以定义一个SQL片断，该SQL片断可以被后面的SQL语句引用，从而使SQL语句的可读性更高。   在关系型数据库中，对于UNION ALL，使用WITH AS定义了一个UNION ALL语句，当该片断被调用2次以上，优化器会自动将该WITH AS短语所获取的数据放入一个Temp表中。   Hive在后面的版本也引入了WITH AS 这个公用表表达式（CTE）语法糖，但是对于后面语句的多次引用是否会继续将该WITH AS短语所
再一次利用with as 优化SQL
落落的专栏 专注SQL调优 性能调优
01-09 1万+
上海的一个哥们问我有个SQL跑了4个小时都没跑完，实在受不了了，找我优化一下。我确实挺佩服他的，要是我遇到跑了几分钟的，就受不了了。 SQL语句和执行计划如下： --sql id：1qbbw3th4x8yc SELECT "VOUCHER".FID "ID", "ENTRIES".FID "ENTRIES.ID", "ENTRIES".FEntryD
with as 语句真的会把数据存内存嘛？（源码剖析）.docx
11-12
然而，如果启用了物化，Hive会在第一次执行`with as`子句后将结果存储在内存中，后续的查询就可以直接从内存中获取数据，避免了重复扫描源表，这在处理大量数据时可能会显著提升性能。 需要注意的是，物化CTE虽然...
python闭包、深浅拷贝、垃圾回收、with语句知识点汇总
09-17
Python编程语言中有许多核心概念，其中包括闭包、深浅拷贝、垃圾回收和with语句。这些知识点对于理解和编写高效、安全的代码至关重要。 **1. 闭包** 闭包是一种特殊的函数，它记住了自己被创建时的环境，即能够访问...
python数据分析与可视化-13-分页查询.ev4.zip
最新发布
06-03
分页查询允许我们有效地管理和展示大数据库的结果，避免一次性加载所有数据导致内存压力过大或者响应时间过长。 在Python中，我们可以使用各种库来实现分页查询，比如SQLAlchemy（用于ORM操作）、Pandas（用于数据...
Python进阶期末模拟卷（含参考答案）
06-07
`with`语句用于上下文管理，与模块导入无关。 7. `try...except...finally`是Python中处理异常的标准结构，`catch`不是Python的异常处理关键字。 8. 在Python中，创建对象时会调用`__init__`方法，这是初始化方法...
SQL Server中选出指定范围行的SQL语句代码
09-14
在SQL Server中，选择指定范围行的SQL语句通常是用于数据分页查询或者特定行的提取，这在处理大量数据时非常有用。由于标准的SQL语法并不直接支持直接选取特定行号的记录，我们需要采取一些策略来实现这个功能。下面...
Max分页、With as内存表 存储过程
分享编程和生活的快乐
12-10 2122
ALTER PROCEDURE [MySchema].[StaNumDiscoverAnaly] (@start int, @pageSize int, @staSel nvarchar(200), @dateS nvarchar(20), @dateE nvarchar(20), @caseId nvarchar(10)) AS DECLARE @strSql nvarchar(3000); DECLARE @tableName nvarchar(20); SELECT @tableName
Hive中使用 with as 优化SQL
热门推荐
Abysscarry的博客
07-31 7万+
背景： 当我们书写一些结构相对复杂的SQL语句时，可能某个子查询在多个层级多个地方存在重复使用的情况，这个时候我们可以使用 with as 语句将其独立出来，极大提高SQL可读性，简化SQL~ 注：目前 oracle、sql server、hive等均支持 with as 用法，但 mysql并不支持！ 一、介绍 with as 也叫做子查询部分，首先定义一个sql片段，该sql片...
Oracel中With...as..的用法，总结
kayla的博客
07-13 1359
Oracel中With...as..的用法，总结： 首先要先了解with...as...的语法： withtempNameas(select....) select... 接下来一1-19中得到11-14为例进行模拟......... 一般的sql如下： select*from ( --模拟一个20行的数据 SELECTL...
在SQL Server中，关于with as使用介绍
weixin_30478923的博客
07-20 733
一．WITH AS的含义 WITH AS短语，也叫做子查询部分（subquery factoring），可以让你做很多事情，定义一个SQL片断，该SQL片断会被整个SQL语句所用到。有的时候，是为了让SQL语句的可读性更高些，也有可能是在UNION ALL的不同部分，作为提供数据的部分。 特别对于UNION ALL比较有用。因为UNION ALL的每个部分可能相同，但是如果每个部分都去执行...
一条SQL能占多大内存？
wujiandao的专栏
06-20 2378
初学计算机时，我经常琢磨的一个问题是：一个进程到底能吃多大内存，能把系统内存吃完？学了数据库后，我又开始问自己类似的问题，一条 SQL 能把数据库内存全部吃完？假设数据库系统内存 有128...
SQL优化 第九章 With改写优化SQL
songjian1104的专栏
08-03 474
参考资料： 本系列博客主要参考资料有CUUG冉乃纲老师数据库教学笔记，《SQL优化核心思想》（罗炳森，黄超，钟侥著），《PostgreSQL技术内幕：查询优化深度探索》（张树杰著），排名不分先后。 9.1 提取公共项 9.1.1. 数据脚本 drop table test1; drop table test2; create table test1 as select * from...
【Hive】with as 语句真的会把数据存内存嘛？（源码剖析）
Sunny的专栏
09-26 2698
0.前言 最近有好几个朋友都有咨询这个问题，大概有两类： 1、为啥我用了with..as效率没有提高？ 2、sql跑不动，改成with..as的写法，会不会更好些？ 网上博客几乎都有结论with ... as语句会把数据放在内存： 一1.1、Hive-SQL 在hive中有一个参数 hive.optimize.cte.materialize.threshold 这个参数在默认情况下是-1（关闭的）；当开启（大于0），比如设置为2，则如果with..as语句被引用2次...
hive中使用with as优化SQL语句
fuyun6363的博客
03-10 6721
目录前言语法注意举例在 select 中使用 CTECTE in Views, CTAS, and Insert Statementswith的相关总结 前言 hive可以通过with查询来提高查询性能，因为先通过with语法将数据查询到内存（既然是加到内存中，如果数据量太大，估计会占用大量资源，可能会报内存溢出），然后后面其它查询可以直接使用，这种方法与创建临时表类似但是不需要创建临时表实体表，...
sql语句中with用法
qq_34247077的博客
10-08 1万+
with嵌套子查询用法 在比较复杂，数据量大的情况下，为了提高查询效率，需要用到一些子查询。一层一层嵌套的子查询不方便，显得sql语句比较繁琐。可以用 with语句 构建临时表查询：具体用法如下： --只有一个临时表时 with tmp as ( select empno ,ename ,sal from db_hive.emp) select t.empno,t.ename,t.sal fr...
oracle with as 多个临时表查询慢
04-05
可能的原因如下： 1. 临时表没有正确的索引，导致查询慢。 2. 查询语句没有使用合适的优化器，导致查询效率低下。 3. 数据量过大，导致查询慢。 4. 数据库配置不合理，例如内存不足、磁盘空间不足等。 解决方法如下： 1. 给临时表添加合适的索引，提高查询效率。 2. 使用合适的查询语句和优化器，提高查询效率。 3. 对数据进行分区、分页等操作，减少查询数据量。 4. 对数据库进行优化配置，例如增加内存、清理磁盘空间等。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

小萝卜算子

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值