update query in Spark SQL

Question

I wonder can I use the update query in sparksql just like:

sqlContext.sql("update users set name = '*' where name is null")

I got the error:

org.apache.spark.sql.AnalysisException: 
Unsupported language features in query:update users set name = '*' where name is null

If the sparksql does not support the update query or am i writing the code incorrectly?

Spark SQL now supports update, delete and such data modification operations if the underlying table is in delta format. Check this out: https://docs.delta.io/0.4.0/delta-update.html#update-a-table — Anjana K V
– Anjana K V, Commented Jan 27, 2022 at 4:56

Pranav Shukla · Accepted Answer · 2016-05-30 06:26:55Z

19

Spark SQL doesn't support UPDATE statements yet.

Hive has started supporting UPDATE since hive version 0.14. But even with Hive, it supports updates/deletes only on those tables that support transactions, it is mentioned in the hive documentation.

See the answers in databricks forums confirming that UPDATES/DELETES are not supported in Spark SQL as it doesn't support transactions. If we think, supporting random updates is very complex with most of the storage formats in big data. It requires scanning huge files, updating specific records and rewriting potentially TBs of data. It is not normal SQL.

answered May 30, 2016 at 6:26

Pranav Shukla

2,2262 gold badges18 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sversch Over a year ago

There is an open ticket in the Spark project to improve support for Hive transaction tables (i.e. Hive tables which support updates) issues.apache.org/jira/browse/SPARK-15348

user3937422 Over a year ago

Any new updates on this whether it is supported now ? or any alternative to do the same ?

Collectives™ on Stack Overflow

update query in Spark SQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related