Using Apache HiveQL
Also available as:
PDF

Use a CTE in a query

You can use a common table expression (CTE) to simplify creating a view or table, selecting data, or inserting data.

  1. Use a CTE to create a table based on another table that you select using the CREATE TABLE AS SELECT (CTAS) clause.
    CREATE TABLE s2 AS WITH q1 AS (SELECT key FROM src WHERE key = '4') SELECT * FROM q1;
  2. Use a CTE to create a view.
    CREATE VIEW v1 AS WITH q1 AS (SELECT key FROM src WHERE key='5') SELECT * from q1; 
  3. Use a CTE to select data.
    WITH q1 AS (SELECT key from src where key = '5') 
                  SELECT * from q1;
  4. Use a CTE to insert data.
    CREATE TABLE s1 LIKE src; WITH q1 AS (SELECT key, value FROM src WHERE key = '5') FROM q1 INSERT OVERWRITE TABLE s1 SELECT *;