Impala - CREATE TABLE after a WITH clause

Question

I have a query with several WITH clauses, then a CREATE TABLE :

    WITH TABLE_1 AS (
        SELECT * FROM SOMEWHERE_1
    ), TABLE_2 AS (
        SELECT * FROM SOMEWHERE_2

    (
        CREATE TABLE TABLE_3 AS
        (
            SELECT TABLE_1.*, TABLE_2.*
            FROM TABLE_1 
            INNER JOIN TABLE_2 ON TABLE_2.key = TABLE_1.key
        )
    )

However I have the following error :

Encountered: CREATE Expected: SELECT, VALUES, WITH CAUSED BY: Exception: Syntax error

So I tried to put the CREATE statement first :

    CREATE TABLE_3 AS
    (
        WITH TABLE_1 AS (
            SELECT * FROM SOMEWHERE_1
        ), TABLE_2 AS (
            SELECT * FROM SOMEWHERE_2

        (
            SELECT TABLE_1.*, TABLE_2.*
            FROM TABLE_1 
            INNER JOIN TABLE_2 ON TABLE_2.key = TABLE_1.key
        )
    )

But now I have the following error :

AnalysisException: Could not resolve table reference: 'TABLE_1'

Note that :

• The above query WORKS without the "CREATE" statement
• My present situation is more complex than this simpe example, and I would like to keep the WITH statements, for clarity.

Answer 1

Hmmm. I think this will work:

CREATE TABLE TABLE_3 AS
    WITH TABLE_1 AS (
          SELECT * FROM SOMEWHERE_1
         ),
         TABLE_2 AS (
          SELECT * FROM SOMEWHERE_2
         )
    SELECT TABLE_1.*, TABLE_2.*
    FROM TABLE_1 INNER JOIN
         TABLE_2
         ON TABLE_2.key = TABLE_1.key;

Of course, you will have other problems, such as the key column being duplicated in the results -- and that should generate another error. In practice, you should select exactly the columns that you want.