You can use a common table expression (CTE) to simplify creating a view or table,
selecting data, or inserting data.
-
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;
-
Use a CTE to create a view.
CREATE VIEW v1 AS WITH q1 AS (SELECT key FROM src WHERE key='5') SELECT * from q1;
-
Use a CTE to select data.
WITH q1 AS (SELECT key from src where key = '5')
SELECT * from q1;
-
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 *;