Pivot in SPARK SQL

Question

Can someone help me how to do a pivot in spark sql? I have been struggling to get this query running.

Here is my requirement:

country date customer attribute_name attribute_value attribute_creation

US 23-Apr-17 Vasan price_start_time 4/6/18 1:23 4/6/18 1:23

US 23-Apr-17 Vasan price_end_time 4/7/18 1:23 4/6/18 1:23 US 23-Apr-17 Vasan price_status MATCH 4/6/18 1:23

US 23-Apr-17 Vasan price_type Deal 4/6/18 1:23

US 23-Apr-17 Gaesh price_start_time 5/6/18 1:23 5/6/18 1:23

US 23-Apr-17 Gaesh price_end_time 5/7/18 1:23 5/6/18 1:23

US 23-Apr-17 Gaesh price_status MATCH 5/6/18 1:23

US 23-Apr-17 Gaesh price_type Deal 5/6/18 1:23

For a SQL (oracle), I would write my query like this,

(select asin,PPST,PPET,PS,PPT from (select * from meta_data where country='US' and date=to_date('{RUN_DATE_YYYY/MM/DD}','YYYY/MM/DD')) pivot ( max(ATTRIBUTE_VALUE) for ATTRIBUTE_NAME in ('price_start_time' PPST,'price_end_time' PPET,'price_status' PS,'price_type' PPT) ))

but for spark sql, I am not sure if there is PIVOT option. Any reference or guidance or query is really appreciative.

Answer 1

There was no PIVOT in Spark using the SQL API until spark 2.4

After Spark 2.4 the PIVOT functionality in SQL works as you describe it.