java和sql生成时间维度数据

JAVA:
POM依赖：
 

        <dependency><groupId>cn.hutool</groupId><artifactId>hutool-all</artifactId><version>5.4.2</version></dependency>

代码：

package com.kone.kcdp.common;import cn.hutool.core.date.DateField;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import org.apache.logging.log4j.core.lookup.JmxRuntimeInputArgumentsLookup;import java.util.List;public class DateGenerator {private static final String DATE_STD_FORMAT = "yyyy-MM-dd";private static final String DATE_TRIM_FORMAT = "yyyyMMdd";private static final String DATE_CN_FORMAT = "yyyy年M月d日";private static final String MONTH_STD_FORMAT = "yyyy-MM";private static final String MONTH_TRIM_FORMAT = "yyyyMM";private static final String MONTH_CN_FORMAT = "yyyy年M月";private static final String YEAR_STD_FORMAT = "yyyy";private static final String YEAR_CN_FORMAT = "yyyy年";public static void dateTemplate() {List<DateTime> dateTimes = DateUtil.rangeToList(DateUtil.date(963843260000l), DateUtil.date(), DateField.DAY_OF_MONTH);dateTimes.forEach(dateTime -> {//日String dateStd = DateUtil.format(dateTime, DATE_STD_FORMAT);String dateTrim = DateUtil.format(dateTime, DATE_TRIM_FORMAT);String dateCn = DateUtil.format(dateTime, DATE_CN_FORMAT);String beginOfDay = DateUtil.beginOfDay(dateTime).toString();String endOfDay = DateUtil.endOfDay(dateTime).toString();int age = DateUtil.ageOfNow(dateTime);//周int weekOfMonth = DateUtil.weekOfMonth(dateTime);int weekOfYear = DateUtil.weekOfYear(dateTime);int dayOfWeek = DateUtil.dayOfWeek(dateTime);String week = DateUtil.dayOfWeekEnum(dateTime).toString();String beginDateOfWeek = DateUtil.format(DateUtil.beginOfWeek(dateTime), DATE_STD_FORMAT);String endDateOfWeek = DateUtil.format(DateUtil.endOfWeek(dateTime), DATE_STD_FORMAT);//月String monthStd = DateUtil.format(dateTime, MONTH_STD_FORMAT);String monthTrim = DateUtil.format(dateTime, MONTH_TRIM_FORMAT);String monthCn = DateUtil.format(dateTime, MONTH_CN_FORMAT);String monthEn = DateUtil.monthEnum(dateTime).toString();String beginDateOfMonth = DateUtil.format(DateUtil.beginOfMonth(dateTime), DATE_STD_FORMAT);String endDateOfMonth = DateUtil.format(DateUtil.endOfMonth(dateTime), DATE_STD_FORMAT);//季度String quarterId = DateUtil.yearAndQuarter(dateTime);int quarterNum = DateUtil.quarter(dateTime);String quarterEn = DateUtil.quarterEnum(dateTime).toString();String quarterCn;String quarterYearCn = quarterId.substring(0, 4) + "年第" + quarterId.substring(4, 5) + "季度";String quarterYearEn = quarterId.substring(0, 4) + " " + quarterEn;switch (quarterEn) {case "Q1":quarterCn = "第一季度";break;case "Q2":quarterCn = "第二季度";break;case "Q3":quarterCn = "第三季度";break;default:quarterCn = "第四季度";break;}String beginDateOfQuarter = DateUtil.format(DateUtil.beginOfQuarter(dateTime), DATE_STD_FORMAT);String endDateOfQuarter = DateUtil.format(DateUtil.endOfQuarter(dateTime), DATE_STD_FORMAT);//年String yearStd = DateUtil.format(dateTime, YEAR_STD_FORMAT);String yearCn = DateUtil.format(dateTime, YEAR_CN_FORMAT);String chineseZodiac = DateUtil.getChineseZodiac(Integer.parseInt(yearStd));String beginDateOfYear = DateUtil.format(DateUtil.beginOfYear(dateTime), DATE_STD_FORMAT);String endDateOfYear = DateUtil.format(DateUtil.endOfYear(dateTime), DATE_STD_FORMAT);});}
}

SQL：

-- CREATE EXTERNAL TABLE `dim.dim_date_base`(
--   `nature_date` string COMMENT '自然日期', 
--   `dim_date` bigint COMMENT '维度日期', 
--   `year` int COMMENT '年', 
--   `month` int COMMENT '月', 
--   `week` int COMMENT '周', 
--   `week_start` string COMMENT '周开始', 
--   `week_end` string COMMENT '周结束', 
--   `week_of_year` int COMMENT '周所属年', 
--   `month_start` string COMMENT '月开始', 
--   `month_end` string COMMENT '月结束', 
--   `is_last_day_of_month` int COMMENT '是否是最后天所属月', 
--   `month_date_cnt` int COMMENT '月日期总数', 
--   `day_of_month` int COMMENT '天所属月', 
--   `day_of_year` int COMMENT '天所属年', 
--   `is_week_end` int COMMENT '是否是周结束', 
--   `quarter` int COMMENT '所属季度')
-- COMMENT '日期域'WITHDateArray AS (SELECTsequence(to_date('2000-01-01', 'yyyy-MM-dd'),to_date('2100-12-31', 'yyyy-MM-dd'),interval 1 day) AS dateArray)insert into table dim.dim_date_baseSELECTdateLog `date` , CAST(regexp_replace(dateLog, '-', '') AS INT) dt,YEAR(dateLog) AS `year`,MONTH(dateLog) AS `month`,DAYOFWEEK(date_add(dateLog,-1)) AS `week`,date_add(dateLog,(DAYOFWEEK(dateLog-1)-1)*-1) week_begin_date ,date_add(dateLog, 7-DAYOFWEEK(dateLog-1) ) week_end_date ,WEEKOFYEAR(dateLog)  week_of_year,concat(substr(dateLog,0,8),'01')  month_begin_date,last_day(dateLog)  month_end_date,if(dateLog=last_day(dateLog),1,0) is_last_day_of_month ,day(last_day(dateLog)) month_date_cnt ,day(dateLog) day_of_month ,dayofyear(dateLog) day_of_year,if( DAYOFWEEK(dateLog) in(1,7),1,0) is_week_end,quarter(dateLog) `quarter`
FROM(SELECTexplode(dateArray) AS dateLogFROMDateArray) t