hive 之with as 和create view 和create temporary table用法

create temporary table test.cc_tmp  as select * from test.cc_join where name like '%c%';

explain 
select * from test.cc_tmp where id >0
union all 
select * from test.cc_tmp where id is null ;

create view  test.cc_tmp_v  as select * from test.cc_join where name like '%c%'

explain 
select * from test.cc_tmp_v where id >0
union all 
select * from test.cc_tmp_v where id is null ;

explain with tmp as 
(select * from test.cc_join where name like '%c%') 

select * from tmp where id >0
union all 
select * from tmp where id is null 

三者

create temporary table 是创建一张临时表，退出当前会话，数据就不见了

通过show create table 可以看到是放在tmp目录的。

 create view 其实和with tmp as 很相似，都是把复杂的可以重用的sql简化，我觉得唯一的区别就是 view是可以创建下次再使用的 但是with只是当前sql有效，甚至不是会话有效。

——————————————————————————————————————————

之前网上不知道看的那篇文章说with里面的内容可以复用，也就是只计算一次 下次就不计算了，直接拿上次的结果。放屁。

贴上 view 和with的explain 可以看到 都是经过了两次计算 

 而create temporary才是真正的一次计算

突然觉得这个例子不好。换个例子

create temporary table test.cc_tmp  as select a.id,b.name from test.cc_join a join test.cc_join b on a.id =b.id

select * from test.cc_tmp 
union all 
select * from test.cc_tmp 

create view  test.cc_tmp_v  as select a.id,b.name from test.cc_join a join test.cc_join b on a.id =b.id

explain 
select * from test.cc_tmp_v 
union all 
select * from test.cc_tmp_v

with tmp as 
(
select a.id,b.name from test.cc_join a join test.cc_join b on a.id =b.id) 
select * from tmp
union all 
select * from tmp 

 可以看到 这个join  with as 和 view 都是执行了两次，但是temporary table 只执行了一次join，所以如果以后尽量不要用with as 卵用没有。

使用temporary table 感觉才是优解

-----------以前有个地方说的不完全正确---------

当我们使用复杂的sql时 比如 select * from a join b join c 这种尽量使用create temporary table。

因为这种join比较耗时 一次即可。

但是如果我们使用的目的仅仅时简化sql比如 有时候查询指定的字段 select a,b,c,d,e,f,g,h from t

这种比较简单的查询还是推荐with tmp as ()语法，因为hive本身查询这种就很快，不需要额外花费时间落地为数据 这样还更耗时