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

原创已于 2023-04-28 16:17:51 修改 · 2.8k 阅读

9 ·

CC 4.0 BY-SA版权

文章标签：

#clickhouse #数据库 #sql

于 2023-04-23 14:26:51 首次发布

文章讲述了在使用Clickhouse的JDBC连接处理大数据量时，如何通过全量更新和增量更新策略避免内存和IO限制。全量更新将数据插入带ShardingKey的分布式表，利用cityHash64避免数据倾斜。增量更新则基于时间戳筛选数据，通过数据回插确保数据完整性，优化性能并保证主键唯一。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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