5. Hive 0.13 Feature: Common Table Expressions

A common table expression, or CTE, is a set of query results obtained from a simple query specified within a WITH clause and which immediately preceeds a SELECT or INSERT keyword. A CTE exists only within the scope of a single SQL statement. One or more CTEs can be used with the following SQL statements:

The following example demonstrates the use of q1 as a CTE in a SELECT statement:

WITH q1 AS (SELECT key from src where key = '5') 
   SELECT * from q1; 

The following example demonstrates the use of q1 as a CTE in an INSERT statement:

CREATE TABLE s1 LIKE src;
   WITH q1 AS (SELECT key, value FROM src WHERE key = '5') 
   FROM q1 INSERT OVERWRITE TABLE s1 SELECT *; 

The following example demonstrates the use of ql as a CTE in a CREATE TABLE AS SELECT clause:

CREATE TABLE s2 AS WITH q1 AS (SELECT key FROM src WHERE key = '4') 
   SELECT * FROM q1; 

The following example demonstrates the use of q1 as a CTE in a CREATE TABLE AS VIEW clause:

CREATE VIEW v1 AS WITH q1 AS (SELECT key FROM src WHERE key='5') 
   SELECT * from q1; 

CTEs are available by default in Hive 0.13. Hive administrators do not need to perform any configuration to enable them.

Limitations of Common Table Expressions