hadoop - How to generate Date Series in HIVE? (Creating table)

Question

Suppose I currently have a table that has 1 row for each account and the data in the tables are:

1. Account Number
2. Start Date
3. End Date

Now I'd like to create a new table that has 1 row for each day the account is open, i.e. 1 day for each row between the start and end dates (inclusive) for each account.

E.g.

Table 1

Account Number    Start Date    End Date
123               1-Jan-17      1-Jul-17
456               1-Feb-17      4-May-17

Table 2 (Desired table)

Account Number    Day
123               1-Jan-17
123               1-Jan-17
     ...
123               1-Jul-17
456               1-Feb-17
456               2-Feb-17
     ...
456               4-May-17

I know in Postgresql there's a function called 'generate series' that would allow you to do that easily. I'm wondering if there's a similar function in HIVE that would allow you to do that as well?

Thanks!

Answer 1

select  t.AccountNumber
       ,date_add (t.StartDate,pe.i)   as Day

from    Table1 t
        lateral view 
        posexplode(split(space(datediff(t.EndDate,t.StartDate)),' ')) pe as i,x