离线数仓——（DIM公共维度层建表及加载数据）

DIM公共维度层建表及加载数据

1. 订单表和订单产品表属于事实表，支付方式表，产品信息表，商家店铺表，产品分类表，商家地域组织表属于维表
   
2. 用什么方式处理维表，每日快照、拉链表？
   1. 小表使用每日快照：产品分类表【 dim_trade_product_cat】、商家店铺表、商家地域组织表、支付方式表
   2. 大表使用拉链表：产品信息表

DIM公共维度层第一张表：产品分类表【dim_trade_product_cat】

create table if not exists dim.dim_trade_product_cat(
	firstId int, -- 一级商品分类id
	firstName string, -- 一级商品分类名称
	secondId int, -- 二级商品分类Id
	secondName string, -- 二级商品分类名称
	thirdId int, -- 三级商品分类id
	thirdName string -- 三级商品分类名称
)
partitioned by (dt string)
STORED AS PARQUET;

DIM公共维度层第一张表：产品分类表【dim_trade_product_cat】加载数据，

#！/bin/bash

source /etc/profile
if [ -n "$1" ]
then
	do_date=$1
else
	do_date=`date -d "-1 day" +%F`
fi

sql="
insert overwrite table dim.dim_trade_product_cat
partition(dt='$do_date')
select
	t1.catid, -- 一级分类id
	t1.catname, -- 一级分类名称
	t2.catid, -- 二级分类id
	t2.catname, -- 二级分类名称
	t3.catid, -- 三级分类id
	t3.catname -- 三级分类名称
from
	-- 商品三级分类数据
	(select catid, catname, parentid
		from ods.ods_trade_product_category
	  where level=3 and dt='$do_date') t3
	
	left join
	-- 商品二级分类数据
	(select catid, catname, parentid
	from ods.ods_trade_product_category
		where level=2 and dt='$do_date') t2
	 on t3.parentid = t2.catid
	
	left join
	-- 商品一级分类数据
	(select catid, catname, parentid
		from ods.ods_trade_product_category
	 where level=1 and dt='$do_date') t1
	on t2.parentid = t1.catid;
"

hive -e "$sql"

ods_trade_product_category表中数据如下：

hive (ods)> select * from ods_trade_product_category;
OK
ods_trade_product_category.catid	ods_trade_product_category.parentid	ods_trade_product_category.catname	ods_trade_product_category.isshow	ods_trade_product_category.sortnum	ods_trade_product_category.isdel	ods_trade_product_category.createtime	ods_trade_product_category.level	ods_trade_product_category.dt
31	0	钟表	1	-5	1	2018-06-28 13:53:41	1	2020-07-01
32	0	电脑、办公	1	-5	1	2018-06-28 13:53:41	1	2020-07-01
33	0	运动户外	1	-5	1	2018-06-28 13:53:41	1	2020-07-01
34	0	礼品	1	-5	1	2018-06-28 13:53:41	1	2020-07-01
35	0	鞋靴	1	-5	1	2018-06-28 13:53:41	1	2020-07-01
36	0	家装建材	1	-5	1	2018-06-28 13:53:41	1	2020-07-01
37	0	服饰内衣	1	-5	1	2018-06-28 13:53:41	1	2020-07-01
38	0	箱包皮具	1	-5	1	2018-06-28 13:53:41	1	2020-07-01
39	0	汽车用品	1	-5	1	2018-06-28 13:53:41	1	2020-07-01
40	0	宠物生活	1	-5	1	2018-06-28 13:53:41	1	2020-07-01
41	0	农资园艺	1	-5	1	2018-06-28 13:53:41	1	2020-07-01
42	0	珠宝首饰	1	-5	1	2018-06-28 13:53:41	1	2020-07-01
43	0	二手商品	1	-5	1	2018-06-28 13:53:41	1	2020-07-01

DIM公共维度层第二张表：商品地域组织表【dim_trade_shops_org】

1. 商品地域组织表【dim_trade_shops_org】包含商家店铺表【ods_trade_shops】、商家地域组织表【ods_trade_shop_admin_org】
2. 在商品地域组织表中【dim_trade_shops_org】的一行数据中体现：商家信息、城市信息、地域信息。信息中包括 id 和 name ；

create table dim.dim_trade_shops_org(
	shopid int,
	shopName string,
	cityId int,
	cityName string ,
	regionId int ,
	regionName string
)
partitioned by (dt string)
STORED AS PARQUET;

DIM公共维度层第二张表：商品地域组织表【dim_trade_shops_org】 加载数据

#！/bin/bash

source /etc/profile

if [ -n "$1" ]
then
	do_date=$1
else
	do_date=`date -d "-1 day" +%F`
fi

sql="
insert overwrite table dim.dim_trade_shops_org
partition(dt='$do_date')
select t1.shopid,
       t1.shopname,
       t2.id as cityid,
       t2.orgname as cityName,
       t3.id as region_id,
       t3.orgname as region_name
  from (select shopId, shopName, areaId
		from ods.ods_trade_shops
		where dt='$do_date') t1

		left join
		(select id, parentId, orgname, orglevel
		     from ods.ods_trade_shop_admin_org
		 where orglevel=2 and dt='$do_date') t2
		on t1.areaid = t2.id

	left join
		(select id, parentId, orgname, orglevel
		  	from ods.ods_trade_shop_admin_org
		   where orglevel=1 and dt='$do_date') t3
		on t2.parentid = t3.id;
"
hive -e "$sql"

DIM公共维度层第三张表：支付方式表【dim_trade_payment】,dim_trade_payment只保留了ods中的表必要的信息。

create table if not exists dim.dim_trade_payment(
	paymentId string, -- 支付方式id
	paymentName string -- 支付方式名称
)
partitioned by (dt string)
STORED AS PARQUET;

DIM公共维度层第三张表：支付方式表【dim_trade_payment】数据加载

#！/bin/bash

source /etc/profile

if [ -n "$1" ]
then
	do_date=$1
else
	do_date=`date -d "-1 day" +%F`
fi

sql="
insert overwrite table dim.dim_trade_payment
partition(dt='$do_date')
select id, payName
	from ods.ods_trade_payments
 where dt='$do_date';
"
hive -e "$sql"

DIM公共维度层第四张表：产品信息表【dim_trade_product_info】,产品信息表是使用拉链表对产品信息进行处理。

1. 产品信息表【dim_trade_product_info】历史数据 => 初始化拉链表(开始日期：当日；结束日期：9999-12-31)【只执行一次】
2. 拉链表的每日处理【每次加载数据时处理】
   1. 新增数据。每日新增数据(ODS) => 开始日期：当日；结束日期：9999-12-31
   2. 历史数据。拉链表(DIM) 与 每日新增数据(ODS) 做左连接
      1. 连接上数据。数据有变化，结束日期：当日；
      2. 未连接上数据。数据无变化，结束日期保持不变；

第一步：创建产品信息表【dim_trade_product_info】：拉链表要增加两列，分别记录生效日期和失效日期

create table dim.dim_trade_product_info(
	`productId` bigint,
	`productName` string,
	`shopId` string,
	`price` decimal,
	`isSale` tinyint,
	`status` tinyint,
	`categoryId` string,
	`createTime` string,
	`modifyTime` string,
	`start_dt` string,
	`end_dt` string
) COMMENT '产品表'
STORED AS PARQUET;

第二步：初始数据加载（历史数据加载，只做一次）

insert overwrite table dim.dim_trade_product_info
select productId,
	productName,
	shopId,
	price,
	isSale,
	status,
	categoryId,
	createTime,
	modifyTime,
	-- modifyTime非空取modifyTime，否则取createTime；substr取日期
	case when modifyTime is not null
		then substr(modifyTime, 0, 10)
		else substr(createTime, 0, 10)
	end as start_dt,
    '9999-12-31' as end_dt
 from ods.ods_trade_product_info
where dt = '2020-07-12';

第三步：增量数据导入（重复执行，每次加载数据执行）

#！/bin/bash

source /etc/profile

if [ -n "$1" ]
then
	do_date=$1
else
	do_date=`date -d "-1 day" +%F`
fi

sql="
insert overwrite table dim.dim_trade_product_info
 select productId,
		productName,
		shopId,
		price,
		isSale,
		status,
		categoryId,
		createTime,
		modifyTime,
		case when modifyTime is not null
			then substr(modifyTime,0,10)
			else substr(createTime,0,10)
		 end as start_dt,
		 '9999-12-31' as end_dt
	from ods.ods_trade_product_info
 where dt='$do_date'

union all

 select dim.productId,
		dim.productName,
		dim.shopId,
		dim.price,
		dim.isSale,
		dim.status,
		dim.categoryId,
		dim.createTime,
		dim.modifyTime,
		dim.start_dt,
		case when dim.end_dt >= '9999-12-31' and ods.productId is not null
			then '$do_date'
			else dim.end_dt
		end as end_dt
	from dim.dim_trade_product_info dim left join
		(select *
			from ods.ods_trade_product_info
		  where dt='$do_date' ) ods
		on dim.productId = ods.productId
"
hive -e "$sql"