文章目录
- 01:油站维度设计
- 02:油站维度构建
01:油站维度设计
-
目标:掌握油站维度的需求与设计
-
路径
- step1:需求
- step2:设计
-
实施
-
需求:构建油站维度表,得到油站id、油站名称、油站所属的地理区域、所属公司、油站状态等
-
设计
-
数据来源
-
ciss_base_oilstation:油站信息表
selectid, name, code,customer_id, customer_name,province, city, region, township,status, customer_classify, dt from one_make_dwd.ciss_base_oilstation where id != '' and name is not null and name != 'null' and customer_id is not null;
-
eos_dict_type:字典状态类别表,记录所有需要使用字典标记的表
select * from eos_dict_type where dicttypename = '油站状态';
-
eos_dict_entry:字典状态明细表,记录所有具体的状态或者类别信息
select * from eos_dict_entry where dicttypeid = 'BUSS_OILSTATION_STATUS';
-
ciss_base_baseinfo:客户公司信息表【公司ID、公司名称】
select ygcode, companyname from one_make_dwd.ciss_base_baseinfo group by ygcode, companyname;
- 数据有重复,做个去重
-
ciss_base_customer:客户信息表【客户id、客户省份名称、所属公司ID】
select code, province, company from one_make_dwd.ciss_base_customer;
-
ciss_base_areas:行政地区信息表
- 通过具体的id关联所有地区信息
-
-
实现设计
- 所有表按照对应字段关联,获取对应的属性字段
-
-
-
小结
- 掌握油站维度的需求与设计
02:油站维度构建
-
目标:实现油站维度的构建
-
实施
-
建维度表
-- 创建油站维度表 create external table if not exists one_make_dws.dim_oilstation(id string comment '油站ID', name string comment '油站名称', code string comment '油站编码', customer_id string comment '客户ID', customer_name string comment '客户名称', province_id int comment '省份id', province_name string comment '省份名称', city_id int comment '城市id', city_name string comment '城市名称', county_id int comment '县城ID', county_name string comment '县城名称', area_id int comment '区域id', area_name string comment '区域名称', customer_classify_id string comment '客户分类ID', customer_classify_name string comment '客户分类名称', status int comment '油站状态(1、2)', status_name string comment '油站状态名(正常、停用)', company_id int comment '所属公司ID', company_name string comment '所属公司名称', customer_province_id int comment '客户所属省份ID', customer_province_name string comment '客户所属省份' ) COMMENT '油站维度表' PARTITIONED BY (dt STRING) STORED AS TEXTFILE LOCATION '/data/dw/dws/one_make/dim_oilstation';
-
抽取数据
insert overwrite table one_make_dws.dim_oilstation partition (dt ='20210101') select oil.id, oil.name, oil.code, customer_id, customer_name, oil.province province_id, p.areaname province_name, oil.city city_id, c.areaname city_name, oil.region county_id, county.areaname county_name, oil.township area_id, a.areaname area_name, oil.customer_classify customer_classify_id, ede.dictname customer_classify_name, oil.status status, eosde.dictname status_name, cbc.company company_id, binfo.companyname company_name, proname.id customer_province_id, proname.areaname customer_province_name from (select id, name, code, customer_id, customer_name, province, city, region, township, status, customer_classify, dtfrom one_make_dwd.ciss_base_oilstation where id != '' and name is not null and name != 'null' and customer_id is not null) oilleft join (select id, areaname, parentid from one_make_dwd.ciss_base_areas where rank = 1) p on oil.province = p.idleft join (select id, areaname, parentid from one_make_dwd.ciss_base_areas where rank = 2) c on oil.city = c.idleft join (select id, areaname, parentid from one_make_dwd.ciss_base_areas where rank = 3) county on oil.region = county.idleft join (select id, areaname, parentid from one_make_dwd.ciss_base_areas where rank = 4) a on oil.township = a.idleft join (select dictid, dictname from one_make_dwd.eos_dict_entry) ede on oil.customer_classify = ede.dictidleft join (select dictid, dictname from one_make_dwd.eos_dict_entry t1 left join one_make_dwd.eos_dict_type t2 on t1.dicttypeid = t2.dicttypeid where t2.dicttypename = '油站状态') eosde on oil.status = eosde.dictid-- 客户所属公司id,所属公司名称,所属省份id,所属省份名称left join (select code, province, company from one_make_dwd.ciss_base_customer) cbc on oil.customer_id = cbc.codeleft join (select id, areaname from one_make_dwd.ciss_base_areas where rank = 1 and id != 83) proname on cbc.province = proname.areanameleft join (select ygcode, companyname from one_make_dwd.ciss_base_baseinfo group by ygcode, companyname) binfo on cbc.company = binfo.ygcode where dt = '20210101';
-
查看结果
-
-
小结
- 实现油站维度的构建