Spark HiveContext : Insert Overwrite the same table it is read from

Question

I want to apply SCD1 and SCD2 using PySpark in HiveContext. In my approach, I am reading incremental data and target table. After reading, I am joining them for upsert approach. I am doing registerTempTable on all the source dataframes. I am trying to write final dataset into target table and I am facing the issue that Insert overwrite is not possible in the table it is read from.

Please suggest some solution for this. I do not want to write intermediate data into a physical table and read it again.

Is there any property or way to store the final data set without keeping the dependency on the table it is read from. This way, It might be possible to overwrite the table.

Please suggest.

Answer 1

I was going through the documentation of spark and a thought clicked to me when I was checking one property there.

As my table was parquet, I used hive meta store to read the data by setting this property to false.

hiveContext.conf("spark.sql.hive.convertMetastoreParquet","false")

This solution is working fine for me.

Answer 2

You should never overwrite a table from which you are reading. It can result in anything between data corruption and complete data loss in case of failure.

It is also important to point out that correctly implemented SCD2 shouldn't never overwrite a whole table and can be implemented as a (mostly) append operation. As far as I am aware SCD1 cannot be efficiently implemented without mutable storage, therefore is not a good fit for Spark.