Manipulating single value of a key inside Map struct in Spark SQL from multiple key-value pair. Apply unbase64 on single value

Question

I have got a requirement where we are receiving a file and it have multiple columns. One column datatype is Map struct. others are String. In this Map struct there are multiple Key:value pairs for every record.

Ex for Map data -

Map (f-expaneded-port : 451,

custNo : 415723, 

channel : netsite, 

y-ub-eg-en-author : "7Im5hbWUiOiJpbnRlcm5hbEFjY291bnRJZHMiLCJ2YWx1ZSI6WyIwNjIw16", /*This value is base64 */

sessionid : e5cdb71d3572dd6f7gh8jh6dssf8g688dda0, 

y-expandeded-proto : https) 

Each record may have different key-value pairs.

Requirement:- We have to find key "y-ub-eg-en-author" and apply unbase64() function on the corresponding Value using Spark Sql to decode the value. If there is no such key(y-ub-eg-en-author) then nothing to be done.

Could anyone let us know the possible solution how we can apply unbase64 function on particular key-value without affecting other key-value pairs.

Thanks

Answer 1

Tried below options and it is working out. Please check it.

1) First Map has been exploded.

2) Applied unbase64 based on Key conditions.

3) Then again exploded columns are changed to MAP. Grouped on primary key column.

val Query="""SELECT CAST(Id AS String) AS Primary_ID,key_index as key_index,case when key_index in ('x-insider-token','x-ent-auth') then CAST(unbase64( CAST(key_value AS STRING) ) AS STRING) else key_value end as value FROM kafka_avro_events LATERAL VIEW EXPLODE(map_value) as key_index, key_value """

val df1 = spark.sql(Query)

val result = df1.withColumn("map", map($"key_index", $"value")).groupBy("Primary_ID").agg(collect_list("map")).as[(String, Seq[Map[String, String]])].map { case (id, list) => (id, list.reduce(_ ++ _)) }.toDF("id", "gMap")

result.show(truncate = false)

+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|id     |gMap                                                                                                                                                                                                                |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|corrId1|Map(f-expaneded-port -> 451, custNo -> 415723, channel -> netsite, y-ub-eg-en-author -> {"decision":"PERMIT","authorized":true} , sessionid -> e5cdb71d3572dd6f7gh8jh6dssf8g688dda0, y-expandeded-proto -> https    |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+