CODEWITHSUNDEEP
hadoophive
Sheng Chai
Sheng Chai

Reputation: 93

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

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!

Upvotes: 9

Views: 16051

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44941

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

Upvotes: 31

Related Questions