Child pages
  • Common Table Expression
Skip to end of metadata
Go to start of metadata

A Common Table Expression (CTE) is a temporary result set derived from a simple query specified in a WITH clause, which immediately precedes a SELECT or INSERT keyword.  The CTE is defined only within the execution scope of a single statement.  One or more CTEs can be used in a Hive SELECT, INSERTCREATE TABLE AS SELECT, or CREATE VIEW AS SELECT statement.

Version

Common Table Expressions are added in Hive 0.13.0 with HIVE-1180.

Common Table Expression Syntax

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 Statements

CTE in Views, CTAS, and Insert Statements

In the second View example, a query's CTE is different from the CTE used when creating the view. The result will contain rows with key = '5' because in the view's query statement the CTE defined in the view definition takes effect.

Also see this JIRA:

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