跳至主要內容

基于订单的拉链表

添码座原创大约 6 分钟案例数据仓库案例数据仓库商品订单拉链表

什么是拉链表

对于订单、账单流水这类数据表，如果将它们导入到数据仓库中会有一些比较特殊的地方。

  • 它们的数据量比较大。尤其是处于大促或者某些运营活动期间，每天的订单量数据可能会非常多。

  • 它们有一些中间字段。例如，订单状态、支付状态等。记录更新前后除了状态字段的值不同，其他字段的值完全一样。

  • 不能单纯按照时间来采集数据，因为用户可能今天下单，但转钟隔夜（也就是第二天才）支付。

所以如果每天都对它们执行全量导入，不仅非常浪费磁盘空间，而且统计的结果也可能不准确，但又不能用增量导入的方式，因为Hive是不支持数据更新的。

这个时候拉链表就派上用场了，它会在增量导入的方式之上进行完善，只把变化的数据导入进来，这样既不会造成大量的数据冗余，也能追溯历史状态。

所谓拉链表，就是一种数据仓库中表存储的优化方式，它会记录某条数据从开始，直到当前状态的所有历史变化的值。

下面就是一张简单的拉链表实例。

拉链表的初始值。

订单号订单状态开始时间结束时间说明
1已创建2024-01-019999-12-31初始数据
2已创建2024-01-019999-12-31初始数据
3已创建2024-01-019999-12-31初始数据

向拉链表中添加数据后。

订单号订单状态开始时间结束时间说明
1已创建2024-01-019999-12-31初始数据
2已创建2024-01-019999-12-31初始数据
3已创建2024-01-012024-01-01初始数据
3已支付2024-01-029999-12-31修改
4已创建2024-01-039999-12-31新增

上面的开始时间和结束时间分别表示某条记录的生命周期开始时间和生命周期结束时间。

  • 初始时，所有记录的生命周期都是从当前时间（例如，2024-01-01）至无限期（例如，9999-12-31），生命周期结束时间为9999-12-31的记录都是有效数据。

  • 当有新的记录插入进来，且和原纪录的订单号相同时，会将原纪录的生命周期结束时间改掉，然后新纪录的生命周期开始时间增加，例如，订单号为3的记录。

因此，如果要查询当前表中所有的有效记录就非常简单了。

> select * from order where endtime = '9999-12-31';

而要查询2024-01-02数据的历史快照，则可以使用下面的查询语句实现。

> select * from order where begintime <= `2024-01-02` and endtime >= '2024-01-02';

生成拉链表

假如有像下面这样的订单数据。

订单数据示例
订单数据示例

生成拉链表的一般过程如下。

  • 首先构建初始的拉链表，新增加两个字段begintime和endtime。默认情况下，begintime就是数据创建时间，例如，2024-01-01，而endtime则为9999-12-31。
构建初始拉链表
构建初始拉链表

  • 然后在事件发生变化时，例如，在2024-01-02时，需要将订单表中发生了变化的数据和新增的数据整合到拉链表中。

    • 此时要先创建一个日更表，将每日新增和变化了的数据保存到其中。
    日更新表
    日更新表
    • 然后对拉链表和日更表执行left join，如果可以关联，就说明该订单的状态发生了变化。
    对拉链表和日更表执行left join
    对拉链表和日更表执行left join
    • 再将拉链表中变化了的数据的endtime修改为2024-01-01，也就是2024-01-02 - 1。
    修改日更表中的数据
    修改日更表中的数据
    • 最后拉链表再和日更表中的数据执行union all，日更表中的数据添加begintime和endtime字段后将其插入到拉链表中。
    最后生成的拉链表
    最后生成的拉链表

ODS层拉链表

可以将前面ODS层的ods_order以拉链表的方式实现数据采集。

  • 首先，通过Sqoop从MySQL的t_order表中抽取数据到HDFS，依据createtime和updatetime这两个字段，抽取2024-02-01~2024-02-03新增和变化的订单数据。

  • 然后，将HDFS中的数据加载到ODS层的ods_order表。

> alter table ods_shopmall.ods_order add if not exists partition(dt = '20240201') location '20240201';
> alter table ods_shopmall.ods_order add if not exists partition(dt = '20240202') location '20240202';
> alter table ods_shopmall.ods_order add if not exists partition(dt = '20240203') location '20240203';
  • 接着，在DWD层的dwd_order表中添加分区和基于日更表来创建拉链表。
> cd /home/work/warehouse_goods_order
> vi dwd_shopmall_order_zipper.sh

#!/bin/bash
# 添加分区和创建拉链表

hive -e "
insert overwrite table dwd_shopmall.dwd_order partition(dt = '20240201')
select
    id,
    userid,
    money,
    type,
    status,
    payid,
    createtime,
    updatetime
from ods_shopmall.ods_order
where dt = '20260201' and id is not null;

insert overwrite table dwd_shopmall.dwd_order partition(dt = '20240202')
select
    id,
    userid,
    money,
    type,
    status,
    payid,
    createtime,
    updatetime
from ods_shopmall.ods_order
where dt = '20260202' and id is not null;

insert overwrite table dwd_shopmall.dwd_order partition(dt = '20240203')
select
    id,
    userid,
    money,
    type,
    status,
    payid,
    createtime,
    updatetime
from ods_shopmall.ods_order
where dt = '20260203' and id is not null;

create external table if not exists dws_shopmall.dws_order_zipper (
    id               bigint,
    userid           bigint,
    money            double,
    type             int,
    status           int,
    payid            bigint,
    createtime       string,
    updatetime       string,
    begintime        string,
    endtime          string
) row format delimited  
  fields terminated by '\t'
  location 'hdfs://server01:9000/data/dws/order_zipper/';
"
  • 建好拉链表之后，就需要往其中填充数据了。
> cd /home/work/warehouse_goods_order
> vi dwd_load_data_to_order_zipper.sh

#!/bin/bash
# 往拉链表中填充数据
if [ "d$1" = "d" ]
then
dt=`date +%Y%m%d --date="1 days ago"`
else
dt=$1
fi

# 日期格式转换，将 20240201 转换为 2024-02-01
dt_new=`date +%Y-%m-%d --date="${dt}"`

hive -e "
insert overwrite table dws_shopmall.dws_order_zipper
select
    id,
    userid,
    money,
    type,
    status,
    payid,
    createtime,
    updatetime,
    begintime,
    endtime
from
(
    select
        doz.id,
        doz.userid,
        doz.money,
        doz.type,
        doz.status,
        doz.payid,
        doz.createtime,
        doz.updatetime,
        doz.begintime,
        case
            when doz.endtime = '9999-12-31' and do.id is not null then date_add('${dt_new}', -1)
        else doz.endtime
        end as endtime
    from dws_shopmall.dws_order_zipper as doz
    left join
    (
        select id from dws_shopmall.dwd_order
        where dt = '${dt}'
    ) as do
    on doz.id = do.id
union all
    select
        do.id,
        do.userid,
        do.money,
        do.type,
        do.status,
        do.payid,
        do.createtime,
        do.updatetime,
        '${dt_new}' as begintime,
        '9999-12-31' as endtime
    from dwd_shopmall.dwd_order as do
    where do.dt = '${dt}'
) as t;
"
  • 然后执行脚本，填充数据。
> cd /home/work/warehouse_goods_order
> sh dwd_load_data_to_order_zipper.sh 20240201
> sh dwd_load_data_to_order_zipper.sh 20240202
> sh dwd_load_data_to_order_zipper.sh 20240203

创建并初始化完拉链表之后，就可以按照之前查询有效记录和历史快照的方式检索数据了。

-- 查询有效记录
> select * from order where endtime = '9999-12-31';

-- 查询历史快照
> select * from order where begintime <= `2024-02-02` and endtime >= '2024-02-02';

可以通过定时任务来定期更新拉链表。

> crontab -e

# 每天凌晨0点1分执行
1 0 * * * /home/work/warehouse_goods_order/dwd_load_data_to_order_zipper.sh

性能问题

当数据量增大的时候，拉链表和普通的数据表一样，也会面临性能问题，但它们的解决方法都是类的。

  • 可以尝试增加索引：一个针对endtime的独立索引，以及一个针对begintime和endtime的联合索引。

  • 可以尝试冷热分离：将全量数据保存在外部磁盘中，仅在内存或经常访问的机器上暴露近一年的拉链数据。



感谢支持

更多内容，请移步《超级个体》。