数据仓库维度表制作

维度表制作"/>

数据仓库维度表制作

一、维度表模型

二、维度表数据生成

基于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]