Clickhouse集群中创建多副本物化视图的方法（重要）

Clickhouse集群中创建多副本物化视图的方法（重要）

• 创建4个节点的Clickhouse高可用集群，host_name为ck01、ck02、ck03、ck04，分片与副本情况见下图：

• 使用nginx配置轮询dns写入数据到2个片区的本地表，此处选择ck02、ck03为写入节点，ck01为ck04的副本节点。

1. 所有节点创建集群数据分区表，用于接收初始数据

cREATE TABLE sdecdmp.D20Data
(
    `CreateDate` Date DEFAULT toDate(now(), 'Asia/Shanghai'),
    `CreateTime` DateTime DEFAULT now(),
    `ein` String,
    `callType` String,
    `command` String,
    `deviceType` String,
    `deviceID` String,
    `uploadTime` Int64,
    `items` Array(String)
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/dmp/D20Data', '{replica}')-- 
PARTITION BY CreateDate
ORDER BY (CreateTime, ein, deviceID, uploadTime) SETTINGS storage_policy='dist', index_granularity = 8192;

2. 所有节点创建初始数据分布式表

CREATE TABLE sdecdmp.D20Data_all
AS sdecdmp.D20Data 
ENGINE = Distributed(dmp, sdecdmp, D20Data, rand());

3. ck02、ck03创建数据物化视图，用于实时解析数据

CREATE MATERIALIZED VIEW sdecdmp.D20DataM 
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/dmp/D20DataM', '{replica}')
PARTITION BY CreateDate
ORDER BY (ein ,uploadTime1 )
SETTINGS storage_policy='dist', index_granularity = 8192
--POPULATE
AS 
SELECT arrayMap(x-> visitParamExtractFloat(toString(x),'value'),items ) as `params.valueM`,
arrayMap(x-> visitParamExtractString(toString(x),'code'),items ) as `params.codeM`,
toDateTime(toInt64(uploadTime)/1000,'Asia/Shanghai' ) as uploadTime1,
ein,deviceID,CreateDate,CreateTime from sdecdmp.D20Data
where  command = 'ENGINE_D20'

4. ck01、ck04创建集群表，不是物化视图，用来保存ck02、ck03的副本，注意使用的是 create table 命令

CREATE table sdecdmp.D20DataM (
    `params.valueM` Array(Float64),
`params.codeM` Array(String),
`uploadTime1` DateTime('Asia/Shanghai'),
`ein` String,
`deviceID` String,
`CreateDate` Date,
`CreateTime` DateTime
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/dmp/D20DataM', '{replica}')
PARTITION BY CreateDate
ORDER BY (ein ,uploadTime1 )
SETTINGS  storage_policy='dist',index_granularity = 8192;

5. 所有节点创建物化视图分布式表

CREATE TABLE sdecdmp.D20DataM_all
AS sdecdmp.D20DataM 
ENGINE = Distributed(dmp, sdecdmp, D20DataM, rand());

使用ZooInspector可以查看集群D20Data、D20DataM主副表关系。
详细搭建集群方法后期整理，有坑的。