Convert SQL Case Statement into Spark

Question

How do I convert this SQL case statement into Spark SQL?

 replace_old_engagements_sql = """ UPDATE """ + my_table_name + """
                            SET Engagement = CASE Engagement
                                                WHEN '800000026680' THEN '800000032764'
                                                WHEN '807000000041' THEN '808000000000'
                                                WHEN '870000012569' THEN '807000000412'
                                                WHEN '807000000279' THEN '808000000223'
                                                WHEN '807000000282' THEN '808000000223'
                                                WHEN '870000000403' THEN '808000000223'
                                            END
                            WHERE LinkedAccountId in ('123456789101','109876543212') AND Engagement IN ('800000026680', '807000000041', '870000012569', '807000000279', '807000000282', '870000000403'); """

Vikrant Singh Rana · Accepted Answer · 2019-09-30 19:35:58Z

3

I guess your spark sql would be something close to this.

    spark.sql("""
INSERT OVERWRITE TABLE db.my_table_name
SELECT
CASE 
  WHEN LinkedAccountId in ('123456789101','109876543212') THEN
    CASE
    WHEN Engagement = '800000026680' THEN '800000032764'
    WHEN Engagement = '807000000041' THEN '808000000000'
    WHEN Engagement = '870000012569' THEN '807000000412'
    WHEN Engagement = '807000000279' THEN '808000000223'
    WHEN Engagement = '807000000282' THEN '808000000223'
    WHEN Engagement = '870000000403' THEN '808000000223'
    ELSE Engagement
    END
  ELSE Engagement
END as Engagement
from db.my_table_name
""")

answered Sep 30, 2019 at 19:35

Vikrant Singh Rana

4,7497 gold badges36 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sangam.gavini · Accepted Answer · 2019-09-26 12:33:18Z

3

# I hope this could be answer for your query in dataframe style which is creating new dataframe with the modified values as per the conditions specified

val out_df = input_df.filter($"LinkedAccountId".isin('123456789101','109876543212') and $"Engagement".isin('800000026680', '807000000041', '870000012569', '807000000279', '807000000282', '870000000403')).withColumn("Engagement",when($"Engagement" === '800000026680', '800000032764').when($"Engagement" === '807000000041','808000000000').when($"Engagement" === '870000012569', '807000000412').when($"Engagement" === '807000000279', '808000000223').when($"Engagement" === '807000000282', '808000000223').when($"Engagement" === '870000000403', '808000000223').otherwise('0'))

edited Sep 26, 2019 at 12:33

answered Sep 25, 2019 at 16:29

sangam.gavini

1969 bronze badges

2 Comments

bluethundr Over a year ago

Thank you. What does 'val' signify in the start of the command. Is that scala?

Vikrant Singh Rana Over a year ago

Yes, you are right. It's Scala. It defines an immutable content. stackoverflow.com/questions/4437373/…

Collectives™ on Stack Overflow

Convert SQL Case Statement into Spark

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related