date - 如何在 Hive 的范围之间按顺序创建日期表？

Question

我正在尝试创建一个带有日期列的表，并且我想在范围之间按顺序插入日期。

这是我尝试过的:

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');

Answer 1

使用 INSERT ... SELECT 时不需要 VALUES 关键字。
工作示例:

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');