Clickhouse - 数据转换/解析

Question

我们使用 Clickhouse 来存储 HAProxy 和 Kong 日志和指标。“管道”是围绕 syslog 协议和 rsyslog 构建的，如下所示：HAProxy/Kong -> local rsyslog -> remote rsyslog (TCP) -> omclickhouse rsyslog module -> clickhouse。系统日志消息的格式当然在 HAProxy 和 Kong 之间有所不同。HAProxy 消息如下所示：1.2.3.4:58629 [06/Jun/2020:14:54:59.932] HTTPS~ HAPBACKEND/HAPSERVER 0/0/1/36/37 200 778 - - ---- 32/32/1/1/0 0/0 "GET /api/map/v2/GetSomeStuff/json?Latitude=47.22960133109915&Longitude=-1.5727845858797176 HTTP/1.1"如此处所述：https ://cbonte.github.io/haproxy-dconv/1.7/configuration.html#8.2.3 ，Kong 消息是基于 JSON 的，看起来像这样：{    "request": {        "method": "GET",        "uri": "/get",        "url": "http://httpbin.org:8000/get",        "size": "75",        "querystring": {},        "headers": {            "accept": "*/*",            "host": "httpbin.org",            "user-agent": "curl/7.37.1"        },        "tls": {            "version": "TLSv1.2",如此处所述：https ://docs.konghq.com/hub/kong-inc/syslog/rsyslog omclickhouse 模块（默认情况下）将所有系统日志消息插入名为“SystemEvents”的表中，该表具有以下结构：出于报告目的，我们对 syslog 消息字段中包含的 HAProxy（或 Kong）详细信息感兴趣，而不是整个 syslog 内容本身。因此，为了能够获得“细粒度”查询功能，我们创建了另一个表，比如“HAPROXY_LOGS”，其结构如下：这就是事情开始变得更奇怪的地方……Clickhouse 本身似乎既不提供某种调度程序，就像 MSSQL，也不提供将编程语言嵌入引擎的方法（PL/pgSQL，PL/ Python - like），也不是触发器（我们还没有研究物化视图）。因此，为了将数据从一个表转换和移动到另一个表，cron 每分钟启动一个 shell 脚本，使用 clickhouse-client 获取输入数据，将其通过管道传输到 Python 脚本，然后其结果本身再次通过管道传输到 clickhouse -插入客户端：HAProxy 和 Kong 解析的 Python 脚本不同。听起来像一个肮脏的黑客...有没有更好的方法来完成同样的事情？（尽管有这个 hack，但整个东西工作得很好，报告构建时间减少了一个很大的因素，Clickhouse 存储 600M+ 行没有任何问题。）

Answer 1

我认为在 ClickHouse 之外转换数据是正确的方法。

不过，CH 可以自己承担。让我们考虑例如 JSON 日志，并将使用物化视图和丰富的json 相关函数集）：

rsyslog omclickhouse 模块（默认情况下）将所有系统日志消息插入名为“SystemEvents”的表中，该表具有以下结构：

┌─severity─┬─facility─┬───────────timestamp─┬─hostname─────────────────┬─tag────────────┬─message──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐

│        6 │       18 │ 2020-06-06 15:01:00 │ reverseproxy.fqdn        │ haproxy[6892]: │  1.2.3.4:57220 [06/Jun/2020:15:00:59.996] HTTPS~ HAPBACKEND/HAPSRV 15/0/1/2/18 500 617 - - ---- 48/42/9/9/0 0/0 "POST /SOAPService HTTP/1.1" │

└──────────┴──────────┴─────────────────────┴──────────────────────────┴────────────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

（我们不想参与自定义 rsyslog 解析 C 模块的开发）

出于报告目的，我们对 syslog 消息字段中包含的 HAProxy（或 Kong）详细信息感兴趣，而不是整个 syslog 内容本身。因此，为了能够获得“细粒度”查询功能，我们创建了另一个表，比如“HAPROXY_LOGS”，其结构如下：

(`CLIENT_IP` String, `CLIENT_PORT` Int32, `REQUEST_DATE` DateTime, `FRONTEND_NAME` String, `BACKEND_NAME` String, `SERVER_NAME` String, `TREQ` Int32, `TWAIT` Int32, `TCONNECTION` Int32, `TRESPONSE` Int32, `TACTIVE` Int32, `STATUS_CODE` Int32, `BYTES_READ` Int32, `CAPTURED_REQUEST_COOKIE` String, `CAPTURED_RESPONSE_COOKIE` String, `TERMINATION_STATE` String, `ACTCONN` Int32, `FECONN` Int32, `BECONN` Int32, `SRV_CONN` Int32, `RETRIES` Int32, `SRV_QUEUE` Int32, `BACKEND_QUEUE` Int32, `METHOD` String, `REQUEST` String, `PARAMETERS` String, `PROTOCOL` String) ENGINE = MergeTree() PARTITION BY toYYYYMM(REQUEST_DATE) ORDER BY (REQUEST_DATE, TRESPONSE, STATUS_CODE, PARAMETERS) SETTINGS index_granularity = 8192

这就是事情开始变得更奇怪的地方……Clickhouse 本身似乎既不提供某种调度程序，就像 MSSQL，也不提供将编程语言嵌入引擎的方法（PL/pgSQL，PL/ Python - like），也不是触发器（我们还没有研究物化视图）。因此，为了将数据从一个表转换和移动到另一个表，cron 每分钟启动一个 shell 脚本，使用 clickhouse-client 获取输入数据，将其通过管道传输到 Python 脚本，然后其结果本身再次通过管道传输到 clickhouse -插入客户端：

* * * * * { /usr/bin/clickhouse-client < /path/clkh/extract-system-events.sql | /path/clkh/latestmessages-to-TSV-pipe.py 2>/path/clkh/errors-haproxy.log ; } |/usr/bin/clickhouse-client --query="INSERT INTO HAPROXY_LOGS FORMAT TSV" >> /var/log/latestmessages-to-TSV-pipe.log

HAProxy 和 Kong 解析的 Python 脚本不同。

听起来像一个肮脏的黑客...

有没有更好的方法来完成同样的事情？

（尽管有这个 hack，但整个东西工作得很好，报告构建时间减少了一个很大的因素，Clickhouse 存储 600M+ 行没有任何问题。）