Clickhouse物化视图

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

经过测试,查询物化视图的分布式表可以查到所有数据聚合后的结果(针对新增数据).是预期的效果.