数据仓库中的拉链表（Clickhouse 实现) 前 言 拉链表是数据仓库中常用的一种保存历史数据的表。曾经 CK 因为不支持 Update 和 delete，从而不能支持拉链表。本文主要研究 CK 拉链表如何实现。值得注意的是 CK 对 Update 的是有限支持，不支持关联更新操作。因此，本文通过 Delete 和 insert 实现拉链表。 在 Clickhouse 中实现拉链表 我们需要一张 ODS 层用户全量表至，至少少需要用它来初始化。 一、每日的用户更新表： 而且我们要确定拉链表的时间粒度，比如说拉链表每天只取一个状态，也就是说如果一 天有 3 个状态变更，我们只取最后一个状态，这种天粒度的表其实已经能解决大部分的问题 了。 另外，补充一下每日的用户更新表该怎么获取，据笔者的经验，有 3 种方式拿到或者间 接拿到每日的用户增量，因为它比较重要，所以详细说明： 我们可以监听 Mysql 数据的变化，比如说用 Canal，最后合并每日的变化，获取到最后 的一个状态。 假设我们每天都会获得一份切片数据，我们可以通过取两天切片数据的不同来作为每日 更新表，这种情况下我们可以对所有的字段先进行 concat，再取 md5，这样就 ok 了。 流水表！有每日的变更流水表。 二、ods 层的 user 表： 现在我们来看一下我们 ods 层的用户资料切片表的结构： CREATE TABLE IF NOT EXISTS user ( user_num String DEFAULT '', mobile String DEFAULT '', reg_date Date ) ENGINE = MergeTree(reg_date, (user_num, reg_date), 8192); 

数据初始化： Insert into user values('00001', '13805180001', '2018-1-1'); Insert into user values('00002', '13805180002', '2018-1-1'); Insert into user values('00003', '13805180003', '2018-1-1'); Insert into user values('00004', '13805180004', '2018-1-1'); Insert into user values('00005', '13805180005', '2018-1-1'); Insert into user values('00006', '13805180006', '2018-1-1'); Insert into user values('00007', '13805180007', '2018-1-1'); Insert into user values('00008', '13805180008', '2018-1-1'); ods 层的 user_update 表 然后我们还需要一张用户每日更新表，前面已经分析过该如果得到这张表，现在我们假 设它已经存在。 CREATE TABLE IF NOT EXISTS user_update ( user_num String DEFAULT '', mobile String DEFAULT '', reg_date Date ) ENGINE = MergeTree(reg_date, (user_num, reg_date), 8192); 更新数据： Insert into user_update values('00001', '13805180011', '2018-1-10'); Insert into user_update values('00002', '13805180012', '2018-1-10'); Insert into user_update values('00003', '13805180013', '2018-1-10'); Insert into user_update values('00004', '13805180014', '2018-1-10'); Insert into user_update values('00005', '13805180015', '2018-1-10'); Insert into user_update values('00006', '13805180016', '2018-1-10'); Insert into user_update values('00007', '13805180017', '2018-1-10'); Insert into user_update values('00008', '13805180018', '2018-1-10'); Insert into user_update values('00009', '13805180009', '2018-1-10'); 

拉链表： 现在我们创建一张拉链表： CREATE TABLE IF NOT EXISTS user_his ( user_num String DEFAULT '', mobile String DEFAULT '', reg_date Date, t_start_time Date, t_end_time Date ) ENGINE = MergeTree(reg_date, (user_num, reg_date, t_start_time, t_end_time), 8192); 拉链表初始化： truncate table user_his; insert into user_his select user_num, mobile, reg_date, reg_date as t_start_time, '2099-12-31' as t_end_time from user; 实现 sql 语句： 创建用户历史临时表： CREATE TABLE IF NOT EXISTS user_his_temp ( user_num String DEFAULT '', mobile String DEFAULT '', reg_date Date, t_start_time Date, t_end_time Date ) ENGINE = MergeTree(reg_date, (user_num, reg_date,t_start_time,t_end_time), 

8192); 拉链处理： Truncate table user_his_temp; // 取用户历史表中老用户手机号码、 insert into user_his_temp SELECT B.user_num, B.mobile, B.reg_date, B.t_start_time, today() as t_end_time FROM user_update AS A ANY inner JOIN user_his AS B ON A.user_num = B.user_num; // 插入老用户新数据，并将结束时间设为'2099-12-31' insert into user_his_temp SELECT user_num, mobile, reg_date, today() as t_start_time, '2099-12-31' as t_end_time FROM user_update; // 删除用户历史表中的老用户 Alter table user_his delete where user_num in (select user_num from user_his_temp) and mobile in (select mobile from user_his_temp); // 将用户历史临时表数据插入到用户历史表中 Insert into user_his select * from user_his_temp; 

拉链表和流水表： 流水表存放的是一个用户的变更记录，比如在一张流水表中，一天的数据中，会存放一 个用户的每条修改记录，但是在拉链表中只有一条记录。 这是拉链表设计时需要注意的一个粒度问题。我们当然也可以设置的粒度更小一些，一 般按天就足够。 查询性能： 拉链表当然也会遇到查询性能的问题，比如说我们存放了 5 年的拉链数据，那么这张表 势必会比较大，当查询的时候性能就比较低了，个人认为两个思路来解决： 在一些查询引擎中，我们对 start_date 和 end_date 做索引，这样能提高不少性能。 保留部分历史数据，比如说我们一张表里面存放全量的拉链表数据，然后再对外暴露一张只 提供近 3 个月数据的拉链表。 注意：update 时，不能更新主键字段。