CREATE TEMP TABLE FROM CTE

[Constantine]

I have written a CTE in Spark SQL

WITH temp_data AS (
 
......
 
)
 
CREATE VIEW AS temp_view FROM SELECT * FROM temp_view; 

I get a cryptic error. Is there a way to create a temp view from CTE using Spark SQL in databricks?

[Kaniz_Fatma]

Hi @Constantine! My name is Kaniz, and I'm the technical moderator here. Great to meet you, and thanks for your question! Let's see if your peers in the community have an answer to your question first. Or else I will get back to you soon. Thanks.

[-werners- · Accepted solution]

In the CTE you can't do a CREATE.

It expects an expression in the form of expression_name [ ( column_name [ , ... ] ) ] [ AS ] ( query )

where expression_name specifies a name for the common table expression.

If you want to create a view from a CTE, you can do this:

-- CTE in CREATE VIEW statement
CREATE VIEW v AS
    WITH t(a, b, c, d) AS (SELECT 1, 2, 3, 4)
    SELECT * FROM t;
SELECT * FROM v;
+---+---+---+---+
|  a|  b|  c|  d|
+---+---+---+---+
|  1|  2|  3|  4|
+---+---+---+---+

PS. snippet from the Spark documentation

[jmorris]

Just want to say that you for replying to this. Just spent hours trying to figure out why my query wasn't working and this did the trick.