hive生成日期维表

电玩女神 2022-10-30 12:21 172阅读 0赞

package com.leju.commerce.util;
    import java.io.FileWriter;
    import java.io.IOException;
    import java.text.SimpleDateFormat;
    import java.util.Calendar;
    
    /**
     * 数据导入
    
    create EXTERNAL table dim_crm_date(
    id int comment 'key',
    time string comment 'fmt:2019-12-03',
    year int comment 'ex:2019',
    month int comment '[1-12]',
    day int comment '[1-31]',
    yearmonth int comment 'ex:202010',
    year_month_day int comment 'ex:20201001',
    month_name string comment 'ex:一月',
    quarter int comment '[1-4]',
    quarter_name string comment 'ex:一季度',
    week int comment '[0-6]',
    week_name string comment 'ex:星期一',
    year_quarter int comment 'ex:20201',
    yearmonth_26 int comment '26号作为下一个月的年月',
    day_26 int comment '26号作为下一个月的日计数'
    )
    comment '时间维度表'
    row format delimited fields terminated by ','
    stored as textfile
    LOCATION '/data/ods/tmp/dim_crm_date'
    
    hdfs dfs -put date.csv hdfs://…………………………………………
     */
    
    public class DateGen {
        static String[] weekName = new String[]{
                "星期日",
                "星期一",
                "星期二",
                "星期三",
                "星期四",
                "星期五",
                "星期六",
        };
    
    
        static String[] quartName = new String[]{
                "一季度",
                "二季度",
                "三季度",
                "四季度",
        };
    
        static String[] monName = new String[]{
                "一月",
                "二月",
                "三月",
                "四月",
                "五月",
                "六月",
                "七月",
                "八月",
                "九月",
                "十月",
                "十一月",
                "十二月",
        };
    
        final static String delimite = ",";
    
        public static void main(String[] args) throws IOException {
            Calendar start = Calendar.getInstance();
            start.set(2000, 0, 1);
            Calendar end = Calendar.getInstance();
            end.set(2040, 11, 31);
            int index = 0;
            final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    
            Calendar lastMonth = Calendar.getInstance();
            try (FileWriter fw = new FileWriter("date.csv")) {
                while (start.compareTo(end) < 0) {
                    int year = start.get(Calendar.YEAR);
                    int month = start.get(Calendar.MONTH);
                    int quarter = month / 3;
                    int day = start.get(Calendar.DAY_OF_MONTH);
                    int week = start.get(Calendar.DAY_OF_WEEK) - 1;
    
                    lastMonth.setTime(start.getTime());
                    lastMonth.add(Calendar.MONTH, -1);
                    int lastMonthMaxDay = lastMonth.getActualMaximum(Calendar.DAY_OF_MONTH);
    
                    int year26 = year;
                    int month26 = month;
                    int day26 = day;
    
                    if (day >= 26) {
                        day26 = day - 25;
                        month26 += 1;
                        if (month26 >= 12) {
                            month26 = 0;
                            year26 += 1;
                        }
                    } else {
                        day26 += (lastMonthMaxDay - 25);
                    }
    
                    StringBuilder sb = new StringBuilder();
                    sb.append(index);
                    sb.append(delimite).append(sdf.format(start.getTime()));
                    sb.append(delimite).append(year);
                    sb.append(delimite).append(month + 1);
                    sb.append(delimite).append(day);
                    sb.append(delimite).append(year * 100 + month + 1);
                    sb.append(delimite).append(year * 10000 + (month + 1) * 100 + day);
                    sb.append(delimite).append(monName[month]);
                    sb.append(delimite).append(quarter + 1);
                    sb.append(delimite).append(quartName[quarter]);
                    sb.append(delimite).append(week);
                    sb.append(delimite).append(weekName[week]);
                    sb.append(delimite).append(year * 10 + quarter + 1);
                    sb.append(delimite).append(year26 * 100 + month26 + 1);
                    sb.append(delimite).append(day26);
                    sb.append('\n');
                    fw.write(sb.toString());
                    start.add(Calendar.DATE, 1);
                    index += 1;
                }
            }
        }
    }