AWS ATHENA Transpose from column into multiple rows

Yoga 0
Viewed 31

I have a csv file uploaded to an S3 bucket which I pick up with AWS Glue then query using Athena. The CSV table is in the format below:

ID Starting Time Ending Time Failure Sector Recycling Rounds
1 5am/8am/1pm 6am/10am/3pm /(5)/ 0/0/
2 4am/6am/3pm/7pm 7am/8am/5pm/11pm ///(1) 1//1/

I want to convert that format into this:

ID Starting Time Ending Time Failure Sector Recycling Rounds
1 5am 6am blank 0
1 8am 10am (5) 0
1 1pm 3pm blank blank
2 4am 7am blank 1
2 6am 8am blank blank
2 3pm 5pm blank 1
2 7pm 11pm (1) blank

How do I accomplish that using SQL in Athena? Any help is appreciated. Thanks!

amazon-athena amazon-web-services presto sql
1 Answers

You can use split to transform varchar into array of varchars and then flatten with unnest which supports multiple arrays:

-- sample data
with dataset (ID, Starting_Time, Ending_Time, Failure_Sector, Recycling_Rounds) AS
         (VALUES (1, '5am/8am/1pm', '6am/10am/3pm', '/(5)/', '0/0/'),
                 (2, '4am/6am/3pm/7pm', '7am/8am/5pm/11pm', '///(1)', '1//1/'))

-- query
select st, et, fs, rr
from dataset,
unnest (split(Starting_Time,'/'), split(Ending_Time,'/'), split(Failure_Sector,'/'), split(Recycling_Rounds,'/')) 
    as t(st, et, fs, rr);

Output:

st et fs rr
5am 6am 0
8am 10am (5) 0
1pm 3pm
4am 7am 1
6am 8am
3pm 5pm 1
7pm 11pm (1)
Guru Stron0
Related