使用Hive SQL创建日期维度表

一、需求

需要在Hive数仓中创建一张时间跨度为2010年-2025年的时间维度表，字段如下：

列	类型	Comment	计算逻辑（用于需求沟通）
date	string	标准日期格式	2020-01-01
date_ds	string	ds日期格式	20200101
year	string	年份	2020
month	string	月份	01
day	string	日期	01
day_of_week	bigint	星期几【1-7】
week_of_year	bigint	本年度第几周	1. 以本年度第一个周一开始计算 2. 本年度前几日如属于上一年度的最后一周，则与上一年度最后一周的序号相同
week_of_month	bigint	本月第几周	与weekOfYear类似

二、代码

SELECT
    `date`,
    date_ds,
    year,
    month,
    day,
    day_of_week,
    weekofyear(`date`) as week_of_year, 
    --from_unixtime(unix_timestamp(`date`, "yyyy-MM-dd"), "W") as week_of_month  
    CAST((day(theMonday) - 1) / 7 + 1 AS BIGINT) as week_of_month
FROM (
  SELECT
    `date`,
    regexp_replace(`date`, '-', '') as date_ds,
    year(`date`) as year,
    month(`date`) as month,
    day(`date`) as day,
    -- 请参看代码拆析 2   date_sub(next_day(`date`, 'Mon'), 7) as theMonday,
    if(datediff(next_day(`date`, 1), `date`) == 7, date_sub(`date`, 6), date_sub(next_day(`date`, 2), 7)) as theMonday,
    -- 版本支持date_format，可以使用： date_format(`date`, 'u') as day_of_week 
    from_unixtime(unix_timestamp(`date`, "yyyy-MM-dd"), "u") as day_of_week 
  FROM (
    SELECT date_add(`start_date`,pos) AS `date`
    from (
      SELECT `start_date` 
      FROM table_contains_startDate
    ) t 
    lateral view posexplode(split(repeat(", ", 9495), ",")) tf AS pos,val --  9495为时间跨度
  ) dates
) dates_expanded
SORT BY `date`
;
-- 需要一张包含起始日期的table_contains_startDate表

代码拆析

-- dates：获取从start_date到往后n天的date字段数据
SELECT date_add(`start_date`,pos) AS `date`
from (
    SELECT `start_date` 
    FROM table_contains_startDate
) t 
lateral view posexplode(split(repeat(", ", 9495), ",")) tf AS pos,val --9495为时间跨度

由于笔者所使用的Hive版本子句中不允许没有FROM语句: FAILED: Error in semantic analysis: ERROR-0-HIVE:00003:{semantic error => sub query must have a from clause!!}}，所以创建了一张只包含起始日期的表table_contains_startDate,如果版本允许可以直接改为：

-- dates：获取从start_date到往后n天的date字段数据
SELECT date_add(`start_date`,pos) AS `date`
from (
    SELECT '2010-01-01'
) t 
lateral view posexplode(split(repeat(", ", 9495), ",")) tf AS pos,val --9495为时间跨度

-- dates_expanded：
if(datediff(next_day(`date`, 1), `date`) == 7, date_sub(`date`, 6), date_sub(next_day(`date`, 2), 7)) as theMonday,
-- 标准Hive函数  date_sub(next_day(`date`, 'Mon'), 7) as theMonday,

--
CAST((day(theMonday) - 1) / 7 + 1 AS BIGINT) as week_of_month

由于week_of_month 字段逻辑与SimpleDateFormat提供的W参数逻辑不同，所以需要另谋他法。总体的思路是：取date所在周的周一 theMonday，使用theMonday的后两位减一的差对7取商再加一即可（说起来很拗口，看代码即可。该思路由力丞大佬特别赞助！）。

笔者公司next_day()函数并非标准的Hive函数且有bug，使用标准Hive函数库的同学使用注释中的语句即可。

三、结果预览

SQL结果插入维度表得到结果如下：

参考资料：