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
Databricks Inc.
160 Spear Street, 13th Floor
San Francisco, CA 94105
info@databricks.com
1-866-330-0121