Clickhouse Dictionaries 字典使用
clickhouse支持从各种数据源添加自己的字典。字典的数据源可以是本地文本、可执行文件
、HTTP（s）资源或其他DBMS。有关更多信息，请参阅“外部词典的来源”。
- 完全或部分将字典存储在RAM中。
- 定期更新字典并动态加载缺失值。换句话说，可以动态加载字典。
外部词典的配置位于一个或多个文件中。 配置的路径在dictionaries_config参数中指定。
字典配置：
1
| <dictionaries_config>*_dictionary.xml</dictionaries_config>
|
当前配置文件目录下，以_dictionary.xml结尾的均作为字典配置文件进行字典加载。
字典可以在服务器启动时或首次使用时加载，具体取决于dictionaries_lazy_load设置。
字典懒加载
1
| <dictionaries_lazy_load>true</dictionaries_lazy_load>
|
如果设置为true, 每个字典只在第一次使用时创建. 如果字典创建失败,则会在调用字典函数时抛出异常.
如果设置为false,所有字典则会在服务器启动时加载，如果加载过程中有异常，则会shutdown 服务。
以mysql为例，尝试下Clickhouse的字典功能。最后的配置附录中包括：
- 以
flat形式存储的Numeric keyKey类型的字典
- 以
complex_key_hashed形式存储的Composite keyKey类型的字典
更多参考见我其他几篇文章：
一、建立外部字典
1.mysql 中建立测试表test_dict，并添加3条记录
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
| SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `test_dict`;
CREATE TABLE `test_dict` (
`id` int(11) NOT NULL,
`code` varchar(20) NOT NULL,
`name` varchar(200) DEFAULT NULL,
`status` tinyint(4) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
BEGIN;
INSERT INTO `test_dict` VALUES (1, 'f', '女性', 1);
INSERT INTO `test_dict` VALUES (2, 'm', '男性', 1);
INSERT INTO `test_dict` VALUES (3, 'cat', '猫', 1);
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
|
2.在clickhouse 配置文件目录建立字典配置文件
1
| vi tmysql_dictionary.xml
|
添加以下内容
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
| <dictionaries>
<dictionary>
<name>dicts</name>
<source>
<mysql>
<replica>
<host>116.255.199.xx</host>
<priority>1</priority>
</replica>
<port>3306</port>
<user>xxxx</user>
<password>xxxxx</password>
<db>db_benchmark</db>
<table>test_dict</table>
<where>status=1</where>
<invalidate_query>select count(*) from test_dict where status=1</invalidate_query>
</mysql>
</source>
<lifetime>
<min>300</min>
<max>360</max>
</lifetime>
<layout>
<flat/>
</layout>
<structure>
<id>
<name>id</name>
</id>
<attribute>
<name>code</name>
<type>String</type>
<null_value></null_value>
</attribute>
<attribute>
<name>name</name>
<type>String</type>
<null_value></null_value>
</attribute>
</structure>
</dictionary>
</dictionaries>
|
3.重新启动clickhouse服务，测试
clickhouse-server –config-file=/etc/clickhouse-server/config.xml
clickhouse-client -m
1
| select * from system.dictionaries where name ='dicts';
|
如果有记录，就说明字典添加成功
二、字典的使用
1.字典函数
具体函数见：ext_dict_functions
2.当需要对字典使用原始数据或者执行join操作时，则可以使用Dictionary engine建立字典视图
创建字典视图语法：
1
| CREATE TABLE %table_name% (%fields%) engine = Dictionary(%dictionary_name%)`
|
1
2
3
| create table test_dicts(id UInt64,code String,name String) engine=Dictionary('dicts');
select * from test_dicts;
|
三、配置文件附录
- 以
flat形式存储的Numeric key型的字典
- 以
complex_key_hashed形式存储的Composite keyKey类型的字典
vim tmysql_dictionary.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
| <dictionaries>
<dictionary>
<name>dicts</name>
<source>
<mysql>
<replica>
<host>116.255.199.xx</host>
<priority>1</priority>
</replica>
<port>3306</port>
<user>db_benchmark</user>
<password>password</password>
<db>db_benchmark</db>
<table>test_dict</table>
<where>status=1</where>
<invalidate_query>select count(*) from test_dict where status=1</invalidate_query>
</mysql>
</source>
<lifetime>
<min>300</min>
<max>360</max>
</lifetime>
<layout>
<flat/>
</layout>
<structure>
<id>
<name>id</name>
</id>
<attribute>
<name>code</name>
<type>String</type>
<null_value></null_value>
</attribute>
<attribute>
<name>name</name>
<type>String</type>
<null_value></null_value>
</attribute>
</structure>
</dictionary>
<dictionary>
<name>mydicts</name>
<source>
<mysql>
<replica>
<host>116.255.199.xx</host>
<priority>1</priority>
</replica>
<port>3306</port>
<user>db_benchmark</user>
<password>password</password>
<db>db_benchmark</db>
<table>test_complex_key_dict</table>
<where></where>
<invalidate_query>select count(*) from test_complex_key_dict</invalidate_query>
</mysql>
</source>
<lifetime>
<min>100</min>
<max>160</max>
</lifetime>
<layout>
<complex_key_hashed />
</layout>
<structure>
<key>
<attribute>
<name>code</name>
<type>String</type>
</attribute>
</key>
<attribute>
<name>value</name>
<type>String</type>
<null_value>??</null_value>
</attribute>
</structure>
</dictionary>
</dictionaries>
|