9.数据仓库搭建之DIM层搭建

最新推荐文章于 2025-01-08 10:24:07 发布
最新推荐文章于 2025-01-08 10:24:07 发布
阅读量2.2k 收藏 10
点赞数 2
分类专栏： 东软睿购跨境电商数仓项目 文章标签： 数据仓库 hive
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/Mmj666/article/details/125785942
版权
本文详细介绍了数据仓库中DIM层的搭建过程，包括商品、用户、地区、优惠券、活动和日期等六个维度的确定、维度表设计、数据装载逻辑，以及首日和每日装载脚本的编写。DIM层采用orc列式存储+snappy压缩，遵循特定命名规范。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

数据仓库搭建之DIM层搭建

在开发数据仓库的DIM层时，我们需要注意以下几点：

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

2）在我们该项目当中，DIM层的数据存储格式为orc列式存储+snappy压缩。

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

1.维度确定

我们根据之前构建的业务总线矩阵，来确定我们当前需要构建的维度表。


我们可以看到，我们所有的业务过程所涉及到的维度有时间、用户、商品、地区、活动、优惠券、支付方式、退单类型、退单原因类型、渠道以及设备。

虽然有这么多的维度，但是我们并不会将这些维度都构建成维度表。但是我们考虑到维度退化，一些维度中字段比较少，较为简单，因此我们将该维度中的字段退化到与之对应的事实表当中。

因此，我们最终选择的维度有时间、用户、商品、地区、活动和优惠券共六个维度。

2.维度表设计

2.1商品维度表

2.1.1确定维度

这里的维度已经确定，是商品维度。

2.1.2确定主维表和相关维表

此处的主维表和相关维表均指电商业务系统中与某维度相关的表。

在这里插入图片描述

由于我们表中的字段有部分冗余（为了提高查询的速度），因此我们最终的主维表和相关维表如下图所示（图中有颜色的是最终的主维表和相关维表）：
在这里插入图片描述

2.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 (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dim/dim_sku_full/'
    TBLPROPERTIES ('orc.compress' = 'snappy');
2.1.4数据的装载逻辑（以2022-05-01为例）

我们需要从每一个表当中取出需要的数据，之后再通过join连接起来。

（1）sku_info表

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='2022-05-01'

（2）spu_info表

select
	id,
	spu_name
from ods_spu_info_full
where dt='2022-05-01'

（3）base_caregory3表

select
	id,
	name,
	category2_id
from ods_base_category3_full
where dt='2022-05-01'

（3）base_caregory2表

select
	id,
	name,	
	category1_id
from ods_base_category2_full
where dt='2022-05-01'

（4）base_caregory1表

select
	id,
	name
from ods_base_category1_full
where dt='2022-05-01'

（5）base_trademark表

select
	id,
	tm_name
from ods_base_trademark_full
where dt='2022-05-01'

（6）sku_attr_value表

select
	sku_id,  		collect_set(named_struct('attr_id',attr_id,'value_id',value_id,'attr_name',attr_name,'value_name',value_name)) attrs
from ods_sku_attr_value_full
where dt='2022-05-01'
group by sku_id

（7）sku_sale_attr_value表

select
	sku_id,     collect_set(named_struct('sale_attr_id',sale_attr_id,'sale_attr_value_id',sale_attr_value_id,'sale_attr_name',sale_attr_name,'sale_attr_value_name',sale_attr_value_name)) sale_attrs
from ods_sku_sale_attr_value_full
where dt='2022-05-01'
group by sku_id

最终，我们将上述从表中取到的数据进行join，然后装载到该商品维度表当中：

with
sku as
(
    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='2022-05-01'
),
spu as
(
    select
        id,
        spu_name
    from ods_spu_info_full
    where dt='2022-05-01'
),
c3 as
(
    select
        id,
        name,
        category2_id
    from ods_base_category3_full
    where dt='2022-05-01'
),
c2 as
(
    select
        id,
        name,
        category1_id
    from ods_base_category2_full
    where dt='2022-05-01'
),
c1 as
(
    select
        id,
        name
    from ods_base_category1_full
    where dt='2022-05-01'
),
tm as
(
    select
        id,
        tm_name
    from ods_base_trademark_full
    where dt='2022-05-01'
),
attr as
(
    select
        sku_id,
        collect_set(named_struct('attr_id',attr_id,'value_id',value_id,'attr_name',attr_name,'value_name',value_name)) attrs
    from ods_sku_attr_value_full
    where dt='2022-05-01'
    group by sku_id
),
sale_attr as
(
    select
        sku_id,
        collect_set(named_struct('sale_attr_id',sale_attr_id,'sale_attr_value_id',sale_attr_value_id,'sale_attr_name',sale_attr_name,'sale_attr_value_name',sale_attr_value_name)) sale_attrs
    from ods_sku_sale_attr_value_full
    where dt='2022-05-01'
    group by sku_id
)
insert overwrite table dim_sku_full partition(dt='2022-05-01')
select
    sku.id,
    sku.price,
    sku.sku_name,
    sku.sku_desc,
    sku.weight,
    sku.is_sale,
    sku.spu_id,
    spu.spu_name,
    sku.category3_id,
    c3.name,
    c3.category2_id,
    c2.name,
    c2.category1_id,
    c1.name,
    sku.tm_id,
    tm.tm_name,
    attr.attrs,
    sale_attr.sale_attrs,
    sku.create_time
from sku
left join spu on sku.spu_id=spu.id
left join c3 on sku.category3_id=c3.id
left join c2 on c3.category2_id=c2.id
left join c1 on c2.category1_id=c1.id
left join tm on sku.tm_id=tm.id
left join attr on sku.id=attr.sku_id
left join sale_attr on sku.id=sale_attr.sku_id;

2.2用户维度表

2.2.1确定维度

这里的维度已经确定，是用户维度。

2.2.2确定主维表和相关维表

此处的主维表和相关维表均指电商业务系统中与某维度相关的表。

在这里插入图片描述

我们最终只选择user_info一张表作为用户维度的主维表，因为我们对用户的地址表不经常使用，因此此处不添加用户地址表进行join。

2.2.3确定维度属性
DROP TABLE IF EXISTS dim_user_zip;
CREATE EXTERNAL TABLE dim_user_zip
(
    `id`           STRING COMMENT '用户id',
    `login_name`   STRING COMMENT '用户名称',
    `nick_name`    STRING COMMENT '用户昵称',
    `name`         STRING COMMENT '用户姓名',
    `phone_num`    STRING COMMENT '手机号码',
    `email`        STRING COMMENT '邮箱',
    `user_level`   STRING COMMENT '用户等级',
    `birthday`     STRING COMMENT '生日',
    `gender`       STRING COMMENT '性别',
    `create_time`  STRING COMMENT '创建时间',
    `operate_time` STRING COMMENT '操作时间',
    `start_date`   STRING COMMENT '开始日期',
    `end_date`     STRING COMMENT '结束日期'
) COMMENT '用户表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dim/dim_user_zip/'
    TBLPROPERTIES ('orc.compress' = 'snappy');
2.2.4数据的分区规划

拉链表的意义就在于能够更加高效的保存维度信息的历史状态。拉链表适合于数据会发生变化，但是变化频率并不高的维度（缓慢变化维）。因此，我们的用户维度表设计为拉链表，因为每天变化的比例并不高。

我们的数据分区规划如下所示：

在这里插入图片描述

我们将全量最新的用户数据存储到dt=9999-12-31分区当中。

2.2.4数据装载
2.2.4.1拉链表数据装载过程

在这里插入图片描述

2.2.4.3拉链表数据流向

在这里插入图片描述

2.2.4.4用户维表首日装载（以2022-05-01作为首日）

用户的首日装载较为简单，即装载全量的数据：

insert overwrite table dim_user_zip partition (dt='9999-12-31')
select
    data.id,
    data.login_name,
    data.nick_name,
    md5(data.name),
    md5(data.phone_num),
    md5(data.email),
    data.user_level,
    data.birthday,
    data.gender,
    data.create_time,
    data.operate_time,
    '2022-05-01' start_date,
    '9999-12-31' end_date
from ods_user_info_inc
where dt='2022-05-01'
and type='bootstrap-insert';
2.2.4.5用户维表每日装载

（1）用户维度表每日装载思路

在这里插入图片描述

（2）具体装载语句

with
tmp as
(
    select
        old.id old_id,
        old.login_name old_login_name,
        old.nick_name old_nick_name,
        old.name old_name,
        old.phone_num old_phone_num,
        old.email old_email,
        old.user_level old_user_level,
        old.birthday old_birthday,
        old.gender old_gender,
        old.create_time old_create_time,
        old.operate_time old_operate_time,
        old.start_date old_start_date,
        old.end_date old_end_date,
最低0.47元/天 解锁文章
数据仓库维度建模实践-体系搭建-网易01.pdf
10-30
目前应用广泛的是如下三层数据模型：操作数据层（ODS）、公共模型层（DWD和DWS）、应用数据层（ADS）。 在公共模型层中，DWD层存储的是明细数据，面向业务过程建模。DWS层是公共汇总数据层，面向分析主题建模，为...
12.数据仓库搭建之ADS层搭建
Mmj666的博客
07-14 3375
12.数据仓库搭建之ADS层搭建
数仓开发之DIM层
JiaXingNashishua的博客
11-09 5034
（1）定义类。
数据仓库分层解析(详细)
最新发布
super_mochi1
01-08 6807
数据仓库分层是一种组织和管理数据仓库的结构化方法，它将数据仓库划分为不同的层次或级别，每个层次具有特定的功能和目的。这种分层方法有助于管理数据仓库中的数据流程、数据处理和数据访问，并提供一种清晰的结构来支持数据管理和分析需求
离线数仓（七）【DIM 层开发】
功不唐捐，玉汝于成
03-13 1879
DIM 层开发
8. 数仓开发之 DIM 层
记录点点滴滴
03-17 6221
数仓开发之 DIM 层商品维度表 DIM 层设计要点： DIM 层的设计依据 : 维度建模理论，该层存储维度模型的维度表 DIM 层的数据存储格式 : orc 列式存储 + snappy 压缩 DIM 层表名的命名规范为 dim_表名_全量表或拉链表标识（ full / zip ） 商品维度表 ...
数据仓库中的DIM层-定义、设计与最佳实践
一个8年大数据开发工程师的碎碎念
07-29 1319
DIM层,全称Dimension层,是数据仓库中用于存储维度数据的层次。维度数据描述了业务实体的属性,如客户、产品、时间等。DIM层为事实数据提供上下文,使分析更加丰富和有意义。
数据仓库之DIM层
jianghuaijie的博客
01-30 7762
一、属性与度量 在介绍维表之前，我们首先要明白一点属性与度量的区别。属性是指是对象的性质或特性，它因对象而异，或随时间而变化，比如姓名和年龄。姓名因人而异，年龄会不断变化。而度量是对属性的标量刻画。并且度量具有统计意义，而属性并不具有统计意义，比如25岁的我在超市买了1瓶300ML农夫山泉，花了2元。这里的1瓶和2块就是对我买水的这个业务过程的度量。超市是（地理维度）属性，25岁是描述实体的属性，300ML是描述农夫山泉的属性。所以我们可以通过2个点来区分属性和度量，1.是否具有统计意义，2.是否会随..
基于阿里云MaxCompute搭建数据仓库（离线）培训视频.zip
09-19
03_数据仓库基本概念 04_数据仓库需求分析和框架选择 05_数据仓库流程设计和数仓分层 06_dataWorks和MaxCompute简单介绍 07_dataWorks购买及创建工作空间 08_数据仓库原始业务表格介绍 09_业务表格同步策略介绍 10_...
基于阿里云搭建实时数据仓库项目项目需求及架构设计.pdf
01-06
"基于阿里云搭建实时数据仓库项目需求及架构设计" 本文档主要讲述基于阿里云搭建实时数据仓库项目的需求和架构设计。该项目的目的是学习搭建一个实时数据仓库，掌握数据采集、存储、计算、输出、展示等整个业务流程...
数据仓库维度建模实践-模型设计-网易03.pdf
10-30
数据仓库维度建模实践模型设计 在数据仓库维度建模实践中，模型设计是非常重要的一步。它强调从业务、数据存取和使用角度合理存储数据，以满足企业数仓的要求：强稳定、高质量、高效率、低成本。 一、数据仓库维度...
提高数据仓库的ETL效率
07-01
为了更好地利用这些数据进行深入分析，搭建数据仓库平台变得至关重要。数据仓库的核心之一是ETL(Extract, Transform, Load)过程，即数据抽取、转换和加载。这一过程主要依赖于SQL语句，因此，优化SQL语句以提高ETL...
数仓学习---9、数仓开发之DIM层
星光不问赶路人，岁月不负有心人
07-20 873
数仓开发之DIM层
【电商数仓】数仓搭建之DIM维度层（用户维度表[拉链表]，首日、每日数据装载脚本）
weixin_43923463的博客
10-02 2339
拉链表，记录每条信息的生命周期，一旦一条记录的生命周期结束，就重新开始一条新的记录，并把当前日期放入生效开始日期。如果当前信息至今有效，在生效结束日期中填入一个极大值（如9999-99-99 ）。表现的就是数据的变化过程。用户ID姓名手机号码开始日期结束日期1张三2019-01-012019-01-011张三2019-01-022019-01-091张三2019-01-109999-99-99。
离线数仓（11）：DIM层实现
yang_shibiao的博客
09-07 1455
对ods层相关维度进行聚合，减少表的数量（例如3级分类聚合成一张分类维度表），后续使用维度减少雪花模型的使用；一般维度表每天创建一个分区（商品维度表等），特殊维度表导入数据（时间维度表），需要记录历史变化的创建拉链表（用户维度表）；因为拉链表是记录数据的新增和变化，所以使用ods层的记录表（ods_fmys_users_record_dt）；如下代码所示，对每个user_id开窗，每个更改时间一条数据，当在这个时间点，用户修改了信息，那也就是说，从这个时间点到下个时间点，用户都使用的是这条记录的信息。
电商数仓项目----笔记七(数仓DIM层)
zmx_messi的博客
12-24 1317
所谓的维度层其实就是分析数据的角度，维度层保存的表其实是分析数据的角度，比如：--性别，年龄，品牌，品类这层的表主要用于统计分析，因此DIM层的数据存储格式为orc列式存储+snappy压缩（时间短）
数仓开发：DIM层数据处理
DrKYQ的博客
05-04 1237
希望对大家有帮助！
数据仓库相关概念的解释
weixin_45866849的博客
03-10 1719
因此，数据仓库中的数据清洗工作主要还是在数据转换的时候进行。（load）至目的端的过程，它能够对各种分布的、异构的元数据（如关系数据）进行抽取，按照预先设计的规则将不完整数据、重复数据以及错误数据等“脏”数据内容进行清晰，得到符合要求的“干净”数据，并加载到数据仓库中进行存储，这些“干净”数据久成为了数据分析、数据挖掘的基石。注意：在这层，理应不是简单的数据接入，而是要考虑一定的数据清洗，比如异常字段的处理、字段命名规范化、时间字段的统一等，一般这些很容易会被忽略，但是却至关重要。
数仓学习笔记(4)——数仓搭建（ODS层和DIM层）
qq_64557330的博客
09-03 2828
目录一、数仓搭建-ODS层1、ODS层(用户行为数据)1.1 创建日志表ods_log1.2 Shell中单引号和双引号区别1.3 ODS层日志表加载数据脚本2、ODS层(业务数据)二、数仓搭建-DIM层1、商品维度表（全量）1.1 建表语句1.2 分区规划1.3 数据装载1.4 Hive读取索引文件问题1.5 首日装载和每日装载2、优惠卷维度表（全量）2.1 建表语句2.2 分区规划2.3 数据装载2.4 首日装载和每日装载3、活动维度表（全量）3.1 建表语句3.2 分区规划3.3 数据装载 3.4 首
搭建一个数据仓库的步骤及代码
05-13
建立数据模型是数据仓库搭建的关键步骤之一，需要根据业务需求设计合适的数据模型。具体步骤包括： - 设计维度表和事实表 - 设计数据架构 - 加载数据到数据仓库中 以下是使用 SQL Server 建立数据模型的示例代码：...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值