Clickhouse Dictionaries 使用样例

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;
-- ----------------------------
-- Table structure for test_dict
-- ----------------------------
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;
-- ----------------------------
-- Records of test_dict
-- ----------------------------
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><!--dbms类型:mysql-->
<replica><!--mysql 服务器,多台/主从可以配置多个-->
<host>116.255.199.xx</host>
<priority>1</priority><!--优先级-->
</replica>
<port>3306</port><!--端口-->
<user>xxxx</user><!--mysql用户名-->
<password>xxxxx</password><!--mysql 密码-->
<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> <!--clickhouse 会在这个时间间隔范围内,随机均匀时间进行字典升级(负载均衡),避免大量同时升级字典源-->
<min>300</min>
<max>360</max>
</lifetime>
<layout><!--字典在内存中存储方式;flat:二维数组(flat arrays),其他类型见文档-->
<flat/>
</layout>
<structure>
<id>
<name>id</name><!--主键,好像必须是正整数型(UInt64)-->
</id>
<attribute><!--其他属性-->
<name>code</name><!--属性名称-->
<type>String</type><!--属性类型-->
<null_value></null_value><!--null值替换-->
</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>
坚持原创技术分享,您的支持将鼓励我继续创作!
分享