目录
一、kafka-console-consumer.sh 查看topic消息结构
三、clickhouse创建merge引擎表(与roomChangedInfo_kafka对应结构)
整体思路
ck建kafka引擎表(根据消息体格式构建) >> ck建merge引擎表(merge引擎不限) >> ck建物化视图(刷新数据进merge表)
知识点:
- kafka安装以及基本命令(本文安装过程略)
- clickhouse建kafka引擎表
- clickhouse建merge引擎表(其他引擎暂无测试)
另外还可以安装kafkacat工具查看kafka,本例全在本机实现,跨服务器调整kafka_broker_list即可。
实施过程
一、kafka-console-consumer.sh 查看topic消息结构
(注意命令结尾加"| head -1" 或"--max-messages 1",表示只看一条,不然会满屏全是消息)
[root@SVRxxxxxxIN50002 sh]# kafka-console-consumer.sh --bootstrap-server 10.x.x.x:9092 --topic roomChangedInfo
二、clickhouse创建kafka引擎表
CREATE TABLE roomChangedInfo_kafka
(
content String COMMENT 'xx',
isOversea String COMMENT 'xxx',
sourceCreateTime UInt64 COMMENT 'xxx',
sourceQmqId String COMMENT 'xxx'
) ENGINE = Kafka
SETTINGS
kafka_broker_list = '10.xx.xxx.xxx:9092',
kafka_topic_list = 'roomChangedInfo',
kafka_group_name = 'ck_xq_ye',
kafka_format = 'JSONEachRow',
kafka_max_block_size = 1048576;
三、clickhouse创建merge引擎表(与roomChangedInfo_kafka对应结构)
CREATE TABLE roomChangedInfo_merge
(
content String COMMENT 'xx',
isOversea String COMMENT 'xxx',
sourceCreateTime UInt64 COMMENT 'xxx',
sourceQmqId String COMMENT 'xxx'
) ENGINE = MergeTree
PARTITION BY toYYYYMMDD(toDate(sourceCreateTime/1000))
ORDER BY (sourceCreateTime)
SETTINGS index_granularity = 8192;
四、创建物化视图(这里物化视图起触发器作用)
CREATE MATERIALIZED VIEW roomChangedInfo_mv TO roomChangedInfo_merge AS
SELECT content, isOversea, sourceCreateTime,sourceQmqId
FROM roomChangedInfo_kafka;