ClickHouse 官方文档：设计可观测性数据模型【2/5】

本文字数：46287；估计阅读时间：116 分钟

作者： ClickHouse官方

本文在公众号【ClickHouseInc】首发

本文是 ClickHouse 官网文档可观测性系列文章，共有 5 篇：

1. 概述

2. 设计数据模型

3. 管理数据

4. 与 OpenTelemetry 集成

5. 使用 Grafana 分析数据

本篇为第二篇《设计数据模型》，正文如下：

我们建议用户为日志和跟踪设计自己的数据模型，主要原因如下：

• 选择合适的主键：默认模式使用了基于 ORDER BY 的设计，这种方式是为特定的访问模式优化的。然而，您的实际访问模式可能与此不完全一致，因此需要根据需求选择主键。

• 提取新结构：用户可能希望从现有列中提取新字段，例如从 Body 列中解析出额外的信息。这可以通过使用物化列来实现，在更复杂的情况下，可以利用物化视图。实现这些功能需要对数据模型进行修改。

• 优化 Map 类型的使用：默认模式中，Map 类型被用来存储属性以保存任意元数据。这种设计非常实用，尤其是当事件的元数据无法事先定义时，因为像 ClickHouse 这样严格类型的数据库难以直接处理这些动态数据。然而，与普通列相比，访问 Map 键及其值的效率较低。为了解决这一问题，可以通过修改模式，将访问频率较高的 Map 键提升为顶层列。详情请参见“使用 SQL 提取结构”。这一过程同样需要调整数据模型。

• 简化 Map 键访问：直接访问 Map 键需要较为冗长的查询语法。用户可以通过定义别名来简化这些查询。详细说明请参见“使用别名”。

• 优化二级索引的使用：默认模式包含二级索引，用于加速 Map 属性的访问和文本查询。这些索引并非总是必要的，同时会占用额外的磁盘空间。在启用这些索引之前，建议进行测试以确认其是否真的需要。更多信息请参见“二级 / 数据跳过索引”。

• 使用编解码器优化压缩：如果用户对数据特性有足够的了解，并有证据表明优化压缩能够显著提升性能，则可以考虑为列自定义编解码器。

以上各项场景将在后文中详细说明。

重要提示：尽管鼓励用户对数据模型进行扩展和优化，以实现最佳的压缩和查询性能，但建议尽可能遵循 OTel（OpenTelemetry）模式中的核心字段命名规则。例如，ClickHouse 的 Grafana 插件依赖一些基础字段（如 Timestamp 和 SeverityText）来辅助构建查询。日志和跟踪所需的必备字段已在文档 [1][2] 中列出。用户也可以通过插件配置自定义这些字段名以覆盖默认设置。

通过 SQL 提取日志结构

无论是结构化日志还是非结构化日志，用户通常需要以下功能：

• 从字符串中提取字段。相比查询时使用字符串操作，提前将字段提取为独立的列能显著提升查询效率。

• 从 Map 类型中提取键值。默认数据模式中，任意属性被存储在 Map 类型的列中。这种设计允许用户在定义日志和追踪时无需提前预定义属性列，这对某些场景非常实用。例如，当从 Kubernetes 收集日志时，通常无法提前确定所有属性，尤其是希望保留 pod 标签以便后续查询时。尽管 Map 提供了灵活性，但其键值的查询效率不如普通列。因此，将常用的 Map 键提取为表的顶层列通常是更高效的选择。

示例查询：

假设我们希望统计接收到最多 POST 请求的 URL 路径。此路径信息以 JSON 数据块的形式存储在 Body 列中（字符串类型）。如果用户在采集器中启用了 json_parser，这部分数据也可能存储在 LogAttributes 列中，类型为 Map(String, String)。

SELECT LogAttributes
FROM otel_logs
LIMIT 1
FORMAT Vertical

Row 1:
──────
Body:       {"remote_addr":"54.36.149.41","remote_user":"-","run_time":"0","time_local":"2019-01-22 00:26:14.000","request_type":"GET","request_path":"\/filter\/27|13 ,27|  5 ,p53","request_protocol":"HTTP\/1.1","status":"200","size":"30577","referer":"-","user_agent":"Mozilla\/5.0 (compatible; AhrefsBot\/6.1; +http:\/\/ahrefs.com\/robot\/)"}
LogAttributes: {'status':'200','log.file.name':'access-structured.log','request_protocol':'HTTP/1.1','run_time':'0','time_local':'2019-01-22 00:26:14.000','size':'30577','user_agent':'Mozilla/5.0 (compatible; AhrefsBot/6.1; +http://ahrefs.com/robot/)','referer':'-','remote_user':'-','request_type':'GET','request_path':'/filter/27|13 ,27|  5 ,p53','remote_addr':'54.36.149.41'}

当 LogAttributes 可用时，可以通过以下查询统计网站中接收到最多 POST 请求的 URL 路径：

SELECT path(LogAttributes['request_path']) AS path, count() AS c
FROM otel_logs
WHERE ((LogAttributes['request_type']) = 'POST')
GROUP BY path
ORDER BY c DESC
LIMIT 5

┌─path─────────────────────┬─────c─┐
│ /m/updateVariation       │ 12182 │
│ /site/productCard        │ 11080 │
│ /site/productPrice       │ 10876 │
│ /site/productModelImages │ 10866 │
│ /site/productAdditives   │ 10866 │
└──────────────────────────┴───────┘

5 rows in set. Elapsed: 0.735 sec. Processed 10.36 million rows, 4.65 GB (14.10 million rows/s., 6.32 GB/s.)
Peak memory usage: 153.71 MiB.

在上述查询中，使用了 Map 的语法（如 LogAttributes['request_path']），同时通过 path 函数去除 URL 的查询参数。

如果用户未启用 JSON 解析，LogAttributes 将为空，则需要通过 JSON 函数从 Body 列中的字符串提取字段。

优先选择 ClickHouse 进行解析

我们建议用户优先在 ClickHouse 中解析结构化日志的 JSON 数据。我们相信 ClickHouse 提供了业内最快的 JSON 解析能力。当然，我们也理解某些用户可能希望将日志存储到其他系统，并不希望将解析逻辑局限在 SQL 中。

SELECT path(JSONExtractString(Body, 'request_path')) AS path, count() AS c
FROM otel_logs
WHERE JSONExtractString(Body, 'request_type') = 'POST'
GROUP BY path
ORDER BY c DESC
LIMIT 5

┌─path─────────────────────┬─────c─┐
│ /m/updateVariation       │ 12182 │
│ /site/productCard        │ 11080 │
│ /site/productPrice       │ 10876 │
│ /site/productAdditives   │ 10866 │
│ /site/productModelImages │ 10866 │
└──────────────────────────┴───────┘

5 rows in set. Elapsed: 0.668 sec. Processed 10.37 million rows, 5.13 GB (15.52 million rows/s., 7.68 GB/s.)
Peak memory usage: 172.30 MiB.

针对非结构化日志的处理

SELECT Body, LogAttributes
FROM otel_logs
LIMIT 1
FORMAT Vertical

Row 1:
──────
Body:       151.233.185.144 - - [22/Jan/2019:19:08:54 +0330] "GET /image/105/brand HTTP/1.1" 200 2653 "https://www.zanbil.ir/filter/b43,p56" "Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/71.0.3578.98 Safari/537.36" "-"
LogAttributes: {'log.file.name':'access-unstructured.log'}

对于非结构化日志，类似的查询需要使用 extractAllGroupsVertical 函数，通过正则表达式解析数据。

SELECT
    path((groups[1])[2]) AS path,
    count() AS c
FROM
(
    SELECT extractAllGroupsVertical(Body, '(\\w+)\\s([^\\s]+)\\sHTTP/\\d\\.\\d') AS groups
    FROM otel_logs
    WHERE ((groups[1])[1]) = 'POST'
)
GROUP BY path
ORDER BY c DESC
LIMIT 5

┌─path─────────────────────┬─────c─┐
│ /m/updateVariation       │ 12182 │
│ /site/productCard        │ 11080 │
│ /site/productPrice       │ 10876 │
│ /site/productModelImages │ 10866 │
│ /site/productAdditives   │ 10866 │
└──────────────────────────┴───────┘

5 rows in set. Elapsed: 1.953 sec. Processed 10.37 million rows, 3.59 GB (5.31 million rows/s., 1.84 GB/s.)

相比结构化日志，解析非结构化日志的查询复杂度更高，性能成本也更大（尤其需要注意性能差异）。因此，我们建议用户尽可能选择使用结构化日志。

使用正则表达式字典进行优化

上述查询可以通过正则表达式字典进一步优化，这将显著提升查询效率。具体操作请参见“使用字典”一节。

以上两种处理场景都可以通过在数据写入时，将查询逻辑预处理并存储到 ClickHouse 来实现。我们将在下文探讨几种具体方法，并说明每种方法的适用场景。

应该选择 OTeL 还是 ClickHouse 进行处理？

用户还可以按照相关文档中的描述，利用 OTel Collector 的处理器和操作符完成日志处理。然而，大多数情况下，用户会发现 ClickHouse 的资源效率和处理速度明显优于 OTel Collector。但需要注意的是，将所有事件处理逻辑放在 SQL 中的主要缺点是，可能导致解决方案过于依赖 ClickHouse。例如，某些用户可能希望通过 OTel Collector 将处理后的日志发送到其他目标存储，例如 S3。

物化字段

物化字段是从其他列提取数据结构的最简单方法。这类字段的值会在数据写入时自动计算，无法通过 INSERT 查询直接指定。

存储成本

物化字段会增加一定的存储开销，因为在写入时，提取的值会被存储为新的磁盘列。

物化字段支持 ClickHouse 的所有表达式，并可使用多种分析函数对数据进行处理，例如字符串操作（包括正则表达式和搜索）、URL 解析、类型转换、从 JSON 中提取值以及数学运算。

我们推荐在基础数据处理场景中使用物化字段，尤其适用于从 Map 类型列中提取值，将其提升为顶层字段，或进行类型转换。在简单的模式设计中，或与物化视图结合使用时，这些字段能发挥最佳效果。例如，在以下日志模式中，JSON 数据已通过采集器提取到 LogAttributes 列中：

CREATE TABLE otel_logs
(
    `Timestamp` DateTime64(9) CODEC(Delta(8), ZSTD(1)),
    `TraceId` String CODEC(ZSTD(1)),
    `SpanId` String CODEC(ZSTD(1)),
    `TraceFlags` UInt32 CODEC(ZSTD(1)),
    `SeverityText` LowCardinality(String) CODEC(ZSTD(1)),
    `SeverityNumber` Int32 CODEC(ZSTD(1)),
    `ServiceName` LowCardinality(String) CODEC(ZSTD(1)),
    `Body` String CODEC(ZSTD(1)),
    `ResourceSchemaUrl` String CODEC(ZSTD(1)),
    `ResourceAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    `ScopeSchemaUrl` String CODEC(ZSTD(1)),
    `ScopeName` String CODEC(ZSTD(1)),
    `ScopeVersion` String CODEC(ZSTD(1)),
    `ScopeAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    `LogAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    `RequestPage` String MATERIALIZED path(LogAttributes['request_path']),
    `RequestType` LowCardinality(String) MATERIALIZED LogAttributes['request_type'],
    `RefererDomain` String MATERIALIZED domain(LogAttributes['referer'])
)
ENGINE = MergeTree
PARTITION BY toDate(Timestamp)
ORDER BY (ServiceName, SeverityText, toUnixTimestamp(Timestamp), TraceId)

关于如何通过 JSON 函数从字符串类型的 Body 列提取数据的等效模式，请参见相关文档【https://pastila.nl/?005cbb97/513b174a7d6114bf17ecc657428cf829#gqoOOiomEjIiG6zlWhE+Sg==】。

在示例中，我们定义了三个物化字段，分别提取了请求页面、请求类型和引用域名。这些字段从 Map 中提取键值，并对其值进行了相应的处理。经过优化后，查询性能显著提升：

SELECT RequestPage AS path, count() AS c
FROM otel_logs
WHERE RequestType = 'POST'
GROUP BY path
ORDER BY c DESC
LIMIT 5

┌─path─────────────────────┬─────c─┐
│ /m/updateVariation       │ 12182 │
│ /site/productCard        │ 11080 │
│ /site/productPrice       │ 10876 │
│ /site/productAdditives   │ 10866 │
│ /site/productModelImages │ 10866 │
└──────────────────────────┴───────┘

5 rows in set. Elapsed: 0.173 sec. Processed 10.37 million rows, 418.03 MB (60.07 million rows/s., 2.42 GB/s.)
Peak memory usage: 3.16 MiB.

注意

默认情况下，物化字段不会出现在 SELECT * 查询的结果中。这是为了确保 SELECT * 的结果可以直接通过 INSERT 插回表中。如果需要包含物化字段，可以通过设置 asterisk_include_materialized_columns=1 来启用该功能，在 Grafana 中也可以启用（参见数据源配置中的“Additional Settings -> Custom Settings”）。

基于物化视图的日志与追踪优化

物化视图为日志和追踪的数据处理提供了一种更强大的 SQL 筛选和转换方式。

通过物化视图，用户可以将计算的开销从查询阶段转移到数据写入阶段。ClickHouse 的物化视图本质上是一个触发器，它会在数据写入表时执行指定查询，并将查询结果存储到另一个“目标”表中。

实时更新的特性

在 ClickHouse 中，物化视图随着数据流入基表实时更新，其功能更接近于持续更新的索引。而在其他数据库中，物化视图通常是查询结果的静态快照，需要手动刷新（类似于 ClickHouse 中的可刷新物化视图）。

物化视图的查询可以是几乎任何 SELECT 语句，包括聚合查询，但在涉及 Joins 时存在一定的限制。对于日志和追踪所需的转换和筛选任务，几乎所有的查询都可以被支持。

需要注意，物化视图的查询只是一个触发器，用于对插入源表的每一行数据执行计算，并将结果写入目标表。

为了避免数据同时存储在源表和目标表中，可以将源表的存储引擎更改为 Null 表引擎，同时保留原始表的模式。OTel 采集器将继续向源表发送数据。例如，对于日志，可以将 otel_logs 表修改为：

CREATE TABLE otel_logs
(
    `Timestamp` DateTime64(9) CODEC(Delta(8), ZSTD(1)),
    `TraceId` String CODEC(ZSTD(1)),
    `SpanId` String CODEC(ZSTD(1)),
    `TraceFlags` UInt32 CODEC(ZSTD(1)),
    `SeverityText` LowCardinality(String) CODEC(ZSTD(1)),
    `SeverityNumber` Int32 CODEC(ZSTD(1)),
    `ServiceName` LowCardinality(String) CODEC(ZSTD(1)),
    `Body` String CODEC(ZSTD(1)),
    `ResourceSchemaUrl` String CODEC(ZSTD(1)),
    `ResourceAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    `ScopeSchemaUrl` String CODEC(ZSTD(1)),
    `ScopeName` String CODEC(ZSTD(1)),
    `ScopeVersion` String CODEC(ZSTD(1)),
    `ScopeAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    `LogAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1))
) ENGINE = Null

Null 表引擎：日志处理优化

Null 表引擎是一个强大的优化工具，可以理解为 /dev/null。该表不会存储任何数据，但所有关联的物化视图会在插入数据时执行相应的操作，随后这些数据会被丢弃。

查询示例

以下查询将行数据转换为所需的格式，从 LogAttributes 中提取所有列（假设这些列由采集器通过 json_parser 操作符设置），并根据简单条件和列定义生成或提取 SeverityText 和 SeverityNumber。在该示例中，我们仅选择已知会被填充的列，忽略例如 TraceId、SpanId 和 TraceFlags 这样的列。

SELECT
        Body, 
    Timestamp::DateTime AS Timestamp,
    ServiceName,
    LogAttributes['status'] AS Status,
    LogAttributes['request_protocol'] AS RequestProtocol,
    LogAttributes['run_time'] AS RunTime,
    LogAttributes['size'] AS Size,
    LogAttributes['user_agent'] AS UserAgent,
    LogAttributes['referer'] AS Referer,
    LogAttributes['remote_user'] AS RemoteUser,
    LogAttributes['request_type'] AS RequestType,
    LogAttributes['request_path'] AS RequestPath,
    LogAttributes['remote_addr'] AS RemoteAddr,
    domain(LogAttributes['referer']) AS RefererDomain,
    path(LogAttributes['request_path']) AS RequestPage,
    multiIf(Status::UInt64 > 500, 'CRITICAL', Status::UInt64 > 400, 'ERROR', Status::UInt64 > 300, 'WARNING', 'INFO') AS SeverityText,
    multiIf(Status::UInt64 > 500, 20, Status::UInt64 > 400, 17, Status::UInt64 > 300, 13, 9) AS SeverityNumber
FROM otel_logs
LIMIT 1
FORMAT Vertical

Row 1:
──────
Body:           {"remote_addr":"54.36.149.41","remote_user":"-","run_time":"0","time_local":"2019-01-22 00:26:14.000","request_type":"GET","request_path":"\/filter\/27|13 ,27|  5 ,p53","request_protocol":"HTTP\/1.1","status":"200","size":"30577","referer":"-","user_agent":"Mozilla\/5.0 (compatible; AhrefsBot\/6.1; +http:\/\/ahrefs.com\/robot\/)"}
Timestamp:      2019-01-22 00:26:14
ServiceName:
Status:         200
RequestProtocol: HTTP/1.1
RunTime:        0
Size:           30577
UserAgent:      Mozilla/5.0 (compatible; AhrefsBot/6.1; +http://ahrefs.com/robot/)
Referer:        -
RemoteUser:     -
RequestType:    GET
RequestPath:    /filter/27|13 ,27|  5 ,p53
RemoteAddr:     54.36.149.41
RefererDomain:
RequestPage:    /filter/27|13 ,27|  5 ,p53
SeverityText:   INFO
SeverityNumber:  9

1 row in set. Elapsed: 0.027 sec.

此外，我们还提取了 Body 列，以便未来可能新增未被 SQL 提取的属性。由于该列压缩性能较好且访问频率低，因此不会对查询性能产生影响。最后，通过类型转换将 Timestamp 列简化为 DateTime 类型，从而节省存储空间（详见“优化类型”一节）。

条件语句的重要性

上述查询中使用了条件语句来提取 SeverityText 和 SeverityNumber。这些语句非常适合制定复杂条件，例如检查 Map 中是否存在某些值。尽管我们假设 LogAttributes 中所有键都存在，但在实际场景中，条件语句与处理空值的函数同样是解析日志的强大工具，建议用户熟练掌握这些功能。

目标表设计

以下是与上述查询匹配的目标表设计：

CREATE TABLE otel_logs_v2
(
    `Body` String,
    `Timestamp` DateTime,
    `ServiceName` LowCardinality(String),
    `Status` UInt16,
    `RequestProtocol` LowCardinality(String),
    `RunTime` UInt32,
    `Size` UInt32,
    `UserAgent` String,
    `Referer` String,
    `RemoteUser` String,
    `RequestType` LowCardinality(String),
    `RequestPath` String,
    `RemoteAddress` IPv4,
    `RefererDomain` String,
    `RequestPage` String,
    `SeverityText` LowCardinality(String),
    `SeverityNumber` UInt8
)
ENGINE = MergeTree
ORDER BY (ServiceName, Timestamp)

该表中使用的数据类型基于“优化类型”一节中的优化建议。

注意事项 

在上述过程中，我们显著优化并调整了数据模式。用户通常还需要保留其他列，例如 Trace 列和 ResourceAttributes 列（通常包含 Kubernetes 元数据）。Grafana 能够利用 Trace 列为日志和追踪数据提供链接功能，详见“使用 Grafana”。

接下来，我们创建一个名为 otel_logs_mv 的物化视图，用于对 otel_logs 表执行上述 SELECT 查询，并将结果存入 otel_logs_v2 表。

CREATE MATERIALIZED VIEW otel_logs_mv TO otel_logs_v2 AS
SELECT
        Body, 
    Timestamp::DateTime AS Timestamp,
    ServiceName,
    LogAttributes['status']::UInt16 AS Status,
    LogAttributes['request_protocol'] AS RequestProtocol,
    LogAttributes['run_time'] AS RunTime,
    LogAttributes['size'] AS Size,
    LogAttributes['user_agent'] AS UserAgent,
    LogAttributes['referer'] AS Referer,
    LogAttributes['remote_user'] AS RemoteUser,
    LogAttributes['request_type'] AS RequestType,
    LogAttributes['request_path'] AS RequestPath,
    LogAttributes['remote_addr'] AS RemoteAddress,
    domain(LogAttributes['referer']) AS RefererDomain,
    path(LogAttributes['request_path']) AS RequestPage,
    multiIf(Status::UInt64 > 500, 'CRITICAL', Status::UInt64 > 400, 'ERROR', Status::UInt64 > 300, 'WARNING', 'INFO') AS SeverityText,
    multiIf(Status::UInt64 > 500, 20, Status::UInt64 > 400, 17, Status::UInt64 > 300, 13, 9) AS SeverityNumber
FROM otel_logs

以下是这一过程的可视化结果：

如果我们重新启动“导出到 ClickHouse”一节中提到的采集器配置，数据将以期望的格式出现在 otel_logs_v2 表中。请注意，此查询中使用了类型化的 JSON 提取函数。

SELECT *
FROM otel_logs_v2
LIMIT 1
FORMAT Vertical

Row 1:
──────
Body:           {"remote_addr":"54.36.149.41","remote_user":"-","run_time":"0","time_local":"2019-01-22 00:26:14.000","request_type":"GET","request_path":"\/filter\/27|13 ,27|  5 ,p53","request_protocol":"HTTP\/1.1","status":"200","size":"30577","referer":"-","user_agent":"Mozilla\/5.0 (compatible; AhrefsBot\/6.1; +http:\/\/ahrefs.com\/robot\/)"}
Timestamp:      2019-01-22 00:26:14
ServiceName:
Status:         200
RequestProtocol: HTTP/1.1
RunTime:        0
Size:           30577
UserAgent:      Mozilla/5.0 (compatible; AhrefsBot/6.1; +http://ahrefs.com/robot/)
Referer:        -
RemoteUser:     -
RequestType:    GET
RequestPath:    /filter/27|13 ,27|  5 ,p53
RemoteAddress:  54.36.149.41
RefererDomain:
RequestPage:    /filter/27|13 ,27|  5 ,p53
SeverityText:   INFO
SeverityNumber:  9

1 row in set. Elapsed: 0.010 sec.

以下是一个等效的物化视图示例，该视图通过 JSON 函数从 Body 列中提取字段：

CREATE MATERIALIZED VIEW otel_logs_mv TO otel_logs_v2 AS
SELECT  Body, 
    Timestamp::DateTime AS Timestamp,
    ServiceName,
    JSONExtractUInt(Body, 'status') AS Status,
    JSONExtractString(Body, 'request_protocol') AS RequestProtocol,
    JSONExtractUInt(Body, 'run_time') AS RunTime,
    JSONExtractUInt(Body, 'size') AS Size,
    JSONExtractString(Body, 'user_agent') AS UserAgent,
    JSONExtractString(Body, 'referer') AS Referer,
    JSONExtractString(Body, 'remote_user') AS RemoteUser,
    JSONExtractString(Body, 'request_type') AS RequestType,
    JSONExtractString(Body, 'request_path') AS RequestPath,
    JSONExtractString(Body, 'remote_addr') AS remote_addr,
    domain(JSONExtractString(Body, 'referer')) AS RefererDomain,
    path(JSONExtractString(Body, 'request_path')) AS RequestPage,
    multiIf(Status::UInt64 > 500, 'CRITICAL', Status::UInt64 > 400, 'ERROR', Status::UInt64 > 300, 'WARNING', 'INFO') AS SeverityText,
    multiIf(Status::UInt64 > 500, 20, Status::UInt64 > 400, 17, Status::UInt64 > 300, 13, 9) AS SeverityNumber
FROM otel_logs

注意处理数据类型

 上述物化视图依赖隐式类型转换，尤其是在使用 LogAttributes Map 时。ClickHouse 通常会自动将提取的值转换为目标表的相应类型，从而简化了查询语法。然而，为确保数据类型处理正确，我们建议用户在创建物化视图时，通过将视图的 SELECT 语句与 INSERT INTO 语句结合测试，目标表应采用相同的模式。以下几种情况需要特别注意：

• 如果 Map 中的键不存在，ClickHouse 会返回空字符串。对于数值字段，用户需要将空字符串映射为适当的默认值。可以使用条件语句实现，例如 if(LogAttributes['status'] = "", 200, LogAttributes['status'])。如果接受默认值，也可以使用类型转换函数，例如 toUInt8OrDefault(LogAttributes['status'])。

• 某些类型的转换可能无法正确完成，例如数值字符串无法直接转换为枚举值。

• JSON 提取函数在未找到值时，会返回该类型的默认值。

  请确保这些默认值符合预期逻辑。

避免使用 Nullable 类型 

在 ClickHouse 中，可观测性数据通常无需区分空值与 NULL。因此，建议避免为日志和追踪数据使用 Nullable 类型。Nullable 会增加额外的存储开销，并可能对查询性能产生负面影响。有关更多信息，请参阅相关文档。

如何选择排序键

提取出目标列后，可以开始优化排序键（主键）的设计。

以下是一些简单的规则，用于帮助选择合适的排序键。这些规则有时可能互相冲突，因此建议按顺序评估。通过这个过程，用户通常可以选择 4-5 个键，足以满足需求：

1. 优先选择常用筛选条件的列：如果用户在进行可观测性分析时，通常以某一列作为筛选条件（例如 pod 名称），该列会频繁出现在 WHERE 子句中。相较于使用频率较低的列，这类列应优先包含在排序键中。

2. 优先选择能排除大量行的列：筛选条件中排除的行越多，读取的数据量越少，从而提高查询效率。服务名称和状态码通常是不错的候选列，但状态码仅在用户筛选条件能够排除大多数行时适用。例如，筛选 500 错误通常只匹配少量行，而筛选 200 状态码可能会匹配大多数行，不适合作为优化点。

3. 选择与其他列高度相关的列：高度相关的列存储在一起时，可以提高数据压缩效率。这对于提升存储性能非常重要。

4. 优化 GROUP BY 和 ORDER BY 操作：包含排序键的列在 GROUP BY 和 ORDER BY 操作中可以显著提高内存使用效率。

在确定排序键的列子集后，还需要按特定顺序声明这些列。排序顺序会显著影响查询中对次级键列的过滤效率，以及表数据文件的压缩率。通常建议将基数较低的列放在前面，但需要平衡这一点与排序键后置列筛选效率较低的情况。最终的设计需要结合访问模式进行权衡，并反复测试不同的排序键组合。关于排序键的进一步优化建议，我们推荐参考相关文章以获得更深入的理解【https://clickhouse.com/docs/en/optimize/sparse-primary-indexes】。

先设计数据结构

我们建议在整理好日志数据的结构之后再决定排序键的设计。避免在排序键中使用属性 Map 的键或 JSON 提取表达式。确保排序键所需的列已经作为表的顶层列。

关于 Map 的使用

在之前的示例中，我们演示了如何使用 map['key'] 语法访问 Map(String, String) 列中的值。除了这种访问方式，ClickHouse 还提供了专用的 Map 函数，可以用来筛选或选择这些列中的数据。

例如，以下查询使用 mapKeys 函数标识 LogAttributes 列中所有的唯一键，随后结合 groupArrayDistinctArray 函数（一个组合器）对结果进行去重处理：

SELECT groupArrayDistinctArray(mapKeys(LogAttributes))
FROM otel_logs
FORMAT Vertical

Row 1:
──────
groupArrayDistinctArray(mapKeys(LogAttributes)): ['remote_user','run_time','request_type','log.file.name','referer','request_path','status','user_agent','remote_addr','time_local','size','request_protocol']

1 row in set. Elapsed: 1.139 sec. Processed 5.63 million rows, 2.53 GB (4.94 million rows/s., 2.22 GB/s.)
Peak memory usage: 71.90 MiB.

避免在列名中使用点号

我们建议避免在 Map 列的名称中使用点号，并可能在未来弃用这种用法。推荐使用下划线（_）作为替代。

使用别名列简化查询

查询 Map 类型数据的性能通常低于普通列。此外，Map 的查询语法较复杂，编写起来较为繁琐。为了解决这一问题，我们建议使用别名列 (Alias columns)。

别名列的值在查询时计算，而不会存储在表中。因此，无法向别名列中插入值。通过定义别名列，可以简化 Map 键的引用，将其映射为普通列，提升查询的可读性。例如：

CREATE TABLE otel_logs
(
    `Timestamp` DateTime64(9) CODEC(Delta(8), ZSTD(1)),
    `TraceId` String CODEC(ZSTD(1)),
    `SpanId` String CODEC(ZSTD(1)),
    `TraceFlags` UInt32 CODEC(ZSTD(1)),
    `SeverityText` LowCardinality(String) CODEC(ZSTD(1)),
    `SeverityNumber` Int32 CODEC(ZSTD(1)),
    `ServiceName` LowCardinality(String) CODEC(ZSTD(1)),
    `Body` String CODEC(ZSTD(1)),
    `ResourceSchemaUrl` String CODEC(ZSTD(1)),
    `ResourceAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    `ScopeSchemaUrl` String CODEC(ZSTD(1)),
    `ScopeName` String CODEC(ZSTD(1)),
    `ScopeVersion` String CODEC(ZSTD(1)),
    `ScopeAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    `LogAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    `RequestPath` String MATERIALIZED path(LogAttributes['request_path']),
    `RequestType` LowCardinality(String) MATERIALIZED LogAttributes['request_type'],
    `RefererDomain` String MATERIALIZED domain(LogAttributes['referer']),
    `RemoteAddr` IPv4 ALIAS LogAttributes['remote_addr']
)
ENGINE = MergeTree
PARTITION BY toDate(Timestamp)
ORDER BY (ServiceName, Timestamp)

在以下示例中，我们定义了多个物化列，以及一个名为 RemoteAddr 的别名列，该列引用了 LogAttributes 中的键 remote_addr。现在，我们可以直接通过该别名列访问 LogAttributes['remote_addr'] 的值，从而简化了查询，例如：

SELECT RemoteAddr
FROM default.otel_logs
LIMIT 5

┌─RemoteAddr────┐
│ 54.36.149.41  │
│ 31.56.96.51   │
│ 31.56.96.51   │
│ 40.77.167.129 │
│ 91.99.72.15   │
└───────────────┘

5 rows in set. Elapsed: 0.011 sec.

此外，通过 ALTER TABLE 命令添加别名列非常简单。添加后，这些列会立即可用，例如：

ALTER TABLE default.otel_logs
    (ADD COLUMN `Size` String ALIAS LogAttributes['size'])

SELECT Size
FROM default.otel_logs_v3
LIMIT 5

┌─Size──┐
│ 30577 │
│ 5667  │
│ 5379  │
│ 1696  │
│ 41483 │
└───────┘

5 rows in set. Elapsed: 0.014 sec.

默认情况下别名列不包含在查询中

默认情况下，SELECT * 查询不会返回别名列。如果需要包含别名列，可以通过设置 asterisk_include_alias_columns=1 来启用该行为。

数据类型与压缩优化

ClickHouse 的通用数据类型优化实践同样适用于可观测性场景。

使用编解码器优化压缩

在优化数据类型的基础上，用户还可以通过选择合适的编解码器进一步提升压缩效率。

对于日志和追踪数据集，ZSTD 编解码器通常是首选。将压缩级别从默认值 1 提高可能会进一步提升压缩效果，但这需要经过测试验证。需要注意的是，压缩级别越高，数据写入时的 CPU 开销也会增加，而压缩率的提升可能有限。

时间戳数据在压缩时可以通过 delta 编码显著提升效率。然而，如果时间戳列用作主键或排序键，可能会导致查询性能下降。建议用户根据需求权衡压缩性能和查询效率。

利用字典优化查询

字典是 ClickHouse 的一项重要特性，能够将来自多种内部和外部来源的键值对数据存储在内存中，实现超低延迟的查询。

字典在许多场景下非常实用。例如，可以在数据写入时动态丰富数据而不降低摄取速度，也能显著提升查询性能，尤其是涉及 JOIN 操作时。尽管在可观测性场景中较少需要 JOIN 操作，字典仍然可以在数据丰富方面提供帮助，无论是在写入还是查询阶段。以下分别提供相关示例。

加速 JOIN 操作

如果需要通过字典加速 JOIN 操作，可参阅相关文档以获取更多详细信息。

插入阶段与查询阶段的比较

字典可以在插入阶段或查询阶段用于丰富数据集，每种方法都有其优缺点。以下是两种方法的总结：

• 插入阶段：如果丰富的值不会频繁变化，并且可以从外部数据源中获取，这种方法通常更适合。在插入时直接丰富数据可以避免查询阶段对字典的查找，从而减少查询延迟。不过，这种方法会增加插入的计算开销，并带来额外的存储需求，因为丰富后的值需要存储为表的列。

• 查询阶段：当字典中的值可能经常变化时，查询阶段的查找更加灵活。这种方式避免了数据变动时需要更新列或重写表的麻烦。但灵活性是以查询时的查找成本为代价的，特别是当需要对大量行进行字典查找时（例如在过滤条件中）。对于仅在 SELECT 中使用的结果丰富任务，这种查询成本通常可以忽略。

我们建议用户熟悉 ClickHouse 字典的基本概念。字典是一种内存中的高效查找表，用户可以通过专用函数快速检索值。

有关简单的丰富数据示例，请参阅字典指南以获取更多详细说明【https://clickhouse.com/docs/en/dictionary】。以下内容将重点介绍常见的可观测性数据丰富任务。

使用 IP 字典进行数据丰富

为日志和追踪数据添加地理信息（如经纬度）是常见的可观测性需求。这可以通过 ip_trie 字典实现。我们使用 DB-IP.com 提供的城市级数据集（在 CC BY 4.0 协议下发布）。

We use the publicly available DB-IP city-level dataset provided by DB-IP.com under the terms of the CC BY 4.0 license.

根据 README 文件的描述，该数据集的结构如下：

| ip_range_start | ip_range_end | country_code | state1 | state2 | city | postcode | latitude | longitude | timezone |

为了查看数据，我们可以使用 url() 表函数：

SELECT *
FROM url('https://raw.githubusercontent.com/sapics/ip-location-db/master/dbip-city/dbip-city-ipv4.csv.gz', 'CSV', '\n       \tip_range_start IPv4, \n       \tip_range_end IPv4, \n     \tcountry_code Nullable(String), \n     \tstate1 Nullable(String), \n       \tstate2 Nullable(String), \n       \tcity Nullable(String), \n     \tpostcode Nullable(String), \n     \tlatitude Float64, \n      \tlongitude Float64, \n     \ttimezone Nullable(String)\n   \t')
LIMIT 1
FORMAT Vertical
Row 1:
──────
ip_range_start: 1.0.0.0
ip_range_end:   1.0.0.255
country_code:   AU
state1:         Queensland
state2:         ᴺᵁᴸᴸ
city:           South Brisbane
postcode:       ᴺᵁᴸᴸ
latitude:       -27.4767
longitude:      153.017
timezone:       ᴺᵁᴸᴸ

为简化操作，我们使用 URL() 表引擎在 ClickHouse 中创建一个表对象，并设置字段名称以便确认总行数：

CREATE TABLE geoip_url(
    ip_range_start IPv4,
    ip_range_end IPv4,
    country_code Nullable(String),
    state1 Nullable(String),
    state2 Nullable(String),
    city Nullable(String),
    postcode Nullable(String),
    latitude Float64,
    longitude Float64,
    timezone Nullable(String)
) engine=URL('https://raw.githubusercontent.com/sapics/ip-location-db/master/dbip-city/dbip-city-ipv4.csv.gz', 'CSV')

select count() from geoip_url;

┌─count()─┐
│ 3261621 │ -- 3.26 million
└─────────┘

由于 ip_trie 字典要求 IP 范围以 CIDR 格式表示，我们需要将 ip_range_start 和 ip_range_end 转换为 CIDR 表示法。

这可以通过以下查询实现：

with
    bitXor(ip_range_start, ip_range_end) as xor,
    if(xor != 0, ceil(log2(xor)), 0) as unmatched,
    32 - unmatched as cidr_suffix,
    toIPv4(bitAnd(bitNot(pow(2, unmatched) - 1), ip_range_start)::UInt64) as cidr_address
select
    ip_range_start,
    ip_range_end,
    concat(toString(cidr_address),'/',toString(cidr_suffix)) as cidr    
from
    geoip_url
limit 4;

┌─ip_range_start─┬─ip_range_end─┬─cidr───────┐
│ 1.0.0.0        │ 1.0.0.255    │ 1.0.0.0/24 │
│ 1.0.1.0        │ 1.0.3.255    │ 1.0.0.0/22 │
│ 1.0.4.0        │ 1.0.7.255    │ 1.0.4.0/22 │
│ 1.0.8.0        │ 1.0.15.255   │ 1.0.8.0/21 │
└────────────────┴──────────────┴────────────┘

4 rows in set. Elapsed: 0.259 sec.

注意 

上述查询较为复杂，感兴趣的用户可以阅读相关文档以获取详细解释。对于快速实现目的，可以直接使用该查询来计算 IP 范围的 CIDR。

接下来，我们创建一个新表用于存储 IP 范围、国家代码和经纬度，并将 GeoIP 数据插入该表：

CREATE TABLE geoip
(
    `cidr` String,
    `latitude` Float64,
    `longitude` Float64,
    `country_code` String
)
ENGINE = MergeTree
ORDER BY cidr

INSERT INTO geoip
WITH
    bitXor(ip_range_start, ip_range_end) as xor,
    if(xor != 0, ceil(log2(xor)), 0) as unmatched,
    32 - unmatched as cidr_suffix,
    toIPv4(bitAnd(bitNot(pow(2, unmatched) - 1), ip_range_start)::UInt64) as cidr_address
SELECT
    concat(toString(cidr_address),'/',toString(cidr_suffix)) as cidr,
    latitude,
    longitude,
    country_code    
FROM geoip_url

为了实现低延迟的 IP 查找，我们将利用 ClickHouse 提供的 ip_trie 字典。该字典能够将网络前缀（CIDR 块）映射到经纬度和国家代码。以下查询定义了一个字典布局，并以我们创建的新表为数据源：

CREATE DICTIONARY ip_trie (
   cidr String,
   latitude Float64,
   longitude Float64,
   country_code String
)
primary key cidr
source(clickhouse(table 'geoip'))
layout(ip_trie)
lifetime(3600);

我们可以通过查询字典中的数据行，验证其是否可用于快速查找操作：

SELECT * FROM ip_trie LIMIT 3

┌─cidr───────┬─latitude─┬─longitude─┬─country_code─┐
│ 1.0.0.0/22 │  26.0998 │   119.297 │ CN           │
│ 1.0.0.0/24 │ -27.4767 │   153.017 │ AU           │
│ 1.0.4.0/22 │ -38.0267 │   145.301 │ AU           │
└────────────┴──────────┴───────────┴──────────────┘

3 rows in set. Elapsed: 4.662 sec.

实现自动定期刷新

在 ClickHouse 中，字典会根据底层表的数据以及配置的 LIFETIME 子句实现定期刷新。若要更新 GeoIP 字典以反映 DB-IP 数据集的最新变化，只需将 geoip_url 远程表中的数据重新插入到 geoip 表中，并在插入时应用所需的转换。

现在，GeoIP 数据已经加载到 ip_trie 字典中（字典名称同样为 ip_trie），我们可以利用它进行 IP 地理定位。这可以通过 dictGet() 函数实现，示例如下：

SELECT dictGet('ip_trie', ('country_code', 'latitude', 'longitude'), CAST('85.242.48.167', 'IPv4')) AS ip_details

┌─ip_details──────────────┐
│ ('PT',38.7944,-9.34284) │
└─────────────────────────┘

1 row in set. Elapsed: 0.003 sec.

通过这种方法，我们能够高效丰富日志数据。此示例中，我们选择在查询时完成数据丰富。

回到原始日志数据集，我们可以利用上述方法按国家对日志数据进行聚合。以下示例基于前面物化视图生成的模式，其中已提取 RemoteAddress 列：

SELECT dictGet('ip_trie', 'country_code', tuple(RemoteAddress)) AS country,
    formatReadableQuantity(count()) AS num_requests
FROM default.otel_logs_v2
WHERE country != ''
GROUP BY country
ORDER BY count() DESC
LIMIT 5

┌─country─┬─num_requests────┐
│ IR      │ 7.36 million    │
│ US      │ 1.67 million    │
│ AE      │ 526.74 thousand │
│ DE      │ 159.35 thousand │
│ FR      │ 109.82 thousand │
└─────────┴─────────────────┘

5 rows in set. Elapsed: 0.140 sec. Processed 20.73 million rows, 82.92 MB (147.79 million rows/s., 591.16 MB/s.)
Peak memory usage: 1.16 MiB.

由于 IP 地址与地理位置的映射可能会发生变化，用户通常更需要知道请求发出时的地理位置，而非当前同一 IP 地址的最新地理位置。因此，在这种场景下，插入时丰富更为合适。这可以通过定义物化列（如下所示）或在物化视图的 SELECT 查询中实现：

CREATE TABLE otel_logs_v2
(
    `Body` String,
    `Timestamp` DateTime,
    `ServiceName` LowCardinality(String),
    `Status` UInt16,
    `RequestProtocol` LowCardinality(String),
    `RunTime` UInt32,
    `Size` UInt32,
    `UserAgent` String,
    `Referer` String,
    `RemoteUser` String,
    `RequestType` LowCardinality(String),
    `RequestPath` String,
    `RemoteAddress` IPv4,
    `RefererDomain` String,
    `RequestPage` String,
    `SeverityText` LowCardinality(String),
    `SeverityNumber` UInt8,
        `Country` String MATERIALIZED dictGet('ip_trie', 'country_code', tuple(RemoteAddress)),
        `Latitude` Float32 MATERIALIZED dictGet('ip_trie', 'latitude', tuple(RemoteAddress)),
        `Longitude` Float32 MATERIALIZED dictGet('ip_trie', 'longitude', tuple(RemoteAddress))
)
ENGINE = MergeTree
ORDER BY (ServiceName, Timestamp)

实现字典的定期更新

为了确保 IP 丰富字典能够基于最新数据进行定期更新，可以利用字典的 LIFETIME 子句，使其定期从底层表重新加载数据。若需更新底层表数据，请参考“可刷新物化视图”【https://clickhouse.com/docs/en/materialized-view/refreshable-materialized-view】。

此外，上述国家和经纬度信息不仅可以用于按国家分组和筛选，还支持更丰富的可视化应用。更多示例请参阅“可视化地理数据”【https://clickhouse.com/docs/en/observability/grafana#visualizing-geo-data】。

使用正则表达式字典高效解析 User Agent

解析 User Agent 字符串是正则表达式应用的经典场景之一，也是日志和追踪数据分析中的常见需求。ClickHouse 支持通过正则表达式树字典高效解析 User Agent 数据。

在 ClickHouse 开源版本中，正则表达式树字典使用 `YAMLRegExpTree` 字典源类型，指定包含正则表达式树的 YAML 文件路径。如果需要自定义正则表达式字典，可参考相关文档了解结构要求【https://clickhouse.com/docs/en/sql-reference/dictionaries#use-regular-expression-tree-dictionary-in-clickhouse-open-source】。以下示例演示如何使用 uap-core 提供的规则进行 User Agent 解析，并加载支持的 CSV 格式字典。这种方法兼容 OSS 和 ClickHouse Cloud。

注意

以下示例基于 2024 年 6 月最新版 uap-core 提供的 User Agent 正则表达式规则快照。该文件会偶尔更新，您可以在官方链接找到最新版本。按照指定步骤，用户可以将规则加载到示例中使用的 CSV 文件中。

首先，我们创建以下内存表，用于存储解析设备、浏览器和操作系统的正则表达式规则：

CREATE TABLE regexp_os
(
    id UInt64,
    parent_id UInt64,
    regexp String,
    keys   Array(String),
    values Array(String)
) ENGINE=Memory;

CREATE TABLE regexp_browser
(
    id UInt64,
    parent_id UInt64,
    regexp String,
    keys   Array(String),
    values Array(String)
) ENGINE=Memory;

CREATE TABLE regexp_device
(
    id UInt64,
    parent_id UInt64,
    regexp String,
    keys   Array(String),
    values Array(String)
) ENGINE=Memory;

这些内存表的数据可以通过公开托管的 CSV 文件填充，使用 url 表函数即可完成：

INSERT INTO regexp_os SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/user_agent_regex/regexp_os.csv', 'CSV', 'id UInt64, parent_id UInt64, regexp String, keys Array(String), values Array(String)')

INSERT INTO regexp_device SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/user_agent_regex/regexp_device.csv', 'CSV', 'id UInt64, parent_id UInt64, regexp String, keys Array(String), values Array(String)')

INSERT INTO regexp_browser SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/user_agent_regex/regexp_browser.csv', 'CSV', 'id UInt64, parent_id UInt64, regexp String, keys Array(String), values Array(String)')

在填充了内存表后，我们可以加载正则表达式字典。需要特别注意的是，字典的键值需要指定为列，这些列将定义我们从 User Agent 数据中提取的属性。

CREATE DICTIONARY regexp_os_dict
(
    regexp String,
    os_replacement String default 'Other',
    os_v1_replacement String default '0',
    os_v2_replacement String default '0',
    os_v3_replacement String default '0',
    os_v4_replacement String default '0'
)
PRIMARY KEY regexp
SOURCE(CLICKHOUSE(TABLE 'regexp_os'))
LIFETIME(MIN 0 MAX 0)
LAYOUT(REGEXP_TREE);

CREATE DICTIONARY regexp_device_dict
(
    regexp String,
    device_replacement String default 'Other',
    brand_replacement String,
    model_replacement String
)
PRIMARY KEY(regexp)
SOURCE(CLICKHOUSE(TABLE 'regexp_device'))
LIFETIME(0)
LAYOUT(regexp_tree);

CREATE DICTIONARY regexp_browser_dict
(
    regexp String,
    family_replacement String default 'Other',
    v1_replacement String default '0',
    v2_replacement String default '0'
)
PRIMARY KEY(regexp)
SOURCE(CLICKHOUSE(TABLE 'regexp_browser'))
LIFETIME(0)
LAYOUT(regexp_tree);

字典加载完成后，可以通过示例 User Agent 测试其提取功能：

WITH 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:127.0) Gecko/20100101 Firefox/127.0' AS user_agent
SELECT
    dictGet('regexp_device_dict', ('device_replacement', 'brand_replacement', 'model_replacement'), user_agent) AS device,
    dictGet('regexp_browser_dict', ('family_replacement', 'v1_replacement', 'v2_replacement'), user_agent) AS browser,
    dictGet('regexp_os_dict', ('os_replacement', 'os_v1_replacement', 'os_v2_replacement', 'os_v3_replacement'), user_agent) AS os

┌─device────────────────┬─browser───────────────┬─os─────────────────────────┐
│ ('Mac','Apple','Mac') │ ('Firefox','127','0') │ ('Mac OS X','10','15','0') │
└───────────────────────┴───────────────────────┴────────────────────────────┘

1 row in set. Elapsed: 0.003 sec.

由于 User Agent 的解析规则更新频率较低，通常只需在出现新浏览器、操作系统或设备时更新字典，因此更适合在插入时完成数据提取操作。

我们可以选择使用物化列或物化视图实现这一目标。以下是对之前定义的物化视图的优化示例：

CREATE MATERIALIZED VIEW otel_logs_mv TO otel_logs_v2
AS SELECT
    Body,
    CAST(Timestamp, 'DateTime') AS Timestamp,
    ServiceName,
    LogAttributes['status'] AS Status,
    LogAttributes['request_protocol'] AS RequestProtocol,
    LogAttributes['run_time'] AS RunTime,
    LogAttributes['size'] AS Size,
    LogAttributes['user_agent'] AS UserAgent,
    LogAttributes['referer'] AS Referer,
    LogAttributes['remote_user'] AS RemoteUser,
    LogAttributes['request_type'] AS RequestType,
    LogAttributes['request_path'] AS RequestPath,
    LogAttributes['remote_addr'] AS RemoteAddress,
    domain(LogAttributes['referer']) AS RefererDomain,
    path(LogAttributes['request_path']) AS RequestPage,
    multiIf(CAST(Status, 'UInt64') > 500, 'CRITICAL', CAST(Status, 'UInt64') > 400, 'ERROR', CAST(Status, 'UInt64') > 300, 'WARNING', 'INFO') AS SeverityText,
    multiIf(CAST(Status, 'UInt64') > 500, 20, CAST(Status, 'UInt64') > 400, 17, CAST(Status, 'UInt64') > 300, 13, 9) AS SeverityNumber,
    dictGet('regexp_device_dict', ('device_replacement', 'brand_replacement', 'model_replacement'), UserAgent) AS Device,
    dictGet('regexp_browser_dict', ('family_replacement', 'v1_replacement', 'v2_replacement'), UserAgent) AS Browser,
    dictGet('regexp_os_dict', ('os_replacement', 'os_v1_replacement', 'os_v2_replacement', 'os_v3_replacement'), UserAgent) AS Os
FROM otel_logs

我们需要对目标表 otel_logs_v2 的模式进行调整，以支持新的数据提取需求：

CREATE TABLE default.otel_logs_v2
(
 `Body` String,
 `Timestamp` DateTime,
 `ServiceName` LowCardinality(String),
 `Status` UInt8,
 `RequestProtocol` LowCardinality(String),
 `RunTime` UInt32,
 `Size` UInt32,
 `UserAgent` String,
 `Referer` String,
 `RemoteUser` String,
 `RequestType` LowCardinality(String),
 `RequestPath` String,
 `remote_addr` IPv4,
 `RefererDomain` String,
 `RequestPage` String,
 `SeverityText` LowCardinality(String),
 `SeverityNumber` UInt8,
 `Device` Tuple(device_replacement LowCardinality(String), brand_replacement LowCardinality(String), model_replacement LowCardinality(String)),
 `Browser` Tuple(family_replacement LowCardinality(String), v1_replacement LowCardinality(String), v2_replacement LowCardinality(String)),
 `Os` Tuple(os_replacement LowCardinality(String), os_v1_replacement LowCardinality(String), os_v2_replacement LowCardinality(String), os_v3_replacement LowCardinality(String))
)
ENGINE = MergeTree
ORDER BY (ServiceName, Timestamp, Status)

在重新启动采集器并按照之前文档步骤摄取结构化日志后，我们可以查询提取出的 Device、Browser 和 Os 列。

SELECT Device, Browser, Os
FROM otel_logs_v2
LIMIT 1
FORMAT Vertical

Row 1:
──────
Device:  ('Spider','Spider','Desktop')
Browser: ('AhrefsBot','6','1')
Os:     ('Other','0','0','0')

使用元组管理复杂结构

在解析 User Agent 数据时，我们使用了元组 (Tuple) 数据类型。对于层级关系明确的复杂结构，元组是推荐的选择。它不仅支持存储不同类型的数据，其子列的性能表现也与普通列一致（与 Map 键的性能不同）。

推荐阅读材料

如果您希望了解更多有关字典的高级用法和示例，我们推荐以下文章：

• 高级字典主题 【https://clickhouse.com/docs/en/dictionary#advanced-dictionary-topics】

• 《使用字典加速查询》【https://clickhouse.com/blog/faster-queries-dictionaries-clickhouse】

• 字典功能详解 【https://clickhouse.com/docs/en/sql-reference/dictionaries】

提升查询性能

ClickHouse 提供了多种技术用于提升查询性能。在优化主键（排序键）以支持最常用的访问模式并实现数据压缩后，您可以考虑以下方法来进一步优化查询效率。这通常是提高性能的最关键一步。

通过物化视图实现增量聚合

在之前的章节中，我们讨论了如何使用物化视图完成数据转换和过滤。而物化视图还可以用于在插入阶段预计算聚合并存储结果。后续的数据插入将自动更新这些结果，从而实现高效的增量聚合。

这一方法的核心在于，聚合结果通常是原始数据的简化表示（例如聚合任务中的部分摘要）。通过将查询简化为从目标表读取聚合结果，与直接在原始数据上执行复杂计算相比，可以显著提升查询速度。

以下示例展示了如何通过结构化日志数据计算每小时的总流量：

SELECT toStartOfHour(Timestamp) AS Hour,
    sum(toUInt64OrDefault(LogAttributes['size'])) AS TotalBytes
FROM otel_logs
GROUP BY Hour
ORDER BY Hour DESC
LIMIT 5

┌────────────────Hour─┬─TotalBytes─┐
│ 2019-01-26 16:00:00 │ 1661716343 │
│ 2019-01-26 15:00:00 │ 1824015281 │
│ 2019-01-26 14:00:00 │ 1506284139 │
│ 2019-01-26 13:00:00 │ 1580955392 │
│ 2019-01-26 12:00:00 │ 1736840933 │
└─────────────────────┴────────────┘

5 rows in set. Elapsed: 0.666 sec. Processed 10.37 million rows, 4.73 GB (15.56 million rows/s., 7.10 GB/s.)
Peak memory usage: 1.40 MiB.

这种查询可能是用户在 Grafana 中常用的折线图请求。虽然在当前数据集（仅 1000 万行）上的性能已经非常快，但随着数据规模扩展到数十亿甚至数万亿行时，我们希望能够保持同样的查询效率。

建议

如果使用前文中通过物化视图生成的 otel_logs_v2 表，该查询的性能将提升 10 倍，因为该表已经提取了 LogAttributes Map 中的 size 键。此处的示例仅为演示原始数据的查询方式。如果这是一个常见查询场景，我们建议使用物化视图以提升查询性能。

如果我们希望在插入阶段通过物化视图计算结果，就需要一个专门的表来接收这些结果。该表需保证每小时仅存储一行数据。如果接收到已存在小时的数据更新，其它列应与当前小时的数据合并。为实现这种增量状态的合并，其它列需支持存储部分计算结果。

在 ClickHouse 中，这可以通过特殊引擎类型 **SummingMergeTree** 实现。该引擎会将所有具有相同排序键的行合并为一行，并对数值列进行求和操作。以下是一个示例表结构，该表会自动将具有相同日期的行合并，并汇总其数值列：

CREATE TABLE bytes_per_hour
(
  `Hour` DateTime,
  `TotalBytes` UInt64
)
ENGINE = SummingMergeTree
ORDER BY Hour

为了演示物化视图的效果，假设 bytes_per_hour 表当前为空，尚未接收到任何数据。我们定义一个物化视图，用于对插入到 otel_logs 表的数据执行 SELECT 查询（按配置的块大小逐批执行），并将查询结果发送到 bytes_per_hour 表。语法如下：

CREATE MATERIALIZED VIEW bytes_per_hour_mv TO bytes_per_hour AS
SELECT toStartOfHour(Timestamp) AS Hour,
       sum(toUInt64OrDefault(LogAttributes['size'])) AS TotalBytes
FROM otel_logs
GROUP BY Hour

此处的 TO 子句 是关键部分，用于指定查询结果的目标表，即 bytes_per_hour。

接下来，如果重新启动 Otel Collector 并重新发送日志数据，bytes_per_hour 表将通过上述查询结果逐步填充。完成后，我们可以验证 bytes_per_hour 的大小——理想情况下，每小时仅有一行数据：

SELECT count()
FROM bytes_per_hour
FINAL

┌─count()─┐
│   113 │
└─────────┘

1 row in set. Elapsed: 0.039 sec

通过存储查询结果，我们成功将 otel_logs 表中的行数从 1000 万减少到了 113 行。关键在于，当新日志插入到 otel_logs 表时，相应的数据会发送到 bytes_per_hour 表并在后台异步合并，从而确保 bytes_per_hour 表始终保持小巧并实时更新。

由于行合并是异步执行的，用户查询时可能会暂时看到某些小时内的多行记录。为确保查询时合并所有未处理的行，我们可以采用以下两种方法：

1. 在查询中为表名添加 FINAL 修饰符（如前文示例中用于计数查询）。

2. 按目标表的排序键（如 Timestamp）进行 GROUP BY 操作，并对指标列求和。

第二种方法通常更高效且灵活，因为表可以用于其他场景，而第一种方法则适用于简单查询。以下分别演示了这两种方法：

SELECT
    Hour,
    sum(TotalBytes) AS TotalBytes
FROM bytes_per_hour
GROUP BY Hour
ORDER BY Hour DESC
LIMIT 5

┌────────────────Hour─┬─TotalBytes─┐
│ 2019-01-26 16:00:00 │ 1661716343 │
│ 2019-01-26 15:00:00 │ 1824015281 │
│ 2019-01-26 14:00:00 │ 1506284139 │
│ 2019-01-26 13:00:00 │ 1580955392 │
│ 2019-01-26 12:00:00 │ 1736840933 │
└─────────────────────┴────────────┘

5 rows in set. Elapsed: 0.008 sec.

SELECT
    Hour,
    TotalBytes
FROM bytes_per_hour
FINAL
ORDER BY Hour DESC
LIMIT 5

┌────────────────Hour─┬─TotalBytes─┐
│ 2019-01-26 16:00:00 │ 1661716343 │
│ 2019-01-26 15:00:00 │ 1824015281 │
│ 2019-01-26 14:00:00 │ 1506284139 │
│ 2019-01-26 13:00:00 │ 1580955392 │
│ 2019-01-26 12:00:00 │ 1736840933 │
└─────────────────────┴────────────┘

5 rows in set. Elapsed: 0.005 sec.

通过这种优化，我们将查询时间从 0.6 秒缩短到了 0.008 秒，性能提升超过 75 倍！

注意

在更大规模的数据集和更复杂的查询场景下，这种优化的效果将更加显著。您可以参阅相关文档获取更多示例。

更复杂的场景：计算唯一值

对于简单求和之外的统计（如每日唯一 IP 地址或唯一用户数），需要使用更复杂的表引擎：AggregatingMergeTree。

假设我们需要计算每天的唯一 IP 地址数，查询示例如下：

SELECT toStartOfHour(Timestamp) AS Hour, uniq(LogAttributes['remote_addr']) AS UniqueUsers
FROM otel_logs
GROUP BY Hour
ORDER BY Hour DESC

┌────────────────Hour─┬─UniqueUsers─┐
│ 2019-01-26 16:00:00 │     4763    │
…
│ 2019-01-22 00:00:00 │     536     │
└─────────────────────┴─────────────┘

113 rows in set. Elapsed: 0.667 sec. Processed 10.37 million rows, 4.73 GB (15.53 million rows/s., 7.09 GB/s.)

为了实现支持增量更新的基数统计，需要使用 AggregatingMergeTree 表引擎。

CREATE TABLE unique_visitors_per_hour
(
  `Hour` DateTime,
  `UniqueUsers` AggregateFunction(uniq, IPv4)
)
ENGINE = AggregatingMergeTree
ORDER BY Hour

为让 ClickHouse 识别将存储聚合状态，我们需要将 UniqueUsers 列定义为 AggregateFunction 类型，并指定部分状态函数（如 `uniq`）和源列类型（如 IPv4）。与 SummingMergeTree 类似，具有相同 ORDER BY 键值的行将在插入时合并（如按小时分组的数据）。

以下是相关物化视图的定义：

CREATE MATERIALIZED VIEW unique_visitors_per_hour_mv TO unique_visitors_per_hour AS
SELECT toStartOfHour(Timestamp) AS Hour,
    uniqState(LogAttributes['remote_addr']::IPv4) AS UniqueUsers
FROM otel_logs
GROUP BY Hour
ORDER BY Hour DESC

请注意，我们为聚合函数添加了 State 后缀，这确保返回的是函数的聚合状态，而非最终结果。聚合状态包含额外的部分数据，用于与其他状态合并。

重新加载数据后（通过重启采集器），我们可以确认 unique_visitors_per_hour 表中包含 113 行记录。

SELECT count()
FROM unique_visitors_per_hour
FINAL
┌─count()─┐
│   113   │
└─────────┘

1 row in set. Elapsed: 0.009 sec.

最终查询时，我们需要使用聚合函数的 Merge 后缀（因为列存储的是部分聚合状态）：

SELECT Hour, uniqMerge(UniqueUsers) AS UniqueUsers
FROM unique_visitors_per_hour
GROUP BY Hour
ORDER BY Hour DESC

┌────────────────Hour─┬─UniqueUsers─┐
│ 2019-01-26 16:00:00 │      4763   │

│ 2019-01-22 00:00:00 │      536    │
└─────────────────────┴─────────────┘

113 rows in set. Elapsed: 0.027 sec.

在此查询中，我们使用了 GROUP BY 而非 FINAL 来完成聚合处理。

增量物化视图加速查询

在选择 ClickHouse 的排序键时，用户需要根据访问模式，将常用于过滤和聚合的列优先设置为排序键的一部分。然而，在可观测性场景中，访问模式通常更加多样化，很难用单一列集完全覆盖所有需求。这种限制在 OTel 的默认模式中尤为明显。以下以追踪（traces）的默认模式为例：

CREATE TABLE otel_traces
(
    `Timestamp` DateTime64(9) CODEC(Delta(8), ZSTD(1)),
    `TraceId` String CODEC(ZSTD(1)),
    `SpanId` String CODEC(ZSTD(1)),
    `ParentSpanId` String CODEC(ZSTD(1)),
    `TraceState` String CODEC(ZSTD(1)),
    `SpanName` LowCardinality(String) CODEC(ZSTD(1)),
    `SpanKind` LowCardinality(String) CODEC(ZSTD(1)),
    `ServiceName` LowCardinality(String) CODEC(ZSTD(1)),
    `ResourceAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    `ScopeName` String CODEC(ZSTD(1)),
    `ScopeVersion` String CODEC(ZSTD(1)),
    `SpanAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    `Duration` Int64 CODEC(ZSTD(1)),
    `StatusCode` LowCardinality(String) CODEC(ZSTD(1)),
    `StatusMessage` String CODEC(ZSTD(1)),
    `Events.Timestamp` Array(DateTime64(9)) CODEC(ZSTD(1)),
    `Events.Name` Array(LowCardinality(String)) CODEC(ZSTD(1)),
    `Events.Attributes` Array(Map(LowCardinality(String), String)) CODEC(ZSTD(1)),
    `Links.TraceId` Array(String) CODEC(ZSTD(1)),
    `Links.SpanId` Array(String) CODEC(ZSTD(1)),
    `Links.TraceState` Array(String) CODEC(ZSTD(1)),
    `Links.Attributes` Array(Map(LowCardinality(String), String)) CODEC(ZSTD(1)),
    INDEX idx_trace_id TraceId TYPE bloom_filter(0.001) GRANULARITY 1,
    INDEX idx_res_attr_key mapKeys(ResourceAttributes) TYPE bloom_filter(0.01) GRANULARITY 1,
    INDEX idx_res_attr_value mapValues(ResourceAttributes) TYPE bloom_filter(0.01) GRANULARITY 1,
    INDEX idx_span_attr_key mapKeys(SpanAttributes) TYPE bloom_filter(0.01) GRANULARITY 1,
    INDEX idx_span_attr_value mapValues(SpanAttributes) TYPE bloom_filter(0.01) GRANULARITY 1,
    INDEX idx_duration Duration TYPE minmax GRANULARITY 1
)
ENGINE = MergeTree
PARTITION BY toDate(Timestamp)
ORDER BY (ServiceName, SpanName, toUnixTimestamp(Timestamp), TraceId)

默认模式针对 ServiceName、SpanName 和 Timestamp 的过滤进行了优化。然而，在追踪分析中，用户还经常需要根据特定的 TraceId 查找并检索关联的 spans。尽管 TraceId 被包含在排序键中，但由于其排序靠后，导致过滤效率较低，通常需要扫描大量数据才能完成单个追踪的检索。

为了解决这一问题，OTel Collector 提供了一个物化视图及其关联表，定义如下：

CREATE TABLE otel_traces_trace_id_ts
(
    `TraceId` String CODEC(ZSTD(1)),
    `Start` DateTime64(9) CODEC(Delta(8), ZSTD(1)),
    `End` DateTime64(9) CODEC(Delta(8), ZSTD(1)),
    INDEX idx_trace_id TraceId TYPE bloom_filter(0.01) GRANULARITY 1
)
ENGINE = MergeTree
ORDER BY (TraceId, toUnixTimestamp(Start))


CREATE MATERIALIZED VIEW otel_traces_trace_id_ts_mv TO otel_traces_trace_id_ts
(
    `TraceId` String,
    `Start` DateTime64(9),
    `End` DateTime64(9)
)
AS SELECT
    TraceId,
    min(Timestamp) AS Start,
    max(Timestamp) AS End
FROM otel_traces
WHERE TraceId != ''
GROUP BY TraceId

此视图确保 otel_traces_trace_id_ts 表能够高效存储每个追踪的最小和最大时间戳。由于该表按 TraceId 排序，可以快速检索对应的时间戳范围。这些时间戳范围随后用于过滤主表 otel_traces，提高了检索效率。例如，当通过 TraceId 检索追踪时，Grafana 使用的查询如下：

WITH 'ae9226c78d1d360601e6383928e4d22d' AS trace_id,
    (
        SELECT min(Start)
          FROM default.otel_traces_trace_id_ts
          WHERE TraceId = trace_id
    ) AS trace_start,
    (
        SELECT max(End) + 1
          FROM default.otel_traces_trace_id_ts
          WHERE TraceId = trace_id
    ) AS trace_end
SELECT
    TraceId AS traceID,
    SpanId AS spanID,
    ParentSpanId AS parentSpanID,
    ServiceName AS serviceName,
    SpanName AS operationName,
    Timestamp AS startTime,
    Duration * 0.000001 AS duration,
    arrayMap(key -> map('key', key, 'value', SpanAttributes[key]), mapKeys(SpanAttributes)) AS tags,
    arrayMap(key -> map('key', key, 'value', ResourceAttributes[key]), mapKeys(ResourceAttributes)) AS serviceTags
FROM otel_traces
WHERE (traceID = trace_id) AND (startTime >= trace_start) AND (startTime <= trace_end)
LIMIT 1000

在此查询中，CTE 用于获取追踪 ID ae9226c78d1d360601e6383928e4d22d 的最小和最大时间戳。随后，这些时间戳被用于过滤主表 otel_traces，以提取相关的 spans。

这种方法不仅适用于追踪，还可以扩展到其他类似的访问模式。在“数据建模”一节中，我们探讨了一个类似的实现方案【https://clickhouse.com/docs/en/materialized-view#lookup-table】。

通过投影优化查询

ClickHouse 的投影功能允许用户为表定义多个 ORDER BY 子句，从而支持更多复杂的查询模式。

在前面的章节中，我们探讨了如何通过物化视图在 ClickHouse 中实现聚合计算、行数据转换，并优化可观测性查询的访问模式。

例如，我们演示了如何通过物化视图，将数据发送到排序键不同的目标表，以优化基于 TraceId 的查询。

投影可以解决类似的问题，允许用户为非主键列优化查询模式。

尽管投影可以为表提供多个排序键，但这一功能存在明显的代价：数据重复。具体来说，数据需要按主键顺序写入，同时也需按每个投影指定的顺序写入。这不仅会降低数据插入的速度，还会显著增加磁盘空间的使用。

投影与物化视图的比较

投影和物化视图在功能上有许多相似之处，但通常情况下，我们更推荐使用物化视图。投影的使用应基于明确的场景需求。以预计算聚合为例，尽管投影支持该功能，我们仍建议优先选择物化视图。

以下是一个查询示例，筛选 otel_logs_v2 表中包含 500 错误代码的记录。这种查询模式在日志分析中非常常见，用户通常希望通过错误代码筛选数据：

SELECT Timestamp, RequestPath, Status, RemoteAddress, UserAgent
FROM otel_logs_v2
WHERE Status = 500
FORMAT `Null`

Ok.

0 rows in set. Elapsed: 0.177 sec. Processed 10.37 million rows, 685.32 MB (58.66 million rows/s., 3.88 GB/s.)
Peak memory usage: 56.54 MiB.

通过 Null 测量性能

在上述查询中，我们使用了 FORMAT Null 输出格式。此设置会强制读取所有查询结果，但不返回数据，从而避免因 LIMIT 提前终止查询的情况。这种方法用于准确测量扫描 1000 万行数据所需的时间。

在上述查询中，使用排序键 (ServiceName, Timestamp) 需要执行线性扫描。虽然我们可以通过将 Status 添加到排序键的末尾来提升查询性能，但另一种更灵活的方式是添加投影。

ALTER TABLE otel_logs_v2 (
  ADD PROJECTION status
  (
     SELECT Timestamp, RequestPath, Status, RemoteAddress, UserAgent ORDER BY Status
  )
)

ALTER TABLE otel_logs_v2 MATERIALIZE PROJECTION status

创建投影时，首先需要定义投影结构，然后执行物化操作。物化过程会将数据以两种不同的顺序存储在磁盘上。投影也可以在创建数据表时定义，这样会在数据插入时自动更新，如下所示：

CREATE TABLE otel_logs_v2
(
    `Body` String,
    `Timestamp` DateTime,
    `ServiceName` LowCardinality(String),
    `Status` UInt16,
    `RequestProtocol` LowCardinality(String),
    `RunTime` UInt32,
    `Size` UInt32,
    `UserAgent` String,
    `Referer` String,
    `RemoteUser` String,
    `RequestType` LowCardinality(String),
    `RequestPath` String,
    `RemoteAddress` IPv4,
    `RefererDomain` String,
    `RequestPage` String,
    `SeverityText` LowCardinality(String),
    `SeverityNumber` UInt8,
        PROJECTION status
    (
           SELECT Timestamp, RequestPath, Status, RemoteAddress, UserAgent
       ORDER BY Status
    )
)
ENGINE = MergeTree
ORDER BY (ServiceName, Timestamp)

需要注意的是，如果通过 ALTER 语句创建投影，在执行 MATERIALIZE PROJECTION 命令时，物化过程是异步的。用户可以通过以下查询检查操作进度，并等待 is_done=1 表示完成：

SELECT parts_to_do, is_done, latest_fail_reason
FROM system.mutations
WHERE (`table` = 'otel_logs_v2') AND (command LIKE '%MATERIALIZE%')

┌─parts_to_do─┬─is_done─┬─latest_fail_reason─┐
│           0 │     1   │                    │
└─────────────┴─────────┴────────────────────┘

1 row in set. Elapsed: 0.008 sec.

重复上述查询后，我们可以发现性能有了显著提升，但这也带来了额外的存储成本。具体的存储开销可以参考“测量表大小和压缩”一节进行评估。

SELECT Timestamp, RequestPath, Status, RemoteAddress, UserAgent
FROM otel_logs_v2
WHERE Status = 500
FORMAT `Null`

0 rows in set. Elapsed: 0.031 sec. Processed 51.42 thousand rows, 22.85 MB (1.65 million rows/s., 734.63 MB/s.)
Peak memory usage: 27.85 MiB.

在示例中，我们在投影中明确指定了查询所需的列。这意味着只有这些列会按 Status 排序存储在磁盘上。如果改为使用 SELECT *，则所有列都会被存储。这种设置虽然可以使更多查询（使用任意列的子集）受益于投影，但会显著增加存储需求。关于如何测量磁盘空间和压缩率，请参阅“测量表大小和压缩”一节。

跳过索引与二级索引优化

在 ClickHouse 中，即使优化了主键设计，仍然会有一些查询需要进行全表扫描。尽管可以通过物化视图或投影来缓解这种情况，但它们需要额外的维护，并要求用户明确其存在并加以利用。传统关系型数据库通常使用二级索引来解决全表扫描的问题，但在 ClickHouse 这样的列式数据库中，二级索引的效果有限。为此，ClickHouse 提供了“跳过索引”功能，可以通过跳过不包含匹配值的大数据块来显著提高查询效率。

默认的 OTel 模式中包含的二级索引尝试加速 Map 的访问，但实践证明这些索引通常效果有限，因此我们不建议在自定义模式中直接复制这些配置。不过，在某些场景下，跳过索引依然非常有用。

在尝试应用二级索引之前，建议用户先仔细阅读并熟悉相关指南。

一般来说，当主键与目标的非主键列或表达式之间存在较强的关联性，并且用户需要查询稀有值（即仅在少数数据块中出现的值）时，二级索引能够显著提高查询效率。

布隆过滤器加速文本搜索

在可观测性查询中，当用户需要对文本数据进行搜索时，二级索引非常实用。例如，基于 ngram 和 token 的布隆过滤器索引（ngrambf_v1 和 tokenbf_v1）能够加速 String 列上的 LIKE、IN 和 hasToken 运算符搜索。基于 token 的索引将非字母数字字符作为分隔符，因此仅支持在查询时匹配 token（或完整单词）。如果需要更细粒度的匹配，可以使用 N-gram 布隆过滤器，将字符串分割为指定大小的 ngram，从而支持子词匹配。

以下是生成并评估 token 的示例，使用 tokens 函数：

SELECT tokens('https://www.zanbil.ir/m/filter/b113')

┌─tokens────────────────────────────────────────────┐
│ ['https','www','zanbil','ir','m','filter','b113'] │
└───────────────────────────────────────────────────┘

1 row in set. Elapsed: 0.008 sec.

对于 ngram 匹配，可使用 ngram 函数并通过第二个参数指定 ngram 的大小：

SELECT ngrams('https://www.zanbil.ir/m/filter/b113', 3)

┌─ngrams('https://www.zanbil.ir/m/filter/b113', 3)────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ ['htt','ttp','tps','ps:','s:/','://','//w','/ww','www','ww.','w.z','.za','zan','anb','nbi','bil','il.','l.i','.ir','ir/','r/m','/m/','m/f','/fi','fil','ilt','lte','ter','er/','r/b','/b1','b11','113'] │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

1 row in set. Elapsed: 0.008 sec.

倒排索引的实验性支持

ClickHouse 当前对倒排索引作为二级索引提供实验性支持。尽管目前不建议在日志数据集中使用倒排索引，但它在未来的生产环境中可能会取代基于 token 的布隆过滤器，用户可关注其后续发展。

在本示例中，我们使用结构化日志数据集。假设需要统计 Referer 列中包含 ultra 的日志数量。

SELECT count()
FROM otel_logs_v2
WHERE Referer LIKE '%ultra%'

┌─count()─┐
│  114514 │
└─────────┘

1 row in set. Elapsed: 0.177 sec. Processed 10.37 million rows, 908.49 MB (58.57 million rows/s., 5.13 GB/s.)

在这种情况下，匹配所需的 ngram 大小为 3。因此，我们创建了一个 ngrambf_v1 索引，其参数为 ngrambf_v1(3, 10000, 3, 7)。

CREATE TABLE otel_logs_bloom
(
    `Body` String,
    `Timestamp` DateTime,
    `ServiceName` LowCardinality(String),
    `Status` UInt16,
    `RequestProtocol` LowCardinality(String),
    `RunTime` UInt32,
    `Size` UInt32,
    `UserAgent` String,
    `Referer` String,
    `RemoteUser` String,
    `RequestType` LowCardinality(String),
    `RequestPath` String,
    `RemoteAddress` IPv4,
    `RefererDomain` String,
    `RequestPage` String,
    `SeverityText` LowCardinality(String),
    `SeverityNumber` UInt8,
    INDEX idx_span_attr_value Referer TYPE ngrambf_v1(3, 10000, 3, 7) GRANULARITY 1
)
ENGINE = MergeTree
ORDER BY (Timestamp)

布隆过滤器参数解释

•  ngram 大小：第一个参数（3）指定 ngram 的大小。

•  过滤器大小 m：第二个参数（10000）决定过滤器的存储容量。

• 哈希函数数量 k：第三个参数（7）表示使用的哈希函数个数。

•  种子值：最后一个参数（7）用于初始化哈希函数的种子。

• k 和 m 的参数设置需要根据实际数据特性调整，例如唯一 ngram/token 的数量以及过滤器排除非匹配值（假阴性）的概率。我们推荐使用相关函数和工具来帮助确定这些参数。

如果参数调优得当，布隆过滤器可以显著提升查询速度：

SELECT count()
FROM otel_logs_bloom
WHERE Referer LIKE '%ultra%'
┌─count()─┐
│   182   │
└─────────┘

1 row in set. Elapsed: 0.077 sec. Processed 4.22 million rows, 375.29 MB (54.81 million rows/s., 4.87 GB/s.)
Peak memory usage: 129.60 KiB.

注意：示例仅供参考

上述示例仅用于说明布隆过滤器的基本用法。我们通常建议用户在数据插入时提取日志结构，而不是依赖基于 token 的布隆过滤器进行文本搜索。但对于堆栈跟踪或其他结构不确定的大型字符串数据，布隆过滤器仍然是有效的解决方案。

布隆过滤器的使用指南

布隆过滤器的主要目标是过滤数据块，从而避免加载列的所有值并执行线性扫描。用户可以通过带参数 indexes=1 的 EXPLAIN 子句来查看跳过的数据块数量。以下是原始表 otel_logs_v2 和带有 ngram 布隆过滤器的表 otel_logs_bloom 的性能对比：

EXPLAIN indexes = 1
SELECT count()
FROM otel_logs_v2
WHERE Referer LIKE '%ultra%'

┌─explain────────────────────────────────────────────────────────────┐
│ Expression ((Project names + Projection))                          │
│   Aggregating                                                      │
│   Expression (Before GROUP BY)                                     │
│       Filter ((WHERE + Change column names to column identifiers)) │
│       ReadFromMergeTree (default.otel_logs_v2)                     │
│       Indexes:                                                     │
│           PrimaryKey                                               │
│           Condition: true                                          │
│           Parts: 9/9                                               │
│           Granules: 1278/1278                                      │
└────────────────────────────────────────────────────────────────────┘

10 rows in set. Elapsed: 0.016 sec.


EXPLAIN indexes = 1
SELECT count()
FROM otel_logs_bloom
WHERE Referer LIKE '%ultra%'

┌─explain────────────────────────────────────────────────────────────┐
│ Expression ((Project names + Projection))                          │
│   Aggregating                                                      │
│   Expression (Before GROUP BY)                                     │
│       Filter ((WHERE + Change column names to column identifiers)) │
│       ReadFromMergeTree (default.otel_logs_bloom)                  │
│       Indexes:                                                     │
│           PrimaryKey                                               │ 
│           Condition: true                                          │
│           Parts: 8/8                                               │
│           Granules: 1276/1276                                      │
│           Skip                                                     │
│           Name: idx_span_attr_value                                │
│           Description: ngrambf_v1 GRANULARITY 1                    │
│           Parts: 8/8                                               │
│           Granules: 517/1276                                       │
└────────────────────────────────────────────────────────────────────┘

布隆过滤器的性能通常只有在其大小小于列本身时才会显著提升。如果过滤器的大小超过列本身，性能上的提升可能非常有限。您可以通过以下查询比较过滤器与列的实际大小：

SELECT
    name,
    formatReadableSize(sum(data_compressed_bytes)) AS compressed_size,
    formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size,
    round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 2) AS ratio
FROM system.columns
WHERE (`table` = 'otel_logs_bloom') AND (name = 'Referer')
GROUP BY name
ORDER BY sum(data_compressed_bytes) DESC

┌─name────┬─compressed_size─┬─uncompressed_size─┬─ratio─┐
│ Referer │ 56.16 MiB       │ 789.21 MiB        │ 14.05 │
└─────────┴─────────────────┴───────────────────┴───────┘

1 row in set. Elapsed: 0.018 sec.


SELECT
    `table`,
    formatReadableSize(data_compressed_bytes) AS compressed_size,
    formatReadableSize(data_uncompressed_bytes) AS uncompressed_size
FROM system.data_skipping_indices
WHERE `table` = 'otel_logs_bloom'

┌─table───────────┬─compressed_size─┬─uncompressed_size─┐
│ otel_logs_bloom │ 12.03 MiB       │ 12.17 MiB         │
└─────────────────┴─────────────────┴───────────────────┘

1 row in set. Elapsed: 0.004 sec.

在上述示例中，布隆过滤器索引的大小为 12MB，比列压缩后的大小 56MB 小了接近 5 倍。

布隆过滤器的性能需要精心调优。我们建议参考相关指南，【https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#bloom-filter】帮助确定适合您数据集的最佳参数设置。此外，请注意，布隆过滤器可能会在数据插入和合并阶段带来较高的性能开销。在将布隆过滤器部署到生产环境前，应充分评估其对插入性能的影响。

更多关于二级跳过索引的详细信息，请参阅相关文档【https://clickhouse.com/docs/en/optimize/skipping-indexes#skip-index-functions】。

优化 Map 类型的查询

在 OTel 模式中，Map 类型被广泛用于存储键值对元数据（如 Kubernetes 标签）。但需注意，当查询 Map 的子键时，整个父列都会被加载。如果 Map 包含大量键，这将显著增加查询开销，因为需要从磁盘读取的数据量远大于单独存储键时的情况。

如果某个键在查询中被频繁使用，建议将其提取为顶层的独立列。这种优化通常在部署后根据实际访问模式进行，因此可能难以在生产环境部署之前预测。有关模式调整的具体方法，请参阅“管理模式更改”章节。

提升数据压缩效率

ClickHouse 在可观测性领域广受欢迎的一个重要原因是其卓越的压缩能力。

高效的压缩不仅能够显著降低存储成本，还能减少磁盘 I/O，从而提升查询和插入的速度。相较于压缩算法可能增加的 CPU 开销，I/O 减少带来的性能提升更加显著。因此，优化数据压缩是提升 ClickHouse 查询性能的关键。

有关如何测量表大小和压缩效果的详细信息，请参阅相关文档【https://clickhouse.com/docs/en/data-compression/compression-in-clickhouse】。

征稿启示

面向社区长期正文，文章内容包括但不限于关于 ClickHouse 的技术研究、项目实践和创新做法等。建议行文风格干货输出&图文并茂。质量合格的文章将会发布在本公众号，优秀者也有机会推荐到 ClickHouse 官网。请将文章稿件的 WORD 版本发邮件至：Tracy.Wang@clickhouse.com

联系我们

手机号：13910395701

邮箱：Tracy.Wang@clickhouse.com

满足您所有的在线分析列式数据库管理需求