'Insert Overwrite in data bricks overwriting complete data in table?

I am have two table 1 is with 50K records and other is with 2.5K records and I want to update this 2.5K records into table one. Currently I was doing this by using INSERT OVERWRITE statement in spark Mapr cluster. And I want to do same in azure databricks. Where I created 2 tables in databricks and read data from on-prem servers into azure then using INSERT OVERWRITE statement. But I was doing this my previus/History data was completely replaced with new data.

In Mapr cluster

src_df_name.write.mode("overwrite").format("hive").saveAsTable(s"cs_hen_mbr_stg") //stage table with 2.5K records.

spark.sql(s"INSERT OVERWRITE TABLE cs_hen_mbr_hist " +
          s"SELECT NAMED_STRUCT('INID',stg.INID,'SEG_NBR',stg.SEG_NBR,'SRC_ID',stg.SRC_ID, "+
          s"'SYS_RULE',stg.SYS_RULE,'MSG_ID',stg.MSG_ID, " +
          s"'TRE_KEY',stg.TRE_KEY,'PRO_KEY',stg.PRO_KEY, " +
          s"'INS_DATE',stg.INS_DATE,'UPDATE_DATE',stg.UPDATE_DATE,'STATUS_KEY',stg.STATUS_KEY) AS key, "+
          s"stg.MEM_KEY,stg.INDV_ID,stg.MBR_ID,stg.SEGO_ID,stg.EMAIL, " +
          s"from cs_hen_mbr_stg stg" )

By doing above in mapr cluster I was able to update values.But i was trying same in azure databricks My history data is getting lost.

In Databriks

val VW_HISTORY_MAIN=spark.read.format("parquet").option("header","true").load(s"${SourcePath}/VW_HISTORY")
VW_HISTORY_MAIN.write.mode("overwrite").format("hive").saveAsTable(s"demo.cs_hen_mbr_stg") //writing this to table in databricks.

spark.sql(s"INSERT OVERWRITE TABLE cs_hen_mbr_hist " +
          s"SELECT NAMED_STRUCT('INID',stg.INID,'SEG_NBR',stg.SEG_NBR,'SRC_ID',stg.SRC_ID, "+
          s"'SYS_RULE',stg.SYS_RULE,'MSG_ID',stg.MSG_ID, " +
          s"'TRE_KEY',stg.TRE_KEY,'PRO_KEY',stg.PRO_KEY, " +
          s"'INS_DATE',stg.INS_DATE,'UPDATE_DATE',stg.UPDATE_DATE,'STATUS_KEY',stg.STATUS_KEY) AS key, "+
          s"stg.MEM_KEY,stg.INDV_ID,stg.MBR_ID,stg.SEGO_ID,stg.EMAIL, " +
          s"from cs_hen_mbr_stg stg" )

Why it is not working with databricks?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source