hive之with as 和 create temporary区别

这篇具有很好参考价值的文章主要介绍了hive之with as 和 create temporary区别。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

        在写hivesql语句时,通常因为实现一个比较复杂的逻辑时,往往使用多层嵌套关联,首先导致代码的可读性较差,其次是代码性能比较低。因为这个原因,很多人都会想方设法去优化代码,提高代码的可读性和性能。在优化中,我们尝尝想到的是去创建临时表的方法。目前创建临时表方法有两种,一种是create temporary会话级临时表创建;另外一种是with as的方式,这种方式更偏向像是视图(子查询)。

接下来我们看看这两种方式的相同点和不同点,什么场景适用什么方式。

相同点:这两种方式对外都可以称为临时表;都可以增加代码的可读性;都可以一定程度上提升复杂代码的性能

不同点:存储方式的不同,create temporary是将生成真实的数据临时存放到hdfs上,等到结束该会话的时候,就会将这个临时数据删除掉,而with as的本质相当于一个视图或者一个子查询,是存放到内存中,如果当前sql结束对应的子查询也随之没了。

对应不同的场景会使用到不同的临时表方式,也可以穿插使用。

例如:在逻辑相对复杂程度比较低,且内存不是特别充足的情况下,可以使用create temporary,在排错的时候相对轻松点。在逻辑比较复杂且内存充足,可以使用with as的形式。如果在逻辑比较复杂,内存不是非常充足,但是想程序稍微快点,并且增加可读性和排错便捷,可以使用两种相结合形式。

下面来看一下两种临时表分别是如何实现的

  创建对应的表结构

-- 需要用到的表结构
create table if not exists person
(
  id           int,
  name         string,
  age          int,
  occupation   string 
)
row format serde 'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
with serdeproperties('serialization.null.format' = 'null')
stored as orc;

create table if not exists diet
(
  id           int,
  breakfast    string,
  lunch        string,
  dinner       string 
)
row format serde 'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
with serdeproperties('serialization.null.format' = 'null')
stored as orc;

create table if not exists hobby
(
  id           int,
  hobby        string
)
row format serde 'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
with serdeproperties('serialization.null.format' = 'null')
stored as orc;

-- 最终插入的结果表
create table if not exists person_diet_hobby
(
  id           int,
  name         string,
  age          int,
  occupation   string,
  breakfast    string,
  lunch        string,
  dinner       string,
  hobby        string
)
row format serde 'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
with serdeproperties('serialization.null.format' = 'null')
stored as orc;

插入对应的数据,以cdh为准语法如下,如果是tdh需要在最后加上 from system.dual:

insert into table person
  (id, name,age,occupation)
  select 001 as id,
         '踢足球' as name,
         10 as age,
         '学生' as occupation;

insert into table diet
  (id, breakfast,lunch,dinner)
  select 001 as id,
         '牛奶和鸡蛋' as breakfast,
         '营养午餐' as lunch,
         '营养晚餐' as dinner;
		 
insert into table hobby
  (id, hobby)
  select 001 as id,
         '踢足球' as hobby;

create temporary

-- 将个人的运动放到个人下
create temporary table person_hobby stored as orc as
select a.id,a.name,a.age,a.occupation,b.hobby from person a
inner join hobby b
on a.id=b.id;

执行上述语句之后,在当前会话中查询这张临时表,可以看到对应的数据,使用 show create table person_hobby 语句可以查看到对应的存储位置

hive temporary,hive,hadoop

hive temporary,hive,hadoop

 关闭会话之后,在重新打开会话,再用上述语句去找这临时表就找不到了,证明这张表已经被删掉了

hive temporary,hive,hadoop

 

根据临时表,将需要的数据全部插入到对应的表中

insert into table person_diet_hobby (id,name,age,occupation,breakfast,lunch,dinner,hobby)
select a.id,a.name,a.age,a.occupation,b.breakfast,b.lunch,b.dinner,a.hobby
 from person_hobby a
 inner join diet b
 on a.id = b.id;

hive temporary,hive,hadoop 

with as 

with as 无法单独的去执行,需要放到一段sql中总体执行,必须要要以插入或者查询结束,前面生成的with as也可以给后面with as重复使用,并且每一段with as需要使用英文逗号做分割,按照上面插入的数据,来看下如何实现(以mr引擎实验)

with person_hobby as (select t1.id,t1.name,t1.age,t1.occupation,t2.hobby from person t1
inner join hobby t2
on t1.id=t2.id),
person_diet_hobby_temp as (select a.id,a.name,a.age,a.occupation,b.breakfast,b.lunch,b.dinner,a.hobby
 from person_hobby a
 inner join diet b
 on a.id = b.id)
insert into table person_diet_hobby (id,name,age,occupation,breakfast,lunch,dinner,hobby)
select id,name,age,occupation,breakfast,lunch,dinner,hobby
from person_diet_hobby_temp;

0: jdbc:hive2://node3:10000> with person_hobby as (select t1.id,t1.name,t1.age,t1.occupation,t2.hobby from person t1
. . . . . . . . . . . . . .> inner join hobby t2
. . . . . . . . . . . . . .> on t1.id=t2.id),
. . . . . . . . . . . . . .> person_diet_hobby_temp as (select a.id,a.name,a.age,a.occupation,b.breakfast,b.lunch,b.dinner,a.hobby
. . . . . . . . . . . . . .>  from person_hobby a
. . . . . . . . . . . . . .>  inner join diet b
. . . . . . . . . . . . . .>  on a.id = b.id)
. . . . . . . . . . . . . .> insert into table person_diet_hobby (id,name,age,occupation,breakfast,lunch,dinner,hobby)
. . . . . . . . . . . . . .> select id,name,age,occupation,breakfast,lunch,dinner,hobby
. . . . . . . . . . . . . .> from person_diet_hobby_temp;
INFO  : Compiling command(queryId=hive_20221222012112_c8d6d18b-d1f7-4a46-b54b-90c440888d31): with person_hobby as (select t1.id,t1.name,t1.age,t1.occupation,t2.hobby from person t1
inner join hobby t2
on t1.id=t2.id),
person_diet_hobby_temp as (select a.id,a.name,a.age,a.occupation,b.breakfast,b.lunch,b.dinner,a.hobby
from person_hobby a
inner join diet b
on a.id = b.id)
insert into table person_diet_hobby (id,name,age,occupation,breakfast,lunch,dinner,hobby)
select id,name,age,occupation,breakfast,lunch,dinner,hobby
from person_diet_hobby_temp
INFO  : Semantic Analysis Completed
INFO  : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:_col0, type:int, comment:null), FieldSchema(name:_col1, type:string, comment:null), FieldSchema(name:_col2, type:int, comment:null), FieldSchema(name:_col3, type:string, comment:null), FieldSchema(name:_col4, type:string, comment:null), FieldSchema(name:_col5, type:string, comment:null), FieldSchema(name:_col6, type:string, comment:null), FieldSchema(name:_col7, type:string, comment:null)], properties:null)
INFO  : Completed compiling command(queryId=hive_20221222012112_c8d6d18b-d1f7-4a46-b54b-90c440888d31); Time taken: 0.208 seconds
INFO  : Executing command(queryId=hive_20221222012112_c8d6d18b-d1f7-4a46-b54b-90c440888d31): with person_hobby as (select t1.id,t1.name,t1.age,t1.occupation,t2.hobby from person t1
inner join hobby t2
on t1.id=t2.id),
person_diet_hobby_temp as (select a.id,a.name,a.age,a.occupation,b.breakfast,b.lunch,b.dinner,a.hobby
from person_hobby a
inner join diet b
on a.id = b.id)
insert into table person_diet_hobby (id,name,age,occupation,breakfast,lunch,dinner,hobby)
select id,name,age,occupation,breakfast,lunch,dinner,hobby
from person_diet_hobby_temp
WARN  : 
INFO  : Query ID = hive_20221222012112_c8d6d18b-d1f7-4a46-b54b-90c440888d31
INFO  : Total jobs = 1
INFO  : Starting task [Stage-13:MAPREDLOCAL] in serial mode
INFO  : Execution completed successfully
INFO  : MapredLocal task succeeded
INFO  : Launching Job 1 out of 1
INFO  : Starting task [Stage-11:MAPRED] in serial mode
INFO  : Number of reduce tasks is set to 0 since there's no reduce operator
INFO  : number of splits:1
INFO  : Submitting tokens for job: job_1668791143457_0511
INFO  : Executing with tokens: []
INFO  : The url to track the job: http://node2:8088/proxy/application_1668791143457_0511/
INFO  : Starting Job = job_1668791143457_0511, Tracking URL = http://node2:8088/proxy/application_1668791143457_0511/
INFO  : Kill Command = /opt/cloudera/parcels/CDH-6.3.2-1.cdh6.3.2.p0.1605554/lib/hadoop/bin/hadoop job  -kill job_1668791143457_0511
INFO  : Hadoop job information for Stage-11: number of mappers: 1; number of reducers: 0
INFO  : 2022-12-22 01:21:28,400 Stage-11 map = 0%,  reduce = 0%
INFO  : 2022-12-22 01:21:35,588 Stage-11 map = 100%,  reduce = 0%, Cumulative CPU 3.03 sec
INFO  : MapReduce Total cumulative CPU time: 3 seconds 30 msec
INFO  : Ended Job = job_1668791143457_0511
INFO  : Starting task [Stage-8:CONDITIONAL] in serial mode
INFO  : Stage-5 is selected by condition resolver.
INFO  : Stage-4 is filtered out by condition resolver.
INFO  : Stage-6 is filtered out by condition resolver.
INFO  : Starting task [Stage-5:MOVE] in serial mode
INFO  : Moving data to directory hdfs://nameservice1/user/hive/warehouse/hs_huangbin.db/person_diet_hobby/.hive-staging_hive_2022-12-22_01-21-12_151_5361479725871257975-20/-ext-10000 from hdfs://nameservice1/user/hive/warehouse/hs_huangbin.db/person_diet_hobby/.hive-staging_hive_2022-12-22_01-21-12_151_5361479725871257975-20/-ext-10003
INFO  : Starting task [Stage-0:MOVE] in serial mode
INFO  : Loading data to table hs_huangbin.person_diet_hobby from hdfs://nameservice1/user/hive/warehouse/hs_huangbin.db/person_diet_hobby/.hive-staging_hive_2022-12-22_01-21-12_151_5361479725871257975-20/-ext-10000
INFO  : Starting task [Stage-3:STATS] in serial mode
INFO  : MapReduce Jobs Launched: 
INFO  : Stage-Stage-11: Map: 1   Cumulative CPU: 3.03 sec   HDFS Read: 13810 HDFS Write: 1562 HDFS EC Read: 0 SUCCESS
INFO  : Total MapReduce CPU Time Spent: 3 seconds 30 msec
INFO  : Completed executing command(queryId=hive_20221222012112_c8d6d18b-d1f7-4a46-b54b-90c440888d31); Time taken: 25.558 seconds
INFO  : OK
5 rows affected (25.798 seconds)
 文章来源地址https://www.toymoban.com/news/detail-562300.html

到了这里,关于hive之with as 和 create temporary区别的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • Hive beeline客户端启动报错Could not open client transport with JDBC Uri: jdbc:hive2://hadoop101:10000: Fail

    在安装hive的时候,启动hiveserver2后,启动beeline客户端报错如下: Could not open client transport with JDBC Uri: jdbc:hive2://hadoop101:10000: Failed to open new session: java.lang.RuntimeException: org.apache.hadoop.ipc.RemoteException(org.apache.hadoop.security.authorize.AuthorizationException): User: root is not allowed to impersonate

    2024年02月11日
    浏览(55)
  • Error: Could not open client transport with JDBC Uri: jdbc:hive2://hadoop102:10000: Failed to open..

    在hive目录下使用beeline命令:  具体的报错信息如下所示: 22/04/10 01:13:24 [main]: WARN jdbc.HiveConnection: Failed to connect to hadoop102:10000 Error: Could not open client transport with JDBC Uri: jdbc:hive2://hadoop102:10000: Failed to open new session: java.lang.RuntimeException: org.apache.hadoop.ipc.RemoteException(org.apache.hado

    2024年02月11日
    浏览(66)
  • 处理大数据的基础架构,OLTP和OLAP的区别,数据库与Hadoop、Spark、Hive和Flink大数据技术

    2022找工作是学历、能力和运气的超强结合体,遇到寒冬,大厂不招人,可能很多算法学生都得去找开发,测开 测开的话,你就得学数据库,sql,oracle,尤其sql要学,当然,像很多金融企业、安全机构啥的,他们必须要用oracle数据库 这oracle比sql安全,强大多了,所以你需要学

    2024年02月08日
    浏览(61)
  • sql中with as用法/with-as 性能调优/with用法

    with as 语句是SQL中的一种常用语法, 它可以为一个查询结果或子查询结果创建一个临时表 ,并且可以在后续的查询中使用这个临时表,在查询结束后该临时表就被清除了。这种语法的使用可以使得复杂的查询变得简单,同时也可以提高查询效率。 WITH AS短语,也叫做子查询部

    2024年02月16日
    浏览(39)
  • 【Mysql】WITH AS 语法详解

    WITH AS 语法是MySQL中的一种临时结果集,它可以在SELECT、INSERT、UPDATE或DELETE语句中使用。通过使用WITH AS语句,可以将一个查询的结果存储在一个临时表中,然后在后续的查询中引用这个临时表。这样可以简化复杂的查询,提高代码的可读性和可维护性。 其中, temporary_table_n

    2024年02月03日
    浏览(55)
  • SQL语句 with as 用法

    WITH  AS短语,也叫做子查询部分(subquery factoring),是用来定义一个SQL片断,该SQL片断会被整个SQL语句所用到。这个语句算是公用表表达式(CTE)。  比如  with A as (select * from class)     select *from A   这个语句的意思就是,先执行select * from class   得到一个结果,将这个结果记

    2024年02月14日
    浏览(43)
  • PgSQL的with as语法

     returning 返回的这一些字段,然后进行汇总为remove_alarms  然后select一下remove_alarms 出来的数据然后保存到tb_alarm_his 里面

    2024年04月09日
    浏览(70)
  • MySQL WITH AS及递归查询

    官网: WITH 是 SQL 中的一个,用于创建临时表达式(也称为 Common Table Expression,CTE),它允许你在一个查询中临时定义一个表达式,然后在后续的查询中引用它。 理解:当我们使用 WITH AS 他会帮我们创建一个临时的表, 这个临时表只在本次SQL中生效 。当我们使用这个

    2024年02月08日
    浏览(49)
  • MySQL中WITH AS语句的使用

    WITH 子句,也称为 Common Table Expressions(CTE),是一种在 SQL 查询中创建临时结果集的方法,存在于单个语句的范围内,以便在查询中多次引用。它可以使 SQL 查询更加模块化和可读。 WITH 子句的语法如下: 其中: cte_name 是 CTE 的名称。 (column_name1, column_name2, …) 是可选的,用于

    2024年04月24日
    浏览(33)
  • Oracle-day3:子查询、with as语句、聚合函数

    例题:

    2024年02月11日
    浏览(38)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包