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;
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')
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