数仓存储架构为：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