Split Hive table column values and compare it to a hive look up table to get the values.

[#1]

Hi, One of my hive table column has value with " comma" delimited. Ex A,B,C . I need to compare these values to another lookup table and get the description of these codes.

For Ex:

Main_Table :

codes date Comp_id

A,B,C 2019-01-01 1

D 2019-01-01 2

E,F 2018-01-02 3

LookUp Table :

codes desc

A Arrangements

B Borrowed

C Completed

D Derived

E Eliminated

My Final Table should be:

Codes date Comp_Id desc

A,B,C 2019-01-01 1 Arrangements Borrowed Completed

D 2019-01-01 2 Derived

E,F 2018-01-02 3 Eliminated Failed

Steps I followed: To split columns in to many and iterate to get the values. But is there any other way based on regular expression replace , can we try something to approach this result?

Pls help , if any ideas. Thanks

[#2]

We are loading data into hive using pyspark. So If anyone have ideas to perform this action using dataframe also, Pls let me know.

thanks

Ashokkumar.R