1

I am writing a query inside of AWS Athena. The Origianl Table is something like:

employee|manager1|manager2|manager3|... | manager10
   12345|A .     |B .     |C .     |... | (null)
   54321|I .     |II .    |III .   |... | X

And the result should look like:

employee | manager
12345 .  | A
12345 .  | B
12345 .  | C
54321    | I
54321 .  | II
54321 .  |...
54321 .  | X

I tried to use unpivot inside of Athena but got some syntax errors. Also, with unpivot it means i need to hardcoding all these columns.

This is my first time with Athena and I don't know if Athena can achieve it or not. And if yes, what's the correct way?

Di Chu
  • 23
  • 1
  • 6
  • Can you do this outside of Athena? I feel like Athena might be a bit clunky for something like this. For instance [PySpark might be an option](https://stackoverflow.com/questions/54030601/how-to-unpivot-columns-into-rows-in-aws-glue-py-spark-script). – JNevill Oct 28 '19 at 19:20
  • this is part of a large project and using Athena is by design by someone else. But thanks for the advice, i am investigating if I can use AWS glue after I select * from the Athena. – Di Chu Oct 28 '19 at 20:37

1 Answers1

3

I just did an unpivot in Athena with arrays. My case didn't have a variable number of columns so you may need to adjust for that, but this should at least work to give you an unpivot:

WITH dataset AS (
  SELECT
    employee,
    ARRAY[manager1, manager2, manager3] AS managers
  FROM
    (SELECT 'A' AS employee, '1' AS manager1, '2' AS manager2, '3' AS manager3)
)
SELECT employee, manager FROM dataset
CROSS JOIN UNNEST(managers) as t(manager)

This will give you the output:

code results

Check the documentation for more info and examples: https://docs.aws.amazon.com/athena/latest/ug/creating-arrays.html

greenbellpepper
  • 412
  • 2
  • 7
  • 14