开启掘金成长之旅！这是我参与「掘金日新计划 · 12 月更文挑战」的第4天，点击查看活动详情 |
文档参考：《ClickHouse原理解析与应用实践(数据库技术丛书)(朱凯)》
61.【数据库】ClickHouse从入门到放弃-概念场景 - 掘金 (juejin.cn)
64.【数据库】ClickHouse从入门到放弃-架构概述 - 掘金 (juejin.cn)
65.【数据库】ClickHouse从入门到放弃-update和delete的使用 - 掘金 (juejin.cn)
66.【数据库】ClickHouse从入门到放弃-数据类型转换 - 掘金 (juejin.cn)
67.【数据库】ClickHouse从入门到放弃-分区表 - 掘金 (juejin.cn)
1.数据字典
数据字典是ClickHouse提供的一种非常简单、实用的存储媒介，它以键值和属性映射的形式定义数据。字典中的数据会主动或者被动（数据是在ClickHouse启动时主动加载还是在首次查询时惰性加载由参数设置决定）加载到内存，并支持动态更新。由于字典数据常驻内存的特性，所以它非常适合保存常量或经常使用的维度表数据，以避免不必要的JOIN查询。
数据字典分为内置与扩展两种形式，顾名思义，内置字典是ClickHouse默认自带的字典，而外部扩展字典是用户通过自定义配置实现的字典。 在正常情况下，字典中的数据只能通过字典函数访问（ClickHouse特别设置了一类字典函数，专门用于字典数据的取用）。但是也有一种例外，那就是使用特殊的字典表引擎。在字典表引擎的帮助下，可以将数据字典挂载到一张代理的数据表下，从而实现数据表与字典数据的JOIN查询。
1.1 内置字典
ClickHouse目前只有一种内置字典——Yandex.Metrica字典。从名称上可以看出，这是用在ClickHouse自家产品上的字典，而它的设计意图是快速存取geo地理数据。但较为遗憾的是，由于版权原因Yandex并没有将geo地理数据开放出来。这意味着ClickHouse目前的内置字典，只是提供了字典的定义机制和取数函数，而没有内置任何现成的数据。所以内置字典的现状较为尴尬，需要遵照它的字典规范自行导入数据。
这两项配置是惰性加载的，只有当字典首次被查询的时候才会触发加载动作。填充Yandex.Metrica字典的geo地理数据由两组模型组成，可以分别理解为地区数据的主表及维度表。这两组模型的数据分别由上述两项配置指定，现在依次介绍它们的具体用法。 1.path_to_regions_hierarchy_file
path_to_regions_hierarchy_file等同于区域数据的主表，由1个regions_hierarchy.txt和多个regions_hierarchy_[name].txt区域层次的数据文件共同组成，缺一不可。其中[name]表示区域标识符，与i18n类似。这些TXT文件内的数据需要使用TabSeparated格式定义，其数据模型的格式如表5-1所示。
2.path_to_regions_names_files
path_to_regions_names_files等同于区域数据的维度表，记录了与区域ID对应的区域名称。维度数据使用6个regions_names_[name].txt文件保存，其中[name]表示区域标识符与regions_hierarchy_[name].txt对应，目前包括ru、en、ua、by、kz和tr。上述这些区域的数据文件必须全部定义，这是因为内置字典在初次加载时，会一次性加载上述6个区域标识的数据文件。如果缺少任何一个文件就会抛出异常并导致初始化失败。
这些TXT文件内的数据同样需要使用TabSeparated格式定义，其数据模型的格式如表5-2所示。
1.2 使用内置字典
在知晓了内置字典的开启方式和Yandex.Metrica字典的数据模型之后，就可以配置字典的数据并使用它们了。首先，在/opt路径下新建geo目录：
# mkdir /opt/geo
接着，进入本书附带的演示代码，找到数据字典目录。为了便于读者测试，事先已经准备好了一份测试数据，将下列用于测试的数据文件复制到刚才已经建好的/opt/geo目录下：
# pwd
/opt/geo
# ll
total 36
-rw-r--r--. 1 root root 3096 Jul 7 20:38 regions_hierarchy_ru.txt
-rw-r--r--. 1 root root 3096 Jul 7 20:38 regions_hierarchy.txt
-rw-r--r--. 1 root root 3957 Jul 7 19:44 regions_names_ar.txt
-rw-r--r--. 1 root root 3957 Jul 7 19:44 regions_names_by.txt
-rw-r--r--. 1 root root 3957 Jul 7 19:44 regions_names_en.txt
-rw-r--r--. 1 root root 3957 Jul 7 19:44 regions_names_kz.txt
-rw-r--r--. 1 root root 3957 Jul 7 19:44 regions_names_ru.txt
-rw-r--r--. 1 root root 3957 Jul 7 19:44 regions_names_tr.txt
-rw-r--r--. 1 root root 3957 Jul 7 19:44 regions_names_ua.txt
最后，找到config.xml并按照5.1.1节介绍的方法开启内置字典。 至此，内置字典就已经全部设置好了，执行下面的语句就能够访问字典中的数据：
SELECT regionToName(toUInt32(20009))
┌─regionToName(toUInt32(20009))─┐
│ Buenos Aires Province │
└────────────────────┘
可以看到，对于Yandex.Metrica字典数据的访问，这里用到了regionToName函数。类似这样的函数还有很多，在ClickHouse中它们被称为Yandex.Metrica函数。关于这套函数的更多用法，请参阅官方手册。
2.外部扩展字典
2.1 准备字典数据
在接下来的篇幅中，会逐个介绍每种扩展字典的使用方法，包括它们的配置形式、数据结构及创建方法，但是在此之前还需要进行一些准备工作。为了便于演示，此处事先准备了三份测试数据，它们均使用CSV格式。其中，第一份是企业组织数据，它将用于flat、hashed、cache、complex_key_hashed和complex_key_cache字典的演示场景。这份数据拥有id、code和name三个字段，数据格式如下所示：
1,"a0001","研发部"
2,"a0002","产品部"
3,"a0003","数据部"
4,"a0004","测试部"
5,"a0005","运维部"
6,"a0006","规划部"
7,"a0007","市场部"
第二份是销售数据，它将用于range_hashed字典的演示场景。这份数据拥有id、start、end和price四个字段，数据格式如下所示： 最后一份是asn数据，它将用于演示ip_trie字典的场景。这份数据拥有ip、asn和country三个字段，数据格式如下所示：
"82.118.230.0/24","AS42831","GB"
"148.163.0.0/17","AS53755","US"
"178.93.0.0/18","AS6849","UA"
"200.69.95.0/24","AS262186","CO"
"154.9.160.0/20","AS174","US"
你可以从下面的地址获取到上述三份数据：
·github.com/nauu/clickh…
下载后，将数据文件上传到ClickHouse节点所在的服务器即可。
2.2 扩展字典配置文件的元素组成
扩展字典的配置文件由config.xml文件中的dictionaries_config配置项指定：
<!-- Configuration of external dictionaries. See:
https://clickhouse.yandex/docs/en/dicts/external_dicts/
-->
<dictionaries_config>*_dictionary.xml</dictionaries_config>
在默认的情况下，ClickHouse会自动识别并加载/etc/clickhouse-server目录下所有以_dictionary.xml结尾的配置文件。同时ClickHouse也能够动态感知到此目录下配置文件的各种变化，并支持不停机在线更新配置文件。
在单个字典配置文件内可以定义多个字典，其中每一个字典由一组dictionary元素定义。在dictionary元素之下又分为5个子元素，均为必填项，它们完整的配置结构如下所示：
<?xml version="1.0"?>
<dictionaries>
<dictionary>
<name>dict_name</name>
<structure>
<!—字典的数据结构 -->
</structure>
<layout>
<!—在内存中的数据格式类型 -->
</layout>
<source>
<!—数据源配置 -->
</source>
<lifetime>
<!—字典的自动更新频率 -->
</lifetime>
</dictionary>
</dictionaries>
在上述结构中，主要配置的含义如下。
·name：字典的名称，用于确定字典的唯一标识，必须全局唯一，多个字典之间不允许重复。 ·structure：字典的数据结构，2.3节会详细介绍。 ·layout：字典的类型，它决定了数据在内存中以何种结构组织和存储。目前扩展字典共拥有7种类型，2.4节会详细介绍。 ·source：字典的数据源，它决定了字典中数据从何处加载。目前扩展字典共拥有文件、数据库和其他三类数据来源，2.5节会详细介绍。 ·lifetime：字典的更新时间，扩展字典支持数据在线更新，2.6节会详细介绍。
2.3 扩展字典的数据结构
扩展字典的数据结构由structure元素定义，由键值key和属性attribute两部分组成，它们分别描述字典的数据标识和字段属性。structure的完整形式如下所示（在后面的查询过程中都会通过这些字段来访问字典中的数据）：
<dictionary>
<structure>
<!— <id> 或 <key> -->
<id>
<!—Key属性-->
</id>
<attribute>
<!—字段属性-->
</attribute>
...
</structure>
</dictionary>
接下来具体介绍key和attribute的含义。
1.key key用于定义字典的键值，每个字典必须包含1个键值key字段，用于定位数据，类似数据库的表主键。键值key分为数值型和复合型两类。
（1）数值型：数值型key由UInt64整型定义，支持flat、hashed、range_hashed和cache类型的字典（扩展字典类型会在后面介绍），它的定义方法如下所示。
<structure>
<id>
<!—名称自定义-->
<name>Id</name>
</id>
省略…
（2）复合型：复合型key使用Tuple元组定义，可以由1到多个字段组成，类似数据库中的复合主键。它仅支持complex_key_hashed、complex_key_cache和ip_trie类型的字典。其定义方法如下所示。
<structure>
<key>
<attribute>
<name>field1</name>
<type>String</type>
</attribute>
<attribute>
<name>field2</name>
<type>UInt64</type>
</attribute>
省略…
</key>
省略…
2.attribute attribute用于定义字典的属性字段，字典可以拥有1到多个属性字段。它的完整定义方法如下所示：
<structure>
省略…
<attribute>
<name>Name</name>
<type>DataType</type>
<!—空字符串-->
<null_value></null_value>
<expression>generateUUIDv4()</expression>
<hierarchical>true</hierarchical>
<injective>true</injective>
<is_object_id>true</is_object_id>
</attribute>
省略…
</structure>
在attribute元素下共有7个配置项，其中name、type和null_value为必填项。这些配置项的详细说明如表5-3所示。
2.4 扩展字典的类型
扩展字典的类型使用layout元素定义，目前共有7种类型。一个字典的类型，既决定了其数据在内存中的存储结构，也决定了该字典支持的key键类型。根据key键类型的不同，可以将它们划分为两类：一类是以flat、hashed、range_hashed和cache组成的单数值key类型，因为它们均使用单个数值型的id；另一类则是由complex_key_hashed、complex_key_cache和ip_trie组成的复合key类型。complex_key_hashed与complex_key_cache字典在功能方面与hashed和cache并无二致，只是单纯地将数值型key替换成了复合型key而已。
接下来会结合2.1节中已准备好的测试数据，逐一介绍7种字典的完整配置方法。通过这个过程，可以领略到不同类型字典的特点以及它们的使用方法。
1.flat
flat字典是所有类型中性能最高的字典类型，它只能使用UInt64数值型key。顾名思义，flat字典的数据在内存中使用数组结构保存，数组的初始大小为1024，上限为500 000，这意味着它最多只能保存500 000行数据。如果在创建字典时数据量超出其上限，那么字典会创建失败。代码清单2-1所示是通过手动创建的flat字典配置文件。
代码清单2-1 flat类型字典的配置文件test_flat_dictionary.xml
<?xml version="1.0"?>
<dictionaries>
<dictionary>
<name>test_flat_dict</name>
<source>
<!—准备好的测试数据-->
<file>
<path>/chbase/data/dictionaries /organization.csv</path>
<format>CSV</format>
</file>
</source>
<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>
<lifetime>
<min>300</min>
<max>360</max>
</lifetime>
</dictionary>
</dictionaries>
在上述的配置中，source数据源是CSV格式的文件，structure数据结构与其对应。将配置文件复制到ClickHouse服务节点的/etc/clickhouse-server目录后，即完成了对该字典的创建过程。查验system.dictionaries系统表后，能够看到flat字典已经创建成功。
SELECT name, type, key, attribute.names, attribute.types FROM system.dictionaries
┌─name──────────┬─type─┬─key────┬─attribute.names─┐
│ test_flat_dict │ Flat │ UInt64 │ ['code','name'] │
└──────────────┴────┴───────┴───────────┘
2.hashed
hashed字典同样只能够使用UInt64数值型key，但与flat字典不同的是，hashed字典的数据在内存中通过散列结构保存，且没有存储上限的制约。代码清单2-2所示是仿照flat创建的hashed字典配置文件。
代码清单2-2 hashed类型字典的配置文件test_hashed_dictionary.xml
<?xml version="1.0"?>
<dictionaries>
<dictionary>
<name>test_hashed_dict</name>
与flat字典配置相同,省略…
<layout>
<hashed/>
</layout>
省略…
</dictionary>
</dictionaries>
3.range_hashed
range_hashed字典可以看作hashed字典的变种，它在原有功能的基础上增加了指定时间区间的特性，数据会以散列结构存储并按照时间排序。时间区间通过range_min和range_max元素指定，所指定的字段必须是Date或者DateTime类型。
现在仿照hashed字典的配置，创建一个名为test_range_hashed_dictionary.xml的配置文件，将layout改为range_hashed并增加range_min和range_max元素。它的完整配置如代码清单2-3所示。
代码清单2-3 range_hashed类型字典的配置文件test_range_hashed_dictionary.xml
<?xml version="1.0"?>
<dictionaries>
<dictionary>
<name>test_range_hashed_dict</name>
<source>
<file>
<path>/chbase/data/dictionaries/sales.csv</path>
<format>CSV</format>
</file>
</source>
<layout>
<range_hashed/>
</layout>
<structure>
<id>
<name>id</name>
</id>
<range_min>
<name>start</name>
</range_min>
<range_max>
<name>end</name>
</range_max>
<attribute>
<name>price</name>
<type>Float32</type>
<null_value></null_value>
</attribute>
</structure>
<lifetime>
<min>300</min>
<max>360</max>
</lifetime>
</dictionary>
</dictionaries>
在上述的配置中，使用了一份销售数据，数据中的start和end字段分别与range_min和range_max对应。将配置文件复制到ClickHouse服务节点的/etc/clickhouse-server目录后，即完成了对该字典的创建过程。查验system.dictionaries系统表后，能够看到range_hashed字典已经创建成功：
SELECT name, type, key, attribute.names, attribute.types FROM system.dictionaries
┌─name───────────┬─type─────┬─key───┬─attribute.names─┐
│ test_range_hashed_dict │ RangeHashed │ UInt64 │ ['price'] │
└──────────────┴────────┴─────┴───────────┘
4.cache
cache字典只能够使用UInt64数值型key，它的字典数据在内存中会通过固定长度的向量数组保存。定长的向量数组又称cells，它的数组长度由size_in_cells指定。而size_in_cells的取值大小必须是2的整数倍，如若不是，则会自动向上取为2的倍数的整数。
cache字典的取数逻辑与其他字典有所不同，它并不会一次性将所有数据载入内存。当从cache字典中获取数据的时候，它首先会在cells数组中检查该数据是否已被缓存。如果数据没有被缓存，它才会从源头加载数据并缓存到cells中。所以cache字典是性能最不稳定的字典，因为它的性能优劣完全取决于缓存的命中率（缓存命中率=命中次数/查询次数），如果无法做到99%或者更高的缓存命中率，则最好不要使用此类型。代码清单2-4所示是仿照hashed创建的cache字典配置文件。
代码清单2-4 cache类型字典的配置文件test_cache_dictionary.xml
<?xml version="1.0"?>
<dictionaries>
<dictionary>
<name>test_cache_dict</name>
<source>
<!—- 本地文件需要通过 executable形式 -->
<executable>
<command>cat /chbase/data/dictionaries/organization.csv</command>
<format>CSV</format>
</executable>
</source>
<layout>
<cache>
<!—- 缓存大小 -->
<size_in_cells>10000</size_in_cells>
</cache>
</layout>
省略…
</dictionary>
</dictionaries>
在上述配置中，layout被声明为cache并将缓存大小size_in_cells设置为10000。关于cells的取值可以根据实际情况考虑，在内存宽裕的情况下设置成1000000000也是可行的。还有一点需要注意，如果cache字典使用本地文件作为数据源，则必须使用executable的形式设置。
5.complex_key_hashed
complex_key_hashed字典在功能方面与hashed字典完全相同，只是将单个数值型key替换成了复合型。代码清单2-5所示是仿照hashed字典进行配置后，将layout改为complex_key_hashed并替换key类型的示例。
代码清单2-5 complex_key_hashed类型字典的配置文件test_complex_key_hashed_dictionary.xml
<?xml version="1.0"?>
<dictionaries>
<dictionary>
<name>test_complex_key_hashed_dict</name>
<!—- 与hashed字典配置相同,省略…-->
<layout>
<complex_key_hashed/>
</layout>
<structure>
<!—- 复合型key -->
<key>
<attribute>
<name>id</name>
<type>UInt64</type>
</attribute>
<attribute>
<name>code</name>
<type>String</type>
</attribute>
</key>
省略…
</structure>
省略…
将配置文件复制到ClickHouse服务节点的/etc/clickhouse-server目录后，即完成了对该字典的创建过程。
6.complex_key_cache
complex_key_cache字典同样与cache字典的特性完全相同，只是将单个数值型key替换成了复合型。现在仿照cache字典进行配置，将layout改为complex_key_cache并替换key类型，如代码清单2-6所示。 代码清单2-6complex_key_cache类型字典的配置文件test_complex_key_cache_dictionary.xml
<?xml version="1.0"?>
<dictionaries>
<dictionary>
<name>test_complex_key_cache_dict</name>
<!—-与cache字典配置相同,省略…-->
<layout>
<complex_key_cache>
<size_in_cells>10000</size_in_cells>
</complex_key_cache>
</layout>
<structure>
<!—- 复合型Key -->
<key>
<attribute>
<name>id</name>
<type>UInt64</type>
</attribute>
<attribute>
<name>code</name>
<type>String</type>
</attribute>
</key>
省略…
</structure>
省略…
将配置文件复制到ClickHouse服务节点的/etc/clickhouse-server目录后，即完成了对该字典的创建过程。
7.ip_trie
虽然同为复合型key的字典，但ip_trie字典却较为特殊，因为它只能指定单个String类型的字段，用于指代IP前缀。ip_trie字典的数据在内存中使用trie树结构保存，且专门用于IP前缀查询的场景，例如通过IP前缀查询对应的ASN信息。它的完整配置如代码清单2-7所示。
代码清单2-7 ip_trie类型字典的配置文件test_ip_trie_dictionary.xml
<?xml version="1.0"?>
<dictionaries>
<dictionary>
<name>test_ip_trie_dict</name>
<source>
<file>
<path>/chbase/data/dictionaries/asn.csv</path>
<format>CSV</format>
</file>
</source>
<layout>
<ip_trie/>
</layout>
<structure>
<!—虽然是复合类型,但是只能设置单个String类型的字段 -->
<key>
<attribute>
<name>prefix</name>
<type>String</type>
</attribute>
</key>
<attribute>
<name>asn</name>
<type>String</type>
<null_value></null_value>
</attribute>
<attribute>
<name>country</name>
<type>String</type>
<null_value></null_value>
</attribute>
</structure>
省略…
</dictionary>
</dictionaries>
通过上述介绍，读者已经知道了7种类型字典的创建方法。在这些字典中，flat、hashed和range_hashed依次拥有最高的性能，而cache性能最不稳定。最后再总结一下这些字典各自的特点，如表所示。
