How to create a table with dates in sequence between range in Hive?

Question

I'm trying to Create a table with column date, And I want to insert date in sequence between Range.

Here's what I have tried:

SET StartDate = '2009-01-01';
SET EndDate = '2016-06-31';

CREATE TABLE DateRangeTable(mydate DATE, qty INT);


INSERT INTO DateRangeTable VALUES (select a.Date, 0
from (
    select current_date - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) AS a where a.Date between '2019-01-01' and '2016-06-30');

did above solution work ? – Mohit Verma Jun 29 '17 at 06:55 — Mohit Verma, Jun 29 '17 at 06:55

score 0 · Answer 1 · answered Jan 20 '22 at 13:17

0

This is the similar one:

select date_add(t.f1, t.start_r - pe.i) as date_range from (select '2022-01-01' as f1,datediff('2022-01-07','2022-01-01') as start_r,0 as end_r) t lateral view posexplode(split(space(start_r - end_r),' ')) pe as i,s;

answered Jan 20 '22 at 13:17

Sachin Devhade

41
2

score 0 · Answer 2 · answered Aug 02 '23 at 23:07

Here is the simple answer:

set lower.bound.date='2023-05-01';
set upper.bound.date='2023-08-31';

with date_seq as(
select posexplode(split(space(datediff(${hiveconf:upper.bound.date},${hiveconf:lower.bound.date})),' '))
)
select date_add(${hiveconf:lower.bound.date},pos) from date_seq;

datediff(...) provides number of days between two dates, spaces(...) created specific number of spaces, the split(...) function splits the data by single space (' '). The posexplode(...) converts row elements into columnar format with pos as index and space as value.

leftjoin · Answer 3 · 2020-12-31T16:15:19.177

You do not need VALUES keyword when using INSERT ... SELECT.

Working example:

set hivevar:start_date=2009-01-01;
set hivevar:end_date=2016-06-31;

CREATE TABLE DateRangeTable(mydate DATE, qty INT);

with date_range as 
(--this query generates date range
select date_add ('${hivevar:start_date}',s.i) as dt 
  from ( select posexplode(split(space(datediff('${hivevar:end_date}','${hivevar:start_date}')),' ')) as (i,x) ) s
) 

INSERT INTO TABLE DateRangeTable
select d.dt, 0 qty
  from date_range d
 where d.dt between '2019-01-01' and '2016-06-30');

How to create a table with dates in sequence between range in Hive?

3 Answers3