ClickHouse学习笔记四之数据字典

1.1 内置字典

ClickHouse 目前只有一种内置字典 —— Yandex.Metrica 字典。从名称上可以看出，这是用在 ClickHouse 自家产品上的字典，而它的设计意图是快速存取 geo 地理数据。但较为遗憾的是，由于版权原因 Yandex 并没有将 geo 地理数据开放出来。这意味着 ClickHouse 目前的内置字典，只是提供了字典的定义机制和取数函数，而没有内置任何现成的数据。所以内置字典的现状较为尴尬，需要遵照它的字典规范自行导入数据。

1.1.1 内置字典配置说明

内置字典在默认的情况下是禁用状态，需要开启后才能使用。开启它的方式也十分简单，只需将 config.xml 文件中 path_to_regions_hierarchy_file 和 path_to_regions_names_files 两项配置打开。

<path_to_regions_hierarchy_file>/opt/geo/regions_hierarchy.txt</path_to_regions_hierarchy_file>
<path_to_regions_names_files>/opt/geo/</path_to_regions_names_files>

这两项配置是惰性加载的，只有当字典首次被查询的时候才会触发加载动作。填充 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 格式定义，其数据模型的格式如表 1-1 所示。

表 1-1 regions_hierarchy 数据模型说明

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 格式定义，其数据模型的格式如表 1-2 所示。

表 1-2 regions_names 数据模型说明

1.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 并按照 1.1.1 节介绍的方法开启内置字典。至此，内置字典就已经全部设置好了，执行下面的语句就能够访问字典中的数据：

SELECT regionToName(toUInt32(20009))
┌─regionToName(toUInt32(20009))─┐
│  Buenos Aires Province         │
└────────────────────┘

可以看到，对于 Yandex.Metrica 字典数据的访问，这里用到了 regionToName 函数。类似这样的函数还有很多，在 ClickHouse 中它们被称为 Yandex.Metrica 函数。关于这套函数的更多用法，请参阅官方手册https://clickhouse.com/docs/sql-reference/statements/create/dictionary/embedded。

1.2 外部扩展字典

外部扩展字典是以插件形式注册到 ClickHouse 中的，由用户自行定义数据模式及数据来源。目前扩展字典支持 7 种类型的内存布局和 4 类数据来源。相比内容十分有限的内置字典，扩展字典才是更加常用的功能。

1.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 四个字段，数据格式如下所示：

1,2016-01-01,2017-01-10,100
2,2016-05-01,2017-07-01,200
3,2014-03-05,2018-01-20,300
4,2018-08-01,2019-10-01,400
5,2017-03-01,2017-06-01,500
6,2017-04-09,2018-05-30,600
7,2018-06-01,2019-01-25,700
8,2019-08-01,2019-12-12,800

最后一份是 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"

1.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：字典的数据结构。

• layout：字典的类型，它决定了数据在内存中以何种结构组织和存储。目前扩展字典共拥有 7 种类型。

• source：字典的数据源，它决定了字典中数据从何处加载。目前扩展字典共拥有文件、数据库和其他三类数据来源。

• lifetime：字典的更新时间，扩展字典支持数据在线更新。

1.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 为必填项。这些配置项的详细说明如表 1-3 所示。

表 1-3 attribute 的配置项说明

注意，假设有两个集合A和B，如果集合A中的每个元素x，在集合B中都有一个唯一与之对应的的元素y，那么集合A到B的映射关系就是单射映射。

1.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 而已。

接下来会结合已准备好的测试数据，逐一介绍 7 种字典的完整配置方法。通过这个过程，可以领略到不同类型字典的特点以及它们的使用方法。

1. flat

flat 字典是所有类型中性能最高的字典类型，它只能使用 UInt64 数值型 key。顾名思义，flat 字典的数据在内存中使用数组结构保存，数组的初始大小为 1024，上限为 500 000，这意味着它最多只能保存 500 000 行数据。如果在创建字典时数据量超出其上限，那么字典会创建失败。

代码清单 1-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 字典的数据在内存中通过散列结构保存，且没有存储上限的制约。

代码清单 1-2 hashed 类型字典的配置文件 test_hashed_dictionary.xml

<?xml version="1.0"?>
<dictionaries>
    <dictionary>
        <name>test_hashed_dict</name>
        <!-- 与 flat 字典配置相同, 省略 … -->
        <layout>
            <hashed/>
        </layout>
        <!-- 省略 … -->
    </dictionary>
</dictionaries>

同样将配置文件复制到 ClickHouse 服务节点的 /etc/clickhouse-server 目录后，即完成了对该字典的创建过程。

3. range_hashed

range_hashed 字典可以看作 hashed 字典的变种，它在原有功能的基础上增加了指定时间区间的特性，数据会以散列结构存储并按照时间排序。时间区间通过 range_min 和 range_max 元素指定，所指定的字段必须是 Date 或者 DateTime 类型。

代码清单 1-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% 或者更高的缓存命中率，则最好不要使用此类型。

代码清单 1-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 替换成了复合型。

代码清单 1-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>
        <!-- 省略 … -->
    </dictionary>
</dictionaries>

将配置文件复制到 ClickHouse 服务节点的 /etc/clickhouse-server 目录后，即完成了对该字典的创建过程。

6. complex_key_cache

complex_key_cache 字典同样与 cache 字典的特性完全相同，只是将单个数值型 key 替换成了复合型。

代码清单 1-6 complex_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>
        <!-- 省略 … -->
    </dictionary>
</dictionaries>

将配置文件复制到 ClickHouse 服务节点的 /etc/clickhouse-server 目录后，即完成了对该字典的创建过程。

7. ip_trie

虽然同为复合型 key 的字典，但 ip_trie 字典却较为特殊，因为它只能指定单个 String 类型的字段，用于指代 IP 前缀。ip_trie 字典的数据在内存中使用 trie 树结构保存，且专门用于 IP 前缀查询的场景，例如通过 IP 前缀查询对应的 ASN 信息。

代码清单 1-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 性能最不稳定。最后再总结一下这些字典各自的特点，如表 1-4 所示。

表 1-4 7 种类型字典的特点总结

1.2.5 扩展字典的数据源

数据源使用 source 元素定义，它指定了字典的数据从何而来。通过 5.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 命令替换成了 HTTP 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 三种数据库。接下来会分别介绍它们的创建方法。

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>
        <!-- 省略 … -->
    </dictionary>
</dictionaries>

其中，各配置项的含义分别如下：

• port：数据库端口。

• user：数据库用户名。

• password：数据库密码。

• replica：数据库 host 地址，支持 MySQL 集群。

• db：database 数据库。

• table：字典对应的数据表。

• where：查询 table 时的过滤条件，非必填项。

• invalidate_query：指定一条 SQL 语句，用于在数据更新时判断是否需要更新，非必填项。5.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', '市场部', NOW());

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>
        <!-- 省略 … -->
    </dictionary>
</dictionaries>

其中，各配置项的含义分别如下：

• host：数据库 host 地址。

• port：数据库端口。

• user：数据库用户名。

• password：数据库密码。

• db：database 数据库。

• table：字典对应的数据表。

• where：查询 table 时的过滤条件，非必填项。

• invalidate_query：指定一条 SQL 语句，用于在数据更新时判断是否需要更新，非必填项。

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>10.37.129.2</host>
                <port>27017</port>
                <user></user>
                <password></password>
                <db>test</db>
                <collection>t_organization</collection>
            </mongodb>
        </source>
        <!-- 省略 … -->
    </dictionary>
</dictionaries>

其中，各配置项的含义分别如下：

• host：数据库 host 地址。

• port：数据库端口。

• user：数据库用户名。

• password：数据库密码。

• db：database 数据库。

• collection：与字典对应的 collection 的名称。

3. 其他类型

除了上述已经介绍过的两类数据源之外，扩展字典还支持通过 ODBC 的方式连接 PostgreSQL 和 MS SQL Server 数据库作为数据源。它们的配置方式与数据库类型数据源大同小异，此处不再赘述，如有需要请参见官方手册。

1.2.6 扩展字典的数据更新策略

扩展字典支持数据的在线更新，更新后无须重启服务。字典数据的更新频率由配置文件中的 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 定义的时间频率被动更新之外，数据字典也能够主动触发更新。执行下面的语句后，将会触发所有数据字典的更新：

SYSTEM RELOAD DICTIONARIES;

也支持指定某个具体字典的更新：

SYSTEM RELOAD DICTIONARY dict_name;

1.2.7 扩展字典的基本操作

至此，我们已经在 ClickHouse 中创建了多种不同类型的扩展字典。接下来将目光聚焦到字典的基本操作上，包括对字典元数据和数据的查询，以及借助字典表引擎访问数据。

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 的形式。