大数据-248 离线数仓 - 维表：商品分类表、地域组织表、商品信息表

基本介绍

首先要确定哪些是事实表、维表：

  • 绿色为事实表
  • 灰色为维表

用什么方式处理维表，每日快照、拉链表？

  • 小表使用每日快照表：产品分类表、商家店铺表、商家地域组织表、支付方式表
  • 大表使用拉链表：产品信息表

商品分类表

范式与反范式

数据库范式是设计关系型数据库结构时的一套指导原则，目的是为了减少数据冗余、确保数据依赖性合理，并提高数据一致性。然而，遵循范式也有一些潜在的缺点：

  • 性能问题：高度规范化的数据库可能会导致查询和连接操作变慢，因为需要在多个表之间进行复杂的连接来获取完整的信息。
  • 复杂性增加：随着范式的深入应用，数据库模式变得更加复杂，维护起来更加困难。
  • 过度设计：有时过于追求范式会导致对简单场景的过度工程化。
  • 读取效率低下：在某些情况下，为了保证写入时的数据完整性，范式可能导致频繁的读取操作变得低效。

解决方案：

  • 选择适当的范式级别
  • 反范式化（Denormalization）
  • 使用缓存机制
  • 分区与分片
  • 索引优化
  • 评估业务需求

创建表

DROP TABLE IF EXISTS 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;

数据加载脚本

vim dim_load_product_cat.sh
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"

商品地域组织表

创建表

商家店铺表、商家地域组织表 => 一张维表。这里也是逆规范化的设计、将商家店铺表、商家地域组织表组织成一张表，并拉宽。

drop table if exists dim.dim_trade_shops_org;
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;

数据加载脚本

vim dim_load_shop_org.sh
#！/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"

商品信息表

数据处理

使用拉链表对商品信息进行处理。

历史数据

历史数据 => 初始化拉链表（开始日期：当日，结束日期：9999-12-31）只执行一次。

每日数据

  • 新增数据：每日新增数据（ODS） => 开始日期：当日，结束日期：9999-12-31
  • 历史数据：拉链表（DIM）与每日新增数据（ODS）做左连接（连接上有数据，数据有变化，结束日期变为当日。为连接上数据，数据无变化，结束日期保持不变）

创建维表

拉链表要增加两列，分别记录生效日期和失效日期：

drop table if exists dim.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';

增量数据导入脚本

重复执行，每次加载数据执行：

vim dim_load_product_info.sh
#！/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"