Hive开启CTE

CTE(Common Table Expression)公共表表达式简称 CTE，处理方式类似于视图扩展，是Hive三种保存临时结果的方法之一（另外两种是临时表和物化视图）， Hive早在 0.13.0 的时候便加入了这个功能。

hive cte的with as 只是定义了一个SQL片段，相当于View，默认是不会缓存数据的，执行计划中并没有缓存过程。要想保存复用数据，可以通过一个参数来开启

set hive.optimize.cte.materialize.threshold=2

1
2
3

这个参数默认值-1（关闭）；设为大于0的数字为开启，比如设置为2后，则如果with 片段使用次数>=2，hive会计算这部分数据并物化，后面引用的时候不再重复计算。

一个或多个 CTE 可以在 Hive SELECT、INSERT、 CREATE TABLE AS SELECT或CREATE VIEW AS SELECT语句中使用。

用法示例

Select 语句中的 CTE

with q1 as ( select key from src where key = '5')
select *
from q1;

 
-- from style
with q1 as (select * from src where key= '5')
from q1
select *;
  
  
-- chaining CTEs
with q1 as ( select key from q2 where key = '5'),
q2 as ( select key from src where key = '5')
select * from (select key from q1) a;
  
  
-- union example
with q1 as (select * from src where key= '5'),
q2 as (select * from src s2 where key = '4')
select * from q1 union all select * from q2;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

视图、CTAS 和Insert 语句中的 CTE

-- insert example
create table s1 like src;
with q1 as ( select key, value from src where key = '5')
from q1
insert overwrite table s1
select *;
 
 
-- ctas example
create table s2 as
with q1 as ( select key from src where key = '4')
select * from q1;
 
 
-- view example
create view v1 as
with q1 as ( select key from src where key = '5')
select * from q1;
select * from v1;
  
  
-- view example, name collision
create view v1 as
with q1 as ( select key from src where key = '5')
select * from q1;
with q1 as ( select key from src where key = '4')  --没有被使用 不会生效
select * from v1;
author csdn qq_19933029
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29