Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

[Hive] Auto-Generate future 12 months for each row

[Hive] Auto-Generate future 12 months for each row

New Contributor

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.

LogDateItemID

2018-09-30

Item1
2018-10-23Item2

The processed table should look like below, where the LogDate is converted to month start and future 11 months are generated

LogDateGeneratedDateItemID
2018-09-012018-09-01Item1
2018-09-012018-10-01Item1
2018-09-012018-11-01Item1
2018-09-012018-12-01Item1
2018-09-012019-01-01Item1
2018-09-012019-02-01Item1
2018-09-012019-03-01Item1
2018-09-012019-04-01Item1
2018-09-012019-05-01Item1
2018-09-012019-06-01Item1
2018-09-01

2019-07-01

Item1
2018-09-012019-08-01Item1
2018-10-012018-10-01Item2
2018-10-012018-11-01Item2
2018-10-012018-12-01Item2
2018-10-012019-01-01Item2
2018-10-012019-02-01Item2
2018-10-012019-03-01Item2
2018-10-012019-04-01Item2
2018-10-012019-05-01Item2
2018-10-012019-06-01Item2
2018-10-012019-07-01Item2
2018-10-012019-08-01Item2
2018-10-012019-09-01Item2

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.

Don't have an account?
Coming from Hortonworks? Activate your account here