数据仓库表设计理论

已于 2024-06-27 14:37:22 修改
阅读量2.7k 收藏 16
点赞数 2
分类专栏： 数据仓库 文章标签： 数据仓库 hive 拉链表 大数据 数据库
于 2023-07-21 21:20:46 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_35128600/article/details/131858887
版权

数据仓库表设计理论

数仓顾名思义是数据仓库，其数据来源大多来自于业务数据(例如:关系型数据库)，当设计数仓中表类型时(拉链表、增量表、全量表、流水表、切片表)时，应先观察业务数据的特点再设计数仓表结构

首先业务数据是会不断增长的-即增量，而在不断增长的前提下业务数据又可以分为两类：

  • 增量更新数据源：数据源允许新增、修改和删除操作的数据源
  • 增量非更新数据源：数据源只允许新增数据，不允许对历史数据进行修改的数据源

业务数据中的这两种数据源类型直接决定了数仓中的表设计的选择

一、增量更新数据源

增量更新数据源是指允许新增、修改和删除操作的数据源。这种数据源的主要特点是：

  1. 数据可修改：可以对历史数据进行修改、覆盖，以反映数据的变更。
  2. 实时性高：由于数据可以随时更新，具有很强的实时性和即时性。

常见的增量更新数据源包括关系型数据库、NoSQL数据库、文件系统等，它们都支持对数据进行新增、修改和删除操作。

示例：关系型数据库中的用户表，其手机号字段经常会出现修改

注册日期用户编号手机号码
2017-01-01001111111
2017-01-01002222222
2017-01-01003333333
2017-01-01004444444

在2017-01-02这一天表中的数据是， 用户002和004资料进行了修改，005是新增用户：

注册日期用户编号手机号码备注
2017-01-01001111111
2017-01-01002233333(由222222变成233333)
2017-01-01003333333
2017-01-01004432432(由444444变成432432)
2017-01-02005555555(2017-01-02新增)

针对这类数据源，我们将分别阐述下数仓中的各种类型的表设计

1.1、全量表

全量表就是存储了全部数据的表，没有分区之分，可以理解为总共就一个分区。

全量表中存储了截至目前为止最新状态的全部记录，这就表示会存在历史状态的更新。

1.1.1、场景
  1. [业务数据]-用户表为例，2020-06-01有三个用户注册，表数据如下：
用户名手机号性别create_time
xiaohong111女2020-06-01 08:15:37
xiaoming222男2020-06-01 09:15:37
xiaojun333男2020-06-01 12:15:37
  1. [离线数仓]-由于离线数仓是T+1，故2020-06-02数仓全量表数据如下：
用户名手机号性别create_timedt
xiaohong111女2020-06-01 08:15:372020-06-01
xiaoming222男2020-06-01 09:15:372020-06-01
xiaojun333男2020-06-01 12:15:372020-06-01
  1. [业务数据]-2020-06-02新增一名用户注册，即新增了一名用户（加粗），数据表如下：
用户名手机号性别create_time
xiaohong111女2020-06-01 08:15:37
xiaoming222男2020-06-01 09:15:37
xiaojun333男2020-06-01 12:15:37
lisa444女2020-06-02 15:15:37

加粗为更新/新增数据

  1. [离线数仓]-由于离线数仓是T+1，故2020-06-03数仓全量表数据如下：
用户名手机号性别create_timedt
xiaohong111女2020-06-01 08:15:372020-06-01
xiaoming222男2020-06-01 09:15:372020-06-01
xiaojun333男2020-06-01 12:15:372020-06-01
xiaohong111女2020-06-01 08:15:372020-06-02

加粗为更新/新增数据

1.1.2、实现方式

全量表的实现方式又分为两种：

  • 全量替换：直接获取全表业务数据替换昨日全量单表
  • 增量替换：通过合并：每日增改数据的临时表 或 流水表，重写后得到全新全量表
1.1.2.1、全量替换
-- 创建一张新的全量单表
CREATE TABLE new_table (col1 STRING, col2 INT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/user/hive/warehouse/new_table';

-- 将全部的业务数据导入至全量单表, 过程不赘述
insert into new_table ..........

-- 将新表名替换为旧表名
ALTER TABLE old_table RENAME TO old_table_backup;
ALTER TABLE new_table RENAME TO old_table;

-- 删除昨日全量单表
DROP TABLE old_table_backup;
1.1.2.2、增量替换

注意：业务数据需要有唯一的ID 和 upadte_time，否则无法使用join替换。

-- 创建一张新的临时增量表
CREATE TABLE incremental_table (id INT, column1 STRING, column2 STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/user/hive/warehouse/incremental_table';

-- 将增量数据加载到一个临时表中，一般是通过业务数据的update_time来区分哪些是增量数据，这里以2023-06-03日凌晨同步为例
insert into incremental_table select * from 业务用户表 where 
update_time >=  '2023-06-02 00:00:00' and update_time < '2023-06-03 00:00:00';

-- 使用LEFT JOIN语句将原表和增量数据合并
SELECT o.*
FROM original_table o
LEFT JOIN incremental_table i ON o.id = i.id
WHERE i.id IS NULL
UNION
SELECT *
FROM incremental_table;


-- 这将生成一个包含原表和增量数据的JOIN结果。在上面的例子中，我们使用LEFT JOIN语句将原表和增量数据合并。
-- 最后，我们可以使用INSERT OVERWRITE语句将合并后的数据插入到原表中
INSERT OVERWRITE TABLE original_table
SELECT o.*
FROM original_table o
LEFT JOIN incremental_table i ON o.id = i.id
WHERE i.id IS NULL
UNION
SELECT *
FROM incremental_table;

总结：全量表设计的优势是不会占用太多磁盘空间；弊端也很明显-不支持历史记录溯源

1.2、增量表

增量表是指只负责追加新的数据记录，而不负责历史数据更改记录，新的数据记录保存在新分区中，历史分区中的数据记录不发生变化。

增量表并不适用增量更新数据源，只适用于增量非更新数据源。

1.3、快照表

快照表是用来存储某个时间点的所有数据-通常粒度是天，相当于是对每天的业务数据做了一次快照，存储当天的全量数据！

  • 例如：快照表中某个分区内的数据是历史到此分区前一天的所有数据，如12号分区中的数据是从历史到11号的所有数据，13号分区中的数据是从历史到12号的所有数据，其他的以此类推。

1.3.1、场景

也可以理解为：每天将业务数据的全量数据存储至数仓-快照分区表的当天分区内，这里假设分区的粒度是天

  1. [业务数据]-用户表为例，2020-06-01有三个用户注册，表数据如下：
用户名手机号性别create_time
xiaohong111女2020-06-01 08:15:37
xiaoming222男2020-06-01 09:15:37
xiaojun333男2020-06-01 12:15:37
  1. [离线数仓]-由于离线数仓是T+1，故2020-06-02数仓快照表数据如下：
用户名手机号性别create_timedt
xiaohong111女2020-06-01 08:15:372020-06-01
xiaoming222男2020-06-01 09:15:372020-06-01
xiaojun333男2020-06-01 12:15:372020-06-01
  1. [业务数据]-2020-06-02新增一名用户注册，即新增了一名用户（加粗），数据表如下：
用户名手机号性别create_time
xiaohong111女2020-06-01 08:15:37
xiaoming222男2020-06-01 09:15:37
xiaojun333男2020-06-01 12:15:37
lisa444女2020-06-02 15:15:37

加粗为更新/新增数据

  1. [离线数仓]-由于离线数仓是T+1，故2020-06-03数仓快照表数据如下：
用户名手机号性别create_timedt
xiaohong111女2020-06-01 08:15:372020-06-01
xiaoming222男2020-06-01 09:15:372020-06-01
xiaojun333男2020-06-01 12:15:372020-06-01
xiaohong111女2020-06-01 08:15:372020-06-02
xiaoming222男2020-06-01 09:15:372020-06-02
xiaojun333男2020-06-01 12:15:372020-06-02
lisa444女2020-06-02 15:15:372020-06-02

加粗为更新/新增数据

1.3.2、实现

的实现方式也分为两种：

  • 全量插入：直接获取全表业务数据插入到数仓快照表的指定分区
  • join插入：通过每日增改数据与数仓快照表前一日数据进行合并后得到全新全量表 再 插入到数仓快照表的指定分区
1.3.2.1 全量插入

先将业务数据全部同步至hive临时表中，随后将hive临时表的业务数据放置在快照分区表的今日分区内:

-- 创建一个新hive临时表，存放当日业务的全量数据
CREATE TABLE temp_table (col1 STRING, col2 INT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/user/hive/warehouse/temp_table';

-- 将全部的业务数据导入至hive临时表, 过程不赘述
insert into temp_table select * from 业务表 where create_time < '2023-05-08 00:00:00' and update_time <= '2023-05-08 00:00:00';

-- 使用INSERT INTO语句将临时表中的数据插入到全量分区表的目标分区中：
INSERT INTO original_table PARTITION (date='2023-05-08')
SELECT * FROM temp_table;

-- 删除hive临时表
DROP TABLE temp_table;
1.3.2.2 增量插入

增量同步顾名思义是将业务数据用户表按天为粒度将增量数据与数仓快照表中的前一天数据进行join对比后放入到指定分区内，该方式需要业务数据有唯一字段（常用ID） 和 update_time时间戳，否则无法使用增量同步

-- 将增量数据加载到一个临时表中，一般是通过业务数据的update_time来区分哪些是增量数据，这里以2023-06-03日凌晨同步为例
insert into incremental_table select * from 业务用户表 where 
update_time >=  '2023-06-02 00:00:00' and update_time < '2023-06-03 00:00:00';

-- 使用LEFT JOIN语句将原表和增量数据合并
SELECT o.*
FROM original_table o
LEFT JOIN incremental_table i ON o.id = i.id
WHERE i.id IS NULL
UNION
SELECT *
FROM incremental_table;


-- 这将生成一个包含原表和增量数据的JOIN结果
-- 最后，我们可以使用INSERT INTO 到快照表的指定分区内插入
INSERT INTO 数仓快照表 PARTITION (date='2023-06-02') 
SELECT o.*
FROM original_table o
LEFT JOIN incremental_table i ON o.id = i.id
WHERE i.id IS NULL
UNION
SELECT *
FROM incremental_table;

总结：此方式便是快照表，该设计的弊端很明显：会大量占用磁盘空间，故并不推荐使用

1.4、流水表

流水表是用于记录数据变更的表，理论上是对于表的每一个修改都会记录，但在实际应用中通常按天为粒度划分，例如流水表中的2017-01-02分区只记录这一天新增和修改的业务数据，这样可以方便地追溯、计算和分析历史数据，同时也提供了可靠的数据源供其他表和报告使用。

1.4.1、场景

还是用user用户数据举例，2017-01-01这一天表中的数据是：

注册日期用户编号手机号码
2017-01-01001111111
2017-01-01002222222
2017-01-01003333333
2017-01-01004444444

此时流水表的数据为：

注册日期用户编号手机号码dt-时间分区字段
2017-01-010011111112017-01-01
2017-01-010022222222017-01-01
2017-01-010033333332017-01-01
2017-01-010044444442017-01-01

在2017-01-02这一天表中的数据是， 用户002和004资料进行了修改，005是新增用户：

注册日期用户编号手机号码备注
2017-01-01001111111
2017-01-01002233333(由222222变成233333)
2017-01-01003333333
2017-01-01004432432(由444444变成432432)
2017-01-02005555555(2017-01-02新增)

此时流水表的数据为：

注册日期用户编号手机号码dt-时间分区字段
2017-01-010011111112017-01-01
2017-01-010022222222017-01-01
2017-01-010033333332017-01-01
2017-01-010044444442017-01-01
2017-01-010022333332017-01-02
2017-01-010044324322017-01-02
2017-01-020055555552017-01-02

1.4.2、实现

-- 创建一个user_update_temp临时表，用于存放当日业务的增量数据
CREATE EXTERNAL TABLE ods.user_update_temp (
    user_num STRING COMMENT '用户编号', 
    mobile STRING COMMENT '手机号码', 
    reg_date STRING COMMENT '注册日期'
)
COMMENT '每日用户资料更新表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
STORED AS ORC
LOCATION '/ods/user_update';

-- 创建一张流水表，用来存储每天变化数据
CREATE EXTERNAL TABLE dws.user_stream (
    user_num STRING COMMENT '用户编号', 
    mobile STRING COMMENT '手机号码', 
    reg_date STRING COMMENT '注册日期'
)
COMMENT '用户流水表'
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
STORED AS ORC
LOCATION '/dws/orders';

-- 通过业务字段update_time将2017-01-02变化的业务数据导入至user_update_temp临时表，过程不赘述
INSERT OVERWRITE ods.user_update_temp .........

-- 使用INSERT INTO语句将ods.user_update中的数据插入到流水表中2017-01-02分区中：
INSERT INTO dws.user_stream PARTITION (dt='2017-01-02')
SELECT * FROM ods.user_update_temp;

注意：流水表很容易和增量表的概念混淆，这里再强调一下：增量表只适用于增量非更新数据源，只负责新增数据，对于历史数据的修改并不记录；而流水表通常用于记录数据变更，包括新增、修改和删除等操作，以便跟踪每个事实或维度的历史变化。为了方便管理和查询，通常将流水表按时间分区。

1.5、拉链表

拉链表是一种维护历史状态以及最新状态数据的表。与快照表类似，算是在快照表的基础上去除了重复状态的数据，使用拉链表在更新频率和比例不是很大的情况下会十分节省存储。

1.5.1、场景

现在以用户的拉链表来说明2017-01-01这一天表中的数据是：

注册日期用户编号手机号码
2017-01-01001111111
2017-01-01002222222
2017-01-01003333333
2017-01-01004444444

在2017-01-02这一天表中的数据是， 用户002和004资料进行了修改，005是新增用户：

注册日期用户编号手机号码备注
2017-01-01001111111
2017-01-01002233333(由222222变成233333)
2017-01-01003333333
2017-01-01004432432(由444444变成432432)
2017-01-02005555555(2017-01-02新增)

在2017-01-03这一天表中的数据是， 用户004和005资料进行了修改，006是新增用户：

注册日期用户编号手机号码备注
2017-01-01001111111
2017-01-01002233333
2017-01-01003333333
2017-01-01004654321(由432432变成654321)
2017-01-02005115115(由555555变成115115)
2017-01-03006666666(2017-01-03新增)

如果在数据仓库中设计成历史拉链表保存该表，则会有下面这样一张表，这是最新一天（即2017-01-03）的数据：

注册日期用户编号手机号码t_start datet_end date
2017-01-010011111112017-01-019999-12-31
2017-01-010022222222017-01-012017-01-01
2017-01-010022333332017-01-029999-12-31
2017-01-010033333332017-01-019999-12-31
2017-01-010044444442017-01-012017-01-01
2017-01-010044324322017-01-022017-01-02
2017-01-010046543212017-01-039999-12-31
2017-01-020055555552017-01-022017-01-02
2017-01-020051151152017-01-039999-12-31
2017-01-030066666662017-01-039999-12-31

说明

  • t_start_date表示该条记录的生命周期开始时间，t_end_date表示该条记录的生命周期结束时间
  • t_end_date = '9999-12-31’表示该条记录目前处于有效状态
  • t_end_date = '2017-01-02’表示该条记录在2017-01-02当日是有效的，在当前日期是无效的
  • 如果查询当前所有有效的记录，则select * from user where t_end_date = ‘9999-12-31’
  • 如果查询2017-01-02的历史快照，则select * from user where t_start_date <= ‘2017-01-02’ and t_end_date >= ‘2017-01-02’。（此处要好好理解，是拉链表比较重要的一块）
  • 解释上一条sql：需求是要查2017-01-02的历史快照，t_start_date是代表这条记录的开始时间，并非是原始数据的时间，例如001用户数据在2017-01-02也有效，故t_start_date <= ‘2017-01-02’；而t_end_date = '2017-01-02’表示该条记录在2017-01-02当日是有效的，又因为t_end_date = '9999-12-31’表示该条记录目前处于有效状态，所以t_end_date >= ‘2017-01-02’

该sql查询结果如下：

注册日期用户编号手机号码t_start datet_end date
2017-01-010011111112017-01-019999-12-31
2017-01-010022333332017-01-029999-12-31
2017-01-010033333332017-01-019999-12-31
2017-01-010044324322017-01-022017-01-02
2017-01-020055555552017-01-022017-01-02

和下图2017-01-02的业务数据比较，结果完全一致：

注册日期用户编号手机号码备注
2017-01-01001111111
2017-01-01002233333(由222222变成233333)
2017-01-01003333333
2017-01-01004432432(由444444变成432432)
2017-01-02005555555(2017-01-02新增)

1.5.2、实现

创建拉链表的前提是先根据全量数据表创建初始拉链表，然后再根据每天的增改数据进行合并更新拉链表；

还是以上面的用户表为例，我们要实现用户的拉链表，在实现它之前，我们需要先确定一下我们有哪些数据源可以用。

  1. 我们需要一张ODS层的用户全量表。至少需要用它来初始化。
  2. 流水表-记录每日增改数据。

建表语句：

-- 先创建一张全量表用于初始化
CREATE EXTERNAL TABLE ods.user (
  user_num STRING COMMENT '用户编号',
  mobile STRING COMMENT '手机号码',
  reg_date STRING COMMENT '注册日期'
) COMMENT '用户资料表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
STORED AS ORC
LOCATION '/ods/user';

-- 最后我们创建一张拉链表：
CREATE EXTERNAL TABLE dws.user_his (
  user_num STRING COMMENT '用户编号',
  mobile STRING COMMENT '手机号码',
  reg_date STRING COMMENT '注册日期',
  t_start_date STRING COMMENT '资料开始日期',
  t_end_date STRING COMMENT '资料结束日期'
) COMMENT '用户资料拉链表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
STORED AS ORC
LOCATION '/dws/user_his';

数据初始化：我们以2017-01-01的数据作为初始化数据

-- 假设ods.user表已经存储了2017-01-01的全量数据，此时拉链表的初始化sql：
INSERT OVERWRITE TABLE dws.user_his
SELECT user_num, mobile, reg_date, '2017-01-01', '9999-12-31'
FROM ods.user;

初始化后的拉链表数据如下：

注册日期用户编号手机号码t_start_datet_end_date
2017-01-010011111112017-01-019999-12-31
2017-01-010022222222017-01-019999-12-31
2017-01-010033333332017-01-019999-12-31
2017-01-010044444442017-01-019999-12-31

数据更新：我们以2017-01-02的数据更新拉链表；

2017-01-02日的流水表数据如下：

注册日期用户编号手机号码dt-时间分区字段
2017-01-010011111112017-01-01
2017-01-010022222222017-01-01
2017-01-010033333332017-01-01
2017-01-010044444442017-01-01
2017-01-010022333332017-01-02
2017-01-010044324322017-01-02
2017-01-020055555552017-01-02
-- 2017-01-02拉链表更新sql:
INSERT OVERWRITE TABLE dws.user_his
SELECT *
FROM
(
    SELECT A.user_num,
           A.mobile,
           A.reg_date,
           A.t_start_date,
           CASE
                WHEN A.t_end_date = '9999-12-31' AND B.user_num IS NOT NULL THEN '2017-01-01'
                ELSE A.t_end_date
           END AS t_end_date
    FROM dws.user_his AS A
    LEFT JOIN dws.user_stream AS B WHERE dt = '2017-01-02'
    ON A.user_num = B.user_num
UNION
    SELECT C.user_num,
           C.mobile,
           C.reg_date,
           '2017-01-02' AS t_start_date,
           '9999-12-31' AS t_end_date
    FROM dws.user_stream AS C WHERE dt = '2017-01-02'
) AS T;

更新后的拉链表数据如下：

注册日期用户编号手机号码t_start_datet_end_date
2017-01-010011111112017-01-019999-12-31
2017-01-010022222222017-01-012017-01-01
2017-01-010033333332017-01-019999-12-31
2017-01-010044444442017-01-012017-01-01
2017-01-010022333332017-01-029999-12-31
2017-01-010044324322017-01-029999-12-31
2017-01-02005555552017-01-029999-12-31
-- 查询有效数据:
select * from dws.user_his where t_end_date = '9999-12-31'

查询有效数据结果：

注册日期用户编号手机号码t_start_datet_end_date
2017-01-010011111112017-01-019999-12-31
2017-01-010033333332017-01-019999-12-31
2017-01-010022333332017-01-029999-12-31
2017-01-010044324322017-01-029999-12-31
2017-01-02005555552017-01-029999-12-31
-- 查询2017-01-01历史数据: 
select * from dws.user_his where t_start_date <= '2017-01-01' and t_end_date >= '2017-01-01'

2017-01-01历史数据：

注册日期用户编号手机号码t_start_datet_end_date
2017-01-010011111112017-01-019999-12-31
2017-01-010022222222017-01-012017-01-01
2017-01-010033333332017-01-019999-12-31
2017-01-010044444442017-01-012017-01-01

1.6、切片表

切片表根据基础表，往往只反映某一个维度的相应数据。其表结构与基础表结构相同，但数据往往只有某一维度，或者某一个事实条件的数据；切片表以某个维度或者一些特定的条件对事实进行汇总计算，并展示为一个交叉分析的表格。与事实表相比，切片表的数据更加聚合，只包含某些维度或者满足某些特定条件的数据。

1.6.1、场景

假设我们有一个基础表（也称为事实表），记录了一家公司的销售订单信息。该表包含以下字段：订单ID、客户ID、产品ID、销售日期、销售数量和销售额等。

订单ID客户ID产品ID销售日期销售数量销售额
1100120012022-01-013150
2100220022022-01-02280
3100320012022-01-03150
4100120032022-01-045250
5100220022022-01-054160

在这张表中，客户端ID、产品ID、销售日期是维度，而销售数量、销售额是事实。

现在我们希望按照客户维度创建一个切片表，以便分析每个客户的销售情况。

具体来说，我们需要选择客户维度，并对销售数量和销售额这两个度量进行聚合计算，通过多维分析工具或者SQL查询，可以生成如下的切片表：

客户ID销售数量总计销售额总计
10018400
10026240
1003150

在这个切片表中，我们只选择了客户维度，然后，我们使用SUM函数对每个客户的销售数量和销售额进行聚合计算，以便更好地分析不同客户之间的销售情况。

1.6.2、实现

在数据仓库中，切片表的存储方式可以根据不同的需求和性能要求而定，一般来说有以下两种常用的存储方式：

  1. 全量替换：每次运行ETL作业时，都会重新生成整个切片表，并将其覆盖原有的数据。这种方式适用于数据量较小、更新频率低的场景，或者需要保证数据完整性和一致性的场景。
  2. 按照天的粒度进行划分：将切片表按照时间维度（如日、周、月等）进行分区，每个分区存储一段时间内的数据。这种方式适用于数据量较大、更新频率高的场景，或者需要快速查询历史数据的场景。

无论采用哪种存储方式，都需要考虑切片表的设计问题。具体来说，以下是一些设计上的注意点：

  1. 维度表的设计：维度表应该是离线化的静态数据，它们应该是变化不大并且计算时被缓存在内存中，以便提高查询性能。因此，在设计维度表时，应该尽可能地避免经常变化的属性。
  2. 表结构的优化：为了提高查询效率，应该尽可能地减少JOIN操作，尽量将不同维度的数据存储在同一个表中。如果必须进行JOIN操作，则应该尽可能地使用分区表或者基于列存储的数据库，以便提高查询性能。
  3. 时间分区：如果采用按照天的粒度进行划分的方式，那么需要将切片表按照时间维度进行分区，并且使用分区键作为查询条件。这样可以大大提高查询性能，同时也方便进行数据备份和恢复。

总之，在设计和实现切片表时，需要考虑不同的因素，包括数据量、更新频率、查询性能、数据一致性等等，以便得出最优的解决方案。

这里以全量替换举例：

-- 按照上面的例子，假设有一个名为sales_order的基础表
CREATE TABLE sales_order (
  order_id int,
  customer_id int,
  product_id int,
  sale_date date,
  quantity int,
  amount double
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

-- 我们可以将原始数据加载到sales_order表中
LOAD DATA INPATH 'path/to/sales.csv' OVERWRITE INTO TABLE sales_order;

-- 接下来，我们可以使用INSERT INTO SELECT语句来创建切片表customer_sales，并将数据按照客户维度进行聚合计算：
CREATE TABLE customer_sales (
  customer_id int,
  total_quantity int,
  total_amount double
)
AS
SELECT 
  customer_id, 
  SUM(quantity) as total_quantity, 
  SUM(amount) as total_amount 
FROM sales_order 
GROUP BY customer_id;

-- 这里采用了全量替换的方式，每次重新运行上面的SQL语句都会完全替换customer_sales表中的数据。

二、增量非更新数据源

当数据源只允许新增数据，不允许对历史数据进行修改时，我们称之为增量不更新数据源。这种数据源的主要特点是：

  1. 数据只能增长：无法对历史数据进行修改、删除或覆盖，只能追加新的数据。
  2. 数据变更历史保留：由于数据源只追加新的数据，因此可以完整地保留数据的变更历史，便于后续的分析和回溯。
  3. 数据量大：由于数据只能增长，因此数据量通常会随着时间的推移而不断增加。
  4. 实时性高：由于数据是实时生成的，具有很强的实时性和即时性。

常见的增量不更新数据源包括：

  1. 日志数据：日志数据是指记录了系统或应用程序操作的信息的数据。由于日志数据一般是只追加不修改，所以可以视为增量不更新数据源。
  2. 传感器数据：传感器数据是指由各种传感器采集的环境、设备或系统状态数据等。这些数据也是只追加不修改的，因此可以视为增量不更新数据源。
  3. 消息系统数据：消息系统数据是指通过消息队列、主题或流处理框架等收集的异步消息数据。与日志数据和传感器数据类似，这些数据也是只追加不修改的。
  4. 用户活动数据：用户活动数据是指记录了用户在应用程序中进行的各种操作和行为的数据。由于这些数据通常无法修改，因此也可以视为增量不更新数据源。

在设计数据模型和存储方案时，需要考虑到数据源的特点，以便满足快速查询和分析的需求，一般来说，可以采用以下策略：

  1. 使用列式存储：由于增量不更新数据源的数据一般只用于查询和分析，因此可以采用列式存储方式，以提高查询性能。
  2. 考虑分区：根据数据源的特点，可以将数据按照时间、地理位置等因素进行分区，以便更快地查询历史数据。
  3. 超大表设计：由于增量不更新数据源的数据量很大，因此需要考虑超大表的设计问题。例如，可以使用分区表、分布式存储等技术来支持数据的高效存储和查询。

2.1、全量表

对于增量不更新数据源，使用全量表进行处理可能会导致性能问题和资源浪费，因为全量表需要重新加载所有数据，并进行完全替换，这将消耗大量的计算资源和时间。

相反，使用增量表可以更有效地处理增量不更新数据源，因为增量表只包含最近追加的数据，并且每次只需要更新最新的增量数据即可。这样，可以避免重复加载和处理历史数据，从而提高处理效率和减少资源消耗。

2.2、增量表

增量表可以高效地处理增量不更新数据源，因为增量表只包含最新的新增数据，并且每次只需要更新最新的增量数据即可。这样，可以避免重复加载和处理历史数据，从而提高处理效率和减少资源消耗。

增量表通常按天为粒度

2.2.1、场景

假设我们有一个传感器监测系统，每个传感器会在固定时间间隔内生成一些数据。这些数据在产生后不会被更改。我们需要设计一个增量表来处理这些传感器数据，并按天分区管理数据。

原始数据格式展示：

字段名称数据类型说明
sensor_idINT传感器ID
timestampTIMESTAMP时间戳
valueFLOAT数据值

14号原始数据：

sensor_idtimestampvalue
12023-05-14 10:00:0023.5
22023-05-14 10:00:0018.2
32023-05-14 10:00:0025.0
42023-05-14 11:00:0024.0
52023-05-14 11:00:0018.4

此时我们设计一个名为sensors_incremental的增量表，用于存储每天新增的传感器数据，并按照日期进行分区。该表包含了四个字段：sensor_id、timestamp、value和dt(时间分区字段)。

增量表初始数据展示如下：

sensor_idtimestampvaluedt(时间分区字段)
12023-05-14 10:00:0023.52023-05-14
22023-05-14 10:00:0018.22023-05-14
32023-05-14 10:00:0025.02023-05-14
42023-05-14 11:00:0024.02023-05-14
52023-05-14 11:00:0018.42023-05-14

第二天原始数据如下：

sensor_idtimestampvalue
12023-05-14 10:00:0023.5
22023-05-14 10:00:0018.2
32023-05-14 10:00:0025.0
32023-05-14 11:00:0024.0
52023-05-14 11:00:0018.4
62023-05-15 11:00:0025.2
72023-05-15 12:00:0023.8
82023-05-15 12:00:0018.6
92023-05-15 12:00:0025.5

第二天增量表sensors_incremental数据：

sensor_idtimestampvaluedt(时间分区字段)
12023-05-14 10:00:0023.52023-05-14
22023-05-14 10:00:0018.22023-05-14
32023-05-14 10:00:0025.02023-05-14
42023-05-14 11:00:0024.02023-05-14
52023-05-14 11:00:0018.42023-05-14
62023-05-15 1100:0025.22023-05-15
72023-05-15 12:00:0023.82023-05-15
82023-05-15 12:00:0018.62023-05-15
92023-05-15 12:00:0025.52023-05-15

2.2.2、实现

-- 创建增量表
CREATE TABLE sensors_incremental (
  sensor_id INT,
  timestamp TIMESTAMP,
  value FLOAT
)
PARTITIONED BY (day DATE)
STORED AS PARQUET;

-- 将原始数据加载到sensors_temp临时表中
LOAD DATA INPATH 'path/to/sensors.csv' OVERWRITE INTO TABLE sensors_temp;

-- 将临时表数据初始化加载进增量表
INSERT INTO sensors_incremental PARTITION (date='2023-05-14') SELECT * FROM sensors_temp;

-- 第二天将15号临时表数据加载进增量表
INSERT INTO sensors_incremental PARTITION (date='2023-05-15') SELECT * FROM sensors_temp;

通过使用以上增量表设计和相应的SQL语句，我们可以有效地处理传感器数据，并实现按天分区的管理和查询。

2.3、快照表

快照表同样适用于增量非更新数据源，快照表是用来存储某个时间点的所有数据-通常粒度是天，相当于是对每天的业务数据做了一次快照，存储当天的全量数据！

其设计和实现与增量更新数据源保持一致，详细参考：1.1.3、快照表

2.4、流水表

流水表同样适用于增量不更新数据源，流水表是用于记录数据变更的表，流水表如果是按天为粒度划分，那和增量表几乎一模一样，因为不涉及到数据的修改，故增量非更新数据源下的流水表和增量表的数据几乎保持一致。

其设计和实现与增量更新数据源保持一致，详细参考：1.1.4、流水表

2.5、拉链表

拉链表同样适用于增量不更新数据源，其设计和实现与增量更新数据源保持一致，详细参考：1.1.5、拉链表

2.6、切片表

切片表是根据事实数据生成的维度表，故同样适用于增量不更新数据源，其设计和实现与增量更新数据源保持一致，详细参考：1.1.6、切片表

三、总结

增量表、全量表、快照表、拉链表、切片表和流水表都是常用的数据表格设计方式，每种表格设计方式都有自己的优劣势和适用场景。

  • 增量表：只记录新增或更改操作的数据表，可以减少存储和处理开销，适用于高频产生新增事件的情况。
  • 全量表：记录所有数据的完整表格，可以提供准确的数据，但无法提供历史溯源记录
  • 快照表：记录某个时间点的部分或全部数据，可用于数据快照和查询。通常使用在查询性质的报表系统中。
  • 拉链表：记录数据变化的时间范围。适用于需要准确跟踪数据变化的场景，例如会计系统。
  • 切片表：记录具有多维度属性的实体，例如客户、产品等，适用于类似于分析型场景。
  • 流水表：记录所有事件（包括新增、删除、修改等），可以追溯完整的历史信息，适用于审计和数据分析以及为其他类型的表提供数据源

总体而言，以上的表格设计方式都有其自身的优缺点以及适用场景，应根据具体的业务需求进行合理选择。

Light Gao
关注
  • 2
    点赞
  • 踩
  • 16
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
一篇文章搞懂数据仓库：维度表（设计原则
m0_60635035的博客
04-01 672
现在能在网上找到很多很多的学习资源，有免费的也有收费的，当我拿到1套比较全的学习资源之前，我并没着急去看第1节，我而是去审视这套资源是否值得学习，有时候也会去问一些学长的意见，如果可以之后，我会对这套学习资源做1个学习计划，我的学习计划主要包括规划图和学习进度表。退化维度是维度建模领域中的一个非常重要的概念，它对理解维度建模有着非常重要的作用，退化维度一般在分析中可以用来做分组使用。维度是维度建模的基础和灵魂。维度表包含了事实表中指定属性的相关详细信息，最常用的维度表有日期维度、城市维度等。
数据仓库理论
03-24
数据仓库是一种特定类型的数据管理系统，专门设计用于支持企业的决策制定过程。它是一个集中的、非易失性的存储库，汇集了来自多个业务源的结构化数据，并保持这些数据的历史版本，以便进行分析和报告。数据仓库的...
数据仓库总结
专注大数据与人工智能技术分享，欢迎私信加群互相学习！
02-09 1673
本系列主要介绍数据仓库相关知识，数据仓库，英文名为Data Warehouse，简写为DW或DWH。数据仓库，是一个面向主题的、集成的、随时间变化的、但信息本身相对稳定的数据集合，用于对管理决策过程的支持。它是单个数据存储，出于分析性报告和决策支持目的而创建。 为需要业务智能的企业，提供指导业务流程改进、监视时间、成本、质量以及控制。 1.2 数据仓库的四个特点 面向主题：数据仓库是按照一定的主题来组织，仅存储与主题相关的数据。主题是指用户在构建数仓时考虑决策时所关注的重点方面，方便以后的数据
数仓知识07：数据增量更新的几种方式
维克先生的博客
05-01 9158
1、增量更新的几种方式 增量更新的本质，其实是获取源表中数据变化的情况（增、删、改），然后将源表中发生的变化同步至目标表中。 不同的方式，获取源表中数据变化的情况不一样，受技术的限制、表结构的限制，某些方式可能无法获取到完整的数据变化情况，因此只能适用于特定的场景。 方式 简述 适用场景 详述 优点 缺点 时间戳增量1 记录每次读数完成时的最大时间戳，后续读数时只获取源头表中新增的数据，将其增量写入到目标表。 源表只增不删不改
仓库库存表的三种设计方式
热门推荐
04-27 1万+
1、实时库存表+库存出入记录表 将出入库单据表和库存表分开，当出入库单据被确认时，修改库存表，库存表中总是只保留当前的库存数据。这样库存表设计大致如下： 库存表（仓库编码、商品编码、库存属性、库存余额、库存价格等） 出入库表（出入单编号，出入日期，调出仓库，调入仓库，商品编码，数量） 这样的逻辑比较自然，但是，在用户要求追溯历史库存的时候，就比较困难了，...
【离线数仓】Day03-系统业务数据仓库：数仓表概念、搭建、数据导入、数据可视化、Azkaban全调度、拉链表的使用...
USTSD的博客
12-05 254
一、电商业务与数据结构简介 1、业务流程 2、常识：SKU/SPU SKU=Stock Keeping Unit(库存量基本单位)。现在已经被引申为产品统一编号的简称，每种产品均对应有唯一的SKU号。SPU(Standard Product Unit)：是商品信息聚合的最小单位，是一组可复用、易检索的标准化信息集合。 3、电商表结构 二、数仓理论 1、表的分类 实体表 维度表：对...
数据仓库-拉链表, 流水表, 全量表, 增量表, 切片表
迷路剑客个人博客
05-18 1万+
数据仓库-拉链表，流水表，全量表，增量表 转载声明 本文大量内容系转载自以下文章，有删改，并参考其他文档资料加入了一些内容： 数据仓库中的增量表，全量表，快照表，拉链表 作者：北京小辉 出处：csdn 什么是全量表，增量表，快照表，拉链表？ 作者：泽米 出处：csdn 一文搞定数据仓库之拉链表，流水表，全量表，增量表 作者：mtj66 出处：csdn 数据仓库中的全量表，增量表，拉链表，流水表及快照表 作者：大数据私房菜 出处：csdn 1 增量表 1.1 概念 增量表：新增数
数据仓库与数据挖掘 理论讲解
11-14
数据仓库是一种专门设计用于支持决策制定的数据存储系统。它从各个业务系统中集成数据，并以一种结构化的、面向主题的方式组织这些数据，便于分析和查询。数据仓库的特点包括： 1. 面向主题：数据仓库围绕特定业务...
数据仓库实验报告.doc
06-23
数据仓库是一种特殊设计的数据库系统，用于支持企业的决策分析...总的来说，数据仓库实验报告不仅提供了理论知识的理解，也通过具体案例展示了数据仓库的实践应用，有助于提升对数据仓库系统设计和管理的实际操作能力。
仓储管理系统数据库设计.rar
07-01
包括 顶层，1层,2层,3层数据流图 CDM，PDM图 以及触发器，函数，数据库脚本，实验报告等
仓库管理系统的数据库设计
04-08
某供应部门管理多个仓库，仓库主要管理物资的入库、出库、库存和采购等事项，建立一个数据库，实现前述要求的功能。
数据仓库建模-大数据技术
最新发布
06-02
数据仓库是一个经过精心设计的、面向主题的、集成的、非易失性的且随时间变化的数据集合，用于支持管理决策。它与操作型数据库不同，后者主要关注事务处理，而数据仓库则专注于数据分析和报告。在大数据背景下，数据...
数据库表结构设计方法及原则
学Java，找哪吒
12-01 1万+
在目前的企业信息系统中，数据库还是最佳的数据存储方式，虽然已经有很多的书籍在指导我们进行数据库设计，但应该那种方式是设计数据库的表结构的最好方法、设计时应遵从什么样的原则、四个范式如何能够用一种方式达到顺畅的应用等是我一直在思考和总结的问题，下文是我针对这几个问题根据自己的设计经历准备总结的一篇文章的提纲，欢迎大家一块进行探讨，集思广益。其中提到了领域建模的概念，但未作详细解释，希望以后能够有时间我们针对这个命题进行深入探讨。 1)不应该针对整个系统进行数据库设计，而应该根据系统架构中的组件划...
【杂记】宽表/窄表—事实表/维度表—数仓分层（ODS/CDM/ADS）—增量表/全量表——电信行业常见指标
积一时之步，臻千里之遥程
05-07 7028
宽表与窄表 宽表：从字面意义上讲就是字段比较多的数据库表。 窄表：严格按照数据库设计三范式。尽量减少数据冗余，但是缺点是修改一个数据可能需要修改多张表 魔百和 魔百和基于家庭宽带网络，通过“魔百和”定制终端，以电视机为显示设备，向用户提供可点播的电视内容及应用， 同时提供多屏互动体验，着力打造“和视界”为多屏首发高清院线。 ODS表及CDR表 ODS——操作性数据 DW——数据仓库 DM——数据集市 1.数据中心整体架构 数据仓库的整理架构，各个系统的元数据通过ETL同步到操作性数据仓库ODS中，对OD
关于库存表的设计
刘欣的CSDN博客
06-03 9620
在设计到WMS仓储管理系统，物料入库的场景，需要有一个库存表来保存物料的数量。 这里出现了一个问题，我是在物料入库代码中直接产生新的物料记录于库存表？ 还是分2步，第一步产生0数量的记录在库存表，第二步在物料入库时候改变库存表的数量？ SAP系统是这样的： T-CODE：MMSC 专门用于创建物料库存 库存表中就有了数量为0的记录 所以，稳妥设计，还是...
数仓平台设计思路01
无名虫博客
02-16 718
大数据行业中，数仓的作用不言而喻，承载着整个公司全业务线的核心，现在就企业内部数据的分析，尤其是各种各样的统计分析报表和用户画像分析，进行阐述。 下图为数仓整体的技术架构： 一个优秀可靠的数仓，一定要结构、分层清晰，能够快速找到数据的位置。主题域的划分，完全可按照公司内部的业务线和结构去划分。 上图中： BDL层，作为缓冲数据层，其实就是贴源数据层，和源数据使用相同的表。直接用源库（传统关系型业...
数据仓库基础理论与实践
数据仓库概论课程旨在使参与者，包括数据仓库的设计者、开发者、管理人员、商业智能集成人员和高级分析人员，能够掌握数据仓库的核心理念，理解其工作原理，并能有效地规划、设计和管理数据仓库项目，以提升企业的...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Light Gao

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值