Kettle生成日期维度数据(数仓项目)

忘是亡心i 2022-10-30 01:26 554阅读 0赞

### 目录 ###

*  生成日期维度数据
 *   *  Hive创建日期维度表
     *  使用Kettle构建以下组件结构图
     *  组件配置
     *  验证数据
     *  报错，请看这

# 生成日期维度数据 #

经过需求分析我们发现指标统计需要的日期类型比较多如果在事实表的时间字段上做文章，比较复杂，甚至有些根本无法获取到比如节假日、休息日、工作日信息。所以我们可以考虑提前准备一张日期维度表，准备好指标统计的日期类型。  
利用我们前面学习的kettle工具生成日期维度数据，需要借助于kettle的js脚本组件。  
kettle实现思路：

*  日期使用 Canlendar、SimpleDateFormat，编写Javascript脚本实现
 *  国家法定节假日实现，需要往[http://timor.tech/api/holiday/info/2018-03-02][http_timor.tech_api_holiday_info_2018-03-02]年月日，发送请求，获取结果，以下为该REST API接口简介

1、接口地址：http://timor.tech/api/holiday/info/数字日期，支持https协议。
    2、返回数据：正常工作日对应结果为 0,，休息日对应结果为 1, 法定节假日对应结果为 2
    3、节假日数据说明：本接口包含2017年起的中国法定节假日数据，数据来源国务院发布的公告，每年更新1次，确保数据最新
    4、示例：
    http://timor.tech/api/holiday/info/2018-03-02
    返回数据：
    { "code":0,"type":{ "type":0,"name":"周五","week":5},"holiday":null}
    
    { 
      "code": 0,              // 0服务正常。-1服务出错
      "type": { 
        "type": enum(0, 1, 2), // 节假日类型，分别表示 工作日、周末、节日。
        "name": "周六",         // 节假日类型中文名，可能值为 周一 至 周日、假期的名字、某某调休。
        "week": enum(1 - 7)    // 一周中的第几天。值为 1 - 7，分别表示 周一 至 周日。
      },
      "holiday": { 
        "holiday": false,     // true表示是节假日，false表示是调休
        "name": "国庆前调休",  // 节假日的中文名。如果是调休，则是调休的中文名，例如'国庆前调休'
        "wage": 1,            // 薪资倍数，1表示是1倍工资
        "after": false,       // 只在调休下有该字段。true表示放完假后调休，false表示先调休再放假
        "target": '国庆节'     // 只在调休下有该字段。表示调休的节假日
      }
    }

<table> 
 <thead> 
  <tr> 
   <th>名</th> 
   <th>类型</th> 
   <th>示例值</th> 
   <th>中文名</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>date_key</td> 
   <td></td> 
   <td>20000101</td> 
   <td>代理键</td> 
  </tr> 
  <tr> 
   <td>date_value</td> 
   <td></td> 
   <td>2000-01-01</td> 
   <td>年-月-日</td> 
  </tr> 
  <tr> 
   <td>day_in_year</td> 
   <td></td> 
   <td>1</td> 
   <td>当年的第几天</td> 
  </tr> 
  <tr> 
   <td>day_in_month</td> 
   <td></td> 
   <td>1</td> 
   <td>当月的第几天</td> 
  </tr> 
  <tr> 
   <td>is_first_day_in_month</td> 
   <td></td> 
   <td>y</td> 
   <td>是否月的第一天</td> 
  </tr> 
  <tr> 
   <td>is_last_day_in_month</td> 
   <td></td> 
   <td>n</td> 
   <td>是否月的最后一天</td> 
  </tr> 
  <tr> 
   <td>weekday</td> 
   <td></td> 
   <td>星期一</td> 
   <td>星期</td> 
  </tr> 
  <tr> 
   <td>week_in_month</td> 
   <td></td> 
   <td>1</td> 
   <td>月的第几个星期</td> 
  </tr> 
  <tr> 
   <td>is_first_day_in_week</td> 
   <td></td> 
   <td>y、n</td> 
   <td>是否周一</td> 
  </tr> 
  <tr> 
   <td>is_dayoff</td> 
   <td></td> 
   <td>y、n</td> 
   <td>是否休息日</td> 
  </tr> 
  <tr> 
   <td>is_workday</td> 
   <td></td> 
   <td>y、n</td> 
   <td>是否工作日</td> 
  </tr> 
  <tr> 
   <td>is_holiday</td> 
   <td></td> 
   <td>y、n</td> 
   <td>是否国家法定节假日</td> 
  </tr> 
  <tr> 
   <td>date_type</td> 
   <td></td> 
   <td>workday、weekend、holiday<br>工作日、周末、法定节假日</td> 
   <td>日期类型<br>工作日：workday<br>国家法定节假日：holiday<br><br>休息日：weekend</td> 
  </tr> 
  <tr> 
   <td>month_number</td> 
   <td></td> 
   <td>1、2、…、12</td> 
   <td>月份</td> 
  </tr> 
  <tr> 
   <td>year</td> 
   <td></td> 
   <td>2000</td> 
   <td>年份</td> 
  </tr> 
  <tr> 
   <td>quarter_name</td> 
   <td></td> 
   <td>Q1</td> 
   <td>季度名称</td> 
  </tr> 
  <tr> 
   <td>quarter_number</td> 
   <td></td> 
   <td>1</td> 
   <td>季度</td> 
  </tr> 
  <tr> 
   <td>year_quarter</td> 
   <td></td> 
   <td>2000-Q1</td> 
   <td>年-季度</td> 
  </tr> 
  <tr> 
   <td>year_month_number</td> 
   <td></td> 
   <td>2000-01</td> 
   <td>年-月份</td> 
  </tr> 
 </tbody> 
</table>

## Hive创建日期维度表 ##

参考SQL语句：

DROP TABLE IF EXISTS `itcast_dw`.`dim_date`;
    CREATE TABLE `itcast_dw`.`dim_date`(
        `date_key` string,
        `date_value` string,
        `day_in_year` string,
        `day_in_month` string,
        `is_first_day_in_month` string,
        `is_last_day_in_month` string,
        `weekday` string,
        `week_in_month` string,
        `is_first_day_in_week` string,
        `is_dayoff` string,
        `is_workday` string,
        `is_holiday` string,
        `date_type` string,
        `month_number` string,                        
        `year` string,
        `year_month_number` string,
        `quarter_name` string,
        `quarter_number` string,
        `year_quarter` string
    )
    stored as parquet TBLPROPERTIES('parquet.compression'='SNAPPY');

## 使用Kettle构建以下组件结构图 ##

*  生成记录
 *  增加序列
 *  Javascript代码
 *  Http Client组件（5个并行度）
 *  JSON Input
 *  Switch/case
 *  字段映射为常量
    
     *  工作日
     *  法定节假日
     *  节假日调休补班
     *  休息日
 *  字段选择
 *  Parquet Output  
    **可以下载直接打开**

链接：https://pan.baidu.com/s/1PQMy8GRm1BMhyE4hxfZazQ 
    提取码：cjha

**kettle生成自定义日期维度数据组件图**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70]

## 组件配置 ##

**生成记录组件-配置限制为 365**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 1]  
**配置增加序列组件**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 2]  
**Javascript组件，生成基本时间维度数据**

// 1. 初始化上游变量
            var initDateStr = "2019-01-01";
            var locale = new java.util.Locale("en", "us");
            var calendar = java.util.Calendar.getInstance();
            var sdf = new java.text.SimpleDateFormat("yyyy-MM-dd", locale);
            var initDate  = sdf.parse(initDateStr);
    
            calendar.setTime(initDate);
            calendar.add(java.util.Calendar.DAY_OF_MONTH, setup);
    
            var curDate = calendar.getTime();
    
            // 2. 生成维度数据
            // 2.1 生成代理键
            sdf.applyPattern("yyyyMMdd");
            var date_key = sdf.format(curDate);
    
            // 2.2 年-月-日
            sdf.applyPattern("yyyy-MM-dd");
            var date_value = sdf.format(curDate);
    
            // 2.3 当年的第几天
            var day_in_year = calendar.get(java.util.Calendar.YEAR) + "";
    
            // 2.4 当月的第几天
            var day_in_month = calendar.get(java.util.Calendar.DAY_OF_MONTH) + "";
    
            // 2.5 是否为当月的第一天
            var is_first_day_in_month = day_in_month.equals("1") ? "y":"n";
    
            // 2.6 是否为当月最后一天
            // 1. 加一天
            calendar.add(java.util.Calendar.DAY_OF_MONTH, 1);
    
            var is_last_day_in_month = calendar.get(java.util.Calendar.DAY_OF_MONTH) == 1 ? "y":"n";
            // 2. 减回去
            calendar.add(java.util.Calendar.DAY_OF_MONTH, -1);
    
            // 2.7 星期
            var weekday = (calendar.get(java.util.Calendar.DAY_OF_WEEK) - 1) + "";
            if(weekday.equals("0")) { 
                weekday = "7";
            }
    
            // 2.8 月的第几个星期
            sdf.applyPattern("W");
            var week_in_month = sdf.format(curDate);
    
            // 2.9 是否周一
            var is_first_day_in_week = calendar.get(java.util.Calendar.DAY_OF_WEEK) == java.util.Calendar.MONDAY ? "y":"n";
    
            // 2.10 是否休息日
            var is_dayoff = "n";
    
            // 2.11 是否工作日
            var is_workday = "n";
    
            // 2.12 是否国家法定节假日
            var is_holiday = "n";
            // 2.12 国家法定节假日获取URL
            var holiday_url = "http://timor.tech/api/holiday/info/" + date_value;
    
            // 2.13 日期类型
            var date_type = "workday";
    
            // 2.14 月份
            sdf.applyPattern("MM");
            var month_number = sdf.format(curDate);
    
            // 2.15 年份
            sdf.applyPattern("yyyy");
            var year = sdf.format(curDate);
    
            // 2.16 年份-月份
            sdf.applyPattern("yyyy-MM");
            var year_month_number = sdf.format(curDate);
    
            // 2.16 季度名称、季度、年季度
            var quarter_name = "";
            var quarter_number = "";
            var year_quarter = "";
    
            switch (calendar.get(java.util.Calendar.MONTH)) { 
                case java.util.Calendar.FEBRUARY:
                case java.util.Calendar.JANUARY:
                case java.util.Calendar.MARCH:
                    quarter_name = "Q1";
                    quarter_number = "1";
                    year_quarter = year + "-" + quarter_name;
                    break;
                case java.util.Calendar.APRIL:
                case java.util.Calendar.MAY:
                case java.util.Calendar.JUNE:
                    quarter_name = "Q2";
                    quarter_number = "2";
                    year_quarter = year + "-" + quarter_name;
                    break;
                case java.util.Calendar.JULY:
                case java.util.Calendar.AUGUST:
                case java.util.Calendar.SEPTEMBER:
                    quarter_name = "Q3";
                    quarter_number = "3";
                    year_quarter = year + "-" + quarter_name;
                    break;
                case java.util.Calendar.OCTOBER:
                case java.util.Calendar.NOVEMBER:
                case java.util.Calendar.DECEMBER:
                    quarter_name = "Q4";
                    quarter_number = "4";
                    year_quarter = year + "-" + quarter_name;
                    break;
            }

**配置HttpClient组件**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 3]

**配置JSON Input组件**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 4]  
**配置 switch/case 组件**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 5]  
**配置工作日、法定节假日、休息日等组件**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 6]  
**配置字段选择组件**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 7]  
**配置parquet输出**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 8]

## 验证数据 ##

select * from itcast_dw.dim_date limit 10;

## 报错，请看这 ##

如果你接收http超时的话，把这两个值调大一点  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 9]

[http_timor.tech_api_holiday_info_2018-03-02]: http://timor.tech/api/holiday/info/2018-03-02
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70]: /images/20221024/d20bc4f2fe39431e9763cb61775e15b2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 1]: /images/20221024/3378f81e21bf40b2ac7cbbc91a593533.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 2]: /images/20221024/ad55344e4d6f42508efafbb4c2842f38.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 3]: /images/20221024/08fc676db08b4e1b834dbc82ff78d1ac.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 4]: /images/20221024/aae775ab17a54480b096de896ed45b34.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 5]: /images/20221024/454569ab78dd43ec92ee7c3d09759fd8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 6]: /images/20221024/fc6908a057c4462a8d81c1d8b8d7da44.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 7]: /images/20221024/83e0ddc451ee4ad1ae6637fe6cc50760.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 8]: /images/20221024/6b455cd6861e4bf4964ac2572b3b8c65.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 9]: /images/20221024/a2c16ae6fc244855a1499ac3c38a8983.png