How to delete from a temp view or equivalent in spark sql databricks?

[User16790091296]

I need to delete from a temp view in databricks, but it looks like i can do only merge, select and insert. Maybe i missed something but I did not find any documentation on this.

[franco_patano]

You cant delete data from a view, but you can create or replace temp view with the same name with the data you want deleted filtered out.

For example, if you have a view myview, that you want to delete the record with id=2, do the following:

CREATE OR REPLACE TEMP VIEW myview

as

select * from myview where id = 2;

[-werners-]

Franco is right, in case you use an ordinary parquet file underneath.

With Delta Lake it is possible to delete data. So basically the data you read (and want to delete from) has to come from Delta lake. Then you can create a view on it and use the DELETE command.

[youssefmrini]

You can't delete a temp view because it's only a view.

[User16857282152]

This is incorrect, see reply below.

If you meant, dropping a temp view does NOT delete the underlying data.

Then that statement is correct.

[User16857282152]

A temp view is a pointer.

The information for a temp view is stored in the spark catalog

You can drop a temp view with

spark.catalog.dropTempView("view_name")

You could also drop a temp view in a sql cell with

DROP TABLE "temp_view_name"

Here is some code to demonstrate

df = spark.sql("select 1 id") # creates a dataframe

df.createOrReplaceTempView("temp_test") # registers it as a temp view

spark.catalog.listTables() # Shows all tables including temp views

spark.catalog.dropTempView("temp_test") # drops the temp view

Note that the dataframe still exists, display(df) to verify. The tempview is merely a pointer to the dataframe.

If you are using sql the sample code would look something like this.

CREATE TEMPORARY VIEW temp_test as select 1; --Creates the view

Drop table temp_test; --drops the view

[crazy_horse]

What about

%sql

DROP TABLE IF EXISTS xxxxx