4. New Feature: Common Table Expressions

A Common Table Expression, or CTE, in SQL 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 do anything to enable them.

Limitations

Hive 0.13 imposes the following restrictions on the use of Common Table Expressions: