hive用temporary table替换with

一、用temporary table替换with的效果

​ 用with从一个宽表中读取了2个字段生成tmb_tb，在后面的查询中两次用到了tmb_tb，执行用时70分钟；

​ 用 temporary table 替代with后（需要把临时表保存到内存中），相同的查询用时50分钟
二、with的缺点

​ with的内容并不会缓存，每用一次就会执行一次。我原本想实现的目标是通过with从宽表中取2个字段生成一个tmp_tb，后面之后直接从tmp_tb中读取这两个字段，但是实际的情况是每从tmp_tb中抽一次数，就会从底层宽表中取数。

验证：

​ 下面的代码，只从tmp_tb中取一次数：
 

with tmp_tb as (
  select dt, uid
  from t_1
  where dt > '2021-06-01'
  union
  select dt, uid
  from t_2
  where dt > '2021-07-01'
)

select dt, uid
from tmp_tb
where dt > '2021-06-01' and dt < '2021-06-10'
group by dt, uid
 

生成的spark的DAG图如下，可以看到，取数的逻辑只执行了一次：

 如下面的代码，两次从tmp_tb中抽取内容然后做join：

with tmp_tb as (
  select dt, uid
  from t_1
  where dt > '2021-06-01'
  union
  select dt, uid
  from t_2
  where dt > '2021-07-01'
)

select uid
from
(
  select dt, uid
	from tmp_tb
	where dt > '2021-06-01' and dt < '2021-06-10'
  group by dt, uid
 ) t1 
 left join
 (
   select dt, uid
   from tmp_tb
   where dt > '2021-06-20' and dt < '2021-06-30'
   group by dt, uid
 ) t2 on t1.uid = t2.uid

​ 生成的spark的DAG如下，可以看到相同的取数逻辑执行了2次

 

三、用temporary table替换

​ 代码：

set
    hive.exec.temporary.table.storage;		--将临时表保存到内存中
create temporary table vip_tmp as
select dt, uid
  from t_1
  where dt > '2021-06-01'
  union
  select dt, uid
  from t_2
  where dt > '2021-07-01'
 ;
 select uid
from
(
  select dt, uid
	from tmp_tb
	where dt > '2021-06-01' and dt < '2021-06-10'
  group by dt, uid
 ) t1 
 left join
 (
   select dt, uid
   from tmp_tb
   where dt > '2021-06-20' and dt < '2021-06-30'
   group by dt, uid
 ) t2 on t1.uid = t2.uid
 order by dt

​ hive会把上面的代码分成2个job提交，第一个job创建临时表，第二个job做关联逻辑