• 使用Hive SQL创建日期维度表


    使用Hive SQL创建日期维度表

    一、需求

    ​ 需要在Hive数仓中创建一张时间跨度为2010年-2025年的时间维度表,字段如下:

    类型 Comment 计算逻辑(用于需求沟通)
    date string 标准日期格式 2020-01-01
    date_ds string ds日期格式 20200101
    year string 年份 2020
    month string 月份 01
    day string 日期 01
    day_of_week bigint 星期几【1-7】
    week_of_year bigint 本年度第几周 1. 以本年度第一个周一开始计算 2. 本年度前几日如属于上一年度的最后一周,则与上一年度最后一周的序号相同
    week_of_month bigint 本月第几周 与weekOfYear类似

    二、代码

    SELECT
        `date`,
        date_ds,
        year,
        month,
        day,
        day_of_week,
        weekofyear(`date`) as week_of_year, 
        --from_unixtime(unix_timestamp(`date`, "yyyy-MM-dd"), "W") as week_of_month  
        CAST((day(theMonday) - 1) / 7 + 1 AS BIGINT) as week_of_month
    FROM (
      SELECT
        `date`,
        regexp_replace(`date`, '-', '') as date_ds,
        year(`date`) as year,
        month(`date`) as month,
        day(`date`) as day,
        -- 请参看代码拆析 2   date_sub(next_day(`date`, 'Mon'), 7) as theMonday,
        if(datediff(next_day(`date`, 1), `date`) == 7, date_sub(`date`, 6), date_sub(next_day(`date`, 2), 7)) as theMonday,
        -- 版本支持date_format,可以使用: date_format(`date`, 'u') as day_of_week 
        from_unixtime(unix_timestamp(`date`, "yyyy-MM-dd"), "u") as day_of_week 
      FROM (
        SELECT date_add(`start_date`,pos) AS `date`
        from (
          SELECT `start_date` 
          FROM table_contains_startDate
        ) t 
        lateral view posexplode(split(repeat(", ", 9495), ",")) tf AS pos,val --  9495为时间跨度
      ) dates
    ) dates_expanded
    SORT BY `date`
    ;
    -- 需要一张包含起始日期的table_contains_startDate表
    

    代码拆析

    -- dates:获取从start_date到往后n天的date字段数据
    SELECT date_add(`start_date`,pos) AS `date`
    from (
        SELECT `start_date` 
        FROM table_contains_startDate
    ) t 
    lateral view posexplode(split(repeat(", ", 9495), ",")) tf AS pos,val --9495为时间跨度
    

    ​ 由于笔者所使用的Hive版本子句中不允许没有FROM语句: FAILED: Error in semantic analysis: ERROR-0-HIVE:00003:{semantic error => sub query must have a from clause!!}},所以创建了一张只包含起始日期的表table_contains_startDate,如果版本允许可以直接改为:

    -- dates:获取从start_date到往后n天的date字段数据
    SELECT date_add(`start_date`,pos) AS `date`
    from (
        SELECT '2010-01-01'
    ) t 
    lateral view posexplode(split(repeat(", ", 9495), ",")) tf AS pos,val --9495为时间跨度
    
    -- dates_expanded:
    if(datediff(next_day(`date`, 1), `date`) == 7, date_sub(`date`, 6), date_sub(next_day(`date`, 2), 7)) as theMonday,
    -- 标准Hive函数  date_sub(next_day(`date`, 'Mon'), 7) as theMonday,
    
    --
    CAST((day(theMonday) - 1) / 7 + 1 AS BIGINT) as week_of_month
    

    ​ 由于week_of_month 字段逻辑与SimpleDateFormat提供的W参数逻辑不同,所以需要另谋他法。总体的思路是:取date所在周的周一 theMonday,使用theMonday的 后两位 减一的差 对7取商 再加一 即可(说起来很拗口,看代码即可。该思路由 力丞 大佬特别赞助!)。

    ​ 笔者公司next_day()函数并非标准的Hive函数且有bug,使用标准Hive函数库的同学使用注释中的语句即可。

    三、结果预览

    ​ SQL结果插入维度表得到结果如下:

    参考资料:

    1. Create calendar dimension table in hive query language (HQL)
    2. HIVE posexplode 时间区间拆分成单独行
    3. Class SimpleDateFormat 参数含义
  • 相关阅读:
    2.7连接数据库中遇见的相应问题1
    linux bash中too many arguments问题的解决方法
    linux系统补丁更新 yum命令
    安装node,linux升级gcc
    python-导出Jenkins任务
    升级openssl和openssh版本
    linux修改文件所属的用户组以及用户
    linux的Umask 为022 和027 都是什么意思?
    keepalived
    自己编写k8s
  • 原文地址:https://www.cnblogs.com/DavonC/p/13868389.html
Copyright © 2020-2023  润新知