Search code examples
sqlimpala

impala how to create view from temp table?

I would like to create a view based on multiple temporary tables, I tried:

with t1 as (
        SELECT * 
        FROM mydb.my_existing_table 
        ),
        
    t2 as (
    SELECT * from t1)
   
    CREATE VIEW myotherdb.my_new_view AS  SELECT *  FROM t2

ParseException: Syntax error in line 21:undefined: CREATE VIEW myotherdb.... ^ Encountered: CREATE Expected: INSERT, SELECT, UPSERT, VALUES, COMMA CAUSED BY: Exception: Syntax error

I can rule out permission errors as this works:

CREATE VIEW myotherdb.my_new_view AS  SELECT * FROM mydb.my_existing_table

There must be some peculiar syntax that I missing when using temporary tables.

Solution

  • This one probably should work:

    CREATE VIEW myotherdb.my_new_view AS  
with t1 as (
        SELECT * 
        FROM mydb.my_existing_table 
        ),
        
    t2 as (
    SELECT * from t1)
SELECT *  FROM t2;