clickhouse同步kafka数据方案

1.步骤

　　kafka作为消息队列通常用来收集各个服务产生的数据，而下游各种数据服务订阅消费数据，本文通过使用clickhouse 自带的kafka 引擎，来同步消费数据。

　　同步步骤：

　　kafka中创建topic，创建消费者并消费该topic（查看消费情况）
　　建立目标表(通常是MergeTree引擎系列），用来存储kafka中的数据；
　　建立kafka引擎表，用于接入kafka数据源；
　　创建Materialized View（物化视图）, 监听kafka中的数据并将数据同步到clickhouse的目标表中；

 

2.创建测试数据源

bin/kafka-topics.sh --create --zookeeper localhost:2181 --replication-factor 1 --partitions 1 --topic kafka-reader

# 创建消费者指定topic
./bin/kafka-console-consumer.sh --bootstrap-server localhost:9092 --from-beginning --topic kafka-reader --group kafka-reader-group

 

3.创建数据储存目标表

CREATE TABLE target(
    day Date,
    level String,
    message String
) ENGINE = SummingMergeTree(day, (day, level), 8192);

 

4.创建kafka消费表

　　1 )使用kafka引擎创建queue表来连接kafka并读取topic中的数据。该数据表订阅了名为kafka-reader的消息主题，且消费组的名称为kafka-reader-group，⽽消息的格式采⽤了JSONEachRow。
　　2 )在此之后，查询这张数据表就能够看到Kafka的数据了。但是再次查询这张便就会没有数据了，这是因为Kafka表引擎在执⾏查询之后就会删除表内的数据。

CREATE TABLE queue (
timestamp DateTime,
level String,
message String
)
ENGINE = Kafka
SETTINGS kafka_broker_list = '192.168.9.226:9092',
kafka_topic_list = 'kafka-reader',
kafka_row_delimiter = '\n',
kafka_group_name = 'kafka-reader-group',
kafka_format = 'JSONEachRow'

　　参数解析–必要参数：

　　kafka_broker_list – 以逗号分隔的kafka的brokers 列表 (192.168.9.226:9092)。
　　kafka_topic_list – topic 列表 (kafka-reader)。
　　kafka_group_name – Kafka 消费组名称 (kafka-reader-group)。如果不希望消息在集群中重复，请在每个分片中使用相同的组名。
　　kafka_format – 消息体格式。JSONEachRow也就是普通的json格式，使用与 SQL 部分的 FORMAT 函数相同表示方法。
　　参数解析–可选参数：

　　kafka_row_delimiter - 每个消息体之间的分隔符。
　　kafka_schema – 如果解析格式需要一个 schema 时，此参数必填。例如，普罗托船长 需要 schema - 文件路径以及根对象 schema.capnp:Message 的名字。
　　kafka_num_consumers – 单个表的消费者数量。默认值是：1，如果一个消费者的吞吐量不足，则指定更多的消费者。消费者的总数不应该超过 topic 中分区的数量，因为每个分区只能分配一个消费者。

5.创建Materialized View（物化视图）传输数据

　　创建好的物化视图，它将会在后台收集数据。可以持续不断地从 Kafka 收集数据并通过 SELECT 将数据转换为所需要的格式。

CREATE MATERIALIZED VIEW consumer TO target
AS SELECT toDate(toDateTime(timestamp)) AS day, level,message
FROM queue;

 

6.测试

　　生产者添加数据：

　　查询目标表，查看消费数据

SELECT *
FROM target

┌────────day─┬─level─┬─message─┐
│ 2020-12-01 │ 11 │ 不开心 │
│ 2020-12-30 │ 13 │ 写博客 │
│ 2020-12-31 │ 15 │ 买可乐 │
│ 2020-12-31 │ 17 │ 真好喝 │
└────────────┴───────┴─────────┘


--查询consumer物化视图表，一般得到的数据和目标表差不多，除非实时数据很多，停止接收topic数据或更改转换逻辑需要停用物化视图，更改完之后再启用物化视图

-- 停用
DETACH TABLE consumer;
-- 启用
ATTACH TABLE consumer;

7.重载数据以及增添数据列

　　1）重读Kafka数默认从Kafka Topic的开始位置开始，并在到达消息时对其进行读取。这是正常的方式，但是有时重新读取消息很有用。

　　例如，您可能想在修复架构中的错误或重新加载备份后重新读取消息。幸运的是，这很容易做到。我们只是在消费者组中重置偏移量。

• 假设我们丢失了读数表中的所有消息，并希望从Kafka重新加载它们。首先，让我们使用TRUNCATE命令重载数据。

TRUNCATE TABLE queue;

• 在重置分区上的偏移之前，我们需要关闭消息使用。通过在ClickHouse中分离queue表来执行此操作，如下所示。
  
  

DETACH TABLE queue;

• 接下来，使用以下Kafka命令在用于queue表的使用者组中重置分区偏移量。

注意：改命令需要在Kafka中进行操作。

./bin/kafka-consumer-groups.sh --bootstrap-server localhost:9092  --topic kafka-reader --group kafka-reader-group  --reset-offsets --to-earliest --execute

• 登录到ClickHouse，重新连接queue表

ATTACH TABLE queue;

　　等待几秒钟，丢失的记录将被恢复。此时可以使用SELECT进行查询。

　　2）添加数据列

　　显示原始Kafka信息作为行通常很有用，Kafka表引擎也定义了虚拟列，以下更改数据表以显示Topic分区和偏移量的方法。

• 分离Kafka表来禁用消息使用。不影响数据的生产

DETACH TABLE queue;

• 依次执行以下SQL命令来更改目标表和实例化视图

注意：我们只是重新创建实例化视图，而我们更改了目标表，该表保留了现有数据。

ALTER TABLE target
ADD COLUMN name String;

• 删除并重新构建视图表

DROP TABLE consumer;

CREATE MATERIALIZED VIEW consumer TO target
AS SELECT toDate(toDateTime(timestamp)) AS day, level,message,name
FROM queue;

• 重新连接queue表来再次启用消息使用

ATTACH TABLE consumer;

• 查询数据表信息

select * from consumer;

8.总结

　　Clickhouse消费kafka数据的过程中，通过kafka引擎表作为一个管道接收流入的数据，而物化视图负责将kafka引擎表的数据实时同步到目标表中，我们通过不同sql语句封装将kafka数据导入到不同目标表中。
　　另需注意：

　　在生产者发送数据后，当所有字段都非null时会写入ch,当某个字段为null时，该条数据不能写入ch,即使在创建物理表时设定了Nullable,但不会导致程序异常，只是不能写入这条记录。但是当创建物理表是设定了Nullable并且kafka引擎表在创建时也给定这个字段Nullable()时，此时这个字段为null值时，该条记录才会被成功写入，但是ch中该字段为null值。当缺失某个字段，这条记录一样会被同步到ch，缺失的字段value为null,当json消息多出一些字段，这条记录一样会被同步到ch,多余的字段会被忽略。ch物理表中的字段如果有Nullable修饰，则kafka引擎表中对应的字段也需要有Nullable修饰。如果不一致会停止接受数据。