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

问题

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

回答

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

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

着陆表应该有一个 `raw` 字符串字段，用于存储原始 json。它还应该有一个或两个其他字段，可用于管理该表，以便随着数据的老化可以对其进行分区和修剪。

*某些集成可以将字段添加到原始数据中，例如，如果使用 ClickHouse Kafka 连接器接收器。

以下是一个简化的示例

创建示例数据库

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

问题​

回答​

问题

回答