SQL: table to key-value table

Question

I have table in HIVE like this:

A    | B   | C  | value
key1 |NULL|NULL| v1
NULL | key2  |NULL| v2
NULL |NULL| key3  | v3
NULL | key4  |NULL| v4

What is the easiest way to transform it to some key-value table like this:

key_type | key_value | value
A | key1 | v1
B | key2 | v2
C | key3 | v3
B | key4 | v4

using Hive-SQL or Spark Dataframe transformation (PySpark)? Thank you for any help.

Answer 1

Using pyspark , you can use greatest after filtering the required columns and returning the column names when the column value is not null:

import pyspark.sql.functions as F

cols = [i for i in df.columns if i!='value'] #['A','B','C']

output = df.select(F.greatest(*[F.when(F.col(i).isNotNull(),i)
                             for i in cols]).alias("key_type")
               ,F.greatest(*[F.col(i) for i in cols]).alias("key_Value"),"value")

---

output.show()

+--------+---------+-----+
|key_type|key_Value|value|
+--------+---------+-----+
|       A|     key1|   v1|
|       B|     key2|   v2|
|       C|     key3|   v3|
|       B|     key4|   v4|
+--------+---------+-----+

Answer 2

Another way to do this. using pyspark.sql.functions.coalesce

from pyspark.sql import functions as F

df.withColumn("key",F.coalesce(*[(F.when(F.col(x).isNotNull(),F.array(F.lit(x),F.col(x)))).alias(x)\
                                 for x in df.columns if x!='value']))\
  .select((F.col("key")[0]).alias("key_type"),(F.col("key")[1]).alias("key_value"),F.col("value")).show()

#+--------+---------+-----+
#|key_type|key_value|value|
#+--------+---------+-----+
#|       A|     key1|   v1|
#|       B|     key2|   v2|
#|       C|     key3|   v3|
#|       B|     key4|   v4|
#+--------+---------+-----+

Answer 3

You can use union all:

select t.key_type, t.key_value, t.value
from ( (select 'a' as key_type, a as key_value, value from t) union all
       (select 'b' as key_type, b as key_value, value from t) union all
       (select 'c' as key_type, c as key_value, value from t) 
     ) t
where t.key_type is not null
order by t.value;