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'); """

sangam.gavini · Answer 1 · 2019-09-26T12:33:18.613

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 '19 at 12:33

answered Sep 25 '19 at 16:29

sangam.gavini

196
9

1

Thank you. What does 'val' signify in the start of the command. Is that scala? – bluethundr Sep 30 '19 at 20:55
Yes, you are right. It's Scala. It defines an immutable content. https://stackoverflow.com/questions/4437373/use-of-def-val-and-var-in-scala – vikrant rana Sep 30 '19 at 21:24

score 3 · Accepted Answer · answered Sep 30 '19 at 19:35

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
""")

Convert SQL Case Statement into Spark

2 Answers2

Linked