收集Rsyslog日志到Clickhouse数据库

1、Rsyslog客户端配置

vim /etc/rsyslog.conf
# 客户端将所有消息日志发送到远端日志服务器
*.* @@remote-log-server:514

# 重启日志服务
systemctl restart rsyslog

2、Rsyslog服务端配置

vim /etc/rsyslog.conf
# 取消注释
# provides UDP syslog reception
module(load="imudp")
input(type="imudp" port="514")

# provides TCP syslog reception
module(load="imtcp")
input(type="imtcp" port="514")

# 部署zookeeper和kafka(略)
# 将日志发送到kafka
yum install -y rsyslog-kafka
cat > /etc/rsyslog.d/kafka.conf <<EOF
module(load="omkafka")
action(type="omkafka" topic="rsyslog" broker="localhost:9092")
EOF

# 重启日志服务
systemctl restart rsyslog

3、Clickhouse-Server配置

vim /etc/clickhouse-server/config.xml
# 取消注释
<listen_host>::</listen_host>

# 创建数据库
clickhouse-client
clickhouse-server :) CREATE DATABASE rsyslog;

# 创建kafka引擎表
CREATE TABLE rsyslog.kafka_queue(
datetime String,
hostname String,
message String
) ENGINE = Kafka()
SETTINGS
kafka_broker_list = 'remote-log-server:9092',
kafka_topic_list = 'rsyslog',
kafka_group_name = 'clickhouse',
kafka_format = 'Regexp',
format_regexp = '(.+?) (.+?) (.+?)',
format_regexp_escaping_rule = 'Raw',
format_regexp_skip_unmatched = 0
;

# 创建kafka数据表
CREATE TABLE rsyslog.kafka_view (
datetime String,
hostname String,
message String
) ENGINE = MergeTree()
ORDER BY datetime;

# 创建物化视图
CREATE MATERIALIZED VIEW rsyslog.consumer TO kafka_view AS SELECT * FROM kafka_queue;

# 尝试查询
SELECT datetime AS `时间`, hostname AS `主机名`, message `消息体` FROM rsyslog.kafka_view WHERE   ( hostname = 'remote-log-server' ) ORDER BY datetime DESC LIMIT 100;