sql delete?

[dan11]

<pre> Hello databricks people, I started working with databricks today. I have a sql script which I developed with sqlite3 on a laptop. I want to port the script to databricks. I started with two sql statements: select count(prop_id) from prop0; delete from prop0 where prop_id is null; They seem like simple statements. When I run them on data bricks I see this: Unsupported language features in query: delete from prop0 where prop_id is null. I find it hard to believe that databricks does not support statement: delete from prop0 where prop_id is null Am I doing something wrong? Is it reasonable to expect that databricks should support: delete from prop0 where prop_id is null ?? </pre>

[dan11]

The js-editor in this forum is horrible; I tried using pre-tags because of the angle-brackets I see in the upper menu and they got sanitized.

[vida]

Hi Dan,

Spark SQL is based on HiveQL. It allows you to use SQL syntax to do big data, like count your data. It does not, however, support operations like delete and update. I cover why in my talk here:

https://spark-summit.org/east-2016/events/not-your-fathers-database-how-to-use-apache-spark-properly...

-Vida

[BaranitharanV]

Vida, there is a document in Databricks menitoning that deletes are permitted on Delta.

https://docs.databricks.com/spark/latest/spark-sql/language-manual/delete.html

Are we missing anything here?

[Bill_Chambers]

Hey Dan, good to hear you're getting started with Databricks. This is not a limitation of Databricks it's a restriction built into Spark itself. Spark is not a data store, it's a distributed computation framework. Therefore deleting data would be unnecessary. If you don't need it, you would just filter it out either in a query or by setting it up as a new table as below.

%sql SELECT * FROM prop0 where prop_id is null AS new_table

It's probably worth your time reading a bit more about the tools that Spark provides, the learning curve is steep but once you get past the first steps you'll start seeing the value! 🙂 I might recommend some of the material that we have in the community edition like some of the CS100 coursework.