INSERT IF NOT EXISTS ELSE UPDATE in Spark SQL

user_2109957 0
Viewed 5212

Is there any provision of doing "INSERT IF NOT EXISTS ELSE UPDATE" in Spark SQL.

I have Spark SQL table "ABC" that has some records. And then i have another batch of records that i want to Insert/update in this table based on whether they exist in this table or not.

is there a SQL command that i can use in SQL query to make this happen?

apache-spark-sql apache-spark
3 Answers

you can use spark sql like this :

select * from (select c.*, row_number() over (partition by tac  order by tag desc) as 
    TAG_NUM from (
    select 
         a.tac
        ,a.name
        ,0 as tag
    from tableA a
    union all
    select 
        b.tac
        ,b.name
         ,1 as tag
    from tableB b) c ) d where TAG_NUM=1

tac is column you want to insert/update by.

user_46525360

I know it's a bit late to share my code, but to add or update my database, i did a fuction that looks like this :

import pandas as pd

#Returns a spark dataframe with added and updated datas
#key parameter is the primary key of the dataframes
#The two parameters dfToUpdate and dfToAddAndUpdate are spark dataframes
def AddOrUpdateDf(dfToUpdate,dfToAddAndUpdate,key):
    #Cast the spark dataframe dfToUpdate to pandas dataframe
    dfToUpdatePandas = dfToUpdate.toPandas()

    #Cast the spark dataframe dfToAddAndUpdate to pandas dataframe
    dfToAddAndUpdatePandas = dfToAddAndUpdate.toPandas()

    #Update the table records with the latest records, and adding new records if there are new records.
    AddOrUpdatePandasDf = pd.concat([dfToUpdatePandas,dfToAddAndUpdatePandas]).drop_duplicates([key], keep = 'last').sort_values(key)

    #Cast back to get a spark dataframe
    AddOrUpdateDf = spark.createDataFrame(AddOrUpdatePandasDf)

    return AddOrUpdateDf

As you can see, we need to cast the spark dataframes to pandas dataframe to be able to do the pd.concat and especially the drop_duplicates with the "keep = 'last'", then we cast back to spark dataframe and return it. I don't think this is the best way to handle the AddOrUpdate, but at least, it works.

user_19036700
Related