ClickHouse 的 Map 类型以及相关操作(十四)

​ 本文来源： ( https://www.cnblogs.com/traditional/tag/ClickHouse：一款速度快到让人发指的列式存储数据库/ )

楔子

之前在介绍数据类型的时候，有一种没有说，就是 Map。Map 是什么想必无需多言，简单来说的话就是维护键值对之间的映射关系，可以通过键迅速定位到值。

下面就先来创建一张表：

1
2
-- 在定义 Map 的时候，必须要指定键值对的类型
CREATE TABLE table_map(a Map(String, UInt64)) ENGINE = Memory();

但是不出意外我们创建表的时候应该会报错，原因就是在表中支持定义 Map 类型的字段还只是试验性的，我们需要将 allow_experimental_map_type 设置为 1，这也是我们单独拿出来介绍的原因。然后我们插入数据：

1
2
3
4
5
set allow_experimental_map_type = 1;
CREATE TABLE table_map(a Map(String, UInt64)) ENGINE = Memory();

INSERT INTO table_map 
VALUES ({'key1': 1, 'key2': 10}), ({'key1':2,'key2':20}), ({'key1':3,'key2':30});

下面来对表进行查询：

1
2
3
4
5
6
7
8
SELECT * FROM table_map;
/*
┌─a────────────────────┐
│ {'key1':1,'key2':10} │
│ {'key1':2,'key2':20} │
│ {'key1':3,'key2':30} │
└──────────────────────┘
*/

如果想选择某个具体的键对应的 value，那么直接通过方括号即可，举个栗子：

1
2
3
4
5
6
7
8
SELECT a['key1'], a['key2'], a FROM table_map;
/*
┌─arrayElement(a, 'key1')─┬─arrayElement(a, 'key2')─┬─a────────────────────┐
│                       1 │                      10 │ {'key1':1,'key2':10} │
│                       2 │                      20 │ {'key1':2,'key2':20} │
│                       3 │                      30 │ {'key1':3,'key2':30} │
└─────────────────────────┴─────────────────────────┴──────────────────────┘
*/

如果查询一个不在 Map 当中 key，那么会返回对应的零值。

1
2
3
4
5
6
7
8
SELECT a['key3'] FROM table_map;
/*
┌─arrayElement(a, 'key3')─┐
│                       0 │
│                       0 │
│                       0 │
└─────────────────────────┘
*/

当然我们也可以根据现有的数组结构创建 Map：

1
2
3
4
5
6
7
8
9
WITH [1, 2, 3] AS key, ['a', 'b', 'c'] AS value
SELECT cast((key, value) AS Map(UInt8, String));
/*
┌─CAST(tuple(key, value), 'Map(UInt8, String)')─┐
│ {1:'a',2:'b',3:'c'}                           │
└───────────────────────────────────────────────┘
*/
-- 从返回的结果集的字段名，我们可以看出，cast(val AS type) 等价于 cast(val, 'type')
-- 比如 cast(3 AS String) 和 cast(3, 'String') 是等价的，不过个人还是习惯前者

我们在选择的时候也可以只选择 key 或者 value。

1
2
3
4
5
6
7
8
SELECT a.keys, a.values FROM table_map;
/*
┌─a.keys──────────┬─a.values─┐
│ ['key1','key2'] │ [1,10]   │
│ ['key1','key2'] │ [2,20]   │
│ ['key1','key2'] │ [3,30]   │
└─────────────────┴──────────┘
*/

然后我们来看看字典都支持哪些函数操作

map：我们除了可以通过大括号创建 Map，也可以通过 map 函数创建

1
2
3
4
5
6
7
8
9
SELECT map('key1', number, 'key2', number * 2) FROM numbers(3);
/*
┌─map('key1', number, 'key2', multiply(number, 2))─┐
│ {'key1':0,'key2':0}                              │
│ {'key1':1,'key2':2}                              │
│ {'key1':2,'key2':4}                              │
└──────────────────────────────────────────────────┘
*/
-- 注意：SELECT {'key1': number, 'key2': number * 2} 是非法的，必须使用 map 函数创建

同理我们插入数据的时候也可以使用 map 函数：

1
2
3
4
5
6
7
8
9
10
11
12
INSERT INTO table_map VALUES (map('key1', 1, 'key2', 10));
SELECT a.keys, a.values FROM table_map;
/*
┌─a.keys──────────┬─a.values─┐
│ ['key1','key2'] │ [1,10]   │
│ ['key1','key2'] │ [2,20]   │
│ ['key1','key2'] │ [3,30]   │
└─────────────────┴──────────┘
┌─a.keys──────────┬─a.values─┐
│ ['key1','key2'] │ [1,10]   │
└─────────────────┴──────────┘
*/

mapContains：检测 Map 里面是否包含某个 key

1
2
3
4
5
6
7
WITH map(1, 3, 2, 5) AS m
SELECT mapContains(m, 1), mapContains(m, 3);
/*
┌─mapContains(m, 1)─┬─mapContains(m, 3)─┐
│                 1 │                 0 │
└───────────────────┴───────────────────┘
*/

mapKeys：等价于 Map.keys

1
2
3
4
5
6
7
8
9
10
11
SELECT a.keys, mapKeys(a) FROM table_map;
/*
┌─a.keys──────────┬─mapKeys(a)──────┐
│ ['key1','key2'] │ ['key1','key2'] │
│ ['key1','key2'] │ ['key1','key2'] │
│ ['key1','key2'] │ ['key1','key2'] │
└─────────────────┴─────────────────┘
┌─a.keys──────────┬─mapKeys(a)──────┐
│ ['key1','key2'] │ ['key1','key2'] │
└─────────────────┴─────────────────┘
*/

mapValues：等价于 Map.values

1
2
3
4
5
6
7
8
9
10
11
SELECT a.values, mapValues(a) FROM table_map;
/*
┌─a.values─┬─mapValues(a)─┐
│ [1,10]   │ [1,10]       │
│ [2,20]   │ [2,20]       │
│ [3,30]   │ [3,30]       │
└──────────┴──────────────┘
┌─a.values─┬─mapValues(a)─┐
│ [1,10]   │ [1,10]       │
└──────────┴──────────────┘
*/

注意：mapKeys、mapValues 相当于数据全量读取，然后再选择所有的 key 或 value，所以建议还是使用 Map.keys、Map.values。但如果将 optimize_functions_to_subcolumns 设置为 1，那么会进行优化：

1
SELECT mapKeys(m), mapValues(m) FROM table 会转化成 SELECT m.keys, m.values FROM table

以上就是 Map 的内容，总的来说还是很简单的。

JSON 的相关操作

既然提到了 Map，那么就不能不提到 JSON，这两者在结构上有着非常高的相似之处，下面就来看看 JSON 支持哪些操作。

isValidJSON：检测 JSON 是否合法

JSON 本质上也是一个字符串，isValidJSON 则是检测该字符串是否符合 JSON 格式。

1
2
3
4
5
6
SELECT isValidJSON('{"a": 1, "b": false}'), isValidJSON('{1, 2, 3}');
/*
┌─isValidJSON('{"a": 1, "b": false}')─┬─isValidJSON('{1, 2, 3}')─┐
│                                   1 │                        0 │
└─────────────────────────────────────┴──────────────────────────┘
*/

JSONHas：检测 JSON 是否包含指定的 key

1
2
3
4
5
6
SELECT JSONHas('{"a": 1, "b": false}', 'a'), JSONHas('{"a": 1, "b": false}', 'a1');
/*
┌─JSONHas('{"a": 1, "b": false}', 'a')─┬─JSONHas('{"a": 1, "b": false}', 'a1')─┐
│                                    1 │                                     0 │
└──────────────────────────────────────┴───────────────────────────────────────┘
*/

JSONLength：获取 JSON 的长度

1
2
3
4
5
6
SELECT JSONLength('{"a": 1, "b": false}');
/*
┌─JSONLength('{"a": 1, "b": false}')─┐
│                                  2 │
└────────────────────────────────────┘
*/

JSONType：获取 JSON 中指定 value 的类型

1
2
3
4
5
6
7
8
WITH '{"a": 1, "b": true, "c": null, "d": "xx", "e": [1, 2, 3], "f": {"a": 1}}' AS j
SELECT JSONType(j, 'a'), JSONType(j, 'b'), JSONType(j, 'c'), 
       JSONType(j, 'd'), JSONType(j, 'e'), JSONType(j, 'f');
/*
┌─JSONType(j, 'a')─┬─JSONType(j, 'b')─┬─JSONType(j, 'c')─┬─JSONType(j, 'd')─┬─JSONType(j, 'e')─┬─JSONType(j, 'f')─┐
│ Int64            │ Bool             │ Null             │ String           │ Array            │ Object           │
└──────────────────┴──────────────────┴──────────────────┴──────────────────┴──────────────────┴──────────────────┘
*/

toJSONString：将其它数据类型转成 JSON

1
2
3
4
5
6
7
-- 不可以写成 {'a': 1, 'b': 2}
SELECT toJSONString(map('a', 1, 'b', 2));
/*
┌─toJSONString(map('a', 1, 'b', 2))─┐
│ {"a":1,"b":2}                     │
└───────────────────────────────────┘
*/

JSONExtract：根据 key，从 JSON 中解析出指定的 value，就类似于根据 key 获取 Map 中的 value 一样

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 在获取 value 的时候，必须要指定 value 是什么类型
-- ClickHouse 中的 Bool 是用整型表示的，所以转成 UInt8、16、32、64 也是可以的
WITH '{"a": 1, "b": true}' AS j
SELECT JSONExtract(j, 'a', 'UInt8'), JSONExtract(j, 'b', 'Bool');
/*
┌─JSONExtract(j, 'a', 'UInt8')─┬─JSONExtract(j, 'b', 'Bool')─┐
│                            1 │                           1 │
└──────────────────────────────┴─────────────────────────────┘
*/

WITH '{"a": [null, 123], "b": {"a": 1}}' AS j
SELECT JSONExtract(j, 'a', 'Array(UInt8)'),
       JSONExtract(j, 'a', 'Array(Nullable(UInt8))');
/*
┌─JSONExtract(j, 'a', 'Array(UInt8)')─┬─JSONExtract(j, 'a', 'Array(Nullable(UInt8))')─┐
│ [0,123]                             │ [NULL,123]                                    │
└─────────────────────────────────────┴───────────────────────────────────────────────┘
*/

如果解析失败，那么会得到相应的零值，举个栗子：

1
2
3
4
5
6
7
WITH '{"a": [null, 123], "b": {"a": 1}}' AS j
SELECT JSONExtract(j, 'a', 'UInt64');
/*
┌─JSONExtract(j, 'a', 'UInt64')─┐
│                             0 │
└───────────────────────────────┘
*/