一、用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做关联逻辑