数仓存储架构为:hive + spark

1、拉链表

https://blog.csdn.net/weixin_40444678/article/details/81083614 https://blog.csdn.net/saqin6255/article/details/80362248

适用场景

  • 数据量较大
  • 表中存在某些字段会被更新
  • 需要查看某一时段的系统快照
  • 变化比例和频率不大(每天新增或变更不大约1%)

如果每天储存全量快照,将存在大量冗余,极大浪费存储

实现方式

业务数据库中的一张表对应数仓中的3张表

  • ODS的全量表
    • 字段:全部字段
    • 来源:首张表 dump 数据库,其余每日通过 merge 流水表
    • 分区:date,每天全量
    • ttl:一般较短(1个月以下)
  • 每日变更流水表(更新或者新增)
    • 字段:全部字段
    • 来源:使用canal从binlog获取
    • 分区:date,每天变更
    • ttl:可以设置较长时间(一个月左右)
  • 拉链表
    • 字段:全量表的主键 + 允许更新的字段 + 状态区间(start_time, end_time)
    • 来源:(前一天全量数据 join 当天流水表 (修改旧的end_time为当天)) union all (变更流水表)
    • 分区:date,每天全量拉链
    • ttl:可以设置较长时间(为了方便回溯),可以考虑按年归档

例子:用户表

业务表定义

  • user_id (主键)
  • username:用户名(不允许改变)
  • reg_time:注册时间(不允许改变)
  • mobile:手机号(允许修改、可能发生变更)

数仓侧表结构

user_update 增量表

  • 表定义
    • user_id (主键)
    • username:用户名(不允许改变)
    • reg_time:注册时间(不允许改变)
    • mobile:手机号(允许修改、可能发生变更)
    • date:日期,分区字段,每天保存变化的部分
  • 数据来源
    • canal binlog(每天调度)

user 全量表

  • 表定义
    • user_id (主键)
    • username:用户名(不允许改变)
    • reg_time:注册时间(不允许改变)
    • mobile:手机号(允许修改、可能发生变更)
    • date,日期,分区字段,全量数据
  • 数据来源
    • ((今天user_update) union all (昨天user)) 然后使用row_number优先选取update的 (每天调度)

user_his 拉链表

  • 表定义
    • user_id (主键)
    • mobile:手机号(允许修改、可能发生变更)
    • date,日期,分区字段,全量拉链
    • start_time
    • end_time
  • 数据来源
    • ((今天user_update) union all (昨天user_his, 修改掉需更新的end_time=99991231为今天))

2、查询数据以字符串的格式保存在hdfs

# 保存到本地
insert overwrite local directory "/tmp/xxx"
select xxx

# 保存到hdfs
insert overwrite  directory "/tmp/mtdata/trans/201810/" ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
select xxx