You are viewing an old version of this page. View the current version.

Compare with Current View Page History

Version 1 Next »

Common Table Expression Syntax

withClause: cteClause (COMMA cteClause)*
cteClause: Identifier AS (select statment)

Additional Grammar Rules

  • The With Clause is not supported within SubQuery Blocks
  • CTEs are supported in Views, CTAS and Insert statements.
  • Recursive Queries are not supported.

Examples

CTE in Select

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 e.g.
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
;

CTE in Views, CTAS, Insert statements

-- insert test
create table s1 like src;
with q1 as ( select key, value from src where key = '5')
from q1
insert overwrite table s1
select *
;

-- ctas
create table s2 as
with q1 as ( select key from src where key = '4')
select * from q1
;

-- view e.g.
create view v1 as
with q1 as ( select key from src where key = '5')
select * from q1
;
select * from v1;
 
-- view e.g., 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
;

In the second View e.g. the result will contain rows with key = '5'; because in the view's statement the CTE defined in the view definition takes effect

Also see the JIRAs:

  • HIVE-1180 Support Common Table Expressions (CTE) in Hive
  • No labels