Hadoop Hive WITH Clause Syntax and Examples

With the Help of Hive WITH clause you can reuse piece of query result in same query construct. You can also improve the Hadoop Hive query using WITH clause. You can simplify the query by moving complex, complicated repetitive code to the WITH clause and refer the logical table created in your SELECT statements.

Hadoop Hive WITH Clause

A Hive WITH Clause can be added before a SELECT statement of you query, to define aliases for complex and complicated expressions that are referenced multiple times within the body of the SELECT statements.

The main advantage of Hive WITH clause is, you can use it wherever SELECT clause is acceptable in the SQL script or query. For instance, you can use it in INSERT, CTAS and SELECT. Another advantage of having WITH clause is, it simplifies the maintenance of your code. 

Related reading:

• Apache Hive Derived Column Support and Alternative
• Apache Hive Derived Tables and Examples

Hadoop WITH Clause Syntax

The syntax and usage of the WITH clause is similar to relational database command.

Below is the syntax of Hadoop Hive WITH clause:

[ <WITH clause> ] < <with list element> [ { <comma> <with list element> }... ]> 
<query expression body>;

Hadoop WITH Clause Examples

Below are the sample example of the Hive WITH clause:

Hive WITH clause example with the SELECT statement

WITH t1 as (SELECT 1), 
t2 as (SELECT 2),
t3 as (SELECT 3)
SELECT * from t1 
UNION ALL
SELECT * from t2
UNION ALL 
SELECT * from t3;

Hive WITH Clause in INSERT Statements

You can use the WITH clause while inserting data to table. For example:

WITH t11 as (SELECT 10),
t12 as (SELECT 20),
t13 as (SELECT 3) 
INSERT INTO t1 
SELECT * from t11 
UNION ALL 
SELECT * from t12 
UNION ALL 
SELECT * from t13;

Read:

• Hadoop Hive Date Functions and Examples
• Commonly used Hadoop Hive Commands
• Hive String Functions and Examples
• Hive Join Types and Examples