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 *;