从JSON数组中收集数据到ClickHouse表中

Question

我在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
...

Answer 1

考虑使用特殊的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。