HIVE CTE

HIVE CTE

CTE(Common Table Expression)

公共表表达式 (CTE) 是从 WITH 子句中指定的简单查询派生的临时结果集，它紧跟在 SELECT 或 INSERT 关键字之前。CTE 仅在单个语句的执行范围内定义。在 Hive SELECT、INSERT、 CREATE TABLE AS SELECT或CREATE VIEW AS SELECT语句中可以使用一个或多个 CTE 。

作用：

相当于视图，定义了一个SQL片段，每次使用时候可以将该定义的SQL片段拿出来再被使用，该SQL片段可以理解为一个变量，主要用途简化SQL，让SQL更简洁，替换子查询，方便定位问题。

语法：

with temp_name as (select statement)

规则：

1.子查询块中不支持 WITH 子句；
2.视图、CTAS(Create Table As Select)和 INSERT 语句支持 CTE；
3.不支持递归查询。

优化：

默认情况下，如果使用CTE后被多次使用，则CTE子句就会被执行多次，若需要用HIVE CTE进行优化，则需要通过参数调优，如下：

hive.optimize.cte.materialize.threshold

该参数默认值为：-1，表示不开启物化，当开启（大于等于0），比如设置为2，表示如果with…as语句被引用2次及以上时，会把with…as语句生成的table物化，从而做到with…as语句只执行一次，来提高效率。
在默认情况下，可以通过explain来查看执行计划：

验证：

with temp as (select rand())

select * from temp 
union all 
select * from temp

运行结果：

从结果来看，产生的随机数不一样，说明CTE被执行了两次。

再将默认值改为1：

set hive.optimize.cte.materialize.threshold = 1 ;

查看运行结果：

从结果上看，产生的随机数一样，说明temp被物化了。物化类似于计算的中间结果进行缓存或落到磁盘，这样每次计算的时候可以从该中间结果中取数，这样才可以达到一次分析，多次使用的目的。