1. 建clickhouse源表_本地表
create table test.trade_d on cluster shards3_replications2
(
DATA_DATE String COMMENT '统计日期',
SSYS_CODE String COMMENT '源系统代码',
INNERORG_CODE String COMMENT '内部机构代码',
INNERORG_NAME String COMMENT '内部机构名称',
START_TM String COMMENT '窗口开始时间',
END_TM String COMMENT '窗口结束时间',
MTCH_AMT Decimal(19, 6) COMMENT '成交金额(万元)',
UPD_TIME DateTime DEFAULT now()
)
engine = ReplicatedReplacingMergeTree
('/clickhouse/test/tables/{shard}/trade_d', '{replica}')
partition by DATA_DATE
order by (DATA_DATE, SSYS_CODE, INNERORG_CODE, START_TM);
2. 建clickhouse源表_分布式表
create table test.trade_d_all on cluster shards3_replications2
(
DATA_DATE String COMMENT '统计日期(yyyyMMdd)',
SSYS_CODE String COMMENT '源系统代码',
INNERORG_CODE String COMMENT '内部机构代码',
INNERORG_NAME String COMMENT '内部机构名称',
START_TM String COMMENT '窗口开始时间',
END_TM String COMMENT '窗口结束时间',
MTCH_AMT Decimal(19, 6) COMMENT '成交金额(万元)',
UPD_TIME DateTime DEFAULT now()
)
ENGINE = Distributed(shards3_replications2, test, trade_d,
hiveHash(INNERORG_CODE));
3. 基于本地表建本地物化视图
物化视图简介 ( 官网 ) :
- ClickHouse 中的物化视图更像是插入触发器。 如果视图查询中有一些聚合,则它仅应用于一批新插入的数据。 对源表现有数据的任何更改(如更新、删除、删除分区等)都不会更改物化视图。
- 如果指定
POPULATE
,则在创建视图时将现有表数据插入到视图中,就像创建一个CREATE TABLE ... AS SELECT ...
一样。 否则,查询仅包含创建视图后插入表中的数据。指定POPULATE时要停止有向该表写入操作的任务.
因为在创建视图期间插入表中的数据不会插入其中.
Clickhouse 批量插入报错:Too many partitions for single INSERT block (more than 100)
max_partitions_per_insert_block 参数用来限制单个插入Block中,包含的最大分区数量,默认值为100。
set global on cluster shards3_replications2 max_partitions_per_insert_block = 1000;
-- 1.物化视图表
-- drop view trade_d_view on cluster shards3_replications2 no delay;
CREATE MATERIALIZED VIEW test.trade_d_view
on cluster shards3_replications2
ENGINE = ReplicatedSummingMergeTree
('/clickhouse/test/tables/{shard}/trade_d_view', '{replica}')
PARTITION BY DATA_DATE ORDER BY (DATA_DATE, SSYS_CODE, INNERORG_CODE)
POPULATE AS
SELECT DATA_DATE,
SSYS_CODE,
INNERORG_CODE,
INNERORG_NAME,
sum(MTCH_AMT) as MTCH_AMT,
FROM test.trade_d
-- 因为历史数据原因,分区比较多,于是拿日期进行过滤
where DATA_DATE > '20220901'
GROUP BY DATA_DATE, SSYS_CODE, INNERORG_CODE, INNERORG_NAME;
4. 基于物化后的本地视图建分布式表
-- drop table test.trade_d_view_all ON CLUSTER shards3_replications2 no delay;
CREATE TABLE IF NOT EXISTS test.trade_d_view_all
ON CLUSTER shards3_replications2
AS test.trade_d_view
ENGINE = Distributed(shards3_replications2, test, trade_d_view, hiveHash(INNERORG_CODE));
经过测试,查询物化视图的分布式表可以查到所有数据聚合后的结果(针对新增数据).是预期的效果.