python clickhouse批量写入 clickhouse 批量更新

转载

mob64ca14144dde 2023-09-23 06:58:36

文章标签 clickhouse 数据库 sql 数据 mysql 文章分类 Python 后端开发

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