一、维度表模型
字段 | 说明 |
---|---|
dt | 日历日期 |
lunar_dt | 农历日期 |
calendar_year | 年份 |
quarter_of_year | 季度 |
month_of_year | 月份 |
week_of_year | 一年第几周 |
dt_of_week | 一周第几天 |
dt_of_month | 一个月第几天 |
dt_of_year | 一年第几天 |
is_last_day_of_month | 是否一个月最后一天 |
is_weekend | 是否周末日 |
is_holiday | 是否节假日 |
holiday_cnt | 节日天数 |
holiday_type | 日历天类型:1:工作日;2:周末(和假期连着按假期,因节假日调休上班按工作日算:3:元旦 4:春节 5:清明节 6:劳动节:7:端午节 8:中秋节 9:国庆 |
二、维度表数据生成
基于python3构建维度表数据。之后可以导入到hdfs,并构建hive表来映射到这份数据。
#!/usr/bin/python
# -*- coding: utf-8 -*-
import sys
import imp
import requests
import json
import datetime
import time
import calendar
import lunardate
import csv
imp.reload(sys) # reload 才能调用 setdefaultencoding 方法
holiday_dict = {}
# 判断二月闰月
def isLeapYear(year):
return calendar.monthrange(year, 2)[1] == 29
# 季度信息
def quarter(month):
return (month - 1) / 3 + 1
# 周信息
def get_week_of_year(dt):
return dt.isocalendar()[1]
def get_day_of_year(dt):
return dt.timetuple().tm_yday
def get_day_of_week(day):
return day.weekday() + 1 % 7
# 返回是否最后一天
def last_day_of_month(year, month, day):
return day == calendar.monthrange(year, month)[1]
def get_is_weekend(day):
return day.weekday() == 5 or day.weekday() == 6
def get_holiday_dict(year, month):
params = {
'resource_id': 6018,
'ie': 'utf8',
'oe': 'utf8',
'format': 'json',
'tn': 'baidu'
}
query_arg = "%d年%d月"
params['query'] = query_arg % (year, month)
res = requests.get(url='https://sp0.baidu.com/8aQDcjqpAAV3otqbppnN2DJv/api.php?', params=params).text
# 取括号内的
res = res[res.find("{"): res.rfind("}") + 1]
js = json.loads(res)
holiday_list = js.get('data')[0].get('holiday')
for i in holiday_list:
list_list = i.get('list')
name = i.get('name')
day_cnt = len(list_list)
for x in list_list:
status = x.get('status')
if status == 2:
day_cnt -= 1
for j in list_list:
dt = j.get('date')
status = j.get('status')
item_info = (status,name,day_cnt)
holiday_dict[dt] = item_info
# 获取阴历日期
def get_lunar_dt(dt):
ymd = dt.split('-')
lunar = lunardate.LunarDate.fromSolarDate(int(ymd[0]), int(ymd[1]), int(ymd[2]))
return "%d-%02d-%02d" % (lunar.year, lunar.month, lunar.day)
def get_is_holiday(day):
dt = day.strftime("%Y-%m-%d")
item = holiday_dict.get(dt)
if item is not None :
return int(item[0]) == 1
else:
return False
def is_work_day(day,is_weekend):
dt = day.strftime("%Y-%m-%d")
item = holiday_dict.get(dt)
if item is not None :
# 如果在holiday列表里但是状态等于2
return int(item[0]) == 2
else:
return not (is_weekend)
def get_holiday_cnt(day):
dt = day.strftime("%Y-%m-%d")
item = holiday_dict.get(dt)
if item is not None and int(item[0]) == 1:
return int(item[2])
else:
return 0
def get_holiday_type(day,is_weekend):
dt = day.strftime("%Y-%m-%d")
item = holiday_dict.get(dt)
if item is not None :
if int(item[0]) == 1:
if str(item[1]) == '元旦' :
return 3
elif str(item[1]) == '春节' :
return 4
elif str(item[1]) == '清明节' :
return 5
elif str(item[1]) == '劳动节' :
return 6
elif str(item[1]) == '端午节' :
return 7
elif str(item[1]) == '中秋节' :
return 8
elif str(item[1]) == '国庆节':
return 9
else:
return 99
else:
return 1
else:
if is_weekend:
return 2
else:
return 1
return 1
def write_csv(data_row):
path = "hive_date_dim.csv"
with open(path,'a+') as f:
csv_write = csv.writer(f)
csv_write.writerow(data_row)
if __name__ == '__main__':
start_date = datetime.datetime(2022,1,1)
#end_date = datetime.datetime(2022,1,31)
end_date = datetime.datetime(2022,12,31)
day = start_date
get_holiday_dict(2020,1)
while(day <= end_date):
dt = day.strftime("%Y-%m-%d")
lunar_dt = get_lunar_dt(dt)
calendar_year = day.year
quarter_of_year = quarter(day.month)
month_of_year = day.month
week_of_year = get_week_of_year(day)
dt_of_week = get_day_of_week(day)
dt_of_month = day.day
dt_of_year = get_day_of_year(day)
is_last_day_of_month = last_day_of_month(calendar_year,month_of_year,dt_of_month)
is_weekend = get_is_weekend(day)
is_holiday = get_is_holiday(day)
holiday_type = get_holiday_type(day,is_weekend)
holiday_cnt = get_holiday_cnt(day)
data_raw = [dt,lunar_dt,calendar_year,quarter_of_year,month_of_year,week_of_year,dt_of_week,dt_of_month,dt_of_year,is_last_day_of_month,is_weekend,is_holiday,holiday_type,holiday_cnt]
print('data_raw', data_raw)
write_csv(data_raw)
print('日期:'+dt+ '年份'+str(calendar_year)+'Q'+str(quarter_of_year)+'月份'+str(month_of_year)+'周'+str(week_of_year)+'星期'+str(dt_of_week)+' 月的第'+str(dt_of_month)+' 年的第'+str(dt_of_year)+str(is_weekend))
day = day + datetime.timedelta(days=1)
数据样例:
日期:2022-01-01年份2022Q1.0月份1周52星期6 月的第1 年的第1True
data_raw ['2022-01-01', '2021-11-29', 2022, 1.0, 1, 52, 6, 1, 1, False, True, False, 2, 0]
日期:2022-01-02年份2022Q1.0月份1周52星期7 月的第2 年的第2True
data_raw ['2022-01-02', '2021-11-30', 2022, 1.0, 1, 52, 7, 2, 2, False, True, False, 2, 0]