维度表制作"/>
数据仓库维度表制作
一、维度表模型
字段 | 说明 |
---|---|
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 csvimp.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_ydaydef 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() == 6def 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='.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 -= 1for 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]) == 1else:return Falsedef is_work_day(day,is_weekend):dt = day.strftime("%Y-%m-%d")item = holiday_dict.get(dt)if item is not None :# 如果在holiday列表里但是状态等于2return int(item[0]) == 2else: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 0def 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 3elif str(item[1]) == '春节' :return 4elif str(item[1]) == '清明节' :return 5elif str(item[1]) == '劳动节' :return 6elif str(item[1]) == '端午节' :return 7elif str(item[1]) == '中秋节' :return 8elif str(item[1]) == '国庆节':return 9else:return 99else:return 1else:if is_weekend:return 2else:return 1return 1def 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_dateget_holiday_dict(2020,1)while(day <= end_date):dt = day.strftime("%Y-%m-%d")lunar_dt = get_lunar_dt(dt)calendar_year = day.yearquarter_of_year = quarter(day.month)month_of_year = day.monthweek_of_year = get_week_of_year(day)dt_of_week = get_day_of_week(day)dt_of_month = day.daydt_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]
更多推荐
数据仓库维度表制作
发布评论