侧边栏壁纸
博主头像
王一川博主等级

努力成为一个不会前端的全栈工程师

  • 累计撰写 70 篇文章
  • 累计创建 20 个标签
  • 累计收到 40 条评论

目 录CONTENT

文章目录

构建 hive 时间维表

王一川
2023-10-31 / 0 评论 / 0 点赞 / 4,902 阅读 / 1,153 字
温馨提示:
本文最后更新于 2023-10-31,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。

众所周知 hive 的时间处理异常繁琐且在一些涉及日期的统计场景中会写较长的 sql,例如:周累计、周环比等;本文将使用维表的形式降低时间处理的复杂度,提前计算好标准时间字符串未来可能需要转换的形式。

一、表设计

结合业务场景常用的时间字符串格式为 yyyyMMdd,因此我们将这种格式字段作为维表的关联键,用来派生剩下的字段,例如 yyyy-MM-dd、yyyy/MM/dd、yyyy、MM、dd 以及令人头疼的周(w),因此当前版本的时间维表 DDL 如下

create table dim_xxx.dim_dateformat
(
    dt          string comment '日期,yyyymmdd',
    dt_format1  string comment '日期,yyyy-mm-dd',
    dt_format2  string comment '日期,yyyy/mm/dd',
    dt_year     string comment '所在年份',
    dt_month    string comment '所在月份',
    dt_day      string comment '所在日',
    dt_week_str string comment '星期(英文)',
    dt_week_num string comment '星期(数字)',
    dt_abs_week bigint comment '绝对周,从 19700101 为第一周',
    dt_rel_week string comment '相对周,从本年的第一个周一为第一周'
) comment '日期维表'
    stored as parquet;

需要解释一下 dt_abs_week 和 dt_rel_week 字段,该字段用于提升周累计、周环比的计算效率。dt_abs_week 绝对周是约定 19700101 为第一周,后续每遇到一个周一加一;dt_rel_week 主要用来对外展示,例如:

  1. 截止昨日周累计:获取通过 dt 获取昨日所在的 dt_abs_week 或 dt_rel_week,从而可以当前周的 dt 范围,根据 dt 关联业务表即可
  2. 周环比:获取通过 dt 获取昨日所在的 dt_abs_week - 1 即可获取环比的所在周,再结合 dt_week_num 可以灵活控制环比整周或环比上周的对应星期

二、填充数据

这里使用 python 生成 csv 并 load 进去即可(这种方式最简单,对比过使用 sql 来实现),因为生产环境 hive 表的存储格式往往不是 textfile,例如博主所在公司所用的存储格式就是 parquet,遵循一切从简的原则,创建同 schema 的 textfile 表(一切从简,注释都不写)

create table dim_xxx.dim_dateformat_load
(
    dt          string,
    dt_format1  string,
    dt_format2  string,
    dt_year     string,
    dt_month    string,
    dt_day      string,
    dt_week_str string,
    dt_week_num string,
    dt_abs_week string,
    dt_rel_week string
)
    row format delimited fields terminated by ','
    stored as textfile;

下面的重点是 python 如何实现,直接上代码

import datetime
import csv

# 定义日期范围
start_date = datetime.date(1970, 1, 1)
end_date = datetime.date(2500, 12, 31)

with open(file='dim_dateformat.csv', mode='w', encoding='utf8', newline='') as f:
    writer = csv.writer(f)

    # 循环遍历
    current_date = start_date
    # 初始绝对周
    abs_week_num = 1
    # 初始相对周
    rel_week_num = 1
    rel_year = 1970
    display_year_of_week = '1970-1'
    while current_date <= end_date:
        # 各种时间格式
        format1 = current_date.strftime("%Y%m%d")
        format2 = current_date.strftime("%Y-%m-%d")
        format3 = current_date.strftime("%Y/%m/%d")
        # 年、月、日、星期
        year = current_date.year
        month = current_date.strftime("%m")
        day = current_date.strftime("%d")
        day_of_week1 = current_date.strftime("%A")
        day_of_week2 = current_date.strftime("%w")

        day_of_week2 = day_of_week2 if day_of_week2 != '0' else '7'

        if day_of_week2 == '1':
            abs_week_num += 1
            # 计算相对周
            rel_week_num += 1
            if rel_year != year:
                rel_year = year
                rel_week_num = 1
            display_year_of_week = str(rel_year) + '-' + str(rel_week_num)

        # 写入 csv
        writer.writerow([format1, format2, format3, year, month, day, day_of_week1, day_of_week2, abs_week_num,
                         display_year_of_week])

        # ++
        current_date += datetime.timedelta(days=1)

解释一下相对周和绝对周的计算方式即可

  1. 初始化 abs_week_num、rel_week_num 为 1,rel_year 为 1970
  2. 如果是周一,abs_week_num 加 1;rel_week_num 加 1 转第 3 步。否则转第 4 步
  3. 如果年份不等于 rel_year 则将当前年份赋值给 rel_year 并重置 rel_week_num 为 1
  4. 写入文件

对于绝对周初始为 1 后逢周一进一即可,对于相对周,对于周的部分也是逢周一进一,若跨年则年份加一后重置周的计数

之后将得到的 dim_dateformat.csv 文件 load 进 dim_dateformat_load 并执行下面 sql

insert overwrite table dim_dateformat
select * from dim_dateformat_load

结果如下

image-20231031213014670

接下来就可以拿着这张维表尽情玩耍吧

0

评论区