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

Viewed 12811

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!

1 Answers
Related