title: How can you perform a Insert overwrite using Spark?
body: <p>I'm trying to transition one of our ETL Hive script to Spark where the Hive ETL script maintains a table where part of data needs to be deleted every night before the new sync. The Hive ETL takes the main table deletes data that in greater than 3 days using insert overwrite. Basically creates a temp table with data that doesn't surpass greater than three days and then overwrites the main table. </p>
  
  <p>With Spark (using Scala) I keep getting this error where I cannot write to the same source. Here's my code:</p>
  
  <pre><code>spark.sql ("Select * from mytbl_hive where dt &gt; date_sub(current_date, 3)").registerTempTable("tmp_mytbl")
  
  val mytbl = sqlContext.table("tmp_mytbl")
  mytbl.write.mode("overwrite").saveTableAs("tmp_mytbl")
  
  //writing back to Hive ...
  
  mytbl.write.mode("overwrite").insertInto("mytbl_hive")
  </code></pre>
  
  <p>I get the error that I cannot write to the table I'm reading from. </p>
  
  <p>Does anyone know of a better way of doing this?</p>