本文使用「署名 4.0 国际 (CC BY 4.0)」许可协议，欢迎转载、或重新修改使用，但需要注明来源。 [署名 4.0 国际 (CC BY 4.0)](https://creativecommons.org/licenses/by/4.0/deed.zh)

本文作者: 苏洋

创建时间: 2021年11月11日
统计字数: 11417字
阅读时间: 23分钟阅读
本文链接: https://soulteary.com/2021/11/11/use-clickhouse-to-process-offline-semi-structured-logs.html

-----


# 使用 ClickHouse 处理离线半结构化日志

在[《快速上手 ClickHouse》](https://soulteary.com/2021/10/16/get-started-quickly-with-clickhouse.html)一文中，我们介绍了 ClickHouse 的基础使用。本篇文章里，将介绍如何使用 Clickhouse 快速处理诸如 Nginx 运行日志等半结构化的离线数据，让这些静态数据能够被快速的查询分析。

## 写在前面

诸如 Nginx 等 Web 服务器、MySQL 等数据库，这类软件在日常运行过程中，都会生产非常多的运行数据，在不进行特别设置的情况下，我们将得到一些有一定格式的纯文本内容，我们一般称之为半结构化内容，比如这样：

```bash
xxx.xxx.xxx.xxx - - [01/Jan/2021:00:02:03 +0000] "GET /feed HTTP/1.1" 301 169 "-" "Winds: Open Source RSS & Podcast app: https://getstream.io/winds/"
xxx.xxx.xxx.xxx - - [01/Jan/2021:00:02:34 +0000] "GET /feed/ HTTP/1.1" 304 0 "-" "Tiny Tiny RSS/20.08-5497a13 (http://tt-rss.org/)"
xxx.xxx.xxx.xxx - - [01/Jan/2021:00:02:54 +0000] "GET /feed/ HTTP/1.1" 304 0 "-" "Tiny Tiny RSS/20.11-242aa6e41 (http://tt-rss.org/)"
```

这些非完全结构化的数据，是不能够被 ClickHouse 解析和使用的，想要做快速的数据分析自然也就无从谈起。

## 开源社区现有方案

为了解决上面的问题，开源社区出现了一些方案。我们大概有以下几种选择：

- 方案一 [Clicktail](https://github.com/Altinity/clicktail)：能够将 MySQL、Nginx等半结构化日志结构化，然后在流式处理的过程中，直接发送至 ClickHouse Client，然后进行结构化导入的开源工具。
- 方案二 [nginx-clickhouse](https://github.com/mintance/nginx-clickhouse)：能够将 Nginx 运行日志结构化并发送至 ClickHouse 进行处理，并提供了 Prometheus 监控抓取使用的性能指标接口。 
- 方案三 [seslog](https://github.com/andrylavr/seslog)：兼容 Nginx 日志协议的服务器，能够将数据转换并写入 Clickhouse 。
- 方案四 [nginx2clickhouse](https://github.com/openbsod/nginx2clickhouse)：由C编写的，基于字符串匹配替换的小工具，能够将 Nginx 日志进行转换处理，方便我们手动导入 ClickHouse。
- 方案五 [rsyslog-nginx-clickhouse](https://github.com/CatWithTail/rsyslog-nginx-clickhouse)：严格意义来说，这不是工具，而是一个文档。介绍了如何配置 Nginx ，并使用 Rsyslog 进行转换，然后存储至 ClickHouse。

将这些方案进行分类的话，大概可以分为下面三种类型：

- 我有**存量的现成数据，需要先使用结构化数据转换，然后手动导入** ClickHouse：方案四
- 我需要**处理实时生产的 Nginx 数据**：方案一，方案二，方案三，方案五
- 如果我想处理**非 Nginx 生产的半结构化数据**，比如 MySQL：只有方案一

### 现有方案存在的问题及亮点

不过，这些方案的诞生都基于非常固定的场景和受众，所以在我们文中提到的场景下（**通用的，适用于批量离线数据处理**），会存在一些问题：

- 看似万能的方案一，开发者已经弃坑。代码比较陈旧，有一些依赖已经找不到了，如果想进行定制和开发，有一定的重构成本。
- 方案二、方案三、方案五，如果想使用，需要使用“日志重放”的方式，效率低、流程复杂。而且这些方案均不能处理 Nginx 日志之外的数据，通用性比较差。
- 方案四，因为程序逻辑固定，所以要求 Nginx 的字段需要重新配置，也就是说，如果不按照这个约定规则进行，则无法进行数据转换、分析。并且这个程序只能处理 Nginx 日志，缺少通用性。

虽然上面的方案各自存在一些场景不适配的问题，但是这些方案之间有都有一些可取之处：

- 方案一至方案四，使用性能下限高的语言（Golang、C）进行工具编写，工具执行效率相比脚本语言高。
- 方案四，无状态的工具使用场景最符合我们的需求，适合离线批处理，解决大尺寸日志的分析导入。
- 方案五，能够灵活配置来源日志，场景适配性强。其中Rsyslog 组件 liblognorm 非常适合作为方案中的 “transporter” 使用。

## 它山之石，可以攻玉

在清楚了解上述方案的问题和优点之后，我们就可以简单的实现我们的工具方案啦，初步思路：

1. 使用 Linux `cat` 、`grep`、`awk` 、`uniq` 以及其他命令快速进行数据预处理。
2. 使用 liblognorm 组件中的 `lognormalizer` 进行日志格式解析和转换，将日志转换为 JSON 格式。
3. 编写一个小脚本针对上一步的数据进行进一步加工处理，修正诸如时间等不符合预期的数据。
4. 使用 ClickHouse Client 直接进行数据导入，以及数据字段调整，然后进行分析。

### 第一步：数据预处理

使用 Linux 命令进行日志的基础处理，在[《日志分析处理：GoAccess 篇》](https://soulteary.com/2020/12/16/log-analysis-and-processing-goaccess.html)一文中有提到，这里就不进一步展开了。感兴趣的同学可以翻阅之前的文章。

### 第二步：使用 lognormalizer 进行日志结构化

这部分的相关代码，我已经上传至 [GitHub](https://github.com/soulteary/offline-logs-to-clickhouse/tree/main/lognormalizer)，有需要可以自取。

`lognormalizer` 被包含在Rsyslog 的组件 [liblognorm](https://github.com/rsyslog/liblognorm) 中。它能够根据配置的方式，快速的将各种具有一定结构的半结构化的内容序列为我们想要的结构化格式。

相比较这几年的后起之秀，官方并未积极维护它的发行版，从 [https://repology.org/project/liblognorm/versions](https://repology.org/project/liblognorm/versions) 可以看到，我们只能在 Fedora 、OpenBSD、Alpine 的发行版上以开箱即用的方式使用到它的最新版本。

**所以，如果追求绝对的高性能，我们需要自行编译。**不过因为它发布了 Alpine 版本的二进制文件，或许使用容器也不失为一个简单愉快的选择。（编译的过程能够单独写一篇文章啦，本文先聊聊容器场景）在介绍容器方案前，额外聊一下编译过程中的小细节。

[官方文档](https://www.liblognorm.com/files/manual/lognormalizer.html)中对于安装部分的介绍存在一些陈旧的内容，需要进行一些升级替换，诸如使用 `libfastjson` 替换 `json-c` 链接库，使用 Rsyslog 的安装依赖完成基础编译环境的部署等，或许后续有时间的时候，我会将它移至到 MacOS，方便平时的使用。

为了愉快使用最新版本的 `lognormalizer` ，我们需要准备一个容器镜像。

```bash
FROM alpine:3.14

ENV LANG en_US.UTF-8
ENV LANGUAGE en_US.UTF-8
ENV LC_ALL=en_US.UTF-8

RUN apk --no-cache --no-progress update && \
    apk --no-cache --no-progress upgrade

RUN apk add liblognorm
```

为了方便后续使用，我将这个镜像推送到了 DockerHub，你可以通过下面的命令直接下载和使用它：

```bash
docker pull soulteary/lognormalizer:2.0.6
```

为了验证 `lognormalizer` 是否能够正常使用，我们可以准备一小部分日志数据，以及编写简单的解析配置，并创建一个应用容器，试着使用它进行一下数据转换。

我们将下面的内容保存为 `parser.rule` （配置来自 `rsyslog-nginx-clickhouse` 方案）：

```bash
version=2

rule=:%clientip:word% %ident:word% %auth:word% [%day:char-to:/%/%month:char-to:/%/%year:number%:%rtime:word% %tz:char-to:]%] "%verb:word% %request:word% HTTP/%httpversion:float%" %response:number% %bytes:number% "%referrer:char-to:"%" "%agent:char-to:"%"%blob:rest%
```

然后顺手抽取日志文件的前三行，作为测试数据：

```bash
head -n 3 logs/2021.log > sample.txt
```

接着，启动一个测试容器，把上面提到的配置和测试日志数据都挂载进容器中：

```bash
docker run --rm -it -v `pwd`/sample.txt:/sample.txt -v `pwd`/parser.rule:/parser.rule soulteary/lognormalizer:2.0.6 sh
```

手动加载数据，使用管道传递给 `lognormalizer` 进行结构化输出，设置输出格式为 `JSON`：

```bash
cat sample.txt | lognormalizer -r parser.rule -e json
```

不出意外，我们将得到类似下面的输出：

```bash
{ "blob": "", "agent": "Tiny Tiny RSS\/20.08-5497a13 (http:\/\/tt-rss.org\/)", "referrer": "-", "bytes": "0", "response": "304", "httpversion": "1.1", "request": "\/feed\/", "verb": "GET", "tz": "+0000", "rtime": "00:02:34", "year": "2021", "month": "Jan", "day": "01", "auth": "-", "ident": "-", "clientip": "xxx.xxx.xxx.xxx" }
...
```

上面的内容，是不是看起来已经搞的差不多了？但是仔细观察，这里的时间有一些问题：“`"month": "Jan"`”，Nginx 日志中默认的时间记录方式，并不适合我们后续在 ClickHouse 中进行结构化查询，所以我们要考虑对它进行进一步的处理，将它处理为“数值”。

### 第三步：编写基础的转换数据

为了兼顾执行效率和简单，我们可以使用 Golang 编写一个小工具，来帮助我们进行第二步中数据的收尾工作。考虑到执行效率，我们同样可以让中间过程的数据直接借助 Linux Pipeline 进行传递，而无需落盘。

想要实现一个简单的，能够通过“管道”读取数据，然后将十二个月的月份字符串名称替换为“数字”的程序，可以考虑下面的方式：

```php
package main

import (
	"bufio"
	"fmt"
	"os"
	"strings"
)

func main() {
	monthLabels := []string{"\"Jan\"", "\"Feb\"", "\"Mar\"", "\"Apr\"", "\"May\"", "\"Jun\"", "\"Jul\"", "\"Aug\"", "\"Sep\"", "\"Oct\"", "\"Nov\"", "\"Dec\""}
	monthValues := []string{"\"01\"", "\"02\"", "\"03\"", "\"04\"", "\"05\"", "\"06\"", "\"07\"", "\"08\"", "\"09\"", "\"10\"", "\"11\"", "\"12\""}

	src, _ := bufio.NewReader(os.Stdin).ReadString('\n')
	for i, monthLabel := range monthLabels {
		if strings.Contains(src, monthLabel) {
			dest := strings.Replace(src, monthLabel, monthValues[i], -1)
			fmt.Println(dest)
			return
		}
	}
	fmt.Println("")
}
```

我们将代码保存为 `test.go` ，然后使用第二步中的数据和简单的命令对其进行测试：

```json
echo '{ "blob": "", "agent": "Tiny Tiny RSS\/20.08-5497a13 (http:\/\/tt-rss.org\/)", "referrer": "-", "bytes": "0", "response": "304", "httpversion": "1.1", "request": "\/feed\/", "verb": "GET", "tz": "+0000", "rtime": "00:02:34", "year": "2021", "month": "Jan", "day": "01", "auth": "-", "ident": "-", "clientip": "xxx.xxx.xxx.xxx" }' | go run test.go 
```

命令执行完毕，会看到数据已经变成了我们预期的样子，月份被正确的转换为了数字：

```json
{ "blob": "", "agent": "Tiny Tiny RSS\/20.08-5497a13 (http:\/\/tt-rss.org\/)", "referrer": "-", "bytes": "0", "response": "304", "httpversion": "1.1", "request": "\/feed\/", "verb": "GET", "tz": "+0000", "rtime": "00:02:34", "year": "2021", "month": "01", "day": "01", "auth": "-", "ident": "-", "clientip": "xxx.xxx.xxx.xxx" }
```

熟悉 Golang 的小伙伴不难发现上面这段示例代码的问题：每次只能接受一条数据进行处理。那么我们继续对它进行一些简单的调整：

```php
package main

import (
	"bufio"
	"fmt"
	"os"
	"strings"
)

func main() {
	monthLabels := []string{"\"Jan\"", "\"Feb\"", "\"Mar\"", "\"Apr\"", "\"May\"", "\"Jun\"", "\"Jul\"", "\"Aug\"", "\"Sep\"", "\"Oct\"", "\"Nov\"", "\"Dec\""}
	monthValues := []string{"\"01\"", "\"02\"", "\"03\"", "\"04\"", "\"05\"", "\"06\"", "\"07\"", "\"08\"", "\"09\"", "\"10\"", "\"11\"", "\"12\""}

	scanner := bufio.NewScanner(os.Stdin)
	for scanner.Scan() {
		src := scanner.Text()
		dest := ""
		for i, monthLabel := range monthLabels {
			if strings.Contains(src, monthLabel) {
				dest = strings.Replace(src, monthLabel, monthValues[i], -1)
				break
			}
		}
		fmt.Println(dest)
	}
}
```

简单调整之后，这个小工具就能和 `cat` 等 Linux 工具正常协作，进行大文件的流式处理啦。如果你愿意的话，还可以进一步进行优化，比如拆分工作线程。以上面这段示例代码为例，处理 2G 不到的文件，大概需要 9s 左右，如果进行优化的话，应该时间消耗能够降低到 1～3s，感兴趣的朋友可以试试。

考虑到这个工具需要和第二步中的 `lognormalizer` 一起使用，所以我们在进行容器构建的时候，可以复用第二步创建的镜像。

```bash
FROM soulteary/lognormalizer:2.0.6 AS Builder
RUN apk add go
COPY datepatcher.go /
RUN go build datepatcher.go

FROM soulteary/lognormalizer:2.0.6
COPY --from=Builder /datepatcher /bin/
```

为了方便使用，我同样地将这个镜像上传到了 DockerHub：

```bash
docker pull soulteary/lognormalizer-with-datapatcher:2.0.6
```

这部分代码，我也上传到了 [GitHub](https://github.com/soulteary/offline-logs-to-clickhouse/tree/main/datepatcher)，可以根据实际需求进行调整。在完成了转换工具镜像的准备后，我们可以编写一个容器编排配置，来使用这个镜像：

```yaml
version: "2"

services:

  converter:
    image: soulteary/lognormalizer-with-datapatcher:2.0.6
    container_name: lognormalizer
    command: tail -f /etc/hostname
    environment:
      - TZ=Asia/Shanghai
    volumes:
      - ./logs:/logs
    tmpfs:
      - /cache
```

使用 `docker-compose down && docker-compose up -d` 启动镜像，将我们需要处理的日志和前文中提到的 `parser.rule` 放置在 `logs` 目录中，然后就可以执行命令进行日志转换了。

这里可以考虑借助 TmpFS 进行数据读写加速，比如先将要处理的数据文件移动至 TmpFS 目录（转换速度至少提升15倍以上）：

```bash
docker exec -it lognormalizer sh -c "cp /logs/2021.log /cache/2021.log"
```

根据你的文件位置，适当调整下面的命令，然后开始转换，等待计算完成即可。

```bash
docker exec -it lognormalizer sh -c "cat /cache/2021.log | lognormalizer -r /logs/parser.rule -e json | datepatcher > /cache/data.log"
```

最后，将计算完成的文件使用 `docker cp` 或者移动到我们挂载出来的目录即可准备导入 ClickHouse。

为了节约空间，你还可以对刚刚我们创建的容器进行一个收尾清理：

```bash
docker kill lognormalizer
docker rm lognormalizer
```

### 第四步：导入 ClickHouse

你可以选择参考[《快速上手 ClickHouse》](https://soulteary.com/2021/10/16/get-started-quickly-with-clickhouse.html)一文中的方式搭建一个 ClickHouse 示例，或者直接使用云端的 ClickHouse 数据库进行操作。我们以容器为例，展开接下来的内容。

使用 `docker exec -it clickhouse clickhouse-client` 进入 Clickhouse 交互控制台，创建一个数据库：

```sql
CREATE DATABASE IF NOT EXISTS soulteary
```

然后创建一个临时表，用于存放我们的日志数据。

```sql
CREATE TABLE IF NOT EXISTS soulteary.tmp
(
    `year` String,
    `month` String,
    `day` String,
    `rtime` String,
    `clientip` String,
    `ident` String,
    `auth` String,
    `verb` String,
    `request` String,
    `httpversion` String,
    `response` UInt16,
    `bytes` UInt64,
    `referrer` String,
    `agent` String,
    `blob` String
)
ENGINE = MergeTree
ORDER BY (year, month, day, rtime)
```

有的同学会好奇，上面的表结构看起来已经很完善了，为啥还要创建临时表呢，直接当正式表来用不可以吗？

这是因为在前几步转换出来的数据将“日期”、“时间”字段进行了拆分，如果我们想要基于时间维度进行快速排序或者统计分析，不论是基于基础的“数字”类型，还是基于基础的“文本”类型，在分析的过程中都会比较低效，所以我们需要考虑使用程序生成这两个字段。那么正式表的表结构又是怎样的呢？

```sql
CREATE TABLE IF NOT EXISTS soulteary.nginx
(
    `date` Date,
    `time` DateTime,
    `year` UInt16,
    `month` UInt16,
    `day` UInt16,
    `rtime` String,
    `clientip` String,
    `ident` String,
    `auth` String,
    `verb` String,
    `request` String,
    `httpversion` String,
    `response` UInt16,
    `bytes` UInt64,
    `referrer` String,
    `agent` String,
    `blob` String
)
ENGINE = MergeTree
ORDER BY (year, month, day, rtime)
SETTINGS index_granularity = 8192
```

可以看到，在正式表中，我们的字段类型有了一些调整，除此之外，也多了 `date` 和 `time` 两个字段。

退出交互式控制台，使用 `docker exec -it clickhouse clickhouse-client` 进入 ClickHouse 容器中，执行命令将前三步中的数据正式导入 Clickhouse。

```bash
cat /var/lib/clickhouse/data.log | clickhouse-client -n --query="SET input_format_skip_unknown_fields=1; INSERT INTO soulteary.tmp FORMAT JSONEachRow"
```

稍等片刻，命令执行完毕之后，执行 `clickhouse-client` 再次进入 ClickHouse 交互式控制台，然后执行语句，开始进行临时表到正式表到数据迁移，和字段补全：

```sql
INSERT INTO soulteary.nginx SELECT
    toDateTime(concat(year, '-', month, '-', day, ' ', rtime)) AS fullDateLabel,
    toDate(fullDateLabel),
    year,
    month,
    day,
    rtime,
    clientip,
    ident,
    auth,
    verb,
    request,
    httpversion,
    response,
    bytes,
    referrer,
    agent,
    blob
FROM soulteary.tmp
```

执行完毕，会看到类似下面的日志输出：

```sql
Query id: 43a1fcbc-1078-4add-b6f7-7c425085ca4b

Ok.

0 rows in set. Elapsed: 4.848 sec. Processed 3.71 million rows, 1.09 GB (764.84 thousand rows/s., 225.44 MB/s.)
```

### 第五步：检查数据

数据迁移完毕，我们可以使用命令检查一下，确保数据迁移完毕。

```sql
SELECT
    prod,
    tmp
FROM
(
    SELECT count() AS prod
    FROM soulteary.nginx
) AS table_prod,
(
    SELECT count() AS tmp
    FROM soulteary.tmp
) AS table_tmp

Query id: 2415f48b-39f2-4bcd-8c5b-0971e4ada6a3

┌────prod─┬─────tmp─┐
│ 3707776 │ 3707776 │
└─────────┴─────────┘

1 rows in set. Elapsed: 0.004 sec. 
```

再手动随便执行一条具体内容的查询：

```sql
SELECT *
FROM soulteary.nginx
LIMIT 1


┌───────date─┬────────────────time─┬─year─┬─month─┬─day─┬─rtime────┬─clientip───────┬─ident─┬─auth─┬─verb─┬─request─┬─httpversion─┬─response─┬─bytes─┬─referrer────────────────────┬─agent─────────────────────────────────────────────┬─blob─┐
│ 2021-01-01 │ 2021-01-01 00:00:00 │ 2021 │     1 │   1 │ 00:00:24 │ 111.231.78.131 │ -     │ -    │ GET  │ /feed/  │ 1.1         │      200 │ 15130 │ https://soulteary.com/feed/ │ FreshRSS/1.16.3-dev (Linux; https://freshrss.org) │      │
└────────────┴─────────────────────┴──────┴───────┴─────┴──────────┴────────────────┴───────┴──────┴──────┴─────────┴─────────────┴──────────┴───────┴─────────────────────────────┴───────────────────────────────────────────────────┴──────┘
```

可以看到数据正确的迁移到了正式表，并且缺失的 `date`、`time` 字段也被正确的补全了。

## 最后

写到这里，如何使用 Clickhouse 处理离线的半结构化数据的话题也就结束了。

或许下一篇 ClickHouse 相关的内容里，我会接着[《装在笔记本里的私有云环境：监控篇》](https://soulteary.com/2021/10/30/private-cloud-environment-installed-in-a-notebook-monitor.html)的话题，聊一聊集成 Grafana 进行可视化。

--EOF