ClickHouse数仓全量更新和增量更新思路

1.全量更新

• 使用clickhouse的jdbc连接别的数据库时,如果拉取一个大数据量的表时，存在io和内存限制可能会导致mermoy limit 报错，如 1亿数据量，会导致数据拉不到，任务失败小数据量的表可以考虑全量更新。
• 先全量更新到接入层，接入层使用的是带shardingkey的分布式表，从源库拉的数直接插入到分布式表中，避免数据倾斜，这里需注意添加cyhash 采用cityHash64对主键进行散列在通过cyhash的shardingkey往分布式表里插，并把插入时间的时间字段即odg_dt取now() 打上时间标签，方便查询，批量更新时时间字段有很大用处

insert into src.src_data select *,cityHash64(id) as cyhash,now() as odg_dt from jdbc('mysql100','select * from test.jierumysql);

#生成中间临时表
drop table if exists shard_detail.shard_detail_data_tmp;
create table shard_detail.shard_detail_data_tmp engine=MergeTree order by  ifNULL(cityHash64(ID),0) as select * from shard_detail.shard_detail_data where 1=0;

• 接入层到明细层数据,插入的数据按照最新时间的数据插入，如果是全量的话，Max(odg_dt)即为最新时间的全量数据

insert into shard_detail.data select * from shard_src.shard_src_data where  odg_dt=(select max(odg_dt) from  shard_src.shard_src_data)
#表更新
rename  table shard_detail.shard_detail_data to shard_detail.shard_detail_data_old
rename table shard_detail.shard_detail_data_tmp to shard_detail.shard_detail_data
drop table shard_detail.shard_detail_data_old

• 在把临时表和正式表实现命名互换，drop掉老表，rename临时表到新表
• 实现全量更新

---

2.增量更新

• 批量更新考虑的是数据更新，大表全量的非必要性
• 批量更新和全量更新的逻辑类似只是在明细层时做数据回插的工作
• 接入层也需要做出更改，从原先的全量变以时间戳筛选

SELECT * FROM jdbc('mysql100','select * from (select * from test.jierumysql WHERE LAST_UPDATE_DATE >= date_add(now(),INTERVAL -5 DAY)) as t')

• 这里是5天的数据量到接入层，接入层不考虑数据重复只存放拉取的数据，通过时间标签更新到明细层通过max(odg_time) 即查询最新的时间的数据，5天
• 增量数据相对与全量的数据多了一步数据回插

insert into shard_detail.shard_detail_data_tmp select * from shard_detail.shard_detail_data where  ID not in (select  ID from shard_detail.shard_detail_data_tmp)
rename  table shard_detail.shard_detail_data to shard_detail.shard_detail_data_old
rename table shard_detail.shard_detail_data_tmp to shard_detail.shard_detail_data
drop table shard_detail.shard_detail_data_old

• 数据回插可以很大效率的保证了数据完整性，相比与大数据量的全量更新，提升性能，大数据量源表，我只需拉一次数据，之后都通过增量更新来保证数据的完整，同时优化速率
• 数据更新时会不会造成主键重复？
• 当更新数据插入分布式表时相同id生成的hash值一样，再分发的时候会插入到同一台服务器上
• 新的数据和老的数据会在同一台机器上，不会存在分布在不同的服务器上
• 数据回插中的id not in 就保证了当临时表里存在这条id的新数据，老数据不会回插到临时表中实现数据的更新，数据增量更新需要保证主键唯一。