title: Spark SQL SaveMode.Overwrite, getting java.io.FileNotFoundException and requiring 'REFRESH TABLE tableName'
body: <p>For spark sql, how should we fetch data from one folder in HDFS, do some modifications, and save the updated data to the same folder in HDFS <strong>via Overwrite save mode</strong> without getting <code>FileNotFoundException</code>?</p>
  <pre class="lang-scala prettyprint-override"><code>import org.apache.spark.sql.{SparkSession,SaveMode}
  import org.apache.spark.SparkConf
  
  val sparkConf: SparkConf = new SparkConf()
  val sparkSession = SparkSession.builder.config(sparkConf).getOrCreate()
  val df = sparkSession.read.parquet(&quot;hdfs://xxx.xxx.xxx.xxx:xx/test/d=2017-03-20&quot;)
  val newDF = df.select(&quot;a&quot;,&quot;b&quot;,&quot;c&quot;)
  
  newDF.write.mode(SaveMode.Overwrite)
       .parquet(&quot;hdfs://xxx.xxx.xxx.xxx:xx/test/d=2017-03-20&quot;) // doesn't work
  newDF.write.mode(SaveMode.Overwrite)
       .parquet(&quot;hdfs://xxx.xxx.xxx.xxx:xx/test/d=2017-03-21&quot;) // works
  </code></pre>
  <p><code>FileNotFoundException</code> happens when we read data from the hdfs dir &quot;d=2017-03-20&quot;, and save (SaveMode.Overwrite) updated data to the same hdfs dir &quot;d=2017-03-20&quot;</p>
  <pre><code>Caused by: org.apache.spark.SparkException: Task failed while writing rows
    at org.apache.spark.sql.execution.datasources.FileFormatWriter$.org$apache$spark$sql$execution$datasources$FileFormatWriter$$executeTask(FileFormatWriter.scala:204)
    at org.apache.spark.sql.execution.datasources.FileFormatWriter$$anonfun$write$1$$anonfun$3.apply(FileFormatWriter.scala:129)
    at org.apache.spark.sql.execution.datasources.FileFormatWriter$$anonfun$write$1$$anonfun$3.apply(FileFormatWriter.scala:128)
    at org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:87)
    at org.apache.spark.scheduler.Task.run(Task.scala:99)
    at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:282)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)
  Caused by: java.io.FileNotFoundException: File does not exist: hdfs://xxx.xxx.xxx.xxx:xx/test/d=2017-03-20/part-05020-35ea100f-829e-43d9-9003061-1788904de770.snappy.parquet
  It is possible the underlying files have been updated. You can explicitly invalidate the cache in Spark by running 'REFRESH TABLE tableName' command in SQL or by recreating the Dataset/DataFrame involved.
    at org.apache.spark.sql.execution.datasources.FileScanRDD$$anon$1.nextIterator(FileScanRDD.scala:157)
    at org.apache.spark.sql.execution.datasources.FileScanRDD$$anon$1.hasNext(FileScanRDD.scala:102)
    at org.apache.spark.sql.catalyst.expressions.GeneratedClass$GeneratedIterator.scan_nextBatch$(Unknown Source)
    at org.apache.spark.sql.catalyst.expressions.GeneratedClass$GeneratedIterator.processNext(Unknown Source)
    at org.apache.spark.sql.execution.BufferedRowIterator.hasNext(BufferedRowIterator.java:43)
    at org.apache.spark.sql.execution.WholeStageCodegenExec$$anonfun$8$$anon$1.hasNext(WholeStageCodegenExec.scala:377)
    at org.apache.spark.sql.execution.datasources.FileFormatWriter$SingleDirectoryWriteTask.execute(FileFormatWriter.scala:243)
    at org.apache.spark.sql.execution.datasources.FileFormatWriter$$anonfun$org$apache$spark$sql$execution$datasources$FileFormatWriter$$executeTask$3.apply(FileFormatWriter.scala:190)
    at org.apache.spark.sql.execution.datasources.FileFormatWriter$$anonfun$org$apache$spark$sql$execution$datasources$FileFormatWriter$$executeTask$3.apply(FileFormatWriter.scala:188)
    at org.apache.spark.util.Utils$.tryWithSafeFinallyAndFailureCallbacks(Utils.scala:1341)
    at org.apache.spark.sql.execution.datasources.FileFormatWriter$.org$apache$spark$sql$execution$datasources$FileFormatWriter$$executeTask(FileFormatWriter.scala:193)
    ... 8 more
  </code></pre>
  <p>The following tries still get the same error, how should I solve this problem by using spark sql? Thank you!</p>
  <pre class="lang-scala prettyprint-override"><code>val hdfsDirPath = &quot;hdfs://xxx.xxx.xxx.xxx:xx/test/d=2017-03-20&quot;
  
  val df = sparkSession.read.parquet(hdfsDirPath)
  
  val newdf = df
  newdf.write.mode(SaveMode.Overwrite).parquet(hdfsDirPath)
  
  // or
  
  val df = sparkSession.read.parquet(hdfsDirPath)
  df.createOrReplaceTempView(&quot;orgtable&quot;)
  sparkSession.sql(&quot;SELECT * from orgtable&quot;).createOrReplaceTempView(&quot;tmptable&quot;)
  
  sparkSession.sql(&quot;TRUNCATE TABLE orgtable&quot;)
  sparkSession.sql(&quot;INSERT INTO orgtable SELECT * FROM tmptable&quot;)
  
  val newdf = sparkSession.sql(&quot;SELECT * FROM orgtable&quot;)
  newdf.write.mode(SaveMode.Overwrite).parquet(hdfsDirPath)
  
  // or
  
  val df = sparkSession.read.parquet(hdfsDirPath)
  df.createOrReplaceTempView(&quot;orgtable&quot;)
  sparkSession.sql(&quot;SELECT * from orgtable&quot;).createOrReplaceTempView(&quot;tmptable&quot;)
  
  sparkSession.sql(&quot;REFRESH TABLE orgtable&quot;)
  sparkSession.sql(&quot;ALTER VIEW tmptable RENAME TO orgtable&quot;)
      
  val newdf = sparkSession.sql(&quot;SELECT * FROM orgtable&quot;)
  newdf.write.mode(SaveMode.Overwrite).parquet(hdfsDirPath)
  </code></pre>