json - 将 JSON 数组中的数据收集到 ClickHouse 表中
问题描述
我在 ClickHouse 表中有一些原始 JSON 数据(实际上是来自 netflow 收集器的 netflow V9),它看起来像这样:
{"AgentID":"10.1.8.1",
"Header":{"Version":9,"Count":2},
"DataSets":[
[{"I":2,"V":"231"},{"I":3,"V":"151"},{"I":8,"V":"109.195.122.130"}],
[{"I":2,"V":"341"},{"I":3,"V":"221"},{"I":8,"V":"109.195.122.233"}]
]}'
我的任务是通过以下方式将 DataSets 数组转换为另一个 ClickHouse 表:
I2 I3 I8
-----------------------------
231 151 109.195.122.130
341 221 109.195.122.233
...
解决方案
要解析 JSON,请考虑使用专门的json 函数:
SELECT
toInt32(column_values[1]) AS I2,
toInt32(column_values[2]) AS I3,
column_values[3] AS I8
FROM
(
SELECT
arrayJoin(JSONExtract(json, 'DataSets', 'Array(Array(Tuple(Int32, String)))')) AS row,
arraySort(x -> (x.1), row) AS row_with_sorted_columns,
arrayMap(x -> (x.2), row_with_sorted_columns) AS column_values
FROM
(
SELECT '{"AgentID":"10.1.8.1", "Header":{"Version":9,"Count":2}, "DataSets":[\n [{"I":3,"V":"151"},{"I":8,"V":"109.195.122.130"},{"I":2,"V":"231"}],\n [{"I":2,"V":"341"},{"I":3,"V":"221"},{"I":8,"V":"109.195.122.233"}]]}' AS json
)
)
/*
┌─I2──┬─I3──┬─I8──────────────┐
│ 231 │ 151 │ 109.195.122.130 │
│ 341 │ 221 │ 109.195.122.233 │
└─────┴─────┴─────────────────┘
*/
(要了解有关 JSON 解析的更多信息,请参阅如何从 clickhouse 中的 json 中提取 json?)
上面的实现依赖于 Datasets-array 的固定结构。正如我在现实世界中所了解的那样,此结构具有任意模式(https://www.iana.org/assignments/ipfix/ipfix.xhtml),例如:
{
"AgentID":"192.168.21.15",
"Header":{},
"DataSets":[
[
{"I":8, "V":"192.16.28.217"},
{"I":12, "V":"180.10.210.240"},
{"I":5, "V":2},
{"I":4, "V":6},
{"I":7, "V":443},
{"I":6, "V":"0x10"}
]
]
}
因此,出现了关于具有任意列数的表的问题。ClickHouse 不支持此功能 - 请参阅在这种情况下如何呈现表格https://stackoverflow.com/search?q=%5Bclickhouse%5D+pivot。
推荐阅读
- c# - C# Google Drive 与第 3 方的连接
- r - 堆积水平条形图
- swift - 在未声明的多个图像视图上使用 Uitapgesture 识别器
- paypal - Paypal 沙箱发布错误 401(未经授权)
- acumatica - 如何使用 Acumatica Contract API 更改库存项目 ID
- python - 一半线程随机未完成并消失
- python - 为另一列中相关的每个值重复列中的值
- c# - Mono 是否支持用户设置中的 StringCollections?
- c++ - 私有变量的 C++ getter 方法不能正常工作(只有 Java 经验)
- pandas - 在 python 3.7 中使用 pandas 进行网络抓取时出现“找不到表”错误