数仓建模 – 电商业务 – 商品主题域建设 – 维度表建设

业务背景

1.目前商品的相关数据都分散在多个系统里面，业务侧从商品维度进行分析时，需要从多处获取数据，而且信息统一和整合，不同的业务方获取的数据存在差异。

2.下游业务需要从商品的各个维度去统计相关的指标时，无法获取完整、准确的数据，导致数据存在多套不同的统计结果。

数据架构

ODS表

表 1：spu 信息表
DROP TABLE IF EXISTS ods_product_spu;
CREATE TABLE IF NOT EXISTS ods_product_spu (
id BIGINT COMMENT '商品 id',
name STRING COMMENT '商品名称',
category_id BIGINT COMMENT '所属分类 id',
brand_id BIGINT COMMENT '品牌 id',
publish_status INT COMMENT '上架状态[0 - 下架，1 - 上架]',
create_time STRING COMMENT '创建时间',
update_time STRING COMMENT '更新时间'
) COMMENT 'spu 信息' STORED AS orc;

表 2：spu 属性表
DROP TABLE IF EXISTS ods_product_spu_attr_value;
CREATE TABLE IF NOT EXISTS ods_product_spu_attr_value (
id BIGINT COMMENT 'id',
spu_id BIGINT COMMENT '商品 id',
attr_id BIGINT COMMENT '属性 id',
attr_name STRING COMMENT '属性名',
attr_value STRING COMMENT '属性值',
sort INT COMMENT '顺序'
) COMMENT 'spu 属性值' STORED AS orc;

表 3：sku 信息表
DROP TABLE IF EXISTS ods_product_sku;
CREATE TABLE IF NOT EXISTS ods_product_sku (
id BIGINT COMMENT 'skuId',
spu_id BIGINT COMMENT 'spuId',
name STRING COMMENT 'sku 名称',
catagory_id BIGINT COMMENT '所属分类 id',
brand_id BIGINT COMMENT '品牌 id',
default_image STRING COMMENT '默认图片',
title STRING COMMENT '标题',
subtitle STRING COMMENT '副标题',
price DOUBLE COMMENT '价格',
weight INT COMMENT '重量（克）'
) COMMENT 'sku 信息' STORED AS orc;

表 4：sku 销售属性表
DROP TABLE IF EXISTS ods_product_sku_attr_value;
CREATE TABLE IF NOT EXISTS ods_product_sku_attr_value (
id BIGINT COMMENT 'id',
sku_id BIGINT COMMENT 'sku_id',
attr_id BIGINT COMMENT 'attr_id',
attr_name STRING COMMENT '销售属性名',
attr_value STRING COMMENT '销售属性值',
sort INT COMMENT '顺序'
) COMMENT 'sku 销售属性&值' STORED AS orc;

表 5：品牌信息表
DROP TABLE IF EXISTS ods_product_brand;
CREATE TABLE IF NOT EXISTS ods_product_brand (
id BIGINT COMMENT '品牌 id',
name STRING COMMENT '品牌名',
logo STRING COMMENT '品牌 logo',
status INT COMMENT '显示状态[0-不显示；1-显示]',
first_letter STRING COMMENT '检索首字母',
sort INT COMMENT '排序',
remark string COMMENT '备注'
) COMMENT '品牌' STORED AS orc;

表 6：商品分类表
DROP TABLE IF EXISTS ods_product_category;
CREATE TABLE IF NOT EXISTS ods_product_category (
id BIGINT COMMENT '分类 id',
name STRING COMMENT '分类名称',
parent_id BIGINT COMMENT '父分类 id',
status INT COMMENT '是否显示[0-不显示，1 显示]',
sort INT COMMENT '排序',
icon STRING COMMENT '图标地址',
unit STRING COMMENT '计量单位'
) COMMENT '商品三级分类' STORED AS orc;

表 7：行政区划表
DROP TABLE IF EXISTS ods_regioninfo;
CREATE TABLE IF NOT EXISTS ods_regioninfo (
regionid STRING COMMENT '地区 ID',
parentid STRING COMMENT '父级区域 ID',
regionname STRING COMMENT '地区名称',
regiontype INT COMMENT '区域类别（0 国家/1 省份/2 城市/3 区县）',
agencyid INT COMMENT '无用字段',
pt STRING COMMENT '系统更新时间'
) COMMENT '行政区划表' STORED AS orc;

dim 表

表 1：sku 商品维度表
-- 1.sku 商品维度表
DROP TABLE IF EXISTS dim_commerce.dim_sku_detailed_info_full;
CREATE TABLE IF NOT EXISTS `dim_commerce.dim_sku_detailed_info_full`(
`sku_id` bigint COMMENT '商品 id',
`sku_name` string COMMENT '商品名称',
`catagory_id` bigint COMMENT '所属三级分类 id',
`catagory_name` string COMMENT '所属三级分类名字',
`brand_id` bigint COMMENT '品牌 id',
`brand_name` string COMMENT '品牌名称',
`sku_default_image` string COMMENT '默认图片',
`sku_title` string COMMENT '标题',
`sku_subtitle` string COMMENT '副标题',
`sku_price` double COMMENT '价格',
`spu_id` bigint COMMENT 'spu 商品 id',
`spu_name` string COMMENT 'spu 商品名称',
`sku_attrs` array<struct<attr_id:bigint,attr_name:string,attr_value:string>> COMMENT 'sku 平台属性'
) COMMENT 'sku 商品维度表'
STORED AS orc;

表 2：spu 商品维度表
-- 2.spu 商品维度表
DROP TABLE IF EXISTS dim_commerce.dim_spu_detailed_info_full;
CREATE TABLE IF NOT EXISTS `dim_commerce.dim_spu_detailed_info_full`(
`spu_id` bigint COMMENT 'spu 商品 id',
`spu_name` string COMMENT 'spu 商品名称',
`spu_publish_status` int COMMENT '上架状态',
`spu_create_time` string COMMENT '创建时间',
`spu_update_time` string COMMENT '更新时间',
`category_id` bigint COMMENT '所属三级分类 id',
`category_name` string COMMENT '所属三级分类名字',
`brand_id` bigint COMMENT '品牌 id',
`brand_name` string COMMENT '品牌名称',
`spu_attrs` array<struct<col1:bigint,col2:string,col3:string>>COMMENT 'spu 平台属性'
) COMMENT 'spu 商品维度表'
STORED AS orc;

表 3：品牌维度表
-- 3.品牌维度表
DROP TABLE IF EXISTS dim_commerce.dim_brand_detailed_info_full;
CREATE TABLE IF NOT EXISTS `dim_commerce.dim_brand_detailed_info_full`(
`id` bigint COMMENT '品牌 id',
`name` string COMMENT '品牌名称',
`logo` string COMMENT '品牌 logo',
`update_time` string COMMENT '更新时间'
) COMMENT '品牌维度表'
STORED AS orc;

表 4：商品分类维度表
-- 4.商品分类维度表
DROP TABLE IF EXISTS dim_commerce.dim_category_detailed_info_full;
CREATE TABLE IF NOT EXISTS `dim_commerce.dim_category_detailed_info_full`(
`category_3_id` bigint COMMENT '三级分类 id',
`category_3_name` string COMMENT '三级分类名称',
`category_2_id` bigint COMMENT '二级分类 id',
`category_2_name` string COMMENT '二级分类名称',
`category_1_id` bigint COMMENT '一级分类 id',
`category_1_name` string COMMENT '一级分类名称'
) COMMENT '商品分类维度表'
STORED AS orc;

表 5：goods 维度表
-- 5.goods 维度表
DROP TABLE IF EXISTS dim_commerce.dim_goods_detailed_info_full;
CREATE TABLE IF NOT EXISTS `dim_commerce.dim_goods_detailed_info_full`(
`goodsid` string COMMENT '商品 ID',
`brand_id` string COMMENT '品类 ID',
`markid` string COMMENT '专场 ID（商品售卖的位置）',
`goodstag` string COMMENT '进货渠道，档口的名字',
`brand_name` string COMMENT '品牌名称（不用，脱敏严重）',
`customtag` string COMMENT '商品的详情',
`goodsname` string COMMENT '竞价排名，BH 为公司的补货',
`clickcount` int COMMENT '商品的点击次数',
`clickcr` int COMMENT '-',
`goodsnumber` int COMMENT '货号',
`goodsweight` int COMMENT '商品重量',
`marketprice` double COMMENT '进货价，成本',
`shopprice` double COMMENT '售价',
`addtime` string COMMENT '新款建档时间，在数据库里',
`isonsale` int COMMENT '是否在售（1 在售，0 否）',
`sales` int COMMENT '真实的销量+刷单的销量',
`realsales` int COMMENT '实际销量',
`extraprice` double COMMENT '特别价格（促销价）',
`goodsno` string COMMENT '货号 ID，一个商品 ID 可能对应多个货号 ID',
`update_time` string COMMENT '更新时间',
`category_3_id` int COMMENT '三级分类 id',
`category_3_name` string COMMENT '三级分类名称',
`category_2_id` int COMMENT '二级分类 id',
`category_2_name` string COMMENT '二级分类名称',
`category_1_id` int COMMENT '一级分类 id',
`category_1_name` string COMMENT '一级分类名称'
) COMMENT 'goods 维度表'
STORED AS orc;

表 6：地区维度表
-- 6.地区维度表
DROP TABLE IF EXISTS dim_commerce.dim_region_info_full;
CREATE TABLE IF NOT EXISTS `dim_commerce.dim_region_info_full`(
`county_id` string COMMENT '区县 id',
`county_name` string COMMENT '区县名称' ,
`city_id` string COMMENT '城市 id' ,
`city_name` string COMMENT '城市名称' ,
`province_id` string COMMENT '省份 id' ,
`province_name` string COMMENT '省份名称' ,
`country_id` string COMMENT '国家 id',
`country_name` string COMMENT '国家名称',
`update_time` string COMMENT '更新时间'
)COMMENT '地区维度表'
STORED AS orc;

表级别的整合主要有两种形式：

1.垂直整合，即不同来源表包含相同的数据集，只是存储的信息不同，可以整合到同一个维度模型中。

2.水平整合，即不同来源表包含不同的数据集，这些子集之间无交叉或存在部分交叉，如果有交叉则去重；如果无交叉，考虑不同子集的自然键是否冲突，不冲突则可以将各子集自然键作为整合后的自然键，或者将各自然键加工成一个超自然键。

维度设计基本方法

1.选择或者新建一个维度，通过之前总线矩阵的构建掌握了目前数仓架构中的维度。

2.确定主维表。此处主维表一般是 ODS 表，直接与业务系统同步。

3.确定相关维表。数仓是业务源系统的数据整合，不同业务系统或者同一业务系统中的表之间存在关联性。跟据对业务的梳理，我们可以确认哪些表和主维表存在关联关系，并选择其中的某些表用于生成维度属性。

4.确定维度属性。本步骤分为两阶段，第一阶段是从主维表中选择维度属性或生成新的维度属性；第二阶段是从相关维表中选择维度属性或生成新的维度属性。