1. 数据字典介绍
数据字典是ClickHouse提供的一种非常简单、实用的存储媒介，它以键值和属性映射的形式定义数据。
字典中的数据会主动或者被动加载到内存，并支持动态更新。
由于字典数据常驻内存的特性，所以它非常适合保存常量或经常使用的维度表数据，以避免不必要的JOIN查询。
在默认情况下，字典中的数据只能通过字典函数访问。
数据字典分为内置与扩展两种形式：
- 内置字典：ClickHouse默认自带的字典
- 扩展字典：用户通过自定义配置实现的字典
在字典表引擎的帮助下，可以将数据字典挂载到一张代理的数据表下，从而实现数据表与字典数据的JOIN查询。
2. 内置字典
- ClickHouse目前只有
Yandex.Metrica一种内置字典 - Yandex.Metrica 设计意图是快速存取
geo地理数据 - 版权原因Yandex并没有将geo地理数据开放出来，只是提供了字典的定义机制和取数函数
- 如果想使用 Yandex.Metrica字典，需要遵照它的字典规范自行导入数据
2.1 内置字典配置说明
- 内置字典在默认的情况下是禁用状态，需要开启后才能使用。
- 内置字典的配置是惰性加载的，只有当字典首次被查询的时候才会触发加载动作。
- 填充 Yandex.Metrica 字典的 geo 地理数据由两组模型组成，可以分别理解为地区数据的主表及维度表。
- 开启它的方式也十分简单，只需将 config.xml 文件的主表与维表两项配置打开即可。
- 这两项配置是惰性加载的，只有当字典首次被查询的时候才会触发加载动作。
2.1.1 path_to_regions_hierarchy_file
path_to_regions_hierarchy_file 相当于区域数据的主表，由1个 regions_hierarchy.txt 和多个 regions_hierarchy_[name].txt 区域层次的数据文件共同组成，缺一不可。其中 [name] 表示区域标识符，与i18n类似。这些TXT文件内的数据需要使用 TabSeparated 格式定义，其数据模型的格式如下表所示。
| 字段名 | 类型 | 必须 | 说明 |
|---|---|---|---|
| Region ID | UInt32 | 是 | 区域 ID |
| Parent Region ID | UInt32 | 是 | 上级区域 ID |
| Region Type | UInt8 | 是 | 区域类型： 1：continent 大陆 3：country 国家 4：federal district 联邦区 5：region 地区 6：city 城市 |
| Population | UInt32 | 否 | 人口 |
2.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 格式定义，其数据模型的格式如下表所示。
| 字段名 | 类型 | 必须 | 说明 |
|---|---|---|---|
| Region ID | UInt32 | 是 | 区域 ID |
| Region Name | UInt32 | 是 | 区域名称 |
2.2 使用内置字典
下载测试数据 https://github.com/nauu/clickhousebook/tree/master/source/dict/built-in/geo
- 在没有配置内置字典的时候
SELECT regionToName(toUInt32(20009));
SELECT regionToName(toUInt32(20009))
Query id: b0aba82e-d5eb-4f88-997a-8d874572a882
0 rows in set. Elapsed: 0.206 sec.
Received exception from server (version 21.4.3):
Code: 156. DB::Exception: Received from localhost:9977. DB::Exception: Embedded dictionaries were not loaded. You need to check configuration file.: While processing regionToName(toUInt32(20009)).- 修改配置文件
# 编辑配置文件
vim /etc/clickhouse-server/config.xml
# 增加配置
/var/lib/clickhouse/geo/regions_hierarchy.txt
/var/lib/clickhouse/geo/
- 创建内置字典数据文件夹并上传字典数据
[root@node3 geo]# cd /var/lib/clickhouse/
[root@node3 geo]# mkdir geo
# 上传数据文件，之后查看
[root@node3 geo]# cd geo
总用量 10256
-rw-r--r-- 1 root root 3096 5月 6 15:59 regions_hierarchy_ru.txt
-rw-r--r-- 1 root root 3096 5月 6 15:59 regions_hierarchy.txt
-rw-r--r-- 1 root root 3957 5月 6 15:59 regions_names_ar.txt
-rw-r--r-- 1 root root 3957 5月 6 15:59 regions_names_by.txt
-rw-r--r-- 1 root root 3957 5月 6 15:59 regions_names_en.txt
-rw-r--r-- 1 root root 3957 5月 6 15:59 regions_names_kz.txt
-rw-r--r-- 1 root root 3957 5月 6 15:59 regions_names_ru.txt
-rw-r--r-- 1 root root 3957 5月 6 15:59 regions_names_tr.txt
-rw-r--r-- 1 root root 3957 5月 6 15:59 regions_names_ua.txt- 重启 clickhouse
# 关闭服务
systemctl stop clickhouse-server.service
# 检查服务是否完全关闭
netstat -nltp | grep clickhouse
# 启动服务
systemctl start clickhouse-server.service- 测试内置字典
SELECT regionToName(toUInt32(20009)) as name;
┌─name──────────────────┐
│ Buenos Aires Province │
└───────────────────────┘3. 外部扩展字典
- 扩展字典与内置字典更加常用。
- 外部扩展字典是以插件形式注册到ClickHouse中的，由用户自行定义数据模式及数据来源。
- 扩展字典支持文件、数据库和其他类型三种数据来源。
- 扩展字典支持flat、hashed、range_hashed、cache、complex_key_hashed、complex_key_cache和ip_trie共 7 种类型。
3.1 配置文件介绍
扩展字典的配置文件由 config.xml 文件中的 dictionaries_config 配置项指定：
<!-- Configurationofexternaldictionaries.See:
https://clickhouse.yandex/docs/en/dicts/external_dicts/ -->
<!-- 会扫描 /etc/clickhouse-server/ 下所有以 _dictionary.xml 结尾的配置文件 -->
<dictionaries_config>
*_dictionary.xml
</dictionaries_config>- ClickHouse 会自动识别并加载 /etc/clickhouseserver 目录下所有以
_dictionary.xml结尾的配置文件。 - ClickHouse 也能够动态感知到此目录下配置文件的各种变化，并支持不停机在线更新配置文件。
- 在单个字典配置文件内可以定义多个字典，其中每一个字典由一组dictionary元素定义。
- 在dictionary元素之下又分为5个子元素，均为必填项。
- 配置结构
<dictionaries>
<dictionary>
<name> dict_name </name>
<layout> 在内存中的数据格式类型 </layout>
<structure> 字典的数据结构 </structure>
<source> 数据源配置 </source>
<lifetime> 字典的自动更新频率 </lifetime>
</dictionary>
</dictionaries>在上述结构中，主要配置的含义如下。
| 配置 | 含义 |
|---|---|
| name | 字典的名称，用于确定字典的唯一标识，必须全局唯一，多个字典之间不允许重复。 |
| layout | 字典的类型，它决定了数据在内存中以何种结构组织和存储。目前扩展字典共拥有7种类型 |
| structure | 字典的数据结构 |
| source | 字典的数据源，它决定了字典中数据从何处加载。目前扩展字典共拥有文件、数据库和其他三类数据来源 |
| lifetime | 字典的更新时间，扩展字典支持数据在线更新 |
3.2 数据结构
扩展字典的数据结构由 structure 元素定义，由键值 key 和属性 attribute 两部分组成，它们分别描述字典的数据标识和字段属性。structure 的完整形式如下所示：
<!-- 在扩展字典的查询过程中都会通过这些字段来访问字典中的数据 -->
<dictionary>
<structure>
<!-- 单值类型 key 使用<id> ， 复合类型 key 使用<key> -->
<id> Key属性 </id>
<attribute> 字段属性 </attribute>
</structure>
</dictionary>3.2.1 key
- key用于定义字典的键值，每个字典必须包含1个键值 key 字段，用于定位数据，类似数据库的表主键。
- 键值key分为数值型和复合型两类。
（1）数值型：数值型key由UInt64整型定义，支持 flat、hashed、range_hashed 和 cache 类型的字典（扩展字典类型会在后面介绍），它的定义方法如下所示。
<structure>
<id>
<name>Id</name>
</id>
...
</structure>（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>
...
</structure>3.2.2 attribute
- attribute 用于定义字典的属性字段，字典可以拥有1到多个属性字段。
- attribute 元素下共有7个配置项，其中name、type和null_value为必填项。
- attribute 的完整定义方法如下所示：
<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>| 配置项 | 是否必填 | 默认值 | 说明 |
|---|---|---|---|
| name | 是 | - | 字段名称 |
| type | 是 | - | 字段类型 |
| null_value | 是 | - | 在查询时，条件 key 没有对应元素的默认值 |
| expression | 否 | 无表达式 | 表达式，可以使用运算符或调用函数 |
| hierarchical | 否 | false | 是否支持层次结构 |
| injective | 否 | false | 是否支持集合单射优化，开启后，在后续的 group by 查询中，如果调用 dictGet函数通过 key 获取 value，则该 value 直接从group by 数据返回 |
| is_object_id | 否 | false | 是否开启MongoDB 优化，通过 ObjectID 对 MongoDB 文档执行查询 |
单射集合：有 A，B 两个集合，假如 A 集合中的每个元素都在 B 集合有一个唯一与之对应的元素，那么A 集合到 B 集合的对应关系即单射集合。
3.3 准备测试数据
- 企业组织数据
将用于flat、hashed、cache、complex_key_hashed和complex_key_cache字典的演示场景。
| id | code | name |
|---|---|---|
| 1 | a0001 | 研发部 |
| 2 | a0002 | 产品部 |
| 3 | a0003 | 数据部 |
下载地址：https://github.com/nauu/clickhousebook/dict/plugin/testdata/organization.csv
- 销售数据
将用于range_hashed字典的演示场景。
| id | start | end | price |
|---|---|---|---|
| 1 | 20160101 | 20170110 | 100 |
| 2 | 20160501 | 20170701 | 200 |
| 3 | 20140305 | 20180120 | 300 |
下载地址：https://github.com/nauu/clickhousebook/dict/plugin/testdata/sales.csv
- 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 |
下载地址：https://github.com/nauu/clickhousebook/dict/plugin/testdata/asn.csv
3.4 扩展字典的类型
一个字典的类型，既决定了其数据在内存中的存储结构，也决定了该字典支持的key键类型。
扩展字典的类型使用 layout 元素定义，目前共有7种类型。
根据key键类型的不同，可以将它们划分为两类：
- 单数值key类型：使用单个数值型的id，flat、hashed、range_hashed和cache
- 复合key类型：complex_key_hashed、complex_key_cache和ip_trie
- complex_key_hashed与complex_key_cache字典在功能方面与hashed和cache并无二致，只是单纯地将数值型key替换成了复合型key而已。
flat、hashed和range_hashed依次拥有最高的性能，而cache性能最不稳定。
| 名称 | 存储结构 | 字典键类型 | 支持数据源 |
|---|---|---|---|
| flat | 数组 | UInt64 | Local File Executable File HTTP DBMS |
| hashed | 散列 | UInt64 | Local File Executable File HTTP DBMS |
| range_hashed | 散列按时间排序 | UInt64和时间 | Local File Executable File HTTP DBMS |
| complex_key_hashed | 散列 | 复合型 key | Local File Executable File HTTP DBMS |
| ip_trie | 层次结构 | 复合型 key (单个 string) |
Local File Executable File HTTP DBMS |
| cache | 固定大小数组 | UInt64 | Executable File HTTP Clickhouse、MySQL |
| complex_key_cache | 固定大小数组 | 复合型 key | Executable File HTTP Clickhouse、MySQL |
3.4.1 flat
flat字典是所有类型中性能最高的字典类型，它只能使用UInt64数值型key。flat 字典的数据在内存中使用数组结构保存，数组的初始大小为1024，上限为500000，这意味着它最多只能保存500000行数据。
如果在创建字典时数据量超出其上限，那么字典会创建失败。
vim /etc/clickhouse-server/flat_dictionary.xml
<!-- 通过手动创建的flat字典配置 -->
<dictionaries>
<dictionary>
<name>test_flat_dict</name>
<source>
<!-- 准备好的测试数据 -->
<file>
<path>/etc/clickhouse-server/dict_data/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/clickhouseserver 目录后，即完成了对该字典的创建过程。查验 system.dictionaries 系统表后，能够看到flat字典已经创建成功。
SELECT name,type,key.names,key.types,attribute.names,attribute.types,origin FROM system.dictionaries;
┌─name───────────┬─type─┬─key.names─┬─key.types──┬─attribute.names─┬─attribute.types─────┬─origin─────────────────────────────────────┐
│ test_flat_dict │ │ ['id'] │ ['UInt64'] │ ['code','name'] │ ['String','String'] │ /etc/clickhouse-server/flat_dictionary.xml │
└────────────────┴──────┴───────────┴────────────┴─────────────────┴─────────────────────┴────────────────────────────────────────────┘3.4.2 hashed
- hashed 字典同样只能够使用UInt64数值型key
- hashed 字典的数据在内存中通过散列结构保存，
- hashed 字典的数据没有存储上限的制约。
vim /etc/clickhouse-server/hashed_dictionary.xml
<!-- 通过手动创建的hashed字典配置 -->
<dictionaries>
<dictionary>
<name>test_hashed_dict</name>
<source>
<!-- 准备好的测试数据 -->
<file>
<path>/etc/clickhouse-server/dict_data/organization.csv</path>
<format>CSV</format>
</file>
</source>
<layout>
<hashed/>
</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/clickhouseserver 目录后，即完成了对该字典的创建过程。查验 system.dictionaries 系统表后，能够看到 hashed 字典已经创建成功。
SELECT name,type,key.names,key.types,attribute.names,attribute.types,origin FROM system.dictionaries;
┌─name─────────────┬─type─┬─key.names─┬─key.types──┬─attribute.names─┬─attribute.types─────┬─origin───────────────────────────────────────┐
│ test_hashed_dict │ │ ['id'] │ ['UInt64'] │ ['code','name'] │ ['String','String'] │ /etc/clickhouse-server/hashed_dictionary.xml │
└──────────────────┴──────┴───────────┴────────────┴─────────────────┴─────────────────────┴──────────────────────────────────────────────┘
3.4.3 range_hashed
- range_hashed 字典可以看作 hashed 字典的变种。
- range_hashed 字典在原有功能的基础上增加了指定时间区间的特性，数据会以散列结构存储并按照时间排序。
- 时间区间通过 range_min 和 range_max 元素指定，所指定的字段必须是 Date 或者 DateTime 类型。
vim /etc/clickhouse-server/range_hashed_dictionary.xml
<!-- 现在仿照hashed字典的配置，创建一个名为/etc/clickhouse-server/range_hashed_dictionary.xml的配置文件 -->
<dictionaries>
<dictionary>
<name>test_range_hashed_dict</name>
<source>
<file>
<path>/etc/clickhouse-server/dict_data/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>
在上述的配置中，使用了一份销售数据，数据中的 star t和 end 字段分别与 range_min 和 range_max 对应。将配置文件复制到ClickHouse服务节点的/etc/clickhouse-server目录后，即完成了对该字典的创建过程。查验system.dictionaries系统表后，能够看到range_hashed字典已经创建成功：
SELECT name,type,key.names,key.types,attribute.names,attribute.types,origin FROM system.dictionaries;
┌─name───────────────────┬─type─┬─key.names─┬─key.types──┬─attribute.names─┬─attribute.types─────┬─origin─────────────────────────────────────────────┐
│ test_range_hashed_dict │ │ ['id'] │ ['UInt64'] │ ['price'] │ ['Float32'] │ /etc/clickhouse-server/range_hashed_dictionary.xml │
└────────────────────────┴──────┴───────────┴────────────┴─────────────────┴─────────────────────┴────────────────────────────────────────────────────┘
3.4.4 cache
cache 字典只能够使用 UInt64 数值型key，它的字典数据在内存中会通过固定长度的向量数组保存。
定长的向量数组又称cells，它的数组长度由 size_in_cells 指定。
size_in_cells 的取值大小必须是2的整数倍，如若不是，则会自动向上取为2的倍数的整数。
cache字典的取数逻辑与其他字典有所不同，它并不会一次性将所有数据载入内存。
当从cache字典中获取数据的时候，它首先会在cells数组中检查该数据是否已被缓存。如果数据没有被缓存，它才会从源头加载数据并缓存到cells中。
所以cache字典是性能最不稳定的字典，因为它的性能优劣完全取决于缓存的命中率（缓存命中率=命中次数/查询次数）。
如果无法做到99%或者更高的缓存命中率，则最好不要使用此类型。
vim /etc/clickhouse-server/cache_dictionary.xml
<dictionaries>
<dictionary>
<name>test_cache_dict</name>
<source>
<!-- 本地文件需要通过executable形式 -->
<executable>
<command>cat /etc/clickhouse-server/dict_data/organization.csv</command>
<format>CSV</format>
</executable>
</source>
<layout>
<cache>
<!-- 缓存大小 -->
<size_in_cells>10000</size_in_cells>
</cache>
</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>在上述配置中，layout被声明为cache并将缓存大小 size_in_cells 设置为10000。关于cells的取值可以根据实际情况考虑，在内存宽裕的情况下设置成1000000000也是可行的。还有一点需要注意，如果cache字典使用本地文件作为数据源，则必须使用executable的形式设置。
SELECT name,type,key.names,key.types,attribute.names,attribute.types,origin FROM system.dictionaries;
┌─name───────────────────┬─type─┬─key.names─┬─key.types──┬─attribute.names─┬─attribute.types─────┬─origin─────────────────────────────────────────────┐
│ test_range_hashed_dict │ │ ['id'] │ ['UInt64'] │ ['price'] │ ['Float32'] │ /etc/clickhouse-server/range_hashed_dictionary.xml │
└────────────────────────┴──────┴───────────┴────────────┴─────────────────┴─────────────────────┴────────────────────────────────────────────────────┘
3.4.5 complex_key_hashed
complex_key_hashed 字典在功能方面与hashed字典完全相同，只是将单个数值型key替换成了复合型。
<dictionaries>
<dictionary>
<name></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>
省略…
vim /etc/clickhouse-server/complex_key_hashed_dictionary.xml
<!-- 通过手动创建的hashed字典配置 -->
<dictionaries>
<dictionary>
<name>test_complex_key_hashed_dict</name>
<source>
<!-- 准备好的测试数据 -->
<file>
<path>/etc/clickhouse-server/dict_data/organization.csv</path>
<format>CSV</format>
</file>
</source>
<layout>
<complex_key_hashed/>
</layout>
<!-- 与测试数据的结构对应 -->
<structure>
<key>
<attribute>
<name>id</name>
<type>UInt64</type>
</attribute>
<attribute>
<name>code</name>
<type>String</type>
</attribute>
</key>
<attribute>
<name>name</name>
<type>String</type>
<null_value></null_value>
</attribute>
</structure>
<lifetime>
<min>300</min>
<max>360</max>
</lifetime>
</dictionary>
</dictionaries>
SELECT name,type,key.names,key.types,attribute.names,attribute.types,origin FROM system.dictionaries;
┌─name─────────────────────────┬─type─┬─key.names─────┬─key.types───────────┬─attribute.names─┬─attribute.types─────┬─origin───────────────────────────────────────────────────┐
│ test_complex_key_hashed_dict │ │ ['id','code'] │ ['UInt64','String'] │ ['name'] │ ['String'] │ /etc/clickhouse-server/complex_key_hashed_dictionary.xml │
└──────────────────────────────┴──────┴───────────────┴─────────────────────┴─────────────────┴─────────────────────┴──────────────────────────────────────────────────────────┘3.4.6 complex_key_cache
complex_key_cache字典同样与cache字典的特性完全相同，只是将单个数值型key替换成了复合型。
vim /etc/clickhouse-server/complex_key_cache_dictionary.xml
<dictionaries>
<dictionary>
<name>test_complex_key_cache_dict</name>
<source>
<!-- 本地文件需要通过executable形式 -->
<executable>
<command>cat /etc/clickhouse-server/dict_data/organization.csv</command>
<format>CSV</format>
</executable>
</source>
<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>
<attribute>
<name>name</name>
<type>String</type>
<null_value></null_value>
</attribute>
</structure>
<lifetime>
<min>300</min>
<max>360</max>
</lifetime>
</dictionary>
</dictionaries>
SELECT name,type,key.names,key.types,attribute.names,attribute.types,origin FROM system.dictionaries;
SELECT
name,
type,
key.names,
key.types,
attribute.names,
attribute.types,
origin
FROM system.dictionaries
Query id: 187447fa-d02a-4b26-98c7-259c2b9243f5
┌─name─────────────────────────┬─type─┬─key.names─────┬─key.types───────────┬─attribute.names─┬─attribute.types─────┬─origin───────────────────────────────────────────────────┐
│ test_complex_key_cache_dict │ │ ['id','code'] │ ['UInt64','String'] │ ['name'] │ ['String'] │ /etc/clickhouse-server/complex_key_cache_dictionary.xml │
└──────────────────────────────┴──────┴───────────────┴─────────────────────┴─────────────────┴─────────────────────┴──────────────────────────────────────────────────────────┘3.4.7 ip_trie
虽然同为复合型key的字典，但ip_trie字典却较为特殊，因为它只能指定单个String类型的字段，用于指代IP前缀。ip_trie字典的数据在内存中使用trie树结构保存，且专门用于IP前缀查询的场景，例如通过IP前缀查询对应的ASN信息。
vim /etc/clickhouse-server/ip_trie_dictionary.xml
<dictionaries>
<dictionary>
<name>test_ip_trie_dict</name>
<source><file>
<path>/etc/clickhouse-server/dict_data/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>
<lifetime>
<min>300</min>
<max>360</max>
</lifetime>
</dictionary>
</dictionaries>
SELECT name,type,key.names,key.types,attribute.names,attribute.types,origin FROM system.dictionaries;
SELECT
name,
type,
key.names,
key.types,
attribute.names,
attribute.types,
origin
FROM system.dictionaries
Query id: 2baf7779-3759-4bf9-8302-bb742a29c917
┌─name─────────────────────────┬─type─┬─key.names─────┬─key.types───────────┬─attribute.names───┬─attribute.types─────┬─origin───────────────────────────────────────────────────┐
│ test_ip_trie_dict │ │ ['prefix'] │ ['String'] │ ['asn','country'] │ ['String','String'] │ /etc/clickhouse-server/ip_trie_dictionary.xml │
└──────────────────────────────┴──────┴───────────────┴─────────────────────┴───────────────────┴─────────────────────┴──────────────────────────────────────────────────────────┘
3.5 数据源
扩展字典数据源使用 source 元素定义，它指定了字典的数据从何而来。扩展字典支持3大类共计9种数据源，接下来会介绍这些数据源的使用方式。
3.5.1 文件类型
3.5.1.1 本地文件
本地文件使用file元素定义。其中，path表示数据文件的绝对路径，而format表示数据格式，例如CSV或者TabSeparated等。它的完整配置如下所示。
<source>
<file>
<path>/etc/clickhouse-server/dict_data/asn.csv</path>
<format>CSV</format>
</file>
</source>3.5.1.2 可执行文件
可执行文件数据源属于本地文件的变种，它需要通过cat命令访问数据文件。对于cache和complex_key_cache类型的字典，必须使用此类型的文件数据源。可执行文件使用executable元素定义。其中，command表示数据文件的绝对路径，format表示数据格式，例如CSV或者TabSeparated等。
<source>
<executable>
<command>cat /etc/clickhouse-server/dict_data/asn.csv</command>
<format>CSV</format>
</executable>
</source>3.5.1.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>3.5.2 数据库类型
数据库类型的数据源比文件类型更适合在正式的生产环境中使用。目前扩展字典支持MySQL、ClickHouse本身及MongoDB三种数据库。接下来会分别介绍它们的创建方法。
3.5.2.1 MySQL
MySQL数据源支持从指定的数据库中提取数据，作为其字典的数据来源。首先，需要准备源头数据，执行下面的语句在MySQL中创建测试表：
-- 创建MySQL 表
CREATETABLE 't_organization'(
`id` int(11) NOTNULLAUTO_INCREMENT,
`code` varchar(40) DEFAULT NULL,
`name` varchar(60) DEFAULT NULL,
`updatetime` datetime DEFAULT NULL,
PRIMARYKEY(`id`)
)ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
-- 插入数据
INSERT INTO t_organization(code,name,updatetime) VALUES ('a0001','研发部',NOW()),('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>
<!-- 数据库host地址，支持MySQL集群 -->
<replica>
<!-- 数据库host地址-->
<host>10.37.129.2</host>
<!-- 优先级 -->
<priority>1</priority>
</replica>
<!-- database数据库 -->
<db>test</db>
<!-- 字典对应的数据表 -->
<table>t_organization</table>
<!-- 非必填项 -->
<!-- 查询table时的过滤条件 -->
<where>id=1</where>
<!-- 指定一条SQL语句，用于在数据更新时判断是否需要更 -->
<invalidate_query>SQL_QUERY</invalidate_query>
</mysql>
</source>
...
</dictionary>
</dictionaries>3.5.2.2 ClickHouse
扩展字典支持将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','市场部',NOW());ClickHouse数据源的配置与MySQL数据源极为相似，所以我们可以仿照MySQL数据源的字典配置，创建一个名为test_ch_dictionary.xml的配置文件，将source替换成ClickHouse数据源：
<dictionaries>
<dictionary>
<name>test_ch_dict</name>
<source>
<clickhouse>
<!-- 数据库host地址 -->
<host>10.37.129.6</host>
<!-- 数据库端口 -->
<port>9000</port>
<!-- 数据库用户名 -->
<user>default</user>
<!-- 数据库密码 -->
<password></password>
<!-- database数据库 -->
<db>default</db>
<!-- 字典对应的数据表 -->
<table>t_organization</table>
<!-- 非必填 -->
<!-- 查询table时的过滤条件 -->
<where>id=1</where>
<!-- 指定一条SQL语句，用于在数据更新时判断是否需要更 -->
<invalidate_query>SQL_QUERY</invalidate_query>
</clickhouse>
</source>
...
</dictionary>
</dictionaries>3.5.2.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>
<mongodb>
<!-- 数据库host地址 -->
<host>10.37.129.2</host>
<!-- 数据库端口 -->
<port>27017</port>
<!-- 数据库用户名 -->
<user></user>
<!-- 数据库密码 -->
<password></password>
<!-- database数据库 -->
<db>test</db>
<!-- 与字典对应的collection的名称 -->
<collection>t_organization</collection>
</mongodb>
</source>
...
</dictionary>
</dictionaries>3.5.3 其他类型
除了上述已经介绍过的两类数据源之外，扩展字典还支持通过ODBC的方式连接PostgreSQL和MSSQLServer数据库作为数据源。它们的配置方式与数据库类型数据源类似。
3.6 数据更新策略
扩展字典支持数据的在线更新，更新后无须重启服务。字典数据的更新频率由配置文件中的lifetime元素指定，单位为秒：
<lifetime>
<min>300</min>
<max>360</max>
</lifetime>- min与max分别指定了更新间隔的上下限。
- ClickHouse 会在这个时间区间内随机触发更新动作，这样能够有效错开更新时间，避免所有字典在同一时间内爆发性的更新。
- 当min和max都是0的时候，将禁用字典更新。
- 对于cache字典而言，lifetime 还代表了它的缓存失效时间。
字典内部拥有版本的概念，在数据更新的过程中，旧版本的字典将持续提供服务，只有当更新完全成功之后，新版本的字典才会替代旧版本。所以更新操作或者更新时发生的异常，并不会对字典的使用产生任何影响。
不同类型的字典数据源，更新机制也稍有差异。总体来说，扩展字典目前并不支持增量更新。但部分数据源能够依照标识判断，只有在源数据发生实质变化后才实施更新动作。这个判断源数据是否被修改的标识，在字典内部称为 previous，它保存了一个用于比对的值。ClickHouse的后台进程每隔5秒便会启动一次数据刷新的判断，依次对比每个数据字典中前后两次previous的值是否相同。若相同，则代表无须更新数据；若不同且满足更新频率，则代表需要更新数据。而对于previous值的获取方式，不同的数据源有不同的实现逻辑，下面详细介绍。
- 文件数据源
文件类型的数据源的previous来自修改文件的系统时间，当前后两次修改时间不同时，才会进行更新。
[root@node3 opt]# stat multisqls
文件："multisqls"
大小：81 块：8 IO 块：4096 普通文件
设备：803h/2051d Inode：2152750551 硬链接：1
权限：(0644/-rw-r--r--) Uid：( 0/ root) Gid：( 0/ root)
最近访问：2021-04-26 15:19:33.637603807 +0800
最近更改：2021-04-26 15:18:43.166934380 +0800
最近改动：2021-04-26 15:18:43.209934098 +0800
创建时间：-
- MySQL(InnoDB)、ClickHouse、ODBC
对于MySQL(InnoDB)、ClickHouse和ODBC数据源，previous 来源于invalidate_query中定义的 SQL 语句。
<source>
<mysql>
<!-- 如果前后两次 updatetime 不一致，则说明源数据发生了变化需要更新数据字典 -->
<invalidate_query>
select updatetime from t_organization where id = 8
</invalidate_query>
</mysql>
</source>
MySQL（MyIMyISAM）
如果 MySQL 使用的是MyIMyISAM表引擎，可以使用 show table status 查询修改时间，进而判断是否需要更新字典
- 其余数据源
除了前面介绍的两种数据源，其他数据源无法通过判断标识符更新的情况那么只能根据 lifetime 设置的时间来进行更新。
数据字典除了被动更新之外还可以进行主动更新：
-- 更新全部字典
system reload dictionaries;
-- 更新指定字典
system reload dictionary dict_name;3.7 元数据介绍
通过 system.dictionaries 系统表可以查看到扩展字典的元数据信息。
desc dictionaries;
| name | type | comment |
|---|---|---|
| database | String | 所在数据库 |
| name | String | 字典名称，在使用字典函数时需要使用字典名称访问数据 |
| uuid | UUID | |
| status | Enum8( ‘NOT_LOADED’ = 0, ‘LOADED’ = 1, ‘FAILED’ = 2, ‘LOADING’ = 3, ‘FAILED_AND_RELOADING’ = 4, ‘LOADED_AND_RELOADING’ = 5, ‘NOT_EXIST’ = 6 ) |
|
| origin | String | |
| type | String | 字典类型 |
| key.names | Array(String) | Key 名称 |
| key.types | Array(String) | Key 数据类型 |
| attribute.names | Array(String) | |
| attribute.types | Array(String) | |
| bytes_allocated | UInt64 | 已经载入数据占用内存字节数 |
| query_count | UInt64 | 字典被查询次数 |
| hit_rate | Float64 | 命中率 |
| element_count | UInt64 | 载入行数 |
| load_factor | Float64 | 数据加载率 |
| source | String | 数据源信息 |
| lifetime_min | UInt64 | 生命周期最小时间 |
| lifetime_max | UInt64 | 生命周期最大时间 |
| loading_start_time | DateTime | 最初加载字典时间 |
| last_successful_update_time | DateTime | 最后更新字典时间 |
| loading_duration | Float32 | 加载字典消耗时间 |
| last_exception | String | 加载字典过程中的异常信息， |
3.8 基本操作
数据字典只能通过字典函数获取，以下为查询示例：
-- 使用 dictGet 函数查询数据
select dictGet('test_flat_dict','name',toUInt64(1)) as dept_name;
┌─dept_name─┐
│ 研发部 │
└───────────┘
-- 使用 dictGet 函数查询复合key数据
select dictGet('test_ip_trie_dict','asn',tuple(IPv4StringToNum('82.118.230.0'))) as asn;
┌─asn─────┐
│ AS42831 │
└─────────┘
3.8.1 字典函数
其他数据字典函数使用方式与dictGet 基本相同，下面列出一些常用的数据字典函数：
| 功能 | 函数 |
|---|---|
| 获取整型数据的函数 | dictGetUInt8 , dictGetUInt16 , dictGetUInt32 , dictGetUInt64 dictGetInt8,dictGetInt16 , dictGetInt32 , dictGetInt64 |
| 获取浮点数据的函数 | dictGetFloat32 , dictGetFloat64 |
| 获取日期数据函数 | dictGetDate , dictGetDateTime |
| 获取字符串数据函数 | dictGetString , dictGetUUID |
-- 使用字典函数查询数据
select dictGetString('test_flat_dict','name',toUInt64(1)) as dept_name;
┌─dept_name─┐
│ 研发部 │
└───────────┘
select dictGet('test_flat_dict',('name','code'),toUInt64(2)) as dept_name;
┌─dept_name──────────┐
│ ('产品部','a0002') │
└────────────────────┘
3.8.2 字典表
除了字典函数还可以使用字典表查询外部字典的数据，字典表使用的表引擎是 DIctionary。
-- 创建字典表
create table t_test_flat_dict(
id UInt64,
code String,
name String
) ENGINE=Dictionary(test_flat_dict)
-- 查询数据
select * from t_test_flat_dict;
┌─id─┬─code──┬─name───┐
│ 1 │ a0001 │ 研发部 │
│ 2 │ a0002 │ 产品部 │
│ 3 │ a0003 │ 数据部 │
│ 4 │ a0004 │ 测试部 │
│ 5 │ a0005 │ 运维部 │
│ 6 │ a0006 │ 规划部 │
│ 7 │ a0007 │ 市场部 │
└────┴───────┴────────┘
3.8.2 使用 DDL 创建字典
-- 创建字典
create dictionary d_test_flat_dict(
id UInt64,
code String,
name String
)
primary key id
layout(FLAT())
source(file(path '/var/lib/clickhouse/user_files/organization.csv' format CSV))
lifetime(300);
-- 查询数据
select * from d_test_flat_dict;
┌─id─┬─code──┬─name───┐
│ 1 │ a0001 │ 研发部 │
│ 2 │ a0002 │ 产品部 │
│ 3 │ a0003 │ 数据部 │
│ 4 │ a0004 │ 测试部 │
│ 5 │ a0005 │ 运维部 │
│ 6 │ a0006 │ 规划部 │
│ 7 │ a0007 │ 市场部 │
└────┴───────┴────────┘
-- 删除字典
drop dictionary d_test_flat_dict;
