工业大数据之行政地区维度设计（14）01：行政地区维度设计目标：掌握行政地区维度的需求及设计路径 step1：需求

01：行政地区维度设计

目标：掌握行政地区维度的需求及设计
路径
- step1：需求
- step2：设计
实施
- 需求：构建行政地区维度表，得到所有省份、城市、县区及乡镇维度信息
  - 省份维度表
```
省份id	省份名称
```
  - 城市维度表
```
省份id	省份名称	城市id	城市名称
```
  - 县区维度表
```
省份id	省份名称	城市id	城市名称	县区id	县区名称
```
  - 乡镇维度表
```
省份id	省份名称	城市id	城市名称	县区id	县区名称	乡镇id	乡镇名称
11        北京市      1101      北京市       110108    海淀区    110108014  清华园街道
```
  - 统计不同地区维度下的网点个数、工单个数、报销金额等
- 设计
  - 数据来源：one_make_dwd.ciss_base_areas
```
select * from one_make_dwd.ciss_base_areas;
```
    - id：该地区数据id
    - parentid：该地区的父级行政区域的id
    - rank：表示行政地区的级别
      - 0：国家：country
      - 1：省份：province
      - 2：城市：city
      - 3：县/区：county
      - 4：乡镇/区域：town
    - areaname：地区名称
    - 举例
      - 清华园街道：4
      - 海淀区
北京市【市级】
- 北京市【省级】

实现思路：以乡镇维度为例

获取所有乡镇的信息

select id area_id,areaname area,parentid from one_make_dwd.ciss_base_areas where rank = 4;

获取所有县区的信息

select id county_id,areaname county,parentid from one_make_dwd.ciss_base_areas where rank = 3;

获取所有省份的信息

select id city_id,areaname city,parentid from one_make_dwd.ciss_base_areas where rank = 2;

获取所有省份的信息

select id province_id,areaname province,parentid from one_make_dwd.ciss_base_areas where rank = 1;

需求：获取每个镇的所有行政地区信息

省份id	省份名称	城市id	城市名称	县区id	县区名称		乡镇id	乡镇名称

实现：下一级地区的父id = 上一级地区的id

select
    province_id,province,city_id,city,county_id,county,area_id,area
from
     ( select id area_id,areaname area,parentid from one_make_dwd.ciss_base_areas where rank = 4 ) town
join ( select id county_id,areaname county,parentid from one_make_dwd.ciss_base_areas where rank = 3 ) county
    on town.parentid = county.county_id
join ( select id city_id,areaname city,parentid from one_make_dwd.ciss_base_areas where rank = 2 ) city
    on county.parentid = city.city_id
join ( select id province_id,areaname province,parentid from one_make_dwd.ciss_base_areas where rank = 1 ) province
    on city.parentid = province.province_id;

小结
- 掌握行政地区维度的需求及设计

02：行政地区维度构建

目标：实现行政地区维度表的构建

实施

建维度库

create database if not exists one_make_dws;

建维度表

区域粒度【乡镇】

create external table if not exists one_make_dws.dim_location_areas(
    id string comment 'id'
    , province_id string comment '省份ID'
    , province string comment '省份名称'
    , province_short_name string comment '省份短名称'
    , city_id string comment '城市ID'
    , city string comment '城市'
    , city_short_name string comment '城市短名称'
    , county_id string comment '县城ID'
    , county string comment '县城'
    , county_short_name string comment '县城短名称'
    , area_id string comment '区域ID'
    , area string comment '区域名称'
    , area_short_name string comment '区域短名称'
) comment '区域维度区域级别表'
stored as orc
tblproperties ("orc.compress"="SNAPPY")
location '/data/dw/dws/one_make/dim_location_areas';

县区粒度

create external table if not exists one_make_dws.dim_location_county(
    id string comment 'id'
    , province_id string comment '省份ID'
    , province string comment '省份名称'
    , province_short_name string comment '省份短名称'
    , city_id string comment '城市ID'
    , city string comment '城市'
    , city_short_name string comment '城市短名称'
    , county_id string comment '县城ID'
    , county string comment '县城'
    , county_short_name string comment '县城短名称'
) comment '区域维度表（县城粒度）'
stored as orc
tblproperties ("orc.compress"="SNAPPY")
location '/data/dw/dws/one_make/dim_location_county';

抽取数据

区域粒度

insert overwrite table one_make_dws.dim_location_areas
select
    /*+repartition(1) */
    t_area.id as id,
    t_province.id as province_id,
    t_province.areaname as province,
    t_province.shortname as province_short_name,
    t_city.id as city_id,
    t_city.areaname as city,
    t_city.shortname as city_short_name,
    t_county.id as county_id,
    t_county.areaname as county,
    t_county.shortname as county_short_name,
    t_area.id as area_id,
    t_area.areaname as area,
    t_area.shortname area_short_name
from
    one_make_dwd.ciss_base_areas t_area
    inner join one_make_dwd.ciss_base_areas t_county on t_area.rank = 4 and t_area.parentid = t_county.id
    inner join one_make_dwd.ciss_base_areas t_city on t_county.parentid = t_city.id
    inner join one_make_dwd.ciss_base_areas t_province on t_city.parentid = t_province.id
    inner join one_make_dwd.ciss_base_areas t_nation on t_province.parentid = t_nation.id
;

县区粒度

insert overwrite table one_make_dws.dim_location_county
select
    /*+repartition(1) */
    t_county.id as id,
    t_province.id as province_id,
    t_province.areaname as province,
    t_province.shortname as province_short_name,
    t_city.id as city_id,
    t_city.areaname as city,
    t_city.shortname as city_short_name,
    t_county.id as county_id,
    t_county.areaname as county,
    t_county.shortname as county_short_name
from
    one_make_dwd.ciss_base_areas t_county
    inner join one_make_dwd.ciss_base_areas t_city on t_county.rank =3 and t_county.parentid = t_city.id
    inner join one_make_dwd.ciss_base_areas t_province on t_city.parentid = t_province.id
    inner join one_make_dwd.ciss_base_areas t_nation on t_province.parentid = t_nation.id
;

SQL语法如何实现分区调整： /*+repartition(1) */

小结
- 实现行政地区维度表的构建
- 自行完善城市粒度、省份粒度

03：日期时间维度设计

目标：掌握日期时间维度的需求与设计
路径
- step1：需求
- step2：设计

实施

需求：构建日期时间维度表，得到所有年、季度、月、周、日的维度信息

dateId	String	日期ID	20210101
yearNameCN	String	年份名称（中文）	2021年
year	Int	年份	2021
yearMonthId	String	年月ID	202101
yearNameCN	String	年月（中文）	2021年01月
quotaId	String	季度ID	2021Q1
quotaNameCN	String	季度名称（中文）	第一季度
quotaNameEN	String	季度名称（英文）	2021 Q1
quotaShortNameEN	String	季度名称（英文简写）	Q1
weekInYearId	String	周ID	2021W1
weekInYearNameCN	String	周（中文）	2021第1周
weekInYearNameEN	String	周（英文）	2021 W1
weekday	Int	星期	5
weekdayCN	String	星期（中文）	星期五
weekdayEN	String	星期（英文）	Friday
weekdayShortNameEN	String	星期（英文缩写）	Fri
yyyyMMdd	String	日期(yyyy-mm-dd)	2021-01-01
yyyyMMddCN	String	日期中文	2021年01月01日
isWorkday	String	是否工作日	n
isWeekend	String	是否周末	n
isHoliday	String	是否法定节假日	y
dateType	String	日期类型	法定节假日 \| 工作日 \| 周末

统计不同时间维度下的呼叫次数、工单数、维修数、安装数、报销金额、核销数等

设计
- 数据来源：提前通过程序生成构建，不是从数据中抽取的
```
2021  2021-10	4	42周		星期日		2021-10-10		yes	 no		周末
```
- 执行周期：每一年生成下一年的每一天的维度信息，增量同步到维度表的分区中

小结
- 掌握日期时间维度的需求与设计

04：日期时间维度构建

目标：实现日期时间维度表的构建

实施

建维度表

-- 创建日期维度表,日期维度表按照年份分区
create external table if not exists one_make_dws.dim_date(
    date_id string comment '日期id'
    , year_name_cn string comment '年份名称（中文）'
    , year_month_id string comment '年月id'
    , year_month_cn string comment '年月（中文）'
    , quota_id string comment '季度id'
    , quota_namecn string comment '季度名称（中文）'
    , quota_nameen string comment '季度名称（英文）'
    , quota_shortnameen string comment '季度名称（英文简写）'
    , week_in_year_id string comment '周id'
    , week_in_year_name_cn string comment '周（中文）'
    , week_in_year_name_en string comment '周（英文）'
    , weekday int comment '星期'
    , weekday_cn string comment '星期（中文）'
    , weekday_en string comment '星期（英文）'
    , weekday_short_name_en string comment '星期（英文缩写）'
    , yyyymmdd string comment '日期_yyyy_mm_dd'
    , yyyymmdd_cn string comment '日期中文'
    , is_workday string comment '是否工作日'
    , is_weekend string comment '是否周末'
    , is_holiday string comment '是否法定节假日'
    , date_type string comment '日期类型'
) comment '时间维度表'
partitioned by (year integer)
stored as orc
location '/data/dw/dws/one_make/dim_date'
;

加载数据

HDFS创建路径

hdfs dfs -mkdir -p /data/dw/dws/one_make/dim_date/2021

可以使用rz方式

yum install -y lrzsz
进入Hadoop容器
rz
hdfs dfs -put part-00000-cf2fc4b3-7485-4861-81e7-da0c3f76e6de-c000.snappy.orc /data/dw/dws/one_make/dim_date/2021/

上传本地文件

node1上操作

mkdir -p /mnt/docker_share/data/DIM_DATE/2021
cd /mnt/docker_share/data/DIM_DATE/2021/
rz

Hadoop镜像

docker exec -it hadoop bash

hdfs dfs -put /mnt/docker_share/data/DIM_DATE/2021 /data/dw/dws/one_make/dim_date/

申明分区

alter table one_make_dws.dim_date add if not exists partition (year='2021') location '/data/dw/dws/one_make/dim_date/2021';

查看数据

select * from one_make_dws.dim_date;

小结
- 实现日期时间维度表的构建