3.离线数仓—DIM层设计开发

前言

前面完成了ODS层的设计开发，下面进行DIM层的设计和开发。

一、DIM层设计分析

1.设计要点

1.1 设计依据

DIM层的设计依据是维度建模理论，该层存储维度模型的维度表。

1.2 压缩类型

DIM层的数据存储格式为orc列式存储，优点是方便取数据。例如要取某个表的某几列，如果采用行式存储，要隔几个数据才取一个数，若采用列式存储，一列的数据都放在一块，可以很快的取出一列的数据。
DIM层的压缩类型选用snappy压缩，解压和压缩的速率都很快。

1.3 命名规范

DIM层表名的命名规范是dim_表名_全量表或者拉链表标识（full/zip）

2.业务总线矩阵

根据业务总线矩阵可以得知，总共有时间、用户、商品、地区等等共计11个维度。
注意的点：
1）其中某些维度与多个业务过程都有联系，例如时间维度，像这种与多种业务过程都有联系的维度只需要设计一个表即可。
2）某些维度只有一两个字段，这就没有必要单独建一个表，可以进行维度退化，将对应的字段添加到事实表中
3）上图中的支付方式、退单类型、退单原因类型、渠道、设备中的字段都很少，进行了维度退化，不单独为这些维度建表

二、DIM层设计实现

需要设计的维度表总共有6张，这里以商品维度表为例，具体说明建表过程。

1.商品维度表

1.1主维度和其他维度

在建商品维度表前，要先确定业务数据库中与商品相关的表有哪些，如下图：

我们在找到了所有跟商品有关的表之后，要确定一个主维表，让主维表作为主体，将其他维表的属性添加进去。这是商品维度，毫无疑问，SKU商品信息表是主维表，其他的表是其他维表。
但是不一定所有与商品有关的表都要用到，例如下图：

SKU平台属性表（是商品和平台属性的关系表）中包含了一些冗余字段，只需要SKU平台属性表这一个表即可满足要求，剩下两个表无需用到。同理右上角的部分也只需要一个SKU销售属性表即可。最终，商品维度表需要的表如下（黑色框部分）：

1.2 确定维度表字段

确定字段需要依次从需要的业务数据库表中拿字段。
首先，从SKU信息表拿需要的字段：

	select
        id,
        price,
        sku_name,
        sku_desc,
        weight,
        is_sale,
        spu_id,
        category3_id,
        tm_id,
        create_time
    from ods_sku_info_full
    where dt='2020-06-14'

从SPU信息表拿需要的字段：

	select
        id,
        spu_name
    from ods_spu_info_full
    where dt='2020-06-14'

从三级分类表、二级分类表、一级分类表拿需要的字段：

    select
        id,
        name,
        category2_id
    from ods_base_category3_full
    where dt='2020-06-14'

    select
        id,
        name,
        category1_id
    from ods_base_category2_full
    where dt='2020-06-14'

    select
        id,
        name
    from ods_base_category1_full
    where dt='2020-06-14'

从品牌表拿需要的字段：

	select
        id,
        tm_name
    from ods_base_trademark_full
    where dt='2020-06-14'

从SKU销售属性表拿需要的数据：

从SKU平台属性表拿需要的数据：

上面所有表拿到的数据就组成了商品维度表的字段。

需要注意的是：平台属性和销售属性都是多值属性

按上图，方式二没办法使用，因为多值属性个数不固定，只能采用方式一。
方法一：首先STRUCT没办法满足需求，因为STRUCT也是需要确定个数的；
方法二：采用MAP，但是有一个问题是MAP的每个key的类型都是固定的，每个value的类型也是固定的
方法三：采用ARRAY[STRUCT<>]的方式，可以很好的解决问题，一个结构体对应一个属性，用这种方式还有另外一个好处就是能够存储更多的数据，除了可以存储name，还可以额外存储id

1.3 建表语句

根据上面确定的字段，确定对应的字段类型，写建表语句如下：

DROP TABLE IF EXISTS dim_sku_full;
CREATE EXTERNAL TABLE dim_sku_full
(
    `id`                   STRING COMMENT 'sku_id',
    `price`                DECIMAL(16, 2) COMMENT '商品价格',
    `sku_name`             STRING COMMENT '商品名称',
    `sku_desc`             STRING COMMENT '商品描述',
    `weight`               DECIMAL(16, 2) COMMENT '重量',
    `is_sale`              BOOLEAN COMMENT '是否在售',
    `spu_id`               STRING COMMENT 'spu编号',
    `spu_name`             STRING COMMENT 'spu名称',
    `category3_id`         STRING COMMENT '三级分类id',
    `category3_name`       STRING COMMENT '三级分类名称',
    `category2_id`         STRING COMMENT '二级分类id',
    `category2_name`       STRING COMMENT '二级分类名称',
    `category1_id`         STRING COMMENT '一级分类id',
    `category1_name`       STRING COMMENT '一级分类名称',
    `tm_id`                STRING COMMENT '品牌id',
    `tm_name`              STRING COMMENT '品牌名称',
    `sku_attr_values`      ARRAY<STRUCT<attr_id :STRING,value_id :STRING,attr_name :STRING,value_name:STRING>> COMMENT '平台属性',
    `sku_sale_attr_values` ARRAY<STRUCT<sale_attr_id :STRING,sale_attr_value_id :STRING,sale_attr_name :STRING,sale_attr_value_name:STRING>> COMMENT '销售属性',
    `create_time`          STRING COMMENT '创建时间'
) COMMENT '商品维度表'
    PARTITIONED BY