开启掘金成长之旅！这是我参与「掘金日新计划 · 12 月更文挑战」的第5天，点击查看活动详情 |
文档参考：《ClickHouse原理解析与应用实践(数据库技术丛书)(朱凯)》
61.【数据库】ClickHouse从入门到放弃-概念场景 - 掘金 (juejin.cn)
64.【数据库】ClickHouse从入门到放弃-架构概述 - 掘金 (juejin.cn)
65.【数据库】ClickHouse从入门到放弃-update和delete的使用 - 掘金 (juejin.cn)
66.【数据库】ClickHouse从入门到放弃-数据类型转换 - 掘金 (juejin.cn)
67.【数据库】ClickHouse从入门到放弃-分区表 - 掘金 (juejin.cn)
68.【数据库】ClickHouse从入门到放弃-数据字典 - 掘金 (juejin.cn)
扩展字典
1 扩展字典的数据源
数据源使用source元素定义，它指定了字典的数据从何而来。通过2.4节其实大家已经领略过本地文件与可执行文件这两种数据源了，但扩展字典支持的数据源远不止这些。现阶段，扩展字典支持3大类共计9种数据源，接下来会以更加体系化的方式逐一介绍它们。
1.文件类型
文件可以细分为本地文件、可执行文件和远程文件三类，它们是最易使用且最为直接的数据源，非常适合在静态数据这类场合中使用。
1）本地文件
本地文件使用file元素定义。其中，path表示数据文件的绝对路径，而format表示数据格式，例如CSV或者TabSeparated等。它的完整配置如下所示。
<source>
<file>
<path>/data/dictionaries/organization.csv</path>
<format>CSV</format>
</file>
</source>
2）可执行文件
可执行文件数据源属于本地文件的变种，它需要通过cat命令访问数据文件。对于cache和complex_key_cache类型的字典，必须使用此类型的文件数据源。可执行文件使用executable元素定义。其中，command表示数据文件的绝对路径，format表示数据格式，例如CSV或者TabSeparated等。它的完整配置如下所示。
<source>
<executable>
<command>cat /data/dictionaries/organization.csv</ command>
<format>CSV</format>
</executable>
</source>
3）远程文件
远程文件与可执行文件类似，只是它将cat命令替换成了post请求，支持HTTP与HTTPS协议。远程文件使用http元素定义。其中，url表示远程数据的访问地址，format表示数据格式，例如CSV或者TabSeparated。它的完整配置如下所示。
source>
<http>
<url>http://10.37.129.6/organization.csv</url>
<format>CSV</format>
</http>
</source>
2.数据库类型
相比文件类型，数据库类型的数据源更适合在正式的生产环境中使用。目前扩展字典支持MySQL、ClickHouse本身及MongoDB三种数据库。接下来会分别介绍它们的创建方法。对于MySQL和MongoDB数据库环境的安装，由于篇幅原因此处不再赘述，而相关的SQL脚本可以在本书附带的源码站点中下载。
1）MySQL
MySQL数据源支持从指定的数据库中提取数据，作为其字典的数据来源。首先，需要准备源头数据，执行下面的语句在MySQL中创建测试表：
CREATE TABLE 't_organization' (
`id` int(11) NOT NULL AUTO_INCREMENT,
`code` varchar(40) DEFAULT NULL,
`name` varchar(60) DEFAULT NULL,
`updatetime` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
接着，写入测试数据：
INSERT INTO t_organization (code, name,updatetime) VALUES('a0001','研发部',NOW())
INSERT INTO t_organization (code, name,updatetime) VALUES('a0002','产品部',NOW())
…
完成上述准备之后，就可以配置MySQL数据源的字典了。现在仿照flat字典进行配置，创建一个名为test_mysql_dictionary.xml的配置文件，将source替换成MySQL数据源：
<dictionaries>
<dictionary>
<name>test_mysql_dict</name>
<source>
<mysql>
<port>3306</port>
<user>root</user>
<password></password>
<replica>
<host>10.37.129.2</host>
<priority>1</priority>
</replica>
<db>test</db>
<table>t_organization</table>
<!--
<where>id=1</where>
<invalidate_query>SQL_QUERY</invalidate_query>
-->
</mysql>
</source>
省略…
其中，各配置项的含义分别如下。
·port：数据库端口。
·user：数据库用户名。
·password：数据库密码。
·replica：数据库host地址，支持MySQL集群。
·db：database数据库。
·table：字典对应的数据表。
·where：查询table时的过滤条件，非必填项。
·invalidate_query：指定一条SQL语句，用于在数据更新时判断是否需要更新，非必填项。2.6节会详细说明。
将配置文件复制到ClickHouse服务节点的/etc/clickhouse-server目录后，即完成了对该字典的创建过程。
2）ClickHouse 扩展字典支持将ClickHouse数据表作为数据来源，这是一种比较有意思的设计。在配置之前同样需要准备数据源的测试数据，执行下面的语句在ClickHouse中创建测试表并写入测试数据：
CREATE TABLE t_organization (
ID UInt64,
Code String,
Name String,
UpdateTime DateTime
) ENGINE = TinyLog;
--写入测试数据
INSERT INTO t_organization VALUES(1,'a0001','研发部',NOW()),(2,'a0002','产品部' ,NOW()),(3,'a0003','数据部',NOW()),(4,'a0004','测试部',NOW()),(5,'a0005','运维部' ,NOW()),(6,'a0006','规划部',NOW()),(7,'a0007','市场
ClickHouse数据源的配置与MySQL数据源极为相似，所以我们可以仿照MySQL数据源的字典配置，创建一个名为test_ch_dictionary.xml的配置文件，将source替换成ClickHouse数据源：
<?xml version="1.0"?>
<dictionaries>
<dictionary>
<name>test_ch_dict</name>
<source>
<clickhouse>
<host>10.37.129.6</host>
<port>9000</port>
<user>default</user>
<password></password>
<db>default</db>
<table>t_organization</table>
<!--
<where>id=1</where>
<invalidate_query>SQL_QUERY</invalidate_query>
-->
</clickhouse>
</source>
省略…
其中，各配置项的含义分别如下。
·host：数据库host地址。
·port：数据库端口。
·user：数据库用户名。
·password：数据库密码。
·db：database数据库。
·table：字典对应的数据表。
·where：查询table时的过滤条件，非必填项。
·invalidate_query：指定一条SQL语句，用于在数据更新时判断是否需要更新，非必填项。在2.6节会详细说明。
3）MongoDB
最后是MongoDB数据源，执行下面的语句，MongoDB会自动创建相应的schema并写入数据：
db.t_organization.insertMany(
[{
id: 1,
code: 'a0001',
name: '研发部'
},
{
id: 2,
code: 'a0002',
name: '产品部'
},
{
id: 3,
code: 'a0003',
name: '数据部'
},
{
id: 4,
code: 'a0004',
name: '测试部'
}]
)
完成上述准备之后就可以配置MongoDB数据源的字典了，同样仿照MySQL字典配置，创建一个名为test_mongodb_dictionary.xml的配置文件，将source替换成mongodb数据源：
<dictionaries>
<dictionary>
<name>test_mongodb_dict</name>
<source>
<source>
<mongodb>
<host>10.37.129.2</host>
<port>27017</port>
<user></user>
<password></password>
<db>test</db>
<collection>t_organization</collection>
</mongodb>
</source>
省略…
3.其他类型
除了上述已经介绍过的两类数据源之外，扩展字典还支持通过ODBC的方式连接PostgreSQL和MS SQL Server数据库作为数据源。它们的配置方式与数据库类型数据源大同小异，此处不再赘述，如有需要请参见官方手册。
2.扩展字典的数据更新策略
扩展字典支持数据的在线更新，更新后无须重启服务。字典数据的更新频率由配置文件中的lifetime元素指定，单位为秒：
<lifetime>
<min>300</min>
<max>360</max>
</lifetime>
其中，min与max分别指定了更新间隔的上下限。ClickHouse会在这个时间区间内随机触发更新动作，这样能够有效错开更新时间，避免所有字典在同一时间内爆发性的更新。当min和max都是0的时候，将禁用字典更新。对于cache字典而言，lifetime还代表了它的缓存失效时间。
字典内部拥有版本的概念，在数据更新的过程中，旧版本的字典将持续提供服务，只有当更新完全成功之后，新版本的字典才会替代旧版本。所以更新操作或者更新时发生的异常，并不会对字典的使用产生任何影响。
不同类型的字典数据源，更新机制也稍有差异。总体来说，扩展字典目前并不支持增量更新。但部分数据源能够依照标识判断，只有在源数据发生实质变化后才实施更新动作。这个判断源数据是否被修改的标识，在字典内部称为previous，它保存了一个用于比对的值。ClickHouse的后台进程每隔5秒便会启动一次数据刷新的判断，依次对比每个数据字典中前后两次previous的值是否相同。若相同，则代表无须更新数据；若不同且满足更新频率，则代表需要更新数据。而对于previous值的获取方式，不同的数据源有不同的实现逻辑，下面详细介绍。 1.文件数据源
对于文件类型的数据源，它的previous值来自系统文件的修改时间，这和Linux系统中的stat查询命令类似：
#stat ./test_flat_dictionary.xml
File: `./test_flat_dictionary.xml'
Size: 926 Blocks: 8 IO Block: 4096 regular file
Access: 2019-07-18 01:15:43.509000359 +0800
Modify: 2019-07-18 01:15:32.000000000 +0800
Change: 2019-07-18 01:15:38.865999868 +0800
当前后两次previous的值不相同时，才会触发数据更新。
2.MySQL(InnoDB)、ClickHouse和ODBC
对于MySQL（InnoDB引擎）、ClickHouse和ODBC数据源，它们的previous值来源于invalidate_query中定义的SQL语句。例如在下面的示例中，如果前后两次的updatetime值不相同，则会判定源数据发生了变化，字典需要更新。
<source>
<mysql>
省略…
<invalidate_query>select updatetime from t_organization where id = 8</invalidate_query>
</mysql>
</source>
这对源表有一定的要求，它必须拥有一个支持判断数据是否更新的字段。
3.MySQL(MyISAM)
如果数据源是MySQL的MyISAM表引擎，则它的previous值要简单得多。因为在MySQL中，使用MyISAM表引擎的数据表支持通过SHOW TABLE STATUS命令查询修改时间。例如在MySQL中执行下面的语句，就能够查询到数据表的Update_time值：
SHOW TABLE STATUS WHERE Name = 't_organization'
所以，如果前后两次Update_time的值不相同，则会判定源数据发生了变化，字典需要更新。
4.其他数据源
除了上面描述的数据源之外，其他数据源目前无法依照标识判断是否跳过更新。所以无论数据是否发生实质性更改，只要满足当前lifetime的时间要求，它们都会执行更新动作。相比之前介绍的更新方式，其他类型的更新效率更低。
除了按照lifetime定义的时间频率被动更新之外，数据字典也能够主动触发更新。执行下面的语句后，将会触发所有数据字典的更新：
SHOW TABLE STATUS WHERE Name = 't_organization'
所以，如果前后两次Update_time的值不相同，则会判定源数据发生了变化，字典需要更新。
4.其他数据源
除了上面描述的数据源之外，其他数据源目前无法依照标识判断是否跳过更新。所以无论数据是否发生实质性更改，只要满足当前lifetime的时间要求，它们都会执行更新动作。相比之前介绍的更新方式，其他类型的更新效率更低。
除了按照lifetime定义的时间频率被动更新之外，数据字典也能够主动触发更新。执行下面的语句后，将会触发所有数据字典的更新：
SYSTEM RELOAD DICTIONARIES
也支持指定某个具体字典的更新：
SYSTEM RELOAD DICTIONARY [dict_name]
3.扩展字典的基本操作
至此，我们已经在ClickHouse中创建了10种不同类型的扩展字典。接下来将目光聚焦到字典的基本操作上，包括对字典元数据和数据的查询，以及借助字典表引擎访问数据。
1.元数据查询
通过system.dictionaries系统表，可以查询扩展字典的元数据信息。例如执行下面的语句就可以看到目前所有已经创建的扩展字典的名称、类型和字段等信息：
SELECT name, type, key, attribute.names, attribute.types, source FROM system.dictionaries
上述代码执行后得到的结果如图所示。
在system.dictionaries系统表内，其主要字段的含义分别如下。
·name：字典的名称，在使用字典函数时需要通过字典名称访问数据。
·type：字典所属类型。
·key：字典的key值，数据通过key值定位。
·attribute.names：属性名称，以数组形式保存。
·attribute.types：属性类型，以数组形式保存，其顺序与attribute.names相同。
·bytes_allocated：已载入数据在内存中占用的字节数。
·query_count：字典被查询的次数。
·hit_rate：字典数据查询的命中率。
·element_count：已载入数据的行数。
·load_factor：数据的加载率。
·source：数据源信息。
·last_exception：异常信息，需要重点关注。如果字典在加载过程中产生异常，那么异常信息会写入此字段。last_exception是获取字典调试信息的主要方式。
2.数据查询
在正常情况下，字典数据只能通过字典函数获取，例如下面的语句就使用到了dictGet('dict_name','attr_name',key)函数：
SELECT dictGet('test_flat_dict','name',toUInt64(1))
如果字典使用了复合型key，则需要使用元组作为参数传入：
SELECT dictGet('test_ip_trie_dict', 'asn', tuple(IPv4StringToNum('82.118.230.0')))
除了dictGet函数之外，ClickHouse还提供了一系列以dictGet为前缀的字典函数，具体如下所示。
·获取整型数据的函数：dictGetUInt8、dictGetUInt16、dictGetUInt32、dictGetUInt64、dictGetInt8、dictGetInt16、dictGetInt32、dictGetInt64。
·获取浮点数据的函数：dictGetFloat32、dictGetFloat64。
·获取日期数据的函数：dictGetDate、dictGetDateTime。
·获取字符串数据的函数：dictGetString、dictGetUUID。
这些函数的使用方法与dictGet大同小异，此处不再赘述。
3.字典表
除了通过字典函数读取数据之外，ClickHouse还提供了另外一种借助字典表的形式来读取数据。字典表是使用Dictionary表引擎的数据表，比如下面的例子：
CREATE TABLE tb_test_flat_dict (
id UInt64,
code String,
name String
) ENGINE = Dictionary(test_flat_dict);
通过这张表，就能查询到字典中的数据。更多关于字典引擎的信息详见第8章。
4.使用DDL查询创建字典
从19.17.4.11版本开始，ClickHouse开始支持使用DDL查询创建字典，例如：
CREATE DICTIONARY test_dict(
id UInt64,
value String
)
PRIMARY KEY id
LAYOUT(FLAT())
SOURCE(FILE(PATH '/usr/bin/cat' FORMAT TabSeparated))
LIFETIME(1)
可以看到，其配置参数与之前并无差异，只是转成了DDL的形式。
