hive: split a row into multiple rows at one minute interval between two dates in hive

hive hiveql

Question by Naresh Y · Jan 30, 2018 at 10:19 PM ·

I've a hive table 'driver_time_stats' with columns slot_id, number_of_drivers, slot_start_time and slot_end_time.

-----------------------------------------------------------------------
slot_id | number_of_drivers | slot_start_time     | slot_end_time
-----------------------------------------------------------------------
1       | 5                 | 2018-01-01 09:30:00 | 2018-01-01 10:00:00
2       | 8                 | 2018-01-01 10:30:00 | 2018-01-01 11:00:00
-----------------------------------------------------------------------

Desired output: each row should be splitter into multiple rows at 1 minute interval between slot_start_time & slot_end_time.

----------------------------------------------------------------------- slot_id | number_of_drivers | slot_start_time | slot_end_time ----------------------------------------------------------------------- 1 | 5 | 2018-01-01 09:30:00 | 2018-01-01 09:31:00 1 | 5 | 2018-01-01 09:31:00 | 2018-01-01 09:32:00 . . . 1 | 5 | 2018-01-01 09:59:00 | 2018-01-01 10:00:00

2 | 8 | 2018-01-01 10:30:00 | 2018-01-01 10:31:00 2 | 8 | 2018-01-01 10:31:00 | 2018-01-01 10:32:00 . . . 2 | 8 | 2018-01-01 10:59:00 | 2018-01-01 11:00:00

I was using lateral view, posexplode e.t.c functions but couldn't able to do it. Can anyone help me here ? in other way, I was trying to slice a record into multiple records at one minute interval in hive. I was able to achieve it in presto using UNNEST, but I want the solution in hive only as out ETLs built on hive.

-Nash

Add comment