ClickHouse JSON 与半结构化数据处理：导入、提取、性能陷阱与建模

JSON 数据特征：
□ 动态 schema：字段不固定（不同行字段不同）
□ 嵌套：多层结构（对象、数组）
□ 稀疏：很多字段只有部分行有值

与列式存储的冲突：
□ 列式靠「固定列 + 类型」高效存储与裁剪
□ JSON 是「动态键 + 变长值」→ 列式优势发挥不出来
□ 整段 JSON 存一个 String 列 → 无法按内部字段裁剪
  → 查询内部字段 = 每行解析 + 全列扫描（慢）

核心矛盾：
□ 想要「查询内部字段」→ 需要「结构化」
□ 想保留「灵活 schema」→ 牺牲「查询性能」
→ 建模就是在「灵活 vs 性能」间找平衡

解决方案总览：
□ 全存 String → 灵活但慢（临时用）
□ 提前展平 → 快但改 schema 要改表
□ 混合（核心字段结构化 + 剩余 JSON 保留）→ 平衡

冲突示意：
JSON 行：{"user":123, "action":"click", "meta":{...}}
整存 String → 查 action 要全列解析
展平列 → user/action 变成列 → 可索引可裁剪
→ 常用字段展平、不常用字段留 JSON

存 JSON 的类型：
□ String：原始 JSON 文本存一列
  → 灵活（任何 JSON 都能存），慢（查询解析）
□ JSON 类型（新版 JSON 列类型）：
  → 半结构化列，内部自动拆子列
  → 比 String 好，但相对展平列仍有开销
□ Map(String, String)：键值对
  → 适合「扁平键值」（无嵌套）
□ 展平为固定列：把已知字段拆成列
  → 最快，但 schema 固定

取舍对比：
□ String：最灵活、最慢 → 保留原始 / 临时
□ JSON 类型：半结构化平衡 → 混合方案主力
□ Map：扁平键值快 → 无嵌套的 KV 场景
□ 展平列：最快 → 高频查询字段

选择逻辑：
□ 字段已知且高频 → 展平列（快）
□ 字段动态但扁平 → Map / JSON 类型
□ 字段嵌套多变 → JSON 类型 / String
□ 要保留原始 → 保留一份 String（原始审计）

实践建议：
□ 核心查询字段 → 展平列
□ 长尾未知字段 → JSON 类型 / String 冗余
→ 混合：一列「核心展平 + 一列 JSON 保留」

-- 混合列类型（示意）
CREATE TABLE events (
  event_time DateTime,
  user_id UInt64,                 -- 核心展平列
  action LowCardinality(String),  -- 核心展平列
  payload JSON,                   -- 剩余半结构化保留
) ENGINE = MergeTree() ORDER BY (event_time, user_id);

导入格式：
□ JSONEachRow：每行一个 JSON 对象（日志/事件最常用）
□ JSONCompact / JSONString：其他 JSON 变体
□ 输入格式在 INSERT 时指定

导入时的字段映射：
□ 直接映射：JSON 字段 → 同名列
  → 列名 = JSON key（匹配则自动映射）
□ 提取（EXTRACT）：从 JSON 提取子字段
  → 例如 metadata.trace_id → trace_id 列
□ 类型转换：JSON 值 → 列类型（对齐类型）

提取方式：
□ 导入时 SQL 提取（SELECT jsonExtract... FROM input）
□ 物化列（MATERIALIZED）：插入时自动算（见第 6 节）
□ 物化视图：导入进明细 → 视图提取宽表

处理动态字段：
□ 未知字段 → 落到 JSON 保留列
□ 类型不一致 → 统一转换（数值化/字符串化）
□ 缺失字段 → 默认值 / NULL 语义

导入最佳实践：
□ 常用字段在导入时展平（一次解析，多次查询快）
□ 原始 JSON 保留一份（审计/回溯）
□ 批量 + 异步（避免逐行解析开销）

-- JSONEachRow 导入（示意）
INSERT INTO events
SELECT
  toDateTime(jsonExtractString(raw, 'event_time')) AS event_time,
  toUInt64(jsonExtractString(raw, 'user_id')) AS user_id,
  raw
FROM input('raw String') FORMAT JSONEachRow;

路径访问语法：
□ 点路径：obj.field.subfield
□ 数组索引：obj.arr[0]
□ 通配/动态：字段名带数字（arr.1、arr.2）

常用函数（提取）：
□ jsonExtractString / jsonExtractUInt / jsonExtractFloat：
  按类型取字段（JSON 路径）
□ JSONPath 语法：$.field.subfield / $['field']
□ 简化访问：列名.字段名（JSON 类型列直接点）

查询写法：
□ 直接点字段：payload.action（JSON 类型列）
□ 显式函数：jsonExtractString(payload, 'action')
□ 过滤：WHERE 字段条件（注意性能，见下节）

数组与嵌套：
□ arrayJoin：展开数组逐行处理
□ JSONExtractArrayRaw：取数组原始
□ 嵌套路径：多级点号

注意：
□ 字段不存在 → 默认值 / 空
□ 类型不匹配 → 转换失败/默认
□ 路径大小写敏感（与原始 JSON 一致）

-- JSON 提取查询示例
SELECT
  user_id,
  payload.action,                                    -- JSON 类型直接点
  jsonExtractUInt(payload, 'meta.retry_count') AS retries,
  jsonExtractString(payload, 'device.os') AS os
FROM events
WHERE payload.action = 'click' AND user_id = 123;

JSON 查询慢的根因：
□ 全列扫描：String 存整段 JSON → 每行解析
  → 没有列式裁剪（JSON 是「一列里的内部结构」）
□ 每行解析开销：jsonExtract 每次查询都解析 JSON
  → CPU 密集，量大时明显
□ 无法走索引：JSON 内部字段不在主键/分区
  → 无法前缀裁剪
□ 稀疏数据：字段很多行没有 → 处理大量空值

性能陷阱清单：
□ 高频字段用 jsonExtract 每次查（应展平）
□ 大 JSON 整列存 String（无裁剪）
□ 过滤条件打在 JSON 内部字段（全表扫）
□ 数组/嵌套滥用（展开成本高）

优化方向：
□ 高频字段 → 展平列（列式 + 索引 + 裁剪）
□ 低频字段 → 保留 JSON（少访问不心疼）
□ 过滤 → 尽量打展平列 / 分区主键
□ 物化列：插入时算好，查询直接读（见下节）
→ JSON 保留「长尾」，别让它扛「高频」

判断：WHERE 打在哪个字段？
打在展平列 → 走索引快
打在 JSON 内部 → 全列解析慢
→ 高频过滤字段必须展平，否则每次查询都解析全列

手段一：展平（Flatten）
□ 高频字段在导入时拆成列
□ 收益：列式存储 + 可索引 + 可裁剪
□ 代价：schema 固定（新字段要改表）

手段二：物化列（MATERIALIZED）
□ 插入时自动计算（从 JSON 提取字段）
□ 查询直接读物化列（不用每次 jsonExtract）
□ 语法：字段定义 MATERIALIZED <表达式>
  → 插入时不需提供，自动生成
□ 收益：查询快（预提取）+ 无手工提取
□ 注意：物化列不占插入数据，占存储

手段三：模式演进（Schema Evolution）
□ 新字段出现 → 加展平列（ADD COLUMN，便宜）
□ 加物化列补提取 → 旧数据回填（默认值）
□ 保留 JSON 原始 → 随时可「补提取」任意字段
  → 原始 JSON 是「演进保险」

组合策略：
□ 核心字段：展平列 / 物化列（快）
□ 长尾字段：JSON 保留（灵活）
□ 演进：加列便宜 + 原始 JSON 兜底

-- 物化列：插入时自动提取（示意）
CREATE TABLE events (
  event_time DateTime,
  raw String,                                  -- 原始保留
  user_id UInt64 MATERIALIZED
    toUInt64(jsonExtractString(raw, 'user_id')), -- 自动提取
  action String MATERIALIZED
    jsonExtractString(raw, 'action')
) ENGINE = MergeTree() ORDER BY event_time;
-- 插入只需提供 raw，user_id/action 自动生成

混合建模模式：
□ 核心层：高频字段 → 展平列（列式 + 索引）
□ 保留层：原始 JSON → 一个 String 列（审计/兜底）
□ 中间层：动态/低频字段 → JSON 类型 / Map

三层的分工：
□ 展平列：查询主力（快）
□ JSON 保留：演进保险（随时补提取）
□ 中间层：半结构化灵活（不长驻列）

设计步骤：
1. 列高频查询字段（TOP N）→ 展平
2. 剩余字段 → 动态归 JSON 保留
3. 特别动态的扁平 KV → Map/JSON 类型
4. 加物化列提取「逐渐变高频」的字段

演进节奏：
□ 新字段观察：先用 JSON 查询（慢但可用）
□ 变高频 → 加物化列/展平（升级为快列）
□ 变冷 → 保持 JSON（不占列空间）

原则：
□ 高频快列、低频慢查（JSON）都合理
□ 别「全部展平」（改表成本高）
□ 别「全部 JSON」（查询全慢）
→ 平衡在「查询热度」驱动的迁移

热度驱动：
字段查询热 → 升级为展平/物化列
字段查询冷 → 留在 JSON（不占列）
→ Schema 是「热度驱动」的动态演化

场景：应用日志（JSON 格式）入 ClickHouse 做分析

链路：
采集（Filebeat/Fluentd）→ 消息队列（Kafka）→ ClickHouse
或直接 Kafka 表引擎接入

Schema 设计：
□ 时间：event_time（展平，分区键）
□ 服务/环境：service、env（展平，低基数）
□ 日志级别：level（展平，枚举/低基数）
□ 核心字段：trace_id、user_id（展平，可过滤）
□ 消息：message（展平 String）
□ 动态字段：extra JSON（原始保留）

查询模式：
□ 按时间范围 + 服务过滤 → 分区 + 主键裁剪
□ 按 level 统计 → 低基数列快
□ 按 trace_id 关联 → 展平列过滤
□ 动态字段 → JSON 查询（低频可接受）

优化：
□ 分区按天（日志量大按小时）
□ 物化列提取 trace_id（从 JSON 自动）
□ TTL 冷层（老日志迁 S3，见 S3 篇）
□ 预聚合（错误数/请求量看板 → 物化视图）

-- 日志表建模（示意）
CREATE TABLE app_logs (
  event_time DateTime,
  service LowCardinality(String),
  level Enum8('debug'=1,'info'=2,'warn'=3,'error'=4),
  trace_id String,
  user_id UInt64,
  message String,
  extra JSON
) ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(event_time)
ORDER BY (event_time, service, level);
-- 时间范围 + 服务 + 级别 → 全走裁剪

方案对比矩阵：
□ 全展平列：最快、存储最优，但 schema 固定
□ JSON 类型：半结构化平衡、支持嵌套、较展平略慢
□ Map：扁平 KV 快、内存可控，但无类型/嵌套
□ String 整存：最灵活最慢，纯兜底

| 方案 | 灵活 | 性能 | 适用 |
|------|------|------|------|
| 展平列 | 低 | 最高 | 高频已知字段 |
| JSON 类型 | 中 | 中 | 动态半结构化 |
| Map | 中低 | 中高 | 扁平 KV |
| String | 高 | 低 | 原始保留/兜底 |

选型决策：
□ 字段已知高频 → 展平列
□ 字段动态嵌套 → JSON 类型
□ 扁平 KV 且值小 → Map
□ 要原始审计 → String 保留

组合实践（最常用）：
□ 核心展平列 + JSON 类型 + 原始 String 保留（三层）
□ 查询走展平列，动态走 JSON，审计走 String

避免：
□ 高频字段放 String（每次查询解析）
□ 大 JSON 嵌套当 Map（Map 不适合嵌套）
□ 全部展平（改表成本高）

一句话选型：
展平=快、JSON=平衡、Map=扁平、String=兜底
→ 常用组合：展平扛高频 + JSON 扛动态 + String 扛原始