title: How can I update/delete data in Spark-hive?
body: <p>I don't think my title can explain the problem so  here is the problem:</p>
  
  <p>Details build.sbt:</p>
  
  <pre><code>name := "Hello"
  scalaVersion := "2.11.8"
  version      := "1.0"
  
  libraryDependencies += "org.apache.spark" %% "spark-core" % "2.1.0"
  libraryDependencies += "org.apache.spark" %% "spark-sql" % "2.1.0"
  libraryDependencies += "org.apache.spark" % "spark-hive_2.11" % "2.1.0"
  </code></pre>
  
  <p>Code:</p>
  
  <pre><code>val sparkSession = SparkSession.builder().enableHiveSupport().appName("HiveOnSpark").master("local").getOrCreate()
  val hiveql : HiveContext  = new HiveContext(sparkSession.sparkContext);
  
  hiveql.sql("drop table if exists test")
  hiveql.sql("create table test (id int, name string) stored as orc tblproperties(\"transactional\"=\"true\")")
  hiveql.sql("insert into test values(1,'Yash')")
  hiveql.sql("insert into test values(2,'Yash')")
  hiveql.sql("insert into test values(3,'Yash')")
  hiveql.sql("select * from test").show()
  hiveql.sql("delete from test where id= 1")
  </code></pre>
  
  <p>Problem:</p>
  
  <pre><code>Exception in thread "main" org.apache.spark.sql.catalyst.parser.ParseException: 
  Operation not allowed: delete from(line 1, pos 0)
  
  == SQL ==
  delete from test where id= 1
  ^^^
  
  at org.apache.spark.sql.catalyst.parser.ParserUtils$.operationNotAllowed(ParserUtils.scala:39)
  at org.apache.spark.sql.execution.SparkSqlAstBuilder$$anonfun$visitFailNativeCommand$1.apply(SparkSqlParser.scala:925)
  at org.apache.spark.sql.execution.SparkSqlAstBuilder$$anonfun$visitFailNativeCommand$1.apply(SparkSqlParser.scala:916)
  at org.apache.spark.sql.catalyst.parser.ParserUtils$.withOrigin(ParserUtils.scala:93)
  at org.apache.spark.sql.execution.SparkSqlAstBuilder.visitFailNativeCommand(SparkSqlParser.scala:916)
  at org.apache.spark.sql.execution.SparkSqlAstBuilder.visitFailNativeCommand(SparkSqlParser.scala:52)
  at org.apache.spark.sql.catalyst.parser.SqlBaseParser$FailNativeCommandContext.accept(SqlBaseParser.java:952)
  at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:42)
  at org.apache.spark.sql.catalyst.parser.AstBuilder$$anonfun$visitSingleStatement$1.apply(AstBuilder.scala:66)
  at org.apache.spark.sql.catalyst.parser.AstBuilder$$anonfun$visitSingleStatement$1.apply(AstBuilder.scala:66)
  at org.apache.spark.sql.catalyst.parser.ParserUtils$.withOrigin(ParserUtils.scala:93)
  at org.apache.spark.sql.catalyst.parser.AstBuilder.visitSingleStatement(AstBuilder.scala:65)
  at org.apache.spark.sql.catalyst.parser.AbstractSqlParser$$anonfun$parsePlan$1.apply(ParseDriver.scala:54)
  at org.apache.spark.sql.catalyst.parser.AbstractSqlParser$$anonfun$parsePlan$1.apply(ParseDriver.scala:53)
  at org.apache.spark.sql.catalyst.parser.AbstractSqlParser.parse(ParseDriver.scala:82)
  at org.apache.spark.sql.execution.SparkSqlParser.parse(SparkSqlParser.scala:45)
  at org.apache.spark.sql.catalyst.parser.AbstractSqlParser.parsePlan(ParseDriver.scala:53)
  at org.apache.spark.sql.SparkSession.sql(SparkSession.scala:592)
  at org.apache.spark.sql.SQLContext.sql(SQLContext.scala:699)
  at main.scala.InitMain$.delayedEndpoint$main$scala$InitMain$1(InitMain.scala:41)
  at main.scala.InitMain$delayedInit$body.apply(InitMain.scala:9)
  at scala.Function0$class.apply$mcV$sp(Function0.scala:34)
  at scala.runtime.AbstractFunction0.apply$mcV$sp(AbstractFunction0.scala:12)
  at scala.App$$anonfun$main$1.apply(App.scala:76)
  at scala.App$$anonfun$main$1.apply(App.scala:76)
  at scala.collection.immutable.List.foreach(List.scala:381)
  at scala.collection.generic.TraversableForwarder$class.foreach(TraversableForwarder.scala:35)
  at scala.App$class.main(App.scala:76)
  at main.scala.InitMain$.main(InitMain.scala:9)
  at main.scala.InitMain.main(InitMain.scala)
  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  at java.lang.reflect.Method.invoke(Method.java:498)
  at com.intellij.rt.execution.application.AppMain.main(AppMain.java:147)
  </code></pre>
  
  <p>Same Problem with update query.</p>
  
  <p>So now as I have gone through <a href="https://forums.databricks.com/questions/6007/can-we-use-updatedelete-queries-in-databricks-with.html" rel="noreferrer">This</a>, <a href="https://community.hortonworks.com/questions/23836/spark-sql-update-command.html" rel="noreferrer">This</a>, <a href="https://stackoverflow.com/questions/37517371/update-query-in-spark-sql">update query in Spark SQL</a>,<a href="https://stackoverflow.com/questions/42062427/operation-not-allowed-alter-table-add-columnsline-1-pos-0/42394017">This</a>, <a href="https://stackoverflow.com/questions/42062427/operation-not-allowed-alter-table-add-columnsline-1-pos-0">This</a> and many others.</p>
  
  <p>I have come to know that Spark doesn't support update/delete but I am in a situation that need to use these both the operations. Can anyone suggest/help somehow.</p>