topic Re: CREATE TEMP TABLE FROM CTE in Data Engineering

CREATE TEMP TABLE FROM CTE

Constantine — Wed, 03 Nov 2021 02:46:29 GMT

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?

Re: CREATE TEMP TABLE FROM CTE

-werners- — Wed, 03 Nov 2021 07:08:56 GMT

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

Re: CREATE TEMP TABLE FROM CTE

jmorris — Thu, 21 Mar 2024 17:55:29 GMT

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.