Clickhouse建表

一、ReplacingMergeTree存储引擎

--drop table doorku.dm_delivery_rate_billno_di on cluster default;

create table doorku.dm_delivery_rate_billno_di on cluster default
(
billno String COMMENT '主单号'
,source_dept_code String COMMENT '站点编码'
,source_dept_name String COMMENT '站点名称'
,enter_net_code String COMMENT '中心代码'
,inc_day String comment '统计日期'
,insertTime DateTime MATERIALIZED now() comment '数据写入时间')
Engine=ReplacingMergeTree () partition by (inc_day)
order by (billno,source_dept_code,enter_net_code)
ttl insertTime + interval 100 day; --保留近100天数据

create table doorku.dm_delivery_rate_billno_di_dumb on cluster default
Engine=ReplacingMergeTree () partition by (inc_day)
order by (billno,source_dept_code,enter_net_code)
ttl insertTime + interval 100 day as doorku.dm_delivery_rate_billno_di;

---为最终要查询的表
create table doorku.dm_delivery_rate_billno_di_n on cluster default
Engine=Distributed(default,doorku,dm_delivery_rate_billno_di,rand()) as doorku.dm_delivery_rate_billno_di;

 

导入前准备语句:
--alter table doorku.dm_delivery_rate_billno_di_dumb drop partition '$[time(yyyyMMdd,-1d)]' ;
truncate table if exists doorku.dm_delivery_rate_billno_di_dumb;

导入后准备语句:
select sleepEachRow(1) from system.numbers limit 3;
alter table doorku.dm_delivery_rate_billno_di replace partition '$[time(yyyyMMdd,-1d)]' from doorku.dm_delivery_rate_billno_di_dumb;
alter table doorku.dm_delivery_rate_billno_di replace partition '$[time(yyyyMMdd,-2d)]' from doorku.dm_delivery_rate_billno_di_dumb;
alter table doorku.dm_delivery_rate_billno_di replace partition '$[time(yyyyMMdd,-3d)]' from doorku.dm_delivery_rate_billno_di_dumb;
alter table doorku.dm_delivery_rate_billno_di replace partition '$[time(yyyyMMdd,-4d)]' from doorku.dm_delivery_rate_billno_di_dumb;
alter table doorku.dm_delivery_rate_billno_di replace partition '$[time(yyyyMMdd,-5d)]' from doorku.dm_delivery_rate_billno_di_dumb;
alter table doorku.dm_delivery_rate_billno_di replace partition '$[time(yyyyMMdd,-6d)]' from doorku.dm_delivery_rate_billno_di_dumb;
alter table doorku.dm_delivery_rate_billno_di replace partition '$[time(yyyyMMdd,-7d)]' from doorku.dm_delivery_rate_billno_di_dumb;
alter table doorku.dm_delivery_rate_billno_di replace partition '$[time(yyyyMMdd,-8d)]' from doorku.dm_delivery_rate_billno_di_dumb;
alter table doorku.dm_delivery_rate_billno_di replace partition '$[time(yyyyMMdd,-9d)]' from doorku.dm_delivery_rate_billno_di_dumb;
alter table doorku.dm_delivery_rate_billno_di replace partition '$[time(yyyyMMdd,-10d)]' from doorku.dm_delivery_rate_billno_di_dumb;

二、ReplicatedReplacingMergeTree存储引擎，分布式

create table doorku.dm_delivery_rate_waybill_new_di on cluster default
(
billno String COMMENT '主单号'
,waybillno String COMMENT '单号'
,source_dept_code String COMMENT '站点编码'
,source_dept_name String COMMENT '站点名称'
,enter_net String COMMENT '网中心名称'
,enter_net_code String COMMENT '网中心代码'
,inc_day String comment '统计日期'
,insertTime DateTime MATERIALIZED now() comment '数据写入时间')
Engine=ReplicatedReplacingMergeTree('/tables/doorku/dm_delivery_rate_waybill_new_di/{shard}', '{replica}') partition by (inc_day)
order by (billno,waybillno,source_dept_code,enter_net_code)
ttl insertTime + interval 100 day;

create table doorku.dm_delivery_rate_waybill_new_di_dumb on cluster default
Engine=ReplicatedReplacingMergeTree('/tables/doorku/dm_delivery_rate_waybill_new_di_dumb/{shard}', '{replica}') partition by (inc_day)
order by (billno,waybillno,source_dept_code,enter_net_code)
ttl insertTime + interval 100 day as doorku.dm_delivery_rate_waybill_new_di;

---为最终查询的表
create table doorku.dm_delivery_rate_waybill_new_di_n on cluster default
Engine=Distributed(default,doorku,dm_delivery_rate_waybill_new_di,rand()) as doorku.dm_delivery_rate_waybill_new_di;

导入前准备语句:
alter table doorku.dm_delivery_rate_waybill_new_di_dumb drop partition '$[time(yyyyMMdd,-1d)]' ;
alter table doorku.dm_delivery_rate_waybill_new_di_dumb drop partition '$[time(yyyyMMdd,-2d)]' ;
alter table doorku.dm_delivery_rate_waybill_new_di_dumb drop partition '$[time(yyyyMMdd,-3d)]' ;
alter table doorku.dm_delivery_rate_waybill_new_di_dumb drop partition '$[time(yyyyMMdd,-4d)]' ;
alter table doorku.dm_delivery_rate_waybill_new_di_dumb drop partition '$[time(yyyyMMdd,-5d)]' ;
alter table doorku.dm_delivery_rate_waybill_new_di_dumb drop partition '$[time(yyyyMMdd,-6d)]' ;
alter table doorku.dm_delivery_rate_waybill_new_di_dumb drop partition '$[time(yyyyMMdd,-7d)]' ;
alter table doorku.dm_delivery_rate_waybill_new_di_dumb drop partition '$[time(yyyyMMdd,-8d)]' ;
alter table doorku.dm_delivery_rate_waybill_new_di_dumb drop partition '$[time(yyyyMMdd,-9d)]' ;
alter table doorku.dm_delivery_rate_waybill_new_di_dumb drop partition '$[time(yyyyMMdd,-10d)]';

 

导入后准备语句:
select sleepEachRow(1) from system.numbers limit 3;
alter table doorku.dm_delivery_rate_waybill_new_di replace partition '$[time(yyyyMMdd,-1d)]' from doorku.dm_delivery_rate_waybill_new_di_dumb;
alter table doorku.dm_delivery_rate_waybill_new_di replace partition '$[time(yyyyMMdd,-2d)]' from doorku.dm_delivery_rate_waybill_new_di_dumb;
alter table doorku.dm_delivery_rate_waybill_new_di replace partition '$[time(yyyyMMdd,-3d)]' from doorku.dm_delivery_rate_waybill_new_di_dumb;
alter table doorku.dm_delivery_rate_waybill_new_di replace partition '$[time(yyyyMMdd,-4d)]' from doorku.dm_delivery_rate_waybill_new_di_dumb;
alter table doorku.dm_delivery_rate_waybill_new_di replace partition '$[time(yyyyMMdd,-5d)]' from doorku.dm_delivery_rate_waybill_new_di_dumb;
alter table doorku.dm_delivery_rate_waybill_new_di replace partition '$[time(yyyyMMdd,-6d)]' from doorku.dm_delivery_rate_waybill_new_di_dumb;
alter table doorku.dm_delivery_rate_waybill_new_di replace partition '$[time(yyyyMMdd,-7d)]' from doorku.dm_delivery_rate_waybill_new_di_dumb;
alter table doorku.dm_delivery_rate_waybill_new_di replace partition '$[time(yyyyMMdd,-8d)]' from doorku.dm_delivery_rate_waybill_new_di_dumb;
alter table doorku.dm_delivery_rate_waybill_new_di replace partition '$[time(yyyyMMdd,-9d)]' from doorku.dm_delivery_rate_waybill_new_di_dumb;
alter table doorku.dm_delivery_rate_waybill_new_di replace partition '$[time(yyyyMMdd,-10d)]' from doorku.dm_delivery_rate_waybill_new_di_dumb;