hive_dim_date

最新推荐文章于 2024-08-15 15:11:58 发布

angela_bigdata

最新推荐文章于 2024-08-15 15:11:58 发布

阅读量840

点赞数

分类专栏： HIVE 文章标签： hive hadoop 数据仓库

本文链接：https://blog.csdn.net/wanglifengchao222/article/details/122920104

版权

HIVE 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

本文档详细介绍了如何创建一个大数据仓库中的日期维度表`dim.dim_date`，包括各字段的含义及数据类型。同时，提供了每日更新此表的SQL插入语句，涉及日期计算、季度划分等复杂逻辑，确保数据的准确性和时效性。

摘要由CSDN通过智能技术生成

1 创建表

create table dim.dim_date(
   riqi string COMMENT'日期',
   nian string COMMENT'年',
   yue string COMMENT'月',
   nian_yue string COMMENT'年-月',
   nianyue string COMMENT'年月',
   nianchu string COMMENT'年初',
   nianmo string COMMENT'年末',
   tianshu int COMMENT'当前月一共的天数',
   jiduchu string COMMENT'季度初',
   jidumo string COMMENT'季度末',
   sjidchu string COMMENT'季度末',
   sjidmo string COMMENT'季度末',
   benjidu string COMMENT'季度末',
   shangjidu string COMMENT'季度末',
   yuechu string COMMENT'季度末',
   yuemo string COMMENT'季度末',
   shangyuemo string COMMENT'季度末',
   benzhouchu string COMMENT'季度末',
   benzhoumo string COMMENT'季度末',
   shangnrq string COMMENT'季度末',
   date_insert string COMMENT'数据入仓库时间'
)ROW FORMAT DELIMITED FIELDS TERMINATED BY '^' STORED AS TEXTFILE;

2-写入数据-每日执行

insert into dim.dim_date
   (riqi,nian,yue
   ,nian_yue
   ,nianyue
   ,nianchu
   ,nianmo
   ,tianshu
   ,jiduchu
   ,jidumo
   ,sjidchu
   ,sjidmo
   ,benjidu
   ,shangjidu
   ,yuechu
   ,yuemo
   ,shangyuemo
   ,benzhouchu
   ,benzhoumo
   ,shangnrq
   ,date_insert)
select ${dayid_today} as riqi,${yearid_today} as nian,substring(${dayid_today},6,2) as yue
   ,${monthid_today} as nian_yue
   ,regexp_replace(${monthid_today},'-','') as nianyue
   ,CONCAT(${yearid_today},'-01-01') as nianchu
   ,CONCAT(${yearid_today},'-12-31') as nianmo
   ,dayofmonth(last_day(${dayid_today})) as tianshu
   ,add_months(concat(year(${dayid_today}),'-',substr(concat('0',floor((month(${dayid_today})+2)/3)*3+1),-2),'-01'),-3) as jiduchu
   ,date_sub(concat(year(${dayid_today}),'-',substr(concat('0',floor((month(${dayid_today})+2)/3)*3+1),-2),'-01'),1) as jidumo
   ,add_months(concat(year(${dayid_today}),'-',substr(concat('0',floor((month(${dayid_today})+2)/3)*3+1),-2),'-01'),-6) as sjidchu
   ,date_sub(add_months(concat(year(${dayid_today}),'-',substr(concat('0',floor((month(${dayid_today})+2)/3)*3+1),-2),'-01'),-3),1) as sjidmo
   ,concat(year(${dayid_today}),floor(substr(${dayid_today},6,2)/3.1)+1) as benjidu
   ,concat(year(add_months(${dayid_today},-3))
   ,floor(substr(add_months(${dayid_today},-3),6,2)/3.1)+1) as shangjidu
   ,date_sub(${dayid_today}
   ,dayofmonth(${dayid_today})-1) as yuechu
   ,last_day(${dayid_today}) as yuemo
   ,last_day(add_months(${dayid_today},-1)) as shangyuemo
   ,date_sub(next_day(${dayid_today},'MO'),7) as benzhouchu
   ,date_sub(next_day(${dayid_today},'MO'),1) as benzhoumo
   ,date_format(add_months(from_unixtime(unix_timestamp(), 'yyyy-MM-dd'),-12),'yyyy-MM-dd') as shangnrq
   ,date_format(current_timestamp,'yyyy-MM-dd HH:mm:ss')