with as 语句真的会把数据存内存嘛？（源码剖析）

最新推荐文章于 2024-07-09 14:48:22 发布
最新推荐文章于 2024-07-09 14:48:22 发布
阅读量1.9k 收藏 5
点赞数
分类专栏： Hive 文章标签： hive
原文链接：https://mp.weixin.qq.com/s/6K8kLwevIcoEW3C7CenNsw
版权

with as常见问题，大概有两类：

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

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

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

前言1：

hive可以通过with查询来提高查询性能，因为先通过with语法将数据查询到内存（既然是加到内存中，如果数据量太大，估计会占用大量资源，可能会报内存溢出），然后后面其他查询可以直接使用，这种方法与创建临时表类似但是不需要创建临时表实体表，内存中的子查询结果再会话结束后会自动删除。使用with as 可提高代码可读性。

前言2：

公用表表达式（CTE）是从with 子句中指定的简单查询派生的临时结果表（会把查询的表数据放到内存中，供其他查询随时使用），该子句紧跟再select或者insert关键字之前，CTE仅在单个语句的执行范围内定义。可以在hive select，insert，create table as select或者create view as select语句中使用一个或多个CTE

使用hive-1180在hive 0.13.0中添加了通用表达式。

简介：

        with ... as... 需要定义一个sql片段，会将这个片段产生的结果集保存在内存中

后续的sql均可以访问这个结果集合，作用与视图或临时表类似

语法说明

        1.with...as...必须和其他语句一起使用

        2.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 = '视频'

 

 

☞空白页
关注
  • 0
    点赞
  • 踩
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SparkSQL中横向迭代计算的4种方式，以及子查询、临时视图、永久视图、缓存表之间的区别？（附案例）
奇迹虎虎的博客
02-28 1270
永久视图 view：永久保存一段查询语句的逻辑；临时视图 temporary view：只在当前会话生效；缓存表cache table：只在当前会话有效，将一段查询结果集缓存到内存；table：永久有效，保存数据结构和数据本身到磁盘；with as：当子查询的嵌套层数太多时，可以用with as 增加可读性。
【Hive】with as 语句真的会把数据存内存嘛？（源码剖析）
Sunny的专栏
09-26 2576
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 6648
目录前言语法注意举例在 select 中使用 CTECTE in Views, CTAS, and Insert Statementswith的相关总结 前言 hive可以通过with查询来提高查询性能，因为先通过with语法将数据查询到内存（既然是加到内存中，如果数据量太大，估计会占用大量资源，可能会报内存溢出），然后后面其它查询可以直接使用，这种方法与创建临时表类似但是不需要创建临时表实体表，...
Hive语法内关于With as 的数据是否会缓存到内存分析
小道的博客
06-21 3314
  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 语句真的会把查询的数据存内存嘛？
淡定一生2333的博客
02-08 558
最近有好几个朋友都有咨询这个问题，大概有两类： 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
hive中的with as
人的大脑是cpu，而不是硬盘
06-16 383
语法 WITH t1 AS ( SELECT * FROM a ), t2 AS ( SELECT * FROM b ) SELECT * FROM t1 一次分析，多次使用，这也是为什么会提供性能的地方，达到了“少读”的目标。 待验证： with as 是提高了可读性，可若是后面多次利用这个片段，那完整的SQL就会异常庞大，放在大数据Hive中，job数量甚至会翻几倍 with不适合过大的表，会落盘，频繁shuffle ，过大时直接落表更好。 ...
Spark-SQL那些事
zhixingheyi_tian的博客
05-18 853
WITH AS WITH AS短语，也叫做子查询部分，定义一个SQL片断后，该SQL片断可以被整个SQL语句所用到。有的时候，with as是为了提高SQL语句的可读性，减少嵌套冗余。 with A as ( select * from user ) select * from A, customer where customer.userid = user.id** 先执行select * from user把结果放到一个临时表A中，作为全局使用。 with as的将频繁执行的s
with as 语句真的会把数据存内存嘛？（源码剖析）.docx
11-12
然而，如果启用了物化，Hive会在第一次执行`with as`子句后将结果存储在内存中，后续的查询就可以直接从内存中获取数据，避免了重复扫描源表，这在处理大量数据时可能会显著提升性能。 需要注意的是，物化CTE虽然...
python with (as)语句实例详解
09-18
在 `with` 语句中，通常会结合 `as` 关键字来创建一个上下文管理器，确保在使用资源后，即使出现异常，也会正确地执行清理操作，释放资源。 `with` 语句的基本语法结构如下： ```python with expression [as ...
sql with as用法详解
01-19
一．WITH AS的含义 WITH AS短语，也叫做子查询部分（subquery factoring），可以让你做很多事情，定义一个SQL片断，该SQL片断会被整个SQL语句所用到。有的时候，是为了让SQL语句的可读性更高些，也有可能是在UNION ALL的不同部分，作为提供数据的部分。 特别对于UNION ALL比较有用。因为UNION ALL的每个部分可能相同，但是如果每个部分都去执行一遍的话，则成本太高，所以可以使用WITH AS短语，则只要执行一遍即可。如果WITH AS短语所定义的表名被调用两次以上，则优化器会自动将WITH AS短语所获取的数据放入一个TEMP表里，如果只是被调用一
巧用with as 对sql语句多次利用
05-22
对于需重复查询的sql语句，可巧用with as 对sql语句进行精简，提高查询效率
SQL WITH AS使用
dbagaoshou的专栏
08-20 489
一．WITH AS的含义 WITH AS短语，也叫做子查询部分（subquery factoring），可以让你做很多事情，定义一个SQL片断，该SQL片断会被整个SQL语句所用到。有的时候，是为了让SQL语句的可读性更高些，也有可能是在UNION ALL的不同部分，作为提供数据的部分。 特别对于UNION ALL比较有用。因为UNION ALL的每个部分可能相同，但是如果每个部分都去执行
存储过程与缓存
慧不如痴
01-26 2166
前言     提升SQL Server最具性能的一个方面就是存储过程，SQL Server具备执行计划的缓存功能，以便计划重用。SQL Server2000增强了ad-hoc执行计划的缓存功能，就处理存储过程上性能最佳，其原因由于存储过程是作为数据库对象来使用;不过，存储过程的使用不当也必然导致缓存执行计划在初始查询时丢失，当然也会导致存储过程的重编译，因而带来不必要的性能损失。本文主要介绍
Spark学习笔记—Spark工作机制
Mr_zhujin的博客
02-18 941
一.Spark执行机制 1.执行机制总览 Spark应用提交后经历一系列转变，最后成为task在各个节点上执行。 RDD的Action算子触发job的提交，提交到Spark的Job生成RDD DAG，由DAGScheduler转换为Stage DAG，每个Stage中产生相应的Task集合，TaskScheduler将任务分发到Executor执行。每个任务对应的数据块，使用用户定义的函数进行处...
SQL之with子句
热门推荐
积一时之步，臻千里之遥程
03-29 3万+
with 语句 with 语句相当于建立了一张 临时虚拟表 即利用with子句为子查询的数据集作为一个内存临时表. 在内存中解析，提高执行效率.，并且提高SQL语句的可读性，用完即销毁。 语法 可以同时定义多个临时表 With Subtable1 as (select 1...), //as和select中的括号都不能省略 Subtable2 as (select 2...), //后面的没有with，逗号分割，同一个主查询同级别地方，with子查询只能定义一次 … Subtablen as (
存储过程中with as 语句查询结果给变量赋值
shirsl的博客
05-17 819
建立临时表，将select的结果放到临时表里面，然后再进行赋值（可能这个时候都不需要赋值了，直接使用select的结果就可以了）在oracle中，当with as语句的查询结果只有一个并且没有传入参数的时候可以对变量进行赋值。但是在DB2中，无论是有传参还是没有传参都无法执行。但是如果是需要传入参数的话，则不可执行；
高并发环境中With查询结果集过大引发的性能问题一例
cnfs3521的博客
08-18 785
我们知道，使用With as方式，可以简化SQL，更重要的是避免重复访问数据。 在一条SQL中，With子查询的数据，一次查询，多次使用，所以，Oracle会自动将With查询的结果集产生为临时表，这些临时数...
hive 调优
最新发布
郭新宇 天道酬勤
07-09 413
在设置动态分区后，产生的文件数会取决于map数和分区数的大小，假设动态分区初始有N个map数，同时生成M个分区，则中间会生成N*M个文件，通常这种情况就是让大部分数据尽量输出到一个reduce中进行处理，但是有些HiveSql不会产生reduce，也就是说文件最后没有进行合并处理，这种情况下可以用distribute by rand()的方式保证数据进行一次reduce操作，实现文件的合并。备注：set设置的参数是生成的文件个数，distribute by rand()保证数据随机分配到50个文件中。
用with as 语句把a.txt的内容打印到屏幕上
03-25
以下是使用 with as 语句将 a.txt 的内容输出到屏幕上的 Python 代码： ``` with open('a.txt', 'r') as f: print(f.read()) ``` 注意：这里假设 a.txt 文件在当前目录中。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值