ClickHouse物化视图（来自OTEL）无法更新问题求助

我尝试在ClickHouse中创建物化视图，将Map类型转为JSON格式，同时从同一Map的子键生成两个JSON列用于动态列。仅将LogAttributes列转为JSON时视图正常，但添加另外两个来自LogAttributes子键的列后，视图无法正常工作。

源表结构 #

CREATE TABLE default.events
(
    `Timestamp` DateTime64(9) CODEC(Delta(8), ZSTD(1)),
    `TimestampTime` DateTime DEFAULT toDateTime(Timestamp),
    `TraceId` String CODEC(ZSTD(1)),
    `SpanId` String CODEC(ZSTD(1)),
    `TraceFlags` UInt8,
    `SeverityText` LowCardinality(String) CODEC(ZSTD(1)),
    `SeverityNumber` UInt8,
    `ServiceName` LowCardinality(String) CODEC(ZSTD(1)),
    `Body` String CODEC(ZSTD(1)),
    `ResourceSchemaUrl` LowCardinality(String) CODEC(ZSTD(1)),
    `ResourceAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    `ScopeSchemaUrl` LowCardinality(String) CODEC(ZSTD(1)),
    `ScopeName` String CODEC(ZSTD(1)),
    `ScopeVersion` LowCardinality(String) CODEC(ZSTD(1)),
    `ScopeAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    `LogAttributes` 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_scope_attr_key mapKeys(ScopeAttributes) TYPE bloom_filter(0.01) GRANULARITY 1,
    INDEX idx_scope_attr_value mapValues(ScopeAttributes) TYPE bloom_filter(0.01) GRANULARITY 1,
    INDEX idx_log_attr_key mapKeys(LogAttributes) TYPE bloom_filter(0.01) GRANULARITY 1,
    INDEX idx_log_attr_value mapValues(LogAttributes) TYPE bloom_filter(0.01) GRANULARITY 1,
    INDEX idx_body Body TYPE tokenbf_v1(32768, 3, 0) GRANULARITY 8
)
ENGINE = MergeTree
PARTITION BY toDate(TimestampTime)
PRIMARY KEY (ServiceName, TimestampTime)
ORDER BY (ServiceName, TimestampTime, Timestamp)
TTL TimestampTime + toIntervalHour(18)
SETTINGS index_granularity = 8192, ttl_only_drop_parts = 1

目标表结构 #

CREATE TABLE default.events_op
(
    `Timestamp` DateTime64(9),
    `TimestampTime` DateTime,
    `TraceId` String,
    `SpanId` String,
    `TraceFlags` UInt8,
    `SeverityText` LowCardinality(String),
    `SeverityNumber` UInt8,
    `ServiceName` LowCardinality(String),
    `Body` String,
    `ResourceSchemaUrl` LowCardinality(String),
    `ResourceAttributes` Map(LowCardinality(String), String),
    `ScopeSchemaUrl` LowCardinality(String),
    `ScopeName` String,
    `ScopeVersion` LowCardinality(String),
    `ScopeAttributes` Map(LowCardinality(String), String),
    `LogAttributes` Map(LowCardinality(String), String),
    `app_side_metrics` JSON,
    `profiling_metrics` JSON,
    `attributes` JSON
)
ENGINE = MergeTree
PARTITION BY ServiceName
ORDER BY (ServiceName, TimestampTime, Timestamp)
SETTINGS index_granularity = 8192

原物化视图（异常版本） #

CREATE MATERIALIZED VIEW default.events_mv_op TO default.events_op
(
    `Timestamp` DateTime64(9),
    `TimestampTime` DateTime,
    `TraceId` String,
    `SpanId` String,
    `TraceFlags` UInt8,
    `SeverityText` LowCardinality(String),
    `SeverityNumber` UInt8,
    `ServiceName` LowCardinality(String),
    `Body` String,
    `ResourceSchemaUrl` LowCardinality(String),
    `ResourceAttributes` Map(LowCardinality(String), String),
    `ScopeSchemaUrl` LowCardinality(String),
    `ScopeName` String,
    `ScopeVersion` LowCardinality(String),
    `ScopeAttributes` Map(LowCardinality(String), String),
    `LogAttributes` Map(LowCardinality(String), String),
    `app_side_metrics` String,
    `profiling_metrics` String,
    `attributes` String
)
AS SELECT
    Timestamp,
    TimestampTime,
    TraceId,
    SpanId,
    TraceFlags,
    SeverityText,
    SeverityNumber,
    ServiceName,
    Body,
    ResourceSchemaUrl,
    ResourceAttributes,
    ScopeSchemaUrl,
    ScopeName,
    ScopeVersion,
    ScopeAttributes,
    LogAttributes,
    toJSONString(LogAttributes['app_side_metrics__stringified']) AS app_side_metrics,
    toJSONString(LogAttributes['profiling_metrics__stringified']) AS profiling_metrics,
    toJSONString(LogAttributes) AS attributes
FROM default.events

问题原因分析 #

• 类型不匹配：目标表中app_side_metrics、profiling_metrics、attributes为JSON类型，但物化视图定义中这三个列是String类型，且toJSONString返回的是字符串格式的JSON，直接插入JSON列会导致类型转换失败。
• 子键空值处理缺失：当LogAttributes中不存在app_side_metrics__stringified或profiling_metrics__stringified键时，LogAttributes['xxx']返回空字符串，toJSONString处理空字符串会生成""，这不是合法的JSON结构，写入JSON列会报错。
• Map转JSON方式错误：toJSONString将Map转为JSON字符串，而JSON列需要的是原生JSON类型值，而非字符串。

修正后的物化视图 #

CREATE MATERIALIZED VIEW default.events_mv_op TO default.events_op
(
    `Timestamp` DateTime64(9),
    `TimestampTime` DateTime,
    `TraceId` String,
    `SpanId` String,
    `TraceFlags` UInt8,
    `SeverityText` LowCardinality(String),
    `SeverityNumber` UInt8,
    `ServiceName` LowCardinality(String),
    `Body` String,
    `ResourceSchemaUrl` LowCardinality(String),
    `ResourceAttributes` Map(LowCardinality(String), String),
    `ScopeSchemaUrl` LowCardinality(String),
    `ScopeName` String,
    `ScopeVersion` LowCardinality(String),
    `ScopeAttributes` Map(LowCardinality(String), String),
    `LogAttributes` Map(LowCardinality(String), String),
    `app_side_metrics` JSON,
    `profiling_metrics` JSON,
    `attributes` JSON
)
AS SELECT
    Timestamp,
    TimestampTime,
    TraceId,
    SpanId,
    TraceFlags,
    SeverityText,
    SeverityNumber,
    ServiceName,
    Body,
    ResourceSchemaUrl,
    ResourceAttributes,
    ScopeSchemaUrl,
    ScopeName,
    ScopeVersion,
    ScopeAttributes,
    LogAttributes,
    -- 子键存在则解析为JSON，否则返回空JSON对象
    ifNotNull(parseJSON(LogAttributes['app_side_metrics__stringified']), '{}'::JSON) AS app_side_metrics,
    ifNotNull(parseJSON(LogAttributes['profiling_metrics__stringified']), '{}'::JSON) AS profiling_metrics,
    -- 直接将Map转为原生JSON类型
    mapToJSON(LogAttributes) AS attributes
FROM default.events

修正关键点 #

• 列类型严格匹配：物化视图中三个动态列的类型改为JSON，与目标表完全一致。
• 原生JSON转换：使用mapToJSON替代toJSONString，直接生成JSON类型值，避免字符串转JSON的额外开销和错误。
• 空值兼容处理：通过ifNotNull和parseJSON确保子键不存在或内容非法时，返回合法的空JSON对象，避免写入失败。

内容的提问来源于stack exchange，提问作者NondairyDig