cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

CREATE TEMP TABLE FROM CTE

Constantine
Contributor III

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?

1 ACCEPTED SOLUTION

Accepted Solutions

-werners-
Esteemed Contributor III

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

View solution in original post

3 REPLIES 3

Kaniz_Fatma
Community Manager
Community Manager

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-
Esteemed Contributor III

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
New Contributor II

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.

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.

If there isn’t a group near you, start one and help create a community that brings people together.

Request a New Group