Created 10-23-2018 01:24 PM
Hello Community,
I have a use-case where each entry in a Hive table needs to be replicated for the future 12 months (on a monthly level, not daily). A pre-requisite of this use-case is to convert the daily date to the month-start. See illustration below for the details.
Consider the following raw table where the items are captured on daily dates granularity.
LogDate | ItemID |
2018-09-30 | Item1 |
2018-10-23 | Item2 |
The processed table should look like below, where the LogDate is converted to month start and future 11 months are generated
LogDate | GeneratedDate | ItemID |
2018-09-01 | 2018-09-01 | Item1 |
2018-09-01 | 2018-10-01 | Item1 |
2018-09-01 | 2018-11-01 | Item1 |
2018-09-01 | 2018-12-01 | Item1 |
2018-09-01 | 2019-01-01 | Item1 |
2018-09-01 | 2019-02-01 | Item1 |
2018-09-01 | 2019-03-01 | Item1 |
2018-09-01 | 2019-04-01 | Item1 |
2018-09-01 | 2019-05-01 | Item1 |
2018-09-01 | 2019-06-01 | Item1 |
2018-09-01 | 2019-07-01 | Item1 |
2018-09-01 | 2019-08-01 | Item1 |
2018-10-01 | 2018-10-01 | Item2 |
2018-10-01 | 2018-11-01 | Item2 |
2018-10-01 | 2018-12-01 | Item2 |
2018-10-01 | 2019-01-01 | Item2 |
2018-10-01 | 2019-02-01 | Item2 |
2018-10-01 | 2019-03-01 | Item2 |
2018-10-01 | 2019-04-01 | Item2 |
2018-10-01 | 2019-05-01 | Item2 |
2018-10-01 | 2019-06-01 | Item2 |
2018-10-01 | 2019-07-01 | Item2 |
2018-10-01 | 2019-08-01 | Item2 |
2018-10-01 | 2019-09-01 | Item2 |
I have see examples of lateral view explode, but it generated daily dates (365 daily future dates, but I am only looking for 11 future entries.