包含日期不同格式、及周信息月信息、年信息、季度信息。直接上代码【基于hive3 和spark2spark3】-- 获取节假日信息生成配置表【网上搜直接落地数据】 -- 国内节假日维表-外部表: dim.dim_yl_dw_hols_base alter table dim.dim_yl_dw_hols_conf_base set tblproperties (external.table.purge true); drop table dim.dim_yl_dw_hols_conf_base; create external table dim.dim_yl_dw_hols_conf_base ( date_str string comment 日期, flag string comment 假日标识 )comment 国内节假日调休配置表 stored as parquet location /dw/hive/dim.db/external/dim_yl_dw_hols_base ; -- 以2024和2025为例 insert into table dim.dim_yl_dw_hols_conf_base values (2024-01-01,元旦) ,(2024-02-10,春节) ,(2024-02-11,春节) ,(2024-02-12,春节) ,(2024-02-13,春节) ,(2024-02-14,春节) ,(2024-02-15,春节) ,(2024-02-16,春节) ,(2024-02-17,春节) ,(2024-02-04,春节调休上班) ,(2024-02-18,春节调休上班) ,(2024-04-04,清明节) ,(2024-04-05,清明节) ,(2024-04-06,清明节) ,(2024-04-07,清明节调休上班) ,(2024-05-01,劳动节) ,(2024-05-02,劳动节) ,(2024-05-03,劳动节) ,(2024-05-04,劳动节) ,(2024-05-05,劳动节) ,(2024-04-28,劳动节调休上班) ,(2024-05-11,劳动节调休上班) ,(2024-06-10,端午节) ,(2024-09-15,中秋节) ,(2024-09-16,中秋节) ,(2024-09-17,中秋节) ,(2024-09-14,中秋节调休上班) ,(2024-10-01,国庆节) ,(2024-10-02,国庆节) ,(2024-10-03,国庆节) ,(2024-10-04,国庆节) ,(2024-10-05,国庆节) ,(2024-10-06,国庆节) ,(2024-10-07,国庆节) ,(2024-09-29,国庆节调休上班) ,(2024-10-12,国庆节调休上班) ,(2025-01-01,元旦) ,(2025-02-15,春节) ,(2025-02-16,春节) ,(2025-02-17,春节) ,(2025-02-18,春节) ,(2025-02-19,春节) ,(2025-02-20,春节) ,(2025-02-21,春节) ,(2025-02-22,春节) ,(2025-02-09,春节调休上班) ,(2025-02-23,春节调休上班) ,(2025-04-04,清明节) ,(2025-04-05,清明节) ,(2025-04-06,清明节) ,(2025-04-07,清明节调休上班) ,(2025-05-01,劳动节) ,(2025-05-02,劳动节) ,(2025-05-03,劳动节) ,(2025-05-04,劳动节) ,(2025-05-05,劳动节) ,(2025-04-27,劳动节调休上班) ,(2025-05-11,劳动节调休上班) ,(2025-05-31,端午节) ,(2025-06-01,端午节) ,(2025-06-02,端午节) ,(2025-06-08,端午节调休上班) ,(2025-10-01,国庆节) ,(2025-10-02,国庆节) ,(2025-10-03,国庆节) ,(2025-10-04,国庆节) ,(2025-10-05,国庆节) ,(2025-10-06,国庆节) ,(2025-10-07,国庆节) ,(2025-10-08,国庆节) ,(2025-09-28,国庆节调休上班) ,(2025-10-12,国庆节调休上班) ;更具项目需要生成日期的基础数据并通过hive 函数进行相应的转换-- 创建日期模型 drop table dim.dim_date_info_base; CREATE EXTERNAL TABLE dim.dim_date_info_config_base( date_id bigint COMMENT 日期ID:20211101, date_mid_desc string COMMENT 中日期:2021-11-01, date_long_desc string COMMENT 长日期:2021年11月01日, year_id int COMMENT 年ID:2021, year_desc string COMMENT 年:2021年, month_id string COMMENT 月ID:2021-11, month_en string COMMENT 月份英文:November, month_long_desc string COMMENT 长月:2021年11月, weekday_cn string COMMENT 周几(中文):星期二, weekday_eg string COMMENT 周几(英文):Tuesday, is_weekend smallint COMMENT 是否周末1是0否, week_id string COMMENT 周ID:2021-44, week_long_desc string COMMENT yyyy年第w周2021年第44周 , daynumber_of_week int COMMENT 本周的第几天:2, daynumber_of_year int COMMENT 今年的第几天319, quarter tinyint COMMENT 季度, monday_date string COMMENT 本周周一日期, month_fst_date string COMMENT 月第一天日期, mont_lst_date string COMMENT 月最后一天日期, hols_flag string comment 假日-调休标签 ) COMMENT 时间维表 stored as parquet LOCATION /dw/hive/dim.db/external/dim_date_info_config_base ; -- 以2028年为最晚时间超前倒推 1999天的日期 with date_base as -- 日期列表 (select date_sub(to_date(2028-12-31), i) AS date_str from (select 1999 as days) b LATERAL VIEW posexplode(split(repeat(,, days), ,)) pe as i, x) insert overwrite table dim.dim_date_info_config_base select date_format(d, yyyyMMdd) as date_id, d as date_mid_desc, concat(year(d), 年, substring(d, 6, 2), 月, substring(d, 9, 2), 日) as date_long_desc, year(d) as year_id, concat(year(d), 年) as year_desc, date_format(d, yyyy-MM) as month_id, date_format(d, MMM) as month_en, concat(year(d), 年, substring(d, 6, 2), 月) as month_long_desc, CASE date_format(d, u) WHEN 1 THEN 周一 WHEN 2 THEN 周二 WHEN 3 THEN 周三 WHEN 4 THEN 周四 WHEN 5 THEN 周五 WHEN 6 THEN 周六 WHEN 7 THEN 周日 end as week_cn, CASE date_format(d, u) WHEN 1 THEN Monday WHEN 2 THEN Tuesday WHEN 3 THEN Wednesday WHEN 4 THEN Thursday WHEN 5 THEN Friday WHEN 6 THEN Saturday WHEN 7 THEN Sunday end as week_en, if(date_format(d, u) in (6, 7), 1, 0) as is_weekend, concat(year(date_sub(next_day(d, monday), 4)), -, weekofyear(d)) as week_id, concat(year(date_sub(next_day(d, monday), 4)), 年第, weekofyear(d), 周) as week_long_desc, date_format(d, u) as daynumber_of_week, datediff(d, concat(year(d), -01-01)) 1 as daynumber_of_year, quarter(d) as quarter, date_sub(next_day(d, MO), 7) as monday_date, date_sub(d, dayofmonth(d) - 1) as month_fst_date, last_day(d) as month_lst_date, t.flag from (select date_str as d from date_base) tmp left join dim.dim_yl_dw_hols_conf_base t on tmp.d t.date_str ;更新在最后2024-12-11spark3 中一方面对数据格式做了更严格的要求类似的string 强转 bigint 的已经行不通通过caststring as bigint 进行转换或者 调整config 配置取消严格模式【SET spark.sql.legacy.timeParserPolicy LEGACY;】另外在 Spark 3.0 及以后的版本中不再支持基于周的模式这是因为在这些版本中引入了新的日期时间解析器EXTRACT 。这意味着在 Spark 3.0 及以后的版本中所有基于周的模式包括e、W、F等都被禁用了。虽然e在 Java 8 中代表“星期几”但在 Spark 3.0 的严格解析器中它被归类为 week-based pattern 并直接抛出异常。SELECT EXTRACT(YEAR FROM 2024-12-11 13:14:52) AS year, EXTRACT(MONTH FROM 2024-12-11 13:14:52) AS month, EXTRACT(DAYOFWEEK FROM 2024-12-11 13:14:52) AS week, EXTRACT(DAY FROM 2024-12-11 13:14:52) AS day, EXTRACT(HOUR FROM 2024-12-11 13:14:52) AS hour, EXTRACT(MINUTE FROM 2024-12-11 13:14:52) AS minute, EXTRACT(SECOND FROM 2024-12-11 13:14:52) AS second ; -- 输出 -- 2024 12 50 11 13 14 52.000000更简单的用法是使用dayofweek函数但需要注意DAYOFWEEK返回的结果中1 代表星期日2 代表星期一依此类推具体结果取决于你的 Spark 配置和时区建议实际测试确认。以下是spark3中的生成日期维表的语句根据表字段类型使用cast进行装换with date_base as -- 日期列表 (select date_sub(to_date(2029-12-31), i) AS date_str from (select 1999 as days) b LATERAL VIEW posexplode(split(repeat(,, days), ,)) pe as i, x) select date_format(d, yyyyMMdd) as date_id, d as date_mid_desc, concat(year(d), 年, substring(d, 6, 2), 月, substring(d, 9, 2), 日) as date_long_desc, year(d) as year_id, concat(year(d), 年) as year_desc, date_format(d, yyyy-MM) as month_id, date_format(d, MMM) as month_en, concat(year(d), 年, substring(d, 6, 2), 月) as month_long_desc, CASE dayofweek(d) WHEN 1 THEN 周日 WHEN 2 THEN 周一 WHEN 3 THEN 周二 WHEN 4 THEN 周三 WHEN 5 THEN 周四 WHEN 6 THEN 周五 WHEN 7 THEN 周六 end as week_cn, CASE dayofweek(d) WHEN 1 THEN Sunday WHEN 2 THEN Monday WHEN 3 THEN Tuesday WHEN 4 THEN Wednesday WHEN 5 THEN Thursday WHEN 6 THEN Friday WHEN 7 THEN Saturday end as week_en, if(dayofweek(d) in (1, 7), 1, 0) as is_weekend, concat(year(date_sub(next_day(d, monday), 4)), -, weekofyear(d)) as week_id, concat(year(date_sub(next_day(d, monday), 4)), 年第, weekofyear(d), 周) as week_long_desc, dayofweek(d) as daynumber_of_week, datediff(d, concat(year(d), -01-01)) 1 as daynumber_of_year, quarter(d) as quarter, date_sub(next_day(d, MO), 7) as monday_date, date_sub(d, dayofmonth(d) - 1) as month_fst_date, last_day(d) as month_lst_date, 暂无 flag from (select date_str as d from date_base) tmp 数仓-日期维表建设
包含日期不同格式、及周信息月信息、年信息、季度信息。直接上代码【基于hive3 和spark2spark3】-- 获取节假日信息生成配置表【网上搜直接落地数据】 -- 国内节假日维表-外部表: dim.dim_yl_dw_hols_base alter table dim.dim_yl_dw_hols_conf_base set tblproperties (external.table.purge true); drop table dim.dim_yl_dw_hols_conf_base; create external table dim.dim_yl_dw_hols_conf_base ( date_str string comment 日期, flag string comment 假日标识 )comment 国内节假日调休配置表 stored as parquet location /dw/hive/dim.db/external/dim_yl_dw_hols_base ; -- 以2024和2025为例 insert into table dim.dim_yl_dw_hols_conf_base values (2024-01-01,元旦) ,(2024-02-10,春节) ,(2024-02-11,春节) ,(2024-02-12,春节) ,(2024-02-13,春节) ,(2024-02-14,春节) ,(2024-02-15,春节) ,(2024-02-16,春节) ,(2024-02-17,春节) ,(2024-02-04,春节调休上班) ,(2024-02-18,春节调休上班) ,(2024-04-04,清明节) ,(2024-04-05,清明节) ,(2024-04-06,清明节) ,(2024-04-07,清明节调休上班) ,(2024-05-01,劳动节) ,(2024-05-02,劳动节) ,(2024-05-03,劳动节) ,(2024-05-04,劳动节) ,(2024-05-05,劳动节) ,(2024-04-28,劳动节调休上班) ,(2024-05-11,劳动节调休上班) ,(2024-06-10,端午节) ,(2024-09-15,中秋节) ,(2024-09-16,中秋节) ,(2024-09-17,中秋节) ,(2024-09-14,中秋节调休上班) ,(2024-10-01,国庆节) ,(2024-10-02,国庆节) ,(2024-10-03,国庆节) ,(2024-10-04,国庆节) ,(2024-10-05,国庆节) ,(2024-10-06,国庆节) ,(2024-10-07,国庆节) ,(2024-09-29,国庆节调休上班) ,(2024-10-12,国庆节调休上班) ,(2025-01-01,元旦) ,(2025-02-15,春节) ,(2025-02-16,春节) ,(2025-02-17,春节) ,(2025-02-18,春节) ,(2025-02-19,春节) ,(2025-02-20,春节) ,(2025-02-21,春节) ,(2025-02-22,春节) ,(2025-02-09,春节调休上班) ,(2025-02-23,春节调休上班) ,(2025-04-04,清明节) ,(2025-04-05,清明节) ,(2025-04-06,清明节) ,(2025-04-07,清明节调休上班) ,(2025-05-01,劳动节) ,(2025-05-02,劳动节) ,(2025-05-03,劳动节) ,(2025-05-04,劳动节) ,(2025-05-05,劳动节) ,(2025-04-27,劳动节调休上班) ,(2025-05-11,劳动节调休上班) ,(2025-05-31,端午节) ,(2025-06-01,端午节) ,(2025-06-02,端午节) ,(2025-06-08,端午节调休上班) ,(2025-10-01,国庆节) ,(2025-10-02,国庆节) ,(2025-10-03,国庆节) ,(2025-10-04,国庆节) ,(2025-10-05,国庆节) ,(2025-10-06,国庆节) ,(2025-10-07,国庆节) ,(2025-10-08,国庆节) ,(2025-09-28,国庆节调休上班) ,(2025-10-12,国庆节调休上班) ;更具项目需要生成日期的基础数据并通过hive 函数进行相应的转换-- 创建日期模型 drop table dim.dim_date_info_base; CREATE EXTERNAL TABLE dim.dim_date_info_config_base( date_id bigint COMMENT 日期ID:20211101, date_mid_desc string COMMENT 中日期:2021-11-01, date_long_desc string COMMENT 长日期:2021年11月01日, year_id int COMMENT 年ID:2021, year_desc string COMMENT 年:2021年, month_id string COMMENT 月ID:2021-11, month_en string COMMENT 月份英文:November, month_long_desc string COMMENT 长月:2021年11月, weekday_cn string COMMENT 周几(中文):星期二, weekday_eg string COMMENT 周几(英文):Tuesday, is_weekend smallint COMMENT 是否周末1是0否, week_id string COMMENT 周ID:2021-44, week_long_desc string COMMENT yyyy年第w周2021年第44周 , daynumber_of_week int COMMENT 本周的第几天:2, daynumber_of_year int COMMENT 今年的第几天319, quarter tinyint COMMENT 季度, monday_date string COMMENT 本周周一日期, month_fst_date string COMMENT 月第一天日期, mont_lst_date string COMMENT 月最后一天日期, hols_flag string comment 假日-调休标签 ) COMMENT 时间维表 stored as parquet LOCATION /dw/hive/dim.db/external/dim_date_info_config_base ; -- 以2028年为最晚时间超前倒推 1999天的日期 with date_base as -- 日期列表 (select date_sub(to_date(2028-12-31), i) AS date_str from (select 1999 as days) b LATERAL VIEW posexplode(split(repeat(,, days), ,)) pe as i, x) insert overwrite table dim.dim_date_info_config_base select date_format(d, yyyyMMdd) as date_id, d as date_mid_desc, concat(year(d), 年, substring(d, 6, 2), 月, substring(d, 9, 2), 日) as date_long_desc, year(d) as year_id, concat(year(d), 年) as year_desc, date_format(d, yyyy-MM) as month_id, date_format(d, MMM) as month_en, concat(year(d), 年, substring(d, 6, 2), 月) as month_long_desc, CASE date_format(d, u) WHEN 1 THEN 周一 WHEN 2 THEN 周二 WHEN 3 THEN 周三 WHEN 4 THEN 周四 WHEN 5 THEN 周五 WHEN 6 THEN 周六 WHEN 7 THEN 周日 end as week_cn, CASE date_format(d, u) WHEN 1 THEN Monday WHEN 2 THEN Tuesday WHEN 3 THEN Wednesday WHEN 4 THEN Thursday WHEN 5 THEN Friday WHEN 6 THEN Saturday WHEN 7 THEN Sunday end as week_en, if(date_format(d, u) in (6, 7), 1, 0) as is_weekend, concat(year(date_sub(next_day(d, monday), 4)), -, weekofyear(d)) as week_id, concat(year(date_sub(next_day(d, monday), 4)), 年第, weekofyear(d), 周) as week_long_desc, date_format(d, u) as daynumber_of_week, datediff(d, concat(year(d), -01-01)) 1 as daynumber_of_year, quarter(d) as quarter, date_sub(next_day(d, MO), 7) as monday_date, date_sub(d, dayofmonth(d) - 1) as month_fst_date, last_day(d) as month_lst_date, t.flag from (select date_str as d from date_base) tmp left join dim.dim_yl_dw_hols_conf_base t on tmp.d t.date_str ;更新在最后2024-12-11spark3 中一方面对数据格式做了更严格的要求类似的string 强转 bigint 的已经行不通通过caststring as bigint 进行转换或者 调整config 配置取消严格模式【SET spark.sql.legacy.timeParserPolicy LEGACY;】另外在 Spark 3.0 及以后的版本中不再支持基于周的模式这是因为在这些版本中引入了新的日期时间解析器EXTRACT 。这意味着在 Spark 3.0 及以后的版本中所有基于周的模式包括e、W、F等都被禁用了。虽然e在 Java 8 中代表“星期几”但在 Spark 3.0 的严格解析器中它被归类为 week-based pattern 并直接抛出异常。SELECT EXTRACT(YEAR FROM 2024-12-11 13:14:52) AS year, EXTRACT(MONTH FROM 2024-12-11 13:14:52) AS month, EXTRACT(DAYOFWEEK FROM 2024-12-11 13:14:52) AS week, EXTRACT(DAY FROM 2024-12-11 13:14:52) AS day, EXTRACT(HOUR FROM 2024-12-11 13:14:52) AS hour, EXTRACT(MINUTE FROM 2024-12-11 13:14:52) AS minute, EXTRACT(SECOND FROM 2024-12-11 13:14:52) AS second ; -- 输出 -- 2024 12 50 11 13 14 52.000000更简单的用法是使用dayofweek函数但需要注意DAYOFWEEK返回的结果中1 代表星期日2 代表星期一依此类推具体结果取决于你的 Spark 配置和时区建议实际测试确认。以下是spark3中的生成日期维表的语句根据表字段类型使用cast进行装换with date_base as -- 日期列表 (select date_sub(to_date(2029-12-31), i) AS date_str from (select 1999 as days) b LATERAL VIEW posexplode(split(repeat(,, days), ,)) pe as i, x) select date_format(d, yyyyMMdd) as date_id, d as date_mid_desc, concat(year(d), 年, substring(d, 6, 2), 月, substring(d, 9, 2), 日) as date_long_desc, year(d) as year_id, concat(year(d), 年) as year_desc, date_format(d, yyyy-MM) as month_id, date_format(d, MMM) as month_en, concat(year(d), 年, substring(d, 6, 2), 月) as month_long_desc, CASE dayofweek(d) WHEN 1 THEN 周日 WHEN 2 THEN 周一 WHEN 3 THEN 周二 WHEN 4 THEN 周三 WHEN 5 THEN 周四 WHEN 6 THEN 周五 WHEN 7 THEN 周六 end as week_cn, CASE dayofweek(d) WHEN 1 THEN Sunday WHEN 2 THEN Monday WHEN 3 THEN Tuesday WHEN 4 THEN Wednesday WHEN 5 THEN Thursday WHEN 6 THEN Friday WHEN 7 THEN Saturday end as week_en, if(dayofweek(d) in (1, 7), 1, 0) as is_weekend, concat(year(date_sub(next_day(d, monday), 4)), -, weekofyear(d)) as week_id, concat(year(date_sub(next_day(d, monday), 4)), 年第, weekofyear(d), 周) as week_long_desc, dayofweek(d) as daynumber_of_week, datediff(d, concat(year(d), -01-01)) 1 as daynumber_of_year, quarter(d) as quarter, date_sub(next_day(d, MO), 7) as monday_date, date_sub(d, dayofmonth(d) - 1) as month_fst_date, last_day(d) as month_lst_date, 暂无 flag from (select date_str as d from date_base) tmp