在写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 语句可以查看到对应的存储位置
关闭会话之后,在重新打开会话,再用上述语句去找这临时表就找不到了,证明这张表已经被删掉了
根据临时表,将需要的数据全部插入到对应的表中
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;
with as
with as 无法单独的去执行,需要放到一段sql中总体执行,必须要要以插入或者查询结束,前面生成的with as也可以给后面with as重复使用,并且每一段with as需要使用英文逗号做分割,按照上面插入的数据,来看下如何实现(以mr引擎实验)文章来源:https://www.toymoban.com/news/detail-562300.html
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模板网!