使用物化视图和落地表提取 JSON 数据的简单示例流程

使用物化视图和落地表提取 JSON 数据的简单示例流程

问题

如何使用源表或落地表处理 JSON 消息，并使用物化视图进行提取？
如何在没有实验性 JSON 对象的情况下处理 JSON？

答案

处理 JSON 数据的常见模式是将数据发送到落地表，并使用 JSONExtract 函数通过物化视图触发器将数据拉取到新表。这通常在以下流程和模式中完成

source data --> MergeTree table --> Materialized View (with base table) --> application/client

落地表应具有一个 raw 字符串字段，您将在其中存储原始 json。它还应该具有一到两个其他字段，这些字段可用于管理该表，以便可以对表进行分区并在数据老化时进行修剪。

*某些集成可以将字段添加到原始数据，例如，如果使用 ClickHouse Kafka Connector Sink。

以下是简化的示例

• 创建示例数据库

create database db1;

• 创建一个落地表，您的原始 json 将插入其中

create table db1.table2_json_raw
(
    id Int32,
    timestamp DateTime,
    raw String
)
engine = MergeTree()
order by timestamp;

• 为物化视图创建基础表

create table db1.table2_json_mv_base
(
 id Int32,
 timestamp DateTime,
 raw_string String,
 custId Int8,
 custName String
)
engine = MergeTree()
order by timestamp;

• 为基础表创建物化视图

create materialized view db1.table2_json_mv to db1.table2_json_mv_base
AS SELECT
 id,
 timestamp,
 raw as raw_string,
 simpleJSONExtractRaw(raw, 'customerId') as custId,
 simpleJSONExtractRaw(raw, 'customerName') as custName
 FROM
db1.table2_json_raw;

• 插入一些示例行

 insert into db1.table2_json_raw
 values
 (1, '2024-05-16 00:00:00', '{"customerId":1, "customerName":"ABC"}'),
 (2, '2024-05-16 00:00:01', '{"customerId":2, "customerName":"XYZ"}');

• 查看提取结果以及将在查询中使用的物化视图

clickhouse-cloud :) select * from db1.table2_json_mv;

SELECT *
FROM db1.table2_json_mv

Query id: 12655fd3-567a-4dfb-9ef7-abc4b11ad044

┌─id─┬───────────timestamp─┬─raw_string─────────────────────────────┬─custId─┬─custName─┐
│  1 │ 2024-05-16 00:00:00 │ {"customerId":1, "customerName":"ABC"} │ 1      │ "ABC"    │
│  2 │ 2024-05-16 00:00:01 │ {"customerId":2, "customerName":"XYZ"} │ 2      │ "XYZ"    │
└────┴─────────────────────┴────────────────────────────────────────┴────────┴──────────┘

其他参考链接
物化视图： https://clickhouse.ac.cn/docs/en/guides/developer/cascading-materialized-views
处理 JSON： https://clickhouse.ac.cn/docs/en/integrations/data-formats/json#other-approaches
JSON 函数： https://clickhouse.ac.cn/docs/en/sql-reference/functions/json-functions