topic Re: How to delete from a temp view or equivalent in spark sql databricks? in Data Engineering

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

User16790091296 — Thu, 24 Jun 2021 15:16:54 GMT

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.

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

franco_patano — Thu, 02 Sep 2021 20:01:39 GMT

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

select * from myview where id = 2;

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

-werners- — Thu, 09 Sep 2021 13:46:53 GMT

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.

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

youssefmrini — Thu, 09 Sep 2021 17:05:14 GMT

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

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

User16857282152 — Thu, 09 Sep 2021 18:48:58 GMT

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

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

User16857282152 — Thu, 09 Sep 2021 18:50:32 GMT

This is incorrect, see reply below.

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

Then that statement is correct.

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

crazy_horse — Mon, 22 Aug 2022 07:33:11 GMT

What about

%sql

DROP TABLE IF EXISTS xxxxx