使用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结果插入维度表得到结果如下:
参考资料: