1. Toy模板网首页
  2. > Toy博客

数据仓库保存历史数据方法之拉链表

3月前 作者：GawynKing 分类：Toy博客 阅读(36) 违法举报

这篇具有很好参考价值的文章主要介绍了数据仓库保存历史数据方法之拉链表。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

一、数据仓库

数据仓库是一个面向主题的、集成的、相对稳定的、反应历史变化的数据集合，用于支持管理决策。

  1. 面向主题：传统的数据库是面向事务处理的，而数据仓库是面向某一领域而组织的数据集合，主题是指用户关心的某一联系紧密的集合。
  2. 集成：数据仓库中数据来源于各个离散的业务系统数据库、外部数据、非结构化数据的集合，数据仓库数据是集成的。
  3. 相对稳定：数据仓库中的数据不应该支持dml操作，而是通过批处理方式进行数据的处理。
  4. 反应历史：数据仓库保存了数据的历史各个版本。

我们今天所介绍的就是数据仓库保留数据历史版本的一种方法-拉链表。

这里我简单介绍一下我们数据仓库中扫采用的架构，主要包括贴源层、明细层、汇总层、集市层、报表层、维度层，简单的介绍如下：

  1. 贴源层：采集的各个业务系统数据首先存储在贴源层中，这里需要注意的是采集业务源数据的方法，增量采集还是全量采集，好的业务系统设计应该支持增量采集（这里留一个问题作为思考：增量采集数据应该满足哪些要求），这样的好处减少了采集数据对仓库资源和业务系统资源的消耗。
  2. 明细层：该层采用规范化方式存储数据，处理数据主要来自于贴源层，实现的目的主要包括面向主题设计存储结构、集成不同业务源数据、统一编码规范、保留历史数据（拉链表主要在这一层中进行设计实现）等仓库基本要处理的
  3. 汇总层：对于明细层整合的数据，针对需要汇总的指标按照业务口径进行计算并且初步反规范化设计实现连接明细层的规范化数据成小宽表，目的方便下一步处理使用。
  4. 集市层：面向不同需求方，按照维度建模方法，进行星型模型设计， 这一层设计完成后的目的要达到可以方便出具报表和日常提数任务。这里有些仓库设计人员还会用另一个思路，即集市层不采用星型模型设计方法，而是设计大宽表，采用这种方式的设计人员主要理由是这种方式方便人们使用。
  5. 报表层：根据各个部门不同需求出具报表。
  6. 维度层：统一存储数仓维表相关数据。

目前数据仓库设计主要有两个阵营，kimball和inmon架构，这里不会针对与这两种放进进行详细说明。个人所接触项目经验，如果极端采用某一种架构，最后数仓项目成功概率都很低，因此个人建议结合两种架构的优点进行数仓设计（即三范式简历数仓明细层，集市层采用星型模型设计方法），合理结合两种思路优点可以有效的避免业务驱动方式带来的烦杂工作以及需求驱动所带来的后期维护及扩展性问题。

二、拉链表原理

这里以一个虚拟的示例简单介绍拉链表实现原理：

1、比如在2017-01-01日，我们初始化了用户数据到数据仓库，我们为初始化到数据仓库中的用户表（customer）添加了一个start_date和end_date字段用来标识该条数据的生命周期，具体如下：

cus_id job start_date end_date

----------------------------------------------------------------------

10001 oracle 2018-01-01 3000-12-21

10002 pgsql 2018-01-01 3000-12-21

10003 mysql 2018-01-01 3000-12-21

10004 java 2018-01-01 3000-12-21

10005 python 2018-01-01 3000-12-21

2、在2017-01-02这一天，10004用户被删除，同时增加了10006及10007用户，10003用户的job由mysql变成了mongodb，明细数据如下：

cus_id job start_date end_date

--------------------------------------------

10001 oracle 2018-01-01 3000-12-21

10002 pgsql 2018-01-01 3000-12-21

10003 mysql 2018-01-01 2018-01-02

10003 mongodb 2018-01-02 3000-12-21

10004 java 2018-01-01 2018-01-02

10005 python 2018-01-01 3000-12-21

10006 docker 2018-01-02 3000-12-21

10007 redis 2018-01-02 3000-12-21

3、在2017-01-03这一天，10007用户被删除，同时10006工作由docker变成了openstack，10003用户工作由mongodb变成了hive，并且增加了10008用户数据，明细数据如下：

cus_id job    start_date end_date

----------------   ----------------------------

10001 oracle    2018-01-01 3000-12-21

10002 pgsql    2018-01-01 3000-12-21

10003 mysql    2018-01-01 2018-01-02

10003 mongodb    2018-01-02 2018-01-03

10003 hive      2018-01-03 3000-12-21

10004 java        2018-01-01 2018-01-02

10005 python    2018-01-01 3000-12-21

10006 docker    2018-01-02 2018-01-03

10006 openstack   2018-01-03 3000-12-21

10007 redis        2018-01-02 2018-01-03

10008 hadoop    2018-01-03 3000-12-21

拉链表原理分析：这里以10003用户为例，通过记录10003用户数据变化时间线我们可以发现如下的规律：

2017-01-01 首次注册，job为mysql；

2017-01-02 工作变更，job变为mongodb；

2017-01-03 工作变更，job变为hive。

在上图中，10003用户工作变更的时间线上，我们可以发现每一个时间点，10003用户只有一个工作。在20170101~20170102期间内10003的job为mysql，在20170102~20170103期间内10003的job为mongodb，在20170103~30001231期间内10003的job为hive。拉链表中每一个记录都满足上边规律，下面让我们想想怎么样准确的访问拉链表数据呢？

拉链表访问方法：

  1. 访问拉链表最新数据：

select * from customer t where t.end_date = '3000-12-31';

  1. 访问2017-01-01这天的历史快照数据：

select * from customer t where t.start_date <= '2017-01-01' and t.end_date > '2017-01-01';

3、访问2017-01-02这天的历史快照数据：

select * from customer t where t.start_date <= '2017-01-02' and t.end_date > '2017-01-02';

4、访问10003用户所有历史数据：

select * from customer t where t.cus_id = '10003';

三、拉链表实现步骤

1、准备数据：

1）2017-01-01初始化数据：

cus_id

job

start_date

end_date

dtype

dw_status

dw_ins_date

10001

oracle

2017-01-01

3000-12-31

C

I

2017-01-01

10002

pgsql

2017-01-01

3000-12-31

C

I

2017-01-01

10003

mysql

2017-01-01

3000-12-31

C

I

2017-01-01

10004

java

2017-01-01

3000-12-31

C

I

2017-01-01

10005

python

2017-01-01

3000-12-31

C

I

2017-01-01

2）2017-01-02增量数据：

cus_id

job

dw_status

dw_ins_date

10003

mongodb

U

2017-01-02

10004

java

D

2017-01-02

10006

docker

I

2017-01-02

10007

redis

I

2017-01-02

3）2017-01-03增量数据：

cus_id

job

dw_status

dw_ins_date

10003

hive

U

2017-01-03

10007

redis

D

2017-01-03

10006

openstack

U

2017-01-03

10008

hadoop

I

2017-01-03

2、数据加载过程：

  1. 初始化customer表：

drop table customer;

create table customer(

cus_id         int,

job            varchar2(20),

start_date     varchar2(10),

end_date       varchar2(10),

dtype          varchar2(1),

dw_status      varchar2(1),

dw_ins_date    varchar2(10)

)

partition by list(end_date)

(

partition cus_par20170101 values('2017-01-01') tablespace users,

partition cus_par20170102 values('2017-01-02') tablespace users,

partition cus_par20170103 values('2017-01-03') tablespace users,

partition cus_par30001231 values('3000-12-31') tablespace users

);

insert into customer(cus_id,job,start_date,end_date,dtype,dw_status,dw_ins_date) values (10001,'oracle','2017-01-01','3000-12-31','C','I','2017-01-01');

insert into customer(cus_id,job,start_date,end_date,dtype,dw_status,dw_ins_date) values (10002,'pgsql','2017-01-01','3000-12-31','C','I','2017-01-01');

insert into customer(cus_id,job,start_date,end_date,dtype,dw_status,dw_ins_date) values (10003,'mysql','2017-01-01','3000-12-31','C','I','2017-01-01');

insert into customer(cus_id,job,start_date,end_date,dtype,dw_status,dw_ins_date) values (10004,'java','2017-01-01','3000-12-31','C','I','2017-01-01');

insert into customer(cus_id,job,start_date,end_date,dtype,dw_status,dw_ins_date) values (10005,'python','2017-01-01','3000-12-31','C','I','2017-01-01');

  1. 初始化2017-01-02号增量表：

create table customer_inc(

cus_id         int,

job            varchar2(20),

dw_status      varchar2(1),

dw_ins_date    varchar2(10)

);

truncate table customer_inc;

insert into customer_inc(cus_id,job,dw_status,dw_ins_date)values(10003,'mongodb','U','2017-01-02');

insert into customer_inc(cus_id,job,dw_status,dw_ins_date)values(10004,'java','D','2017-01-02');

insert into customer_inc(cus_id,job,dw_status,dw_ins_date)values(10006,'docker','I','2017-01-02');

insert into customer_inc(cus_id,job,dw_status,dw_ins_date)values(10007,'redis','I','2017-01-02');

  1. 创建中间表：

drop table customer_tmp0;

create table customer_tmp0(

cus_id         int,

job            varchar2(20),

start_date     varchar2(10),

end_date       varchar2(10),

dtype          varchar2(1),

dw_status      varchar2(1),

dw_ins_date    varchar2(10)

)

partition by list(dtype)

(

partition cus_dtype_H values('H') tablespace users,

partition cus_dtype_C values('C') tablespace users

);

3、刷新customer_inc表数据到customer表（2017-01-02）：

  1. customer表最新分区和customer_inc表中更新和删除数据连接，处理customer最新分区中变化数据：

insert into customer_tmp0

select 

t1.cus_id,

t1.job,

t1.start_date,

case when t2.cus_id is null then t1.end_date else '2017-01-02' end as end_date,

case when t2.cus_id is null then 'C' else 'H' end dtype,

case when t2.cus_id is null then t1.dw_status else t2.dw_status end dw_status,

case when t2.cus_id is null then t1.dw_ins_date else t2.dw_ins_date end as dw_ins_date

from customer t1 left join customer_inc t2 on t1.cus_id = t2.cus_id and t2.dw_status in ('D','U')

where t1.end_date = '3000-12-31'

order by cus_id asc

;

2）将customer表中更新和插入数据插入到customer_tmp0临时表中：

insert into customer_tmp0

select 

t1.cus_id,

t1.job,

'2017-01-02' as start_date,

'3000-12-31' as end_date,

'C' as dtype,

t1.dw_status,

'2017-01-03' as dw_ins_date

from customer_inc t1

where t1.dw_status in ('I','U')

;

3）同步表到customer事实表，这一步可以使用交换分区操作：

alter table customer truncate partition cus_par30001231;

insert into customer

select * from customer_tmp0;

4）查看结果：

SQL> select * from customer order by cus_id asc;

    CUS_ID JOB                  START_DATE END_DATE   DTYPE DW_STATUS DW_INS_DATE

---------- -------------------- ---------- ---------- ----- --------- -----------

     10001 oracle               2017-01-01 3000-12-31 C     I         2017-01-01

     10002 pgsql                2017-01-01 3000-12-31 C     I         2017-01-01

     10003 mysql                2017-01-01 2017-01-02 H     U         2017-01-02

     10003 mongodb              2017-01-02 3000-12-31 C     U         2017-01-03

     10004 java                 2017-01-01 2017-01-02 H     D         2017-01-02

     10005 python               2017-01-01 3000-12-31 C     I         2017-01-01

     10006 docker               2017-01-02 3000-12-31 C     I         2017-01-03

     10007 redis                2017-01-02 3000-12-31 C     I         2017-01-03

8 rows selected

SQL>

4、刷新customer_inc表数据到customer表（2017-01-03）

1）初始化2017-01-02号增量表：

truncate table customer_inc;

insert into customer_inc(cus_id,job,dw_status,dw_ins_date)values(10003,'hive','U','2017-01-03');

insert into customer_inc(cus_id,job,dw_status,dw_ins_date)values(10008,'hadoop','I','2017-01-03');

insert into customer_inc(cus_id,job,dw_status,dw_ins_date)values(10006,'openstack','U','2017-01-03');

insert into customer_inc(cus_id,job,dw_status,dw_ins_date)values(10007,'redis','D','2017-01-03');

  1. customer表最新分区和customer_inc表中更新和删除数据连接，处理customer最新分区中变化数据：

truncate table customer_tmp0;

insert into customer_tmp0

select 

t1.cus_id,

t1.job,

t1.start_date,

case when t2.cus_id is null then t1.end_date else '2017-01-03' end as end_date,

case when t2.cus_id is null then 'C' else 'H' end dtype,

case when t2.cus_id is null then t1.dw_status else t2.dw_status end dw_status,

case when t2.cus_id is null then t1.dw_ins_date else t2.dw_ins_date end as dw_ins_date

from customer t1 left join customer_inc t2 on t1.cus_id = t2.cus_id and t2.dw_status in ('D','U')

where t1.end_date = '3000-12-31'

order by cus_id asc

;

3）将customer表中更新和插入数据插入到customer_tmp0临时表中：

insert into customer_tmp0

select 

t1.cus_id,

t1.job,

'2017-01-03' as start_date,

'3000-12-31' as end_date,

'C' as dtype,

t1.dw_status,

'2017-01-04' as dw_ins_date

from customer_inc t1

where t1.dw_status in ('I','U')

;

  1. 表到customer事实表，这一步可以使用交换分区操作：

alter table customer truncate partition cus_par30001231;

insert into customer

select * from customer_tmp0;

  1. 查看结果

SQL> select * from customer order by cus_id asc;

      CUS_ID JOB                  START_DATE END_DATE   DTYPE DW_STATUS DW_INS_DATE

----------- -------------------- ---------- ---------- ----- --------- -----------

      10001 oracle               2017-01-01 3000-12-31 C     I         2017-01-01

      10002 pgsql                2017-01-01 3000-12-31 C     I         2017-01-01

      10003 mongodb              2017-01-02 2017-01-03 H     U         2017-01-03

      10003 hive                 2017-01-03 3000-12-31 C     U         2017-01-04

      10003 mysql                2017-01-01 2017-01-02 H     U         2017-01-02

      10004 java                 2017-01-01 2017-01-02 H     D         2017-01-02

      10005 python               2017-01-01 3000-12-31 C     I         2017-01-01

      10006 docker               2017-01-02 2017-01-03 H     U         2017-01-03

      10006 openstack            2017-01-03 3000-12-31 C     U         2017-01-04

      10007 redis                2017-01-02 2017-01-03 H     D         2017-01-03

      10008 hadoop               2017-01-03 3000-12-31 C     I         2017-01-04

11 rows selected

SQL>文章来源地址https://www.toymoban.com/news/detail-839435.html

5、查询拉链表：

  1. 查询拉链表最新数据：

SQL> select * from customer where end_date = '3000-12-31' order by cus_id asc;

   CUS_ID JOB                  START_DATE END_DATE   DTYPE DW_STATUS DW_INS_DATE

--------- -------------------- ---------- ---------- ----- --------- -----------

    10001 oracle               2017-01-01 3000-12-31 C     I         2017-01-01

    10002 pgsql                2017-01-01 3000-12-31 C     I         2017-01-01

    10003 hive                 2017-01-03 3000-12-31 C     U         2017-01-04

    10005 python               2017-01-01 3000-12-31 C     I         2017-01-01

    10006 openstack            2017-01-03 3000-12-31 C     U         2017-01-04

    10008 hadoop               2017-01-03 3000-12-31 C     I         2017-01-04

6 rows selected

SQL>

  1. 查询2017-01-01历史快照数据：

SQL> select * from customer where start_date <= '2017-01-01' and end_date > '2017-01-01' order by cus_id asc;

   CUS_ID JOB                  START_DATE END_DATE   DTYPE DW_STATUS DW_INS_DATE

--------- -------------------- ---------- ---------- ----- --------- -----------

    10001 oracle               2017-01-01 3000-12-31 C     I         2017-01-01

    10002 pgsql                2017-01-01 3000-12-31 C     I         2017-01-01

    10003 mysql                2017-01-01 2017-01-02 H     U         2017-01-02

    10004 java                 2017-01-01 2017-01-02 H     D         2017-01-02

    10005 python               2017-01-01 3000-12-31 C     I         2017-01-01

SQL>

3）查询2017-01-02历史快照数据：

SQL> select * from customer where start_date <= '2017-01-02' and end_date > '2017-01-02' order by cus_id asc;

    CUS_ID JOB                  START_DATE END_DATE   DTYPE DW_STATUS DW_INS_DATE

---------- -------------------- ---------- ---------- ----- --------- -----------

     10001 oracle               2017-01-01 3000-12-31 C     I         2017-01-01

     10002 pgsql                2017-01-01 3000-12-31 C     I         2017-01-01

     10003 mongodb              2017-01-02 2017-01-03 H     U         2017-01-03

     10005 python               2017-01-01 3000-12-31 C     I         2017-01-01

     10006 docker               2017-01-02 2017-01-03 H     U         2017-01-03

     10007 redis                2017-01-02 2017-01-03 H     D         2017-01-03

6 rows selected

SQL>

4）查看10003用户的所有数据：

SQL> select * from customer where cus_id = '10003';

    CUS_ID JOB                  START_DATE END_DATE   DTYPE DW_STATUS DW_INS_DATE

---------- -------------------- ---------- ---------- ----- --------- -----------

     10003 mysql                2017-01-01 2017-01-02 H     U         2017-01-02

     10003 mongodb              2017-01-02 2017-01-03 H     U         2017-01-03

     10003 hive                 2017-01-03 3000-12-31 C     U         2017-01-04

SQL>

到了这里，关于数据仓库保存历史数据方法之拉链表的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处： 如若内容造成侵权/违法违规/事实不符，请点击违法举报进行投诉反馈，一经查实，立即删除！

分享到：
领支付宝红包 赞助服务器费用

相关文章

  • 业务测试——历史数据

    业务测试历史数据的必要性 1.保留上一版本的呈现效果以及数据正确性 2.做发版前后数据、样式一致性校验 3.后端处理历史数据，覆盖各类场景，保证客户的现有数据不会被影响，造成线上事务 4.为测试过程的覆盖度以及产品迭代的质量保驾护航 如何做历史数据(发版前截图

    2024年02月14日
    浏览(28)
  • 网站域名历史记录批量查询-老域名建站历史快照数据查询

    网站域名历史记录批量查询-老域名建站历史快照数据查询

      域名建站历史查询软件是一种用于查询一个域名被使用的网站的历史记录的工具。它可以提供许多有用的信息，包括该网站的创建和修改日期、使用的网站建设平台、使用的CMS系统、网站的历史页面内容和页面结构等。 域名建站历史查询软件的作用是帮助网站管理员、S

    2024年02月08日
    浏览(131)
  • TDengine（taos）数据库导出历史数据

    TDengine（taos）数据库导出历史数据

    业务需求：导出某个站点的累计充电量，累计放电量，光伏总放电量，进线总功率的所有数据‘ 提示Database changed；即为使用成功； realId即为我想要导出的表，需要导出里面所有的字段； select * from yc_1680037147048042498_1011 /data.csv; 运行即可得到.csv文件； 注意导出路径里不能有

    2024年02月08日
    浏览(37)
  • 自动清理 ES 历史数据

    自动清理 ES 历史数据

    目录 一、 背景 二、解决方案 三、实现操作 三、合并定时任务的例子         随着业务的增长和时间的变化，ES 数据库的存储空间越来越大，存储数据多数为系统监控日志，保存的数据不需要长期保留，多数情况只需要保留几个月ES数据即可，既可以减轻ES服务器的负载和

    2024年02月08日
    浏览(35)
  • 新浪股票接口获取历史数据

    新浪股票接口获取历史数据

    这两天做了一个调用新浪股票接口获取实时以及历史股票数据的应用，因为新浪没有公开关于其接口的官方文档，所以通过各种百度差了很多关于新浪股票接口的使用，不过大家基本都是转载或者直接复制，对于实时数据的获取讲的很详细，但是缺少获取历史数据的方法。

    2024年02月10日
    浏览(36)
  • sql server删除历史数据

    sql server删除历史数据

    datediff函数 : datepart的取值可以是year,quarter,Month,dayofyear,Day,Week,Hour,minute,second,millisecond startdate 是从 enddate 减去。如果 startdate 比 enddate 晚，返回负值。 删除2023年以前的数据 运行结果如下：

    2024年02月10日
    浏览(37)

  • TiDB（5）：TiDB-读取历史数据

    接下来介绍 TiDB 如何读取历史版本数据，包括具体的操作流程以及历史数据的保存策略。 1 功能说明 TiDB 实现了通过标准 SQL 接口读取历史数据功能，无需特殊的 client 或者 driver。当数据被更新、删除后，依然可以通过 SQL 接口将更新/删除前的数据读取出来。 另外即使在更新

    2024年02月12日
    浏览(44)
  • 记一次Hbase2.1.x历史数据数据迁移方案

    记一次Hbase2.1.x历史数据数据迁移方案

    2024年02月07日
    浏览(30)
  • 数据泄露的平均成本创历史新高

    数据泄露的平均成本创历史新高

    IBM Security 发布了年度数据泄露成本报告，显示数据泄露的全球平均成本在 2023 年达到 445 万美元，创下该报告的历史新高，并且比过去 3 年增加了 15%。 检测和升级成本在同一时间段内跃升了 42%，占违规成本的最高部分，并表明转向更复杂的违规调查。 根据 2023 年 IBM 报告，

    2024年02月14日
    浏览(35)
  • 通联历史数据如何自动化导入 DolphinDB

    通联历史数据如何自动化导入 DolphinDB

    在部署完 DolphinDB 后，需要将历史数据批量导入数据库，再进行数据查询、计算和分析等操作。为便于用户快速导入通联历史 Level-2 行情数据，DolphinDB 开发了  DolphinDBModules::easyTLDataImport  模块（简称  easyTLDataImport  模块），主要用于通联历史 Level-2 行情数据的自动化导入，

    2024年02月11日
    浏览(27)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包，优惠每天领

二维码1

领取红包

二维码2

领红包