词典是一种映射（key -> attributes），方便各种类型的参考列表。
ClickHouse 支持用于处理词典的特殊函数，这些函数可以在查询中使用。使用函数处理词典比与参考表进行 JOIN 更容易、更高效。
ClickHouse 支持
教程
如果您刚开始在 ClickHouse 中使用词典，我们有一个涵盖该主题的教程。请查看 这里 。
您可以从各种数据源添加自己的词典。词典的来源可以是 ClickHouse 表、本地文本或可执行文件、HTTP(s) 资源，或者另一个 DBMS。有关更多信息，请参阅 "词典来源 "。
ClickHouse
完全或部分地将词典存储在 RAM 中。
定期更新词典并动态加载缺失值。换句话说，词典可以动态加载。
允许使用 xml 文件或 DDL 查询 创建词典。
词典的配置可以位于一个或多个 xml 文件中。配置路径在 dictionaries_config 参数中指定。
词典可以在服务器启动时或首次使用时加载，具体取决于 dictionaries_lazy_load 设置。
dictionaries 系统表包含有关服务器上配置的词典的信息。对于每个词典，您可以在其中找到
词典的状态。
配置参数。
指标，例如分配给词典的 RAM 量或自词典成功加载以来的查询次数。
提示
如果您在 ClickHouse Cloud 中使用词典，请使用 DDL 查询选项创建词典，并将词典创建为用户 default。此外，请在 云兼容性指南 中验证受支持的词典来源列表。
使用 DDL 查询创建词典
词典可以使用 DDL 查询 创建，这是推荐的方法，因为使用 DDL 创建的词典
不会向服务器配置文件添加额外的记录。
可以将词典作为第一类实体来处理，就像表或视图一样。
可以使用熟悉的 SELECT 直接读取数据，而不是词典表函数。请注意，通过 SELECT 语句直接访问词典时，缓存的词典将仅返回缓存的数据，而非缓存的词典将返回其存储的所有数据。
可以轻松地重命名词典。
使用配置文件创建词典
注意
使用配置文件创建词典不适用于 ClickHouse Cloud。请使用 DDL（见上文），并将词典创建为用户 default。
词典配置文件具有以下格式
<clickhouse>
<comment>An optional element with any content. Ignored by the ClickHouse server.</comment>
<!--Optional element. File name with substitutions-->
<include_from>/etc/metrika.xml</include_from>
<dictionary>
<!-- Dictionary configuration. -->
<!-- There can be any number of dictionary sections in a configuration file. -->
</dictionary>
</clickhouse>
您可以在同一个文件中 配置 任意数量的词典。
注意
您可以通过在 SELECT 查询中描述它来转换小型词典的值（请参阅 transform 函数）。此功能与词典无关。
配置词典
提示
如果您在 ClickHouse Cloud 中使用词典，请使用 DDL 查询选项创建词典，并将词典创建为用户 default。此外，请在 云兼容性指南 中验证受支持的词典来源列表。
如果词典使用 xml 文件配置，则词典配置具有以下结构
<dictionary>
<name>dict_name</name>
<structure>
<!-- Complex key configuration -->
</structure>
<source>
<!-- Source configuration -->
</source>
<layout>
<!-- Memory layout configuration -->
</layout>
<lifetime>
<!-- Lifetime of dictionary in memory -->
</lifetime>
</dictionary>
相应的 DDL-query 具有以下结构
CREATE DICTIONARY dict_name
(
... -- attributes
)
PRIMARY KEY ... -- complex or single key configuration
SOURCE(...) -- Source configuration
LAYOUT(...) -- Memory layout configuration
LIFETIME(...) -- Lifetime of dictionary in memory
将词典存储在内存中
有多种方法可以将词典存储在内存中。
我们推荐 flat 、hashed 和 complex_key_hashed ，它们提供最佳的处理速度。
不建议使用缓存，因为性能可能较差且难以选择最佳参数。请在 cache 部分中了解更多信息。
有几种方法可以提高词典性能
在 GROUP BY 之后调用处理词典的函数。
将要提取的属性标记为注入性。如果不同的键对应于不同的属性值，则该属性称为注入性。因此，当 GROUP BY 使用按键获取属性值的函数时，该函数会自动从 GROUP BY 中取出。
ClickHouse 会为词典错误生成异常。示例
无法加载正在访问的词典。
查询 cached 词典时出错。
您可以在 system.dictionaries 表中查看词典列表及其状态。对于每个词典，您可以在其中找到
提示
如果您在 ClickHouse Cloud 中使用词典，请使用 DDL 查询选项创建词典，并将词典创建为用户 default。此外，请在 云兼容性指南 中验证受支持的词典来源列表。
配置如下
<clickhouse>
<dictionary>
...
<layout>
<layout_type>
<!-- layout settings -->
</layout_type>
</layout>
...
</dictionary>
</clickhouse>
相应的 DDL-query
CREATE DICTIONARY (...)
...
LAYOUT(LAYOUT_TYPE(param value)) -- layout settings
...
词典中没有带有 complex-key* 字样的布局具有 UInt64 类型的键，complex-key* 词典具有复合键（复杂，具有任意类型）。
XML 词典中的 UInt64 键使用 <id> 标签定义。
配置示例（列 key_column 具有 UInt64 类型）
...
<structure>
<id>
<name>key_column</name>
</id>
...
复合 complex 键 XML 词典使用 <key> 标签定义。
复合键的配置示例（键具有一个 String 类型的元素）
...
<structure>
<key>
<attribute>
<name>country_code</name>
<type>String</type>
</attribute>
</key>
...
将词典存储在内存中的方法
将词典数据存储在内存中的各种方法与 CPU 和 RAM 使用量之间的权衡有关。在 选择布局 段落的词典相关 博客文章 中发布的决策树是决定使用哪种布局的一个很好的起点。
flat
词典完全以扁平数组的形式存储在内存中。词典使用多少内存？数量与最大键的大小成比例（使用的空间）。
词典键具有 UInt64 类型，并且值限制为 max_array_size（默认值为 500,000）。如果在创建词典时发现更大的键，ClickHouse 会抛出异常并且不会创建词典。词典扁平数组的初始大小由 initial_array_size 设置控制（默认值为 1024）。
支持所有类型的来源。在更新时，数据（来自文件或表）会全部读取。
此方法提供了所有可用存储方法的最佳性能。
配置示例
<layout>
<flat>
<initial_array_size>50000</initial_array_size>
<max_array_size>5000000</max_array_size>
</flat>
</layout>
或者
LAYOUT(FLAT(INITIAL_ARRAY_SIZE 50000 MAX_ARRAY_SIZE 5000000))
hashed
词典完全以哈希表的形式存储在内存中。词典可以包含具有任何标识符的任意数量的元素。实际上，键的数量可以达到数千万个。
词典键具有 UInt64 类型。
支持所有类型的来源。在更新时，数据（来自文件或表）会全部读取。
配置示例
<layout>
<hashed />
</layout>
或者
配置示例
<layout>
<hashed>
<!-- If shards greater then 1 (default is `1`) the dictionary will load
data in parallel, useful if you have huge amount of elements in one
dictionary. -->
<shards>10</shards>
<!-- Size of the backlog for blocks in parallel queue.
Since the bottleneck in parallel loading is rehash, and so to avoid
stalling because of thread is doing rehash, you need to have some
backlog.
10000 is good balance between memory and speed.
Even for 10e10 elements and can handle all the load without starvation. -->
<shard_load_queue_backlog>10000</shard_load_queue_backlog>
<!-- Maximum load factor of the hash table, with greater values, the memory
is utilized more efficiently (less memory is wasted) but read/performance
may deteriorate.
Valid values: [0.5, 0.99]
Default: 0.5 -->
<max_load_factor>0.5</max_load_factor>
</hashed>
</layout>
或者
LAYOUT(HASHED([SHARDS 1] [SHARD_LOAD_QUEUE_BACKLOG 10000] [MAX_LOAD_FACTOR 0.5]))
sparse_hashed
类似于 hashed，但使用更少的内存以牺牲更多的 CPU 使用量为代价。
词典键具有 UInt64 类型。
配置示例
<layout>
<sparse_hashed>
<!-- <shards>1</shards> -->
<!-- <shard_load_queue_backlog>10000</shard_load_queue_backlog> -->
<!-- <max_load_factor>0.5</max_load_factor> -->
</sparse_hashed>
</layout>
或者
LAYOUT(SPARSE_HASHED([SHARDS 1] [SHARD_LOAD_QUEUE_BACKLOG 10000] [MAX_LOAD_FACTOR 0.5]))
也可以对这种类型的词典使用 shards，并且对于 sparse_hashed 比 hashed 更重要，因为 sparse_hashed 速度较慢。
complex_key_hashed
这种存储类型用于复合 键 。类似于 hashed。
配置示例
<layout>
<complex_key_hashed>
<!-- <shards>1</shards> -->
<!-- <shard_load_queue_backlog>10000</shard_load_queue_backlog> -->
<!-- <max_load_factor>0.5</max_load_factor> -->
</complex_key_hashed>
</layout>
或者
LAYOUT(COMPLEX_KEY_HASHED([SHARDS 1] [SHARD_LOAD_QUEUE_BACKLOG 10000] [MAX_LOAD_FACTOR 0.5]))
complex_key_sparse_hashed
这种存储类型用于复合 键 。类似于 sparse_hashed 。
配置示例
<layout>
<complex_key_sparse_hashed>
<!-- <shards>1</shards> -->
<!-- <shard_load_queue_backlog>10000</shard_load_queue_backlog> -->
<!-- <max_load_factor>0.5</max_load_factor> -->
</complex_key_sparse_hashed>
</layout>
或者
LAYOUT(COMPLEX_KEY_SPARSE_HASHED([SHARDS 1] [SHARD_LOAD_QUEUE_BACKLOG 10000] [MAX_LOAD_FACTOR 0.5]))
hashed_array
词典完全存储在内存中。每个属性都存储在一个数组中。键属性以哈希表的形式存储，其中值是属性数组中的索引。词典可以包含具有任何标识符的任意数量的元素。实际上，键的数量可以达到数千万个。
词典键具有 UInt64 类型。
支持所有类型的来源。在更新时，数据（来自文件或表）会全部读取。
配置示例
<layout>
<hashed_array>
</hashed_array>
</layout>
或者
LAYOUT(HASHED_ARRAY([SHARDS 1]))
complex_key_hashed_array
这种存储类型用于复合 键 。类似于 hashed_array 。
配置示例
<layout>
<complex_key_hashed_array />
</layout>
或者
LAYOUT(COMPLEX_KEY_HASHED_ARRAY([SHARDS 1]))
range_hashed
词典以哈希表的形式存储在内存中，其中包含有序的范围数组及其相应的值。
此存储方法的工作方式与哈希方式相同，并允许使用日期/时间（任意数字类型）范围，除了键之外。
示例：表包含以下格式的每个广告主的折扣
┌─advertiser_id─┬─discount_start_date─┬─discount_end_date─┬─amount─┐
│ 123 │ 2015-01-16 │ 2015-01-31 │ 0.25 │
│ 123 │ 2015-01-01 │ 2015-01-15 │ 0.15 │
│ 456 │ 2015-01-01 │ 2015-01-15 │ 0.05 │
└───────────────┴─────────────────────┴───────────────────┴────────┘
要使用日期范围样本，请在 结构 中定义 range_min 和 range_max 元素。这些元素必须包含 name 和 type（如果未指定 type，将使用默认类型 - Date）。type 可以是任何数字类型（Date / DateTime / UInt64 / Int32 / 其他）。
注意
range_min 和 range_max 的值应适合 Int64 类型。
示例
<layout>
<range_hashed>
<!-- Strategy for overlapping ranges (min/max). Default: min (return a matching range with the min(range_min -> range_max) value) -->
<range_lookup_strategy>min</range_lookup_strategy>
</range_hashed>
</layout>
<structure>
<id>
<name>advertiser_id</name>
</id>
<range_min>
<name>discount_start_date</name>
<type>Date</type>
</range_min>
<range_max>
<name>discount_end_date</name>
<type>Date</type>
</range_max>
...
或者
CREATE DICTIONARY discounts_dict (
advertiser_id UInt64,
discount_start_date Date,
discount_end_date Date,
amount Float64
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(TABLE 'discounts'))
LIFETIME(MIN 1 MAX 1000)
LAYOUT(RANGE_HASHED(range_lookup_strategy 'max'))
RANGE(MIN discount_start_date MAX discount_end_date)
要使用这些词典，需要将一个额外的参数传递给 dictGet 函数，用于选择范围
dictGet('dict_name', 'attr_name', id, date)
查询示例
SELECT dictGet('discounts_dict', 'amount', 1, '2022-10-20'::Date);
此函数返回指定 id 和包含传递日期的日期范围的值。
算法的详细信息
如果未找到 id 或未找到该 id 的范围，则返回属性类型的默认值。
如果有重叠的范围且 range_lookup_strategy=min，则返回具有最小 range_min 的匹配范围。如果找到多个范围，则返回具有最小 range_max 的范围。如果再次找到多个范围（多个范围具有相同的 range_min 和 range_max），则返回其中的一个随机范围。
如果有重叠的范围且 range_lookup_strategy=max，则返回具有最大 range_min 的匹配范围。如果找到多个范围，则返回具有最大 range_max 的范围。如果再次找到多个范围（多个范围具有相同的 range_min 和 range_max），则返回其中的一个随机范围。
如果 range_max 为 NULL，则该范围是开放的。NULL 被视为最大的可能值。对于 range_min，可以使用 1970-01-01 或 0 (-MAX_INT) 作为开放值。
配置示例
<clickhouse>
<dictionary>
...
<layout>
<range_hashed />
</layout>
<structure>
<id>
<name>Abcdef</name>
</id>
<range_min>
<name>StartTimeStamp</name>
<type>UInt64</type>
</range_min>
<range_max>
<name>EndTimeStamp</name>
<type>UInt64</type>
</range_max>
<attribute>
<name>XXXType</name>
<type>String</type>
<null_value />
</attribute>
</structure>
</dictionary>
</clickhouse>
或者
CREATE DICTIONARY somedict(
Abcdef UInt64,
StartTimeStamp UInt64,
EndTimeStamp UInt64,
XXXType String DEFAULT ''
)
PRIMARY KEY Abcdef
RANGE(MIN StartTimeStamp MAX EndTimeStamp)
具有重叠范围和开放范围的配置示例
CREATE TABLE discounts
(
advertiser_id UInt64,
discount_start_date Date,
discount_end_date Nullable(Date),
amount Float64
)
ENGINE = Memory;
INSERT INTO discounts VALUES (1, '2015-01-01', Null, 0.1);
INSERT INTO discounts VALUES (1, '2015-01-15', Null, 0.2);
INSERT INTO discounts VALUES (2, '2015-01-01', '2015-01-15', 0.3);
INSERT INTO discounts VALUES (2, '2015-01-04', '2015-01-10', 0.4);
INSERT INTO discounts VALUES (3, '1970-01-01', '2015-01-15', 0.5);
INSERT INTO discounts VALUES (3, '1970-01-01', '2015-01-10', 0.6);
SELECT * FROM discounts ORDER BY advertiser_id, discount_start_date;
┌─advertiser_id─┬─discount_start_date─┬─discount_end_date─┬─amount─┐
│ 1 │ 2015-01-01 │ ᴺᵁᴸᴸ │ 0.1 │
│ 1 │ 2015-01-15 │ ᴺᵁᴸᴸ │ 0.2 │
│ 2 │ 2015-01-01 │ 2015-01-15 │ 0.3 │
│ 2 │ 2015-01-04 │ 2015-01-10 │ 0.4 │
│ 3 │ 1970-01-01 │ 2015-01-15 │ 0.5 │
│ 3 │ 1970-01-01 │ 2015-01-10 │ 0.6 │
└───────────────┴─────────────────────┴───────────────────┴────────┘
-- RANGE_LOOKUP_STRATEGY 'max'
CREATE DICTIONARY discounts_dict
(
advertiser_id UInt64,
discount_start_date Date,
discount_end_date Nullable(Date),
amount Float64
)
PRIMARY KEY advertiser_id
SOURCE(CLICKHOUSE(TABLE discounts))
LIFETIME(MIN 600 MAX 900)
LAYOUT(RANGE_HASHED(RANGE_LOOKUP_STRATEGY 'max'))
RANGE(MIN discount_start_date MAX discount_end_date);
select dictGet('discounts_dict', 'amount', 1, toDate('2015-01-14')) res;
┌─res─┐
│ 0.1 │ -- the only one range is matching: 2015-01-01 - Null
└─────┘
select dictGet('discounts_dict', 'amount', 1, toDate('2015-01-16')) res;
┌─res─┐
│ 0.2 │ -- two ranges are matching, range_min 2015-01-15 (0.2) is bigger than 2015-01-01 (0.1)
└─────┘
select dictGet('discounts_dict', 'amount', 2, toDate('2015-01-06')) res;
┌─res─┐
│ 0.4 │ -- two ranges are matching, range_min 2015-01-04 (0.4) is bigger than 2015-01-01 (0.3)
└─────┘
select dictGet('discounts_dict', 'amount', 3, toDate('2015-01-01')) res;
┌─res─┐
│ 0.5 │ -- two ranges are matching, range_min are equal, 2015-01-15 (0.5) is bigger than 2015-01-10 (0.6)
└─────┘
DROP DICTIONARY discounts_dict;
-- RANGE_LOOKUP_STRATEGY 'min'
CREATE DICTIONARY discounts_dict
(
advertiser_id UInt64,
discount_start_date Date,
discount_end_date Nullable(Date),
amount Float64
)
PRIMARY KEY advertiser_id
SOURCE(CLICKHOUSE(TABLE discounts))
LIFETIME(MIN 600 MAX 900)
LAYOUT(RANGE_HASHED(RANGE_LOOKUP_STRATEGY 'min'))
RANGE(MIN discount_start_date MAX discount_end_date);
select dictGet('discounts_dict', 'amount', 1, toDate('2015-01-14')) res;
┌─res─┐
│ 0.1 │ -- the only one range is matching: 2015-01-01 - Null
└─────┘
select dictGet('discounts_dict', 'amount', 1, toDate('2015-01-16')) res;
┌─res─┐
│ 0.1 │ -- two ranges are matching, range_min 2015-01-01 (0.1) is less than 2015-01-15 (0.2)
└─────┘
select dictGet('discounts_dict', 'amount', 2, toDate('2015-01-06')) res;
┌─res─┐
│ 0.3 │ -- two ranges are matching, range_min 2015-01-01 (0.3) is less than 2015-01-04 (0.4)
└─────┘
select dictGet('discounts_dict', 'amount', 3, toDate('2015-01-01')) res;
┌─res─┐
│ 0.6 │ -- two ranges are matching, range_min are equal, 2015-01-10 (0.6) is less than 2015-01-15 (0.5)
└─────┘
complex_key_range_hashed
字典以哈希表的形式存储在内存中，其中包含有序的范围数组及其对应的值（参见 range_hashed ）。这种存储类型用于复合 键 。
配置示例
CREATE DICTIONARY range_dictionary
(
CountryID UInt64,
CountryKey String,
StartDate Date,
EndDate Date,
Tax Float64 DEFAULT 0.2
)
PRIMARY KEY CountryID, CountryKey
SOURCE(CLICKHOUSE(TABLE 'date_table'))
LIFETIME(MIN 1 MAX 1000)
LAYOUT(COMPLEX_KEY_RANGE_HASHED())
RANGE(MIN StartDate MAX EndDate);
cache
字典存储在具有固定数量单元格的缓存中。这些单元格包含频繁使用的元素。
词典键具有 UInt64 类型。
在搜索字典时，首先搜索缓存。对于每个数据块，所有未在缓存中找到或已过时的键都使用 SELECT attrs... FROM db.table WHERE id IN (k1, k2, ...) 从源请求。然后，接收到的数据被写入缓存。
如果未在字典中找到键，则会创建更新缓存任务并将其添加到更新队列中。可以使用设置 max_update_queue_size、update_queue_push_timeout_milliseconds、query_wait_timeout_milliseconds、max_threads_for_updates 来控制更新队列属性。
对于缓存字典，可以设置缓存中数据的过期 生命周期 。如果自加载单元格中的数据以来已经超过 lifetime，则该单元格的值将不被使用，并且键变为过期。下次需要使用该键时，会重新请求该键。可以使用设置 allow_read_expired_keys 配置此行为。
这是存储字典的所有方式中最不有效的一种。缓存的速度很大程度上取决于正确的设置和使用场景。只有当命中率足够高时（建议 99% 及以上），缓存类型的字典才能表现良好。可以在 system.dictionaries 表中查看平均命中率。
如果将设置 allow_read_expired_keys 设置为 1，默认值为 0。那么字典可以支持异步更新。如果客户端请求键，并且所有键都在缓存中，但其中一些已过期，则字典将向客户端返回已过期的键，并异步从源请求它们。
为了提高缓存性能，请使用带有 LIMIT 的子查询，并在外部使用字典调用该函数。
支持所有类型的源。
设置示例
<layout>
<cache>
<!-- The size of the cache, in number of cells. Rounded up to a power of two. -->
<size_in_cells>1000000000</size_in_cells>
<!-- Allows to read expired keys. -->
<allow_read_expired_keys>0</allow_read_expired_keys>
<!-- Max size of update queue. -->
<max_update_queue_size>100000</max_update_queue_size>
<!-- Max timeout in milliseconds for push update task into queue. -->
<update_queue_push_timeout_milliseconds>10</update_queue_push_timeout_milliseconds>
<!-- Max wait timeout in milliseconds for update task to complete. -->
<query_wait_timeout_milliseconds>60000</query_wait_timeout_milliseconds>
<!-- Max threads for cache dictionary update. -->
<max_threads_for_updates>4</max_threads_for_updates>
</cache>
</layout>
或者
LAYOUT(CACHE(SIZE_IN_CELLS 1000000000))
设置足够大的缓存大小。您需要进行实验以选择单元格的数量
设置一些值。
运行查询，直到缓存完全填满。
使用 system.dictionaries 表评估内存消耗。
增加或减少单元格的数量，直到达到所需的内存消耗。
注意
不要使用 ClickHouse 作为源，因为它处理具有随机读取的查询速度很慢。
complex_key_cache
这种存储类型用于复合 键 。类似于 cache。
ssd_cache
类似于 cache，但将数据存储在 SSD 上，索引存储在 RAM 中。所有与更新队列相关的缓存字典设置也适用于 SSD 缓存字典。
词典键具有 UInt64 类型。
<layout>
<ssd_cache>
<!-- Size of elementary read block in bytes. Recommended to be equal to SSD's page size. -->
<block_size>4096</block_size>
<!-- Max cache file size in bytes. -->
<file_size>16777216</file_size>
<!-- Size of RAM buffer in bytes for reading elements from SSD. -->
<read_buffer_size>131072</read_buffer_size>
<!-- Size of RAM buffer in bytes for aggregating elements before flushing to SSD. -->
<write_buffer_size>1048576</write_buffer_size>
<!-- Path where cache file will be stored. -->
<path>/var/lib/clickhouse/user_files/test_dict</path>
</ssd_cache>
</layout>
或者
LAYOUT(SSD_CACHE(BLOCK_SIZE 4096 FILE_SIZE 16777216 READ_BUFFER_SIZE 1048576
PATH '/var/lib/clickhouse/user_files/test_dict'))
complex_key_ssd_cache
这种存储类型用于复合 键 。类似于 ssd_cache。
direct
字典不存储在内存中，而直接在处理请求期间转到源。
词典键具有 UInt64 类型。
支持所有类型的 源 ，除了本地文件。
配置示例
<layout>
<direct />
</layout>
或者
complex_key_direct
这种存储类型用于复合 键 。类似于 direct。
ip_trie
此字典专为按网络前缀查找 IP 地址而设计。它以 CIDR 表示法存储 IP 范围，并允许快速确定给定 IP 属于哪个前缀（例如子网或 ASN 范围），使其非常适合基于 IP 的搜索，例如地理位置或网络分类。
VIDEO
示例
假设我们在 ClickHouse 中有一个表，其中包含我们的 IP 前缀和映射
CREATE TABLE my_ip_addresses (
prefix String,
asn UInt32,
cca2 String
)
ENGINE = MergeTree
PRIMARY KEY prefix;
INSERT INTO my_ip_addresses VALUES
('202.79.32.0/20', 17501, 'NP'),
('2620:0:870::/48', 3856, 'US'),
('2a02:6b8:1::/48', 13238, 'RU'),
('2001:db8::/32', 65536, 'ZZ')
;
让我们为该表定义一个 ip_trie 字典。ip_trie 布局需要一个复合键
<structure>
<key>
<attribute>
<name>prefix</name>
<type>String</type>
</attribute>
</key>
<attribute>
<name>asn</name>
<type>UInt32</type>
<null_value />
</attribute>
<attribute>
<name>cca2</name>
<type>String</type>
<null_value>??</null_value>
</attribute>
...
</structure>
<layout>
<ip_trie>
<!-- Key attribute `prefix` can be retrieved via dictGetString. -->
<!-- This option increases memory usage. -->
<access_to_key_from_attributes>true</access_to_key_from_attributes>
</ip_trie>
</layout>
或者
CREATE DICTIONARY my_ip_trie_dictionary (
prefix String,
asn UInt32,
cca2 String DEFAULT '??'
)
PRIMARY KEY prefix
SOURCE(CLICKHOUSE(TABLE 'my_ip_addresses'))
LAYOUT(IP_TRIE)
LIFETIME(3600);
该键必须只有一个 String 类型的属性，其中包含允许的 IP 前缀。目前不支持其他类型。
语法是
dictGetT('dict_name', 'attr_name', ip)
该函数接受 UInt32 用于 IPv4，或 FixedString(16) 用于 IPv6。例如
SELECT dictGet('my_ip_trie_dictionary', 'cca2', toIPv4('202.79.32.10')) AS result;
┌─result─┐
│ NP │
└────────┘
SELECT dictGet('my_ip_trie_dictionary', 'asn', IPv6StringToNum('2001:db8::1')) AS result;
┌─result─┐
│ 65536 │
└────────┘
SELECT dictGet('my_ip_trie_dictionary', ('asn', 'cca2'), IPv6StringToNum('2001:db8::1')) AS result;
┌─result───────┐
│ (65536,'ZZ') │
└──────────────┘
目前不支持其他类型。该函数返回与此 IP 地址对应的前缀的属性。如果有重叠的前缀，则返回最具体的前缀。
数据必须完全适合 RAM。
使用 LIFETIME 刷新字典数据
ClickHouse 定期根据 LIFETIME 标签（以秒为单位定义）更新字典。LIFETIME 是完全下载的字典的更新间隔，以及缓存字典的失效间隔。
在更新期间，仍然可以查询字典的旧版本。字典更新（不包括首次使用加载字典时）不会阻止查询。如果在更新过程中发生错误，则错误会写入服务器日志，并且查询可以使用字典的旧版本继续进行。如果字典更新成功，则旧版本的字典将被原子地替换。
设置示例
提示
如果您在 ClickHouse Cloud 中使用词典，请使用 DDL 查询选项创建词典，并将词典创建为用户 default。此外，请在 云兼容性指南 中验证受支持的词典来源列表。
<dictionary>
...
<lifetime>300</lifetime>
...
</dictionary>
或者
CREATE DICTIONARY (...)
...
LIFETIME(300)
...
设置 <lifetime>0</lifetime>（LIFETIME(0)）可防止字典更新。
您可以设置更新的时间间隔，ClickHouse 将在此范围内选择一个均匀随机的时间。这对于在大量服务器上更新时分发对字典源的负载是必要的。
设置示例
<dictionary>
...
<lifetime>
<min>300</min>
<max>360</max>
</lifetime>
...
</dictionary>
或者
LIFETIME(MIN 300 MAX 360)
如果 <min>0</min> 和 <max>0</max>，ClickHouse 不会按超时重新加载字典。在这种情况下，如果字典配置文件已更改或执行了 SYSTEM RELOAD DICTIONARY 命令，ClickHouse 可以提前重新加载字典。
在更新字典时，ClickHouse 服务器会根据 源 的类型应用不同的逻辑
对于文本文件，它会检查修改时间。如果时间与之前记录的时间不同，则更新字典。
来自其他来源的字典默认每次都会更新。
对于其他来源（ODBC、PostgreSQL、ClickHouse 等），您可以设置一个查询，该查询仅在它们真正更改时才更新字典，而不是每次都更新。为此，请执行以下步骤
字典表必须具有一个字段，该字段在源数据更新时始终会更改。
源的设置必须指定一个检索更改字段的查询。ClickHouse 服务器将查询结果解释为一行，如果该行相对于其先前状态已更改，则更新字典。在 源 的设置中，使用 <invalidate_query> 字段指定查询。
设置示例
<dictionary>
...
<odbc>
...
<invalidate_query>SELECT update_time FROM dictionary_source where id = 1</invalidate_query>
</odbc>
...
</dictionary>
或者
...
SOURCE(ODBC(... invalidate_query 'SELECT update_time FROM dictionary_source where id = 1'))
...
对于 Cache、ComplexKeyCache、SSDCache 和 SSDComplexKeyCache 字典，支持同步和异步更新。
对于 Flat、Hashed、HashedArray、ComplexKeyHashed 字典，也可以仅请求自上次更新后已更改的数据。如果将 update_field 作为字典源配置的一部分指定，则先前更新时间的秒数将添加到数据请求中。根据源类型（Executable、HTTP、MySQL、PostgreSQL 或 ODBC），在从外部源请求数据之前，将对 update_field 应用不同的逻辑。
如果源是 HTTP，则 update_field 将作为带有上次更新时间作为参数值的查询参数添加。
如果源是 Executable，则 update_field 将作为带有上次更新时间作为参数值的可执行脚本参数添加。
如果源是 ClickHouse、MySQL、PostgreSQL、ODBC，则会在 WHERE 中添加一个额外的部分，其中 update_field 与上次更新时间进行比较，以确定是否大于或等于。
默认情况下，此 WHERE 条件在 SQL 查询的最高级别进行检查。或者，可以使用 {condition} 关键字在查询内的任何其他 WHERE 子句中检查该条件。示例
...
SOURCE(CLICKHOUSE(...
update_field 'added_time'
QUERY '
SELECT my_arr.1 AS x, my_arr.2 AS y, creation_time
FROM (
SELECT arrayZip(x_arr, y_arr) AS my_arr, creation_time
FROM dictionary_source
WHERE {condition}
)'
))
...
如果设置了 update_field 选项，则可以设置额外的选项 update_lag。从请求更新的数据之前，从先前的更新时间中减去 update_lag 选项的值。
设置示例
<dictionary>
...
<clickhouse>
...
<update_field>added_time</update_field>
<update_lag>15</update_lag>
</clickhouse>
...
</dictionary>
或者
...
SOURCE(CLICKHOUSE(... update_field 'added_time' update_lag 15))
...
字典来源
提示
如果您在 ClickHouse Cloud 中使用词典，请使用 DDL 查询选项创建词典，并将词典创建为用户 default。此外，请在 云兼容性指南 中验证受支持的词典来源列表。
字典可以从许多不同的来源连接到 ClickHouse。
如果字典使用 xml 文件配置，则配置如下所示
<clickhouse>
<dictionary>
...
<source>
<source_type>
<!-- Source configuration -->
</source_type>
</source>
...
</dictionary>
...
</clickhouse>
在 DDL 查询 的情况下，上述配置将如下所示
CREATE DICTIONARY dict_name (...)
...
SOURCE(SOURCE_TYPE(param1 val1 ... paramN valN)) -- Source configuration
...
源在 source 部分中配置。
对于源类型 本地文件 、可执行文件 、HTTP(S) 、ClickHouse ，可用的可选设置
<source>
<file>
<path>/opt/dictionaries/os.tsv</path>
<format>TabSeparated</format>
</file>
<settings>
<format_csv_allow_single_quotes>0</format_csv_allow_single_quotes>
</settings>
</source>
或者
SOURCE(FILE(path './user_files/os.tsv' format 'TabSeparated'))
SETTINGS(format_csv_allow_single_quotes = 0)
源类型 (source_type)
本地文件
设置示例
<source>
<file>
<path>/opt/dictionaries/os.tsv</path>
<format>TabSeparated</format>
</file>
</source>
或者
SOURCE(FILE(path './user_files/os.tsv' format 'TabSeparated'))
设置字段
path – 文件的绝对路径。format – 文件格式。支持 格式 中描述的所有格式。
当通过 DDL 命令 (CREATE DICTIONARY ...) 创建源为 FILE 的字典时，源文件需要位于 user_files 目录中，以防止数据库用户访问 ClickHouse 节点上的任意文件。
参见
可执行文件
使用可执行文件取决于 字典在内存中存储方式 。如果字典使用 cache 和 complex_key_cache 存储，ClickHouse 通过向可执行文件的 STDIN 发送请求来请求必要的键。否则，ClickHouse 会启动可执行文件，并将它的输出视为字典数据。
设置示例
<source>
<executable>
<command>cat /opt/dictionaries/os.tsv</command>
<format>TabSeparated</format>
<implicit_key>false</implicit_key>
</executable>
</source>
设置字段
command — 可执行文件的绝对路径，或者如果命令的目录在 PATH 中，则为文件名。format — 文件格式。支持 格式 中描述的所有格式。command_termination_timeout — 可执行脚本应包含一个主读写循环。在销毁字典后，管道关闭，可执行文件将有 command_termination_timeout 秒时间来关闭，然后 ClickHouse 将向子进程发送 SIGTERM 信号。command_termination_timeout 以秒为单位指定。默认值为 10。可选参数。command_read_timeout - 从命令 stdout 读取数据的超时时间，单位为毫秒。默认值为 10000。可选参数。command_write_timeout - 向命令 stdin 写入数据的超时时间，单位为毫秒。默认值为 10000。可选参数。implicit_key — 可执行源文件只能返回值，并且与请求键的对应关系由结果中行的顺序隐式确定。默认值为 false。execute_direct - 如果 execute_direct = 1，则 command 将在由 user_scripts_path 指定的 user_scripts 文件夹中搜索。可以使用空格分隔符指定额外的脚本参数。例如：script_name arg1 arg2。如果 execute_direct = 0，则 command 作为参数传递给 bin/sh -c。默认值为 0。可选参数。send_chunk_header - 控制在向进程发送数据块之前是否发送行数。可选。默认值为 false。
该字典源只能通过 XML 配置进行配置。禁用通过 DDL 创建具有可执行源的字典；否则，数据库用户将能够执行 ClickHouse 节点上的任意二进制文件。
可执行池
可执行池允许从进程池加载数据。此源不适用于需要从源加载所有数据的字典布局。如果字典 存储 使用 cache、complex_key_cache、ssd_cache、complex_key_ssd_cache、direct 或 complex_key_direct 布局，则可执行池有效。
可执行池将生成一个具有指定命令的进程池并保持运行，直到它们退出。程序应从 STDIN 读取数据，只要可用就输出结果到 STDOUT。它可以等待 STDIN 上的下一个数据块。ClickHouse 不会在处理完一个数据块后关闭 STDIN，而会在需要时管道另一个数据块。可执行脚本应准备好以这种方式处理数据——它应轮询 STDIN 并尽早将数据刷新到 STDOUT。
设置示例
<source>
<executable_pool>
<command><command>while read key; do printf "$key\tData for key $key\n"; done</command</command>
<format>TabSeparated</format>
<pool_size>10</pool_size>
<max_command_execution_time>10<max_command_execution_time>
<implicit_key>false</implicit_key>
</executable_pool>
</source>
设置字段
command — 可执行文件的绝对路径，或者如果程序目录写入 PATH，则为文件名。format — 文件格式。支持 "格式 " 中描述的所有格式。pool_size — 池的大小。如果将 0 指定为 pool_size，则没有池大小限制。默认值为 16。command_termination_timeout — 可执行脚本应包含主读写循环。在销毁字典后，管道关闭，可执行文件将有 command_termination_timeout 秒时间来关闭，然后 ClickHouse 将向子进程发送 SIGTERM 信号。以秒为单位指定。默认值为 10。可选参数。max_command_execution_time — 可执行脚本命令处理数据块的最大执行时间。以秒为单位指定。默认值为 10。可选参数。command_read_timeout - 从命令 stdout 读取数据的超时时间，单位为毫秒。默认值为 10000。可选参数。command_write_timeout - 向命令 stdin 写入数据的超时时间，单位为毫秒。默认值为 10000。可选参数。implicit_key — 可执行源文件只能返回值，并且与请求键的对应关系由结果中行的顺序隐式确定。默认值为 false。可选参数。execute_direct - 如果 execute_direct = 1，则 command 将在由 user_scripts_path 指定的 user_scripts 文件夹中搜索。可以使用空格分隔符指定额外的脚本参数。例如：script_name arg1 arg2。如果 execute_direct = 0，则 command 作为参数传递给 bin/sh -c。默认值为 1。可选参数。send_chunk_header - 控制在向进程发送数据块之前是否发送行数。可选。默认值为 false。
该字典源只能通过 XML 配置进行配置。禁用通过 DDL 创建具有可执行源的字典，否则，数据库用户将能够在 ClickHouse 节点上执行任意二进制文件。
HTTP(S)
使用 HTTP(S) 服务器取决于 字典在内存中存储方式 。如果字典使用 cache 和 complex_key_cache 存储，ClickHouse 通过发送 POST 方法的请求来请求必要的键。
设置示例
<source>
<http>
<url>http://[::1]/os.tsv</url>
<format>TabSeparated</format>
<credentials>
<user>user</user>
<password>password</password>
</credentials>
<headers>
<header>
<name>API-KEY</name>
<value>key</value>
</header>
</headers>
</http>
</source>
或者
SOURCE(HTTP(
url 'http://[::1]/os.tsv'
format 'TabSeparated'
credentials(user 'user' password 'password')
headers(header(name 'API-KEY' value 'key'))
))
为了使 ClickHouse 能够访问 HTTPS 资源，您必须 在服务器配置中配置 openSSL 。
设置字段
url – 源 URL。format – 文件格式。支持 "格式 " 中描述的所有格式。credentials – 基本 HTTP 身份验证。可选参数。user – 身份验证所需的用户名。password – 身份验证所需的密码。headers – 用于 HTTP 请求的所有自定义 HTTP 标头条目。可选参数。header – 单个 HTTP 标头条目。name – 用于请求中发送的标头的标识符名称。value – 为特定标识符名称设置的值。
使用 DDL 命令 (CREATE DICTIONARY ...) 创建字典时，HTTP 字典的远程主机将针对 config 中 remote_url_allow_hosts 部分的内容进行检查，以防止数据库用户访问任意 HTTP 服务器。
DBMS
ODBC
您可以使用此方法连接具有 ODBC 驱动程序的任何数据库。
设置示例
<source>
<odbc>
<db>DatabaseName</db>
<table>ShemaName.TableName</table>
<connection_string>DSN=some_parameters</connection_string>
<invalidate_query>SQL_QUERY</invalidate_query>
<query>SELECT id, value_1, value_2 FROM ShemaName.TableName</query>
</odbc>
</source>
或者
SOURCE(ODBC(
db 'DatabaseName'
table 'SchemaName.TableName'
connection_string 'DSN=some_parameters'
invalidate_query 'SQL_QUERY'
query 'SELECT id, value_1, value_2 FROM db_name.table_name'
))
设置字段
db – 数据库名称。如果数据库名称在 <connection_string> 参数中设置，则省略它。table – 表名和架构（如果存在）。connection_string – 连接字符串。invalidate_query – 用于检查字典状态的查询。可选参数。有关详细信息，请参阅 使用 LIFETIME 刷新字典数据 部分。background_reconnect – 如果连接失败，则在后台重新连接到副本。可选参数。query – 自定义查询。可选参数。
注意
table 和 query 字段不能一起使用。并且必须声明 table 或 query 字段中的一个。
ClickHouse 从 ODBC 驱动程序接收引号符号，并为驱动程序中的所有设置加上引号，因此有必要相应地设置表名以匹配数据库中的表名大小写。
如果您在使用 Oracle 时遇到编码问题，请参阅相应的 常见问题解答 项目。
ODBC 字典功能的已知漏洞
注意
通过 ODBC 驱动程序连接参数 Servername 可以被替换。在这种情况下，odbc.ini 中的 USERNAME 和 PASSWORD 值将被发送到远程服务器，并且可能被泄露。
不安全使用的示例
让我们配置 unixODBC 用于 PostgreSQL。/etc/odbc.ini 的内容
[gregtest]
Driver = /usr/lib/psqlodbca.so
Servername = localhost
PORT = 5432
DATABASE = test_db
#OPTION = 3
USERNAME = test
PASSWORD = test
如果您随后发出如下查询
SELECT * FROM odbc('DSN=gregtest;Servername=some-server.com', 'test_db');
ODBC 驱动程序会将 odbc.ini 中的 USERNAME 和 PASSWORD 值发送到 some-server.com。
连接 Postgresql 的示例
Ubuntu 操作系统。
安装 unixODBC 和 PostgreSQL 的 ODBC 驱动程序
$ sudo apt-get install -y unixodbc odbcinst odbc-postgresql
配置 /etc/odbc.ini（或 ~/.odbc.ini，如果您以运行 ClickHouse 的用户身份登录）
[DEFAULT]
Driver = myconnection
[myconnection]
Description = PostgreSQL connection to my_db
Driver = PostgreSQL Unicode
Database = my_db
Servername = 127.0.0.1
UserName = username
Password = password
Port = 5432
Protocol = 9.3
ReadOnly = No
RowVersioning = No
ShowSystemTables = No
ConnSettings =
ClickHouse 中的字典配置
<clickhouse>
<dictionary>
<name>table_name</name>
<source>
<odbc>
<!-- You can specify the following parameters in connection_string: -->
<!-- DSN=myconnection;UID=username;PWD=password;HOST=127.0.0.1;PORT=5432;DATABASE=my_db -->
<connection_string>DSN=myconnection</connection_string>
<table>postgresql_table</table>
</odbc>
</source>
<lifetime>
<min>300</min>
<max>360</max>
</lifetime>
<layout>
<hashed/>
</layout>
<structure>
<id>
<name>id</name>
</id>
<attribute>
<name>some_column</name>
<type>UInt64</type>
<null_value>0</null_value>
</attribute>
</structure>
</dictionary>
</clickhouse>
或者
CREATE DICTIONARY table_name (
id UInt64,
some_column UInt64 DEFAULT 0
)
PRIMARY KEY id
SOURCE(ODBC(connection_string 'DSN=myconnection' table 'postgresql_table'))
LAYOUT(HASHED())
LIFETIME(MIN 300 MAX 360)
您可能需要编辑 odbc.ini 以指定带有驱动程序的库的完整路径 DRIVER=/usr/local/lib/psqlodbcw.so。
连接 MS SQL Server 的示例
Ubuntu 操作系统。
安装用于连接到 MS SQL 的 ODBC 驱动程序
$ sudo apt-get install tdsodbc freetds-bin sqsh
配置驱动程序
$ cat /etc/freetds/freetds.conf
...
[MSSQL]
host = 192.168.56.101
port = 1433
tds version = 7.0
client charset = UTF-8
# test TDS connection
$ sqsh -S MSSQL -D database -U user -P password
$ cat /etc/odbcinst.ini
[FreeTDS]
Description = FreeTDS
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
FileUsage = 1
UsageCount = 5
$ cat /etc/odbc.ini
# $ cat ~/.odbc.ini # if you signed in under a user that runs ClickHouse
[MSSQL]
Description = FreeTDS
Driver = FreeTDS
Servername = MSSQL
Database = test
UID = test
PWD = test
Port = 1433
# (optional) test ODBC connection (to use isql-tool install the [unixodbc](https://packages.debian.org/sid/unixodbc)-package)
$ isql -v MSSQL "user" "password"
备注
在 ClickHouse 中配置字典
<clickhouse>
<dictionary>
<name>test</name>
<source>
<odbc>
<table>dict</table>
<connection_string>DSN=MSSQL;UID=test;PWD=test</connection_string>
</odbc>
</source>
<lifetime>
<min>300</min>
<max>360</max>
</lifetime>
<layout>
<flat />
</layout>
<structure>
<id>
<name>k</name>
</id>
<attribute>
<name>s</name>
<type>String</type>
<null_value></null_value>
</attribute>
</structure>
</dictionary>
</clickhouse>
或者
CREATE DICTIONARY test (
k UInt64,
s String DEFAULT ''
)
PRIMARY KEY k
SOURCE(ODBC(table 'dict' connection_string 'DSN=MSSQL;UID=test;PWD=test'))
LAYOUT(FLAT())
LIFETIME(MIN 300 MAX 360)
Mysql
设置示例
<source>
<mysql>
<port>3306</port>
<user>clickhouse</user>
<password>qwerty</password>
<replica>
<host>example01-1</host>
<priority>1</priority>
</replica>
<replica>
<host>example01-2</host>
<priority>1</priority>
</replica>
<db>db_name</db>
<table>table_name</table>
<where>id=10</where>
<invalidate_query>SQL_QUERY</invalidate_query>
<fail_on_connection_loss>true</fail_on_connection_loss>
<query>SELECT id, value_1, value_2 FROM db_name.table_name</query>
</mysql>
</source>
或者
SOURCE(MYSQL(
port 3306
user 'clickhouse'
password 'qwerty'
replica(host 'example01-1' priority 1)
replica(host 'example01-2' priority 1)
db 'db_name'
table 'table_name'
where 'id=10'
invalidate_query 'SQL_QUERY'
fail_on_connection_loss 'true'
query 'SELECT id, value_1, value_2 FROM db_name.table_name'
))
设置字段
port – MySQL 服务器上的端口。您可以为所有副本指定它，也可以为每个副本单独指定（在 <replica> 内部）。
user – MySQL 用户名。您可以为所有副本指定它，也可以为每个副本单独指定（在 <replica> 内部）。
password – MySQL 用户的密码。您可以为所有副本指定它，也可以为每个副本单独指定（在 <replica> 内部）。
replica – 副本配置部分。可以有多个部分。
replica/host – MySQL 主机。replica/priority – 副本优先级。在尝试连接时，ClickHouse 会按照优先级顺序遍历副本。数字越小，优先级越高。
db – 数据库名称。
table – 表名称。
where – 选择条件。条件的语法与 MySQL 中的 WHERE 子句相同，例如 id > 10 AND id < 20。可选参数。
invalidate_query – 用于检查字典状态的查询。可选参数。有关详细信息，请参阅 使用 LIFETIME 刷新字典数据 部分。
fail_on_connection_loss – 配置参数，控制服务器在连接丢失时的行为。如果为 true，则在客户端和服务器之间的连接丢失时立即抛出异常。如果为 false，ClickHouse 服务器会在抛出异常之前重试执行查询三次。请注意，重试会导致响应时间增加。默认值：false。
query – 自定义查询。可选参数。
注意
table 或 where 字段不能与 query 字段一起使用。并且 table 或 query 字段中必须声明一个。
注意
没有显式的 secure 参数。建立 SSL 连接时，安全性是强制性的。
可以通过套接字连接到本地主机上的 MySQL。为此，请设置 host 和 socket。
设置示例
<source>
<mysql>
<host>localhost</host>
<socket>/path/to/socket/file.sock</socket>
<user>clickhouse</user>
<password>qwerty</password>
<db>db_name</db>
<table>table_name</table>
<where>id=10</where>
<invalidate_query>SQL_QUERY</invalidate_query>
<fail_on_connection_loss>true</fail_on_connection_loss>
<query>SELECT id, value_1, value_2 FROM db_name.table_name</query>
</mysql>
</source>
或者
SOURCE(MYSQL(
host 'localhost'
socket '/path/to/socket/file.sock'
user 'clickhouse'
password 'qwerty'
db 'db_name'
table 'table_name'
where 'id=10'
invalidate_query 'SQL_QUERY'
fail_on_connection_loss 'true'
query 'SELECT id, value_1, value_2 FROM db_name.table_name'
))
ClickHouse
设置示例
<source>
<clickhouse>
<host>example01-01-1</host>
<port>9000</port>
<user>default</user>
<password></password>
<db>default</db>
<table>ids</table>
<where>id=10</where>
<secure>1</secure>
<query>SELECT id, value_1, value_2 FROM default.ids</query>
</clickhouse>
</source>
或者
SOURCE(CLICKHOUSE(
host 'example01-01-1'
port 9000
user 'default'
password ''
db 'default'
table 'ids'
where 'id=10'
secure 1
query 'SELECT id, value_1, value_2 FROM default.ids'
));
设置字段
host – ClickHouse 主机。如果它是本地主机，则查询将在没有任何网络活动的情况下处理。为了提高容错能力，您可以创建一个 分布式 表，并在后续配置中输入它。port – ClickHouse 服务器上的端口。user – ClickHouse 用户名。password – ClickHouse 用户的密码。db – 数据库名称。table – 表名称。where – 选择条件。可以省略。invalidate_query – 用于检查字典状态的查询。可选参数。有关详细信息，请参阅 使用 LIFETIME 刷新字典数据 部分。secure - 使用 ssl 进行连接。query – 自定义查询。可选参数。
注意
table 或 where 字段不能与 query 字段一起使用。并且 table 或 query 字段中必须声明一个。
MongoDB
设置示例
<source>
<mongodb>
<host>localhost</host>
<port>27017</port>
<user></user>
<password></password>
<db>test</db>
<collection>dictionary_source</collection>
<options>ssl=true</options>
</mongodb>
</source>
或者
<source>
<mongodb>
<uri>mongodb://:27017/test?ssl=true</uri>
<collection>dictionary_source</collection>
</mongodb>
</source>
或者
SOURCE(MONGODB(
host 'localhost'
port 27017
user ''
password ''
db 'test'
collection 'dictionary_source'
options 'ssl=true'
))
设置字段
host – MongoDB 主机。port – MongoDB 服务器上的端口。user – MongoDB 用户名。password – MongoDB 用户的密码。db – 数据库名称。collection – 集合名称。options - MongoDB 连接字符串选项（可选参数）。
或者
SOURCE(MONGODB(
uri 'mongodb://:27017/clickhouse'
collection 'dictionary_source'
))
设置字段
uri - 建立连接的 URI。collection – 集合名称。
关于引擎的更多信息
Redis
设置示例
<source>
<redis>
<host>localhost</host>
<port>6379</port>
<storage_type>simple</storage_type>
<db_index>0</db_index>
</redis>
</source>
或者
SOURCE(REDIS(
host 'localhost'
port 6379
storage_type 'simple'
db_index 0
))
设置字段
host – Redis 主机。port – Redis 服务器上的端口。storage_type – 使用于处理键的内部 Redis 存储结构。simple 用于简单来源和哈希的单个键来源，hash_map 用于哈希的两个键来源。范围来源和具有复杂键的缓存来源不受支持。可以省略，默认值为 simple。db_index – Redis 逻辑数据库的特定数字索引。可以省略，默认值为 0。
Cassandra
设置示例
<source>
<cassandra>
<host>localhost</host>
<port>9042</port>
<user>username</user>
<password>qwerty123</password>
<keyspase>database_name</keyspase>
<column_family>table_name</column_family>
<allow_filtering>1</allow_filtering>
<partition_key_prefix>1</partition_key_prefix>
<consistency>One</consistency>
<where>"SomeColumn" = 42</where>
<max_threads>8</max_threads>
<query>SELECT id, value_1, value_2 FROM database_name.table_name</query>
</cassandra>
</source>
设置字段
host – Cassandra 主机或逗号分隔的主机列表。port – Cassandra 服务器上的端口。如果未指定，则使用默认端口 9042。user – Cassandra 用户名。password – Cassandra 用户的密码。keyspace – 键空间（数据库）名称。column_family – 列族（表）名称。allow_filtering – 标志，允许或不允许对聚类键列进行潜在的昂贵条件。默认值为 1。partition_key_prefix – Cassandra 表主键中分区键列的数量。对于组合键字典是必需的。字典定义中键列的顺序必须与 Cassandra 中的顺序相同。默认值为 1（第一个键列是分区键，其他键列是聚类键）。consistency – 一致性级别。可能的值：One、Two、Three、All、EachQuorum、Quorum、LocalQuorum、LocalOne、Serial、LocalSerial。默认值为 One。where – 可选的选择条件。max_threads – 用于从组合键字典中的多个分区加载数据的最大线程数。query – 自定义查询。可选参数。
注意
column_family 或 where 字段不能与 query 字段一起使用。并且 column_family 或 query 字段中必须声明一个。
PostgreSQL
设置示例
<source>
<postgresql>
<host>postgresql-hostname</hoat>
<port>5432</port>
<user>clickhouse</user>
<password>qwerty</password>
<db>db_name</db>
<table>table_name</table>
<where>id=10</where>
<invalidate_query>SQL_QUERY</invalidate_query>
<query>SELECT id, value_1, value_2 FROM db_name.table_name</query>
</postgresql>
</source>
或者
SOURCE(POSTGRESQL(
port 5432
host 'postgresql-hostname'
user 'postgres_user'
password 'postgres_password'
db 'db_name'
table 'table_name'
replica(host 'example01-1' port 5432 priority 1)
replica(host 'example01-2' port 5432 priority 2)
where 'id=10'
invalidate_query 'SQL_QUERY'
query 'SELECT id, value_1, value_2 FROM db_name.table_name'
))
设置字段
host – PostgreSQL 服务器上的主机。您可以为所有副本指定它，也可以为每个副本单独指定（在 <replica> 内部）。port – PostgreSQL 服务器上的端口。您可以为所有副本指定它，也可以为每个副本单独指定（在 <replica> 内部）。user – PostgreSQL 用户名。您可以为所有副本指定它，也可以为每个副本单独指定（在 <replica> 内部）。password – PostgreSQL 用户的密码。您可以为所有副本指定它，也可以为每个副本单独指定（在 <replica> 内部）。replica – 副本配置部分。可以有多个部分
replica/host – PostgreSQL 主机。replica/port – PostgreSQL 端口。replica/priority – 副本优先级。在尝试连接时，ClickHouse 会按照优先级顺序遍历副本。数字越小，优先级越高。
db – 数据库名称。table – 表名称。where – 选择条件。条件的语法与 PostgreSQL 中的 WHERE 子句相同。例如，id > 10 AND id < 20。可选参数。invalidate_query – 用于检查字典状态的查询。可选参数。有关详细信息，请参阅 使用 LIFETIME 刷新字典数据 部分。background_reconnect – 如果连接失败，则在后台重新连接到副本。可选参数。query – 自定义查询。可选参数。
注意
table 或 where 字段不能与 query 字段一起使用。并且 table 或 query 字段中必须声明一个。
YTsaurus
设置示例
<source>
<ytsaurus>
<http_proxy_urls>https://:8000</http_proxy_urls>
<cypress_path>//tmp/test</cypress_path>
<oauth_token>password</oauth_token>
<check_table_schema>1</check_table_schema>
</ytsaurus>
</source>
或者
SOURCE(YTSAURUS(
http_proxy_urls 'https://:8000'
cypress_path '//tmp/test'
oauth_token 'password'
))
设置字段
http_proxy_urls – YTsaurus http 代理的 URL。cypress_path – Cypress 路径到表来源。oauth_token – OAuth 令牌。
Null
一种特殊来源，可用于创建虚拟（空）字典。此类字典可用于测试或使用具有分离的数据和查询节点的设置，在具有分布式表的节点上。
CREATE DICTIONARY null_dict (
id UInt64,
val UInt8,
default_val UInt8 DEFAULT 123,
nullable_val Nullable(UInt8)
)
PRIMARY KEY id
SOURCE(NULL())
LAYOUT(FLAT())
LIFETIME(0);
字典键和字段
提示
如果您在 ClickHouse Cloud 中使用词典，请使用 DDL 查询选项创建词典，并将词典创建为用户 default。此外，请在 云兼容性指南 中验证受支持的词典来源列表。
structure 子句描述了字典键和可用于查询的字段。
XML 描述
<dictionary>
<structure>
<id>
<name>Id</name>
</id>
<attribute>
<!-- Attribute parameters -->
</attribute>
...
</structure>
</dictionary>
属性在元素中描述
<id> — 键列<attribute> — 数据列：可以有多个属性。
DDL 查询
CREATE DICTIONARY dict_name (
Id UInt64,
-- attributes
)
PRIMARY KEY Id
...
属性在查询体中描述
PRIMARY KEY — 键列AttrName AttrType — 数据列。可以有多个属性。
ClickHouse 支持以下类型的键
数字键。UInt64。在 <id> 标签或使用 PRIMARY KEY 关键字中定义。
复合键。不同类型值的集合。在 <key> 标签或 PRIMARY KEY 关键字中定义。
XML 结构可以包含 <id> 或 <key>。DDL 查询必须包含单个 PRIMARY KEY。
数字键
类型：UInt64。
配置示例
<id>
<name>Id</name>
</id>
配置字段
对于 DDL 查询
CREATE DICTIONARY (
Id UInt64,
...
)
PRIMARY KEY Id
...
复合键
键可以是任何类型字段的 tuple。在这种情况下，布局 必须是 complex_key_hashed 或 complex_key_cache。
提示
复合键可以包含单个元素。这使得可以使用字符串作为键，例如。
键结构在 <key> 元素中设置。键字段以与字典 属性 相同的格式指定。示例
<structure>
<key>
<attribute>
<name>field1</name>
<type>String</type>
</attribute>
<attribute>
<name>field2</name>
<type>UInt32</type>
</attribute>
...
</key>
...
或者
CREATE DICTIONARY (
field1 String,
field2 UInt32
...
)
PRIMARY KEY field1, field2
...
对于对 dictGet* 函数的查询，将元组作为键传递。示例：dictGetString('dict_name', 'attr_name', tuple('string for field1', num_for_field2))。
配置示例
<structure>
...
<attribute>
<name>Name</name>
<type>ClickHouseDataType</type>
<null_value></null_value>
<expression>rand64()</expression>
<hierarchical>true</hierarchical>
<injective>true</injective>
<is_object_id>true</is_object_id>
</attribute>
</structure>
或者
CREATE DICTIONARY somename (
Name ClickHouseDataType DEFAULT '' EXPRESSION rand64() HIERARCHICAL INJECTIVE IS_OBJECT_ID
)
配置字段
分层字典
ClickHouse 支持具有 数字键 的分层字典。
查看以下分层结构
0 (Common parent)
│
├── 1 (Russia)
│ │
│ └── 2 (Moscow)
│ │
│ └── 3 (Center)
│
└── 4 (Great Britain)
│
└── 5 (London)
这个层级结构可以表示为以下字典表。
region_id parent_region region_name 1 0 俄罗斯 2 1 莫斯科 3 2 中心区 4 0 英国 5 4 伦敦
这个表包含一列 parent_region，其中包含该元素最近父级的键。
ClickHouse 支持外部字典属性的层级属性。此属性允许您配置类似于上述描述的层级字典。
函数 dictGetHierarchy 允许您获取元素的父链。
对于我们的示例，字典的结构可以是以下形式
<dictionary>
<structure>
<id>
<name>region_id</name>
</id>
<attribute>
<name>parent_region</name>
<type>UInt64</type>
<null_value>0</null_value>
<hierarchical>true</hierarchical>
</attribute>
<attribute>
<name>region_name</name>
<type>String</type>
<null_value></null_value>
</attribute>
</structure>
</dictionary>
多边形字典
此字典针对点在多边形内的查询进行了优化，本质上是“反地理编码”查找。给定坐标（纬度/经度），它有效地找到包含该点的多边形/区域（来自许多多边形集合，例如国家或区域边界）。它非常适合将位置坐标映射到其包含的区域。
VIDEO
多边形字典配置示例
提示
如果您在 ClickHouse Cloud 中使用词典，请使用 DDL 查询选项创建词典，并将词典创建为用户 default。此外，请在 云兼容性指南 中验证受支持的词典来源列表。
<dictionary>
<structure>
<key>
<attribute>
<name>key</name>
<type>Array(Array(Array(Array(Float64))))</type>
</attribute>
</key>
<attribute>
<name>name</name>
<type>String</type>
<null_value></null_value>
</attribute>
<attribute>
<name>value</name>
<type>UInt64</type>
<null_value>0</null_value>
</attribute>
</structure>
<layout>
<polygon>
<store_polygon_key_column>1</store_polygon_key_column>
</polygon>
</layout>
...
</dictionary>
相应的 DDL 查询
CREATE DICTIONARY polygon_dict_name (
key Array(Array(Array(Array(Float64)))),
name String,
value UInt64
)
PRIMARY KEY key
LAYOUT(POLYGON(STORE_POLYGON_KEY_COLUMN 1))
...
在配置多边形字典时，键必须具有以下两种类型之一
一个简单的多边形。它是一个点的数组。
多边形。它是一个多边形的数组。每个多边形是点的二维数组。此数组的第一个元素是多边形的外部边界，后续元素指定要从其排除的区域。
点可以指定为坐标的数组或元组。在当前实现中，仅支持二维点。
用户可以使用 ClickHouse 支持的所有格式上传他们自己的数据。
有 3 种 内存存储 类型可用
POLYGON_SIMPLE。这是一个朴素的实现，对每个查询对所有多边形进行线性遍历，并检查每个多边形的成员资格，而不使用额外的索引。
POLYGON_INDEX_EACH。为每个多边形构建一个单独的索引，允许您快速检查它在大多数情况下是否属于（针对地理区域进行了优化）。此外，还在考虑的区域上叠加一个网格，从而大大缩小了考虑的多边形数量。网格通过递归地将单元格划分为 16 个相等的部分来创建，并使用两个参数进行配置。当递归深度达到 MAX_DEPTH 或单元格不再交叉超过 MIN_INTERSECTIONS 个多边形时，划分停止。为了响应查询，存在相应的单元格，并交替访问存储在其中的多边形的索引。
POLYGON_INDEX_CELL。这种放置方式也创建了上述网格。可用相同的选项。对于每个网格单元格，都会在落入其中的所有多边形片段上构建一个索引，从而可以快速响应请求。
POLYGON。POLYGON_INDEX_CELL 的同义词。
使用标准的 函数 处理字典进行字典查询。一个重要的区别是，这里的键将是您想要查找包含它们的的多边形的点。
示例
使用上述定义的字典的示例
CREATE TABLE points (
x Float64,
y Float64
)
...
SELECT tuple(x, y) AS key, dictGet(dict_name, 'name', key), dictGet(dict_name, 'value', key) FROM points ORDER BY x, y;
执行最后一条命令的结果是，对于 'points' 表中的每个点，将找到包含该点的最小面积多边形，并输出请求的属性。
示例
您可以通过 SELECT 查询读取多边形字典中的列，只需在字典配置或相应的 DDL 查询中启用 store_polygon_key_column = 1 即可。
查询
CREATE TABLE polygons_test_table
(
key Array(Array(Array(Tuple(Float64, Float64)))),
name String
) ENGINE = TinyLog;
INSERT INTO polygons_test_table VALUES ([[[(3, 1), (0, 1), (0, -1), (3, -1)]]], 'Value');
CREATE DICTIONARY polygons_test_dictionary
(
key Array(Array(Array(Tuple(Float64, Float64)))),
name String
)
PRIMARY KEY key
SOURCE(CLICKHOUSE(TABLE 'polygons_test_table'))
LAYOUT(POLYGON(STORE_POLYGON_KEY_COLUMN 1))
LIFETIME(0);
SELECT * FROM polygons_test_dictionary;
结果
┌─key─────────────────────────────┬─name──┐
│ [[[(3,1),(0,1),(0,-1),(3,-1)]]] │ Value │
└─────────────────────────────────┴───────┘
正则表达式树字典
此字典允许您根据分层正则表达式模式将键映射到值。它针对模式匹配查找（例如，通过匹配正则表达式来对用户代理字符串等字符串进行分类）而不是精确键匹配进行了优化。
VIDEO
在 ClickHouse 开源中使用正则表达式树字典
正则表达式树字典在 ClickHouse 开源中使用 YAMLRegExpTree 源定义，该源提供指向包含正则表达式树的 YAML 文件的路径。
CREATE DICTIONARY regexp_dict
(
regexp String,
name String,
version String
)
PRIMARY KEY(regexp)
SOURCE(YAMLRegExpTree(PATH '/var/lib/clickhouse/user_files/regexp_tree.yaml'))
LAYOUT(regexp_tree)
...
字典源 YAMLRegExpTree 表示正则表达式树的结构。例如
- regexp: 'Linux/(\d+[\.\d]*).+tlinux'
name: 'TencentOS'
version: '\1'
- regexp: '\d+/tclwebkit(?:\d+[\.\d]*)'
name: 'Android'
versions:
- regexp: '33/tclwebkit'
version: '13'
- regexp: '3[12]/tclwebkit'
version: '12'
- regexp: '30/tclwebkit'
version: '11'
- regexp: '29/tclwebkit'
version: '10'
此配置包含一个正则表达式树节点列表。每个节点具有以下结构
regexp ：节点的正则表达式。attributes ：用户定义的字典属性列表。在本例中，有两个属性：name 和 version。第一个节点定义了这两个属性。第二个节点仅定义属性 name。属性 version 由第二个节点的子节点提供。
属性的值可以包含 反向引用 ，引用匹配正则表达式的捕获组。在示例中，第一个节点中属性 version 的值由正则表达式中的捕获组 (\d+[\.\d]*) 的反向引用 \1 组成。反向引用编号范围为 1 到 9，写为 $1 或 \1（对于数字 1）。在查询执行期间，反向引用将被匹配的捕获组替换。
子节点 ：正则表达式树节点的子节点列表，每个子节点都有自己的属性和（可能）子节点。字符串匹配以深度优先的方式进行。如果字符串匹配正则表达式节点，则字典会检查它是否也匹配节点的子节点。如果是这样，则最深匹配节点的属性将被分配。子节点的属性会覆盖父节点的同名属性。YAML 文件中子节点的名称可以是任意的，例如，上面的示例中的 versions。
正则表达式树字典仅允许使用函数 dictGet、dictGetOrDefault 和 dictGetAll 进行访问。
示例
SELECT dictGet('regexp_dict', ('name', 'version'), '31/tclwebkit1024');
结果
┌─dictGet('regexp_dict', ('name', 'version'), '31/tclwebkit1024')─┐
│ ('Android','12') │
└─────────────────────────────────────────────────────────────────┘
在这种情况下，我们首先在顶层第二个节点中匹配正则表达式 \d+/tclwebkit(?:\d+[\.\d]*)。然后，字典继续查找子节点，并发现该字符串也匹配 3[12]/tclwebkit。结果，属性 name 的值为 Android（在第一层定义），属性 version 的值为 12（在子节点中定义）。
借助强大的 YAML 配置文件，我们可以将正则表达式树字典用作用户代理字符串解析器。我们支持 uap-core 并演示如何在功能测试 02504_regexp_dictionary_ua_parser 中使用它
收集属性值
有时，返回多个匹配正则表达式的值而不是仅仅返回叶节点的值会很有用。在这种情况下，可以使用专门的 dictGetAll
默认情况下，每个键返回的匹配数量不受限制。可以将限制作为 dictGetAll 的可选第四个参数传递。数组以 拓扑顺序 填充，这意味着子节点先于父节点，同级节点遵循源中的顺序。
示例
CREATE DICTIONARY regexp_dict
(
regexp String,
tag String,
topological_index Int64,
captured Nullable(String),
parent String
)
PRIMARY KEY(regexp)
SOURCE(YAMLRegExpTree(PATH '/var/lib/clickhouse/user_files/regexp_tree.yaml'))
LAYOUT(regexp_tree)
LIFETIME(0)
# /var/lib/clickhouse/user_files/regexp_tree.yaml
- regexp: 'clickhouse\.com'
tag: 'ClickHouse'
topological_index: 1
paths:
- regexp: 'clickhouse\.com/docs(.*)'
tag: 'ClickHouse Documentation'
topological_index: 0
captured: '\1'
parent: 'ClickHouse'
- regexp: '/docs(/|$)'
tag: 'Documentation'
topological_index: 2
- regexp: 'github.com'
tag: 'GitHub'
topological_index: 3
captured: 'NULL'
CREATE TABLE urls (url String) ENGINE=MergeTree ORDER BY url;
INSERT INTO urls VALUES ('clickhouse.com'), ('clickhouse.com/docs/en'), ('github.com/clickhouse/tree/master/docs');
SELECT url, dictGetAll('regexp_dict', ('tag', 'topological_index', 'captured', 'parent'), url, 2) FROM urls;
结果
┌─url────────────────────────────────────┬─dictGetAll('regexp_dict', ('tag', 'topological_index', 'captured', 'parent'), url, 2)─┐
│ clickhouse.com │ (['ClickHouse'],[1],[],[]) │
│ clickhouse.com/docs/en │ (['ClickHouse Documentation','ClickHouse'],[0,1],['/en'],['ClickHouse']) │
│ github.com/clickhouse/tree/master/docs │ (['Documentation','GitHub'],[2,3],[NULL],[]) │
└────────────────────────────────────────┴───────────────────────────────────────────────────────────────────────────────────────┘
匹配模式
可以使用某些字典设置修改模式匹配行为
regexp_dict_flag_case_insensitive：使用不区分大小写的匹配（默认为 false）。可以使用 (?i) 和 (?-i) 在单个表达式中覆盖。regexp_dict_flag_dotall：允许 '.' 匹配换行符（默认为 false）。
在 ClickHouse Cloud 中使用正则表达式树字典
上述使用的 YAMLRegExpTree 源在 ClickHouse 开源中有效，但在 ClickHouse Cloud 中无效。要在 ClickHouse Cloud 中使用正则表达式树字典，首先在 ClickHouse 开源中从 YAML 文件本地创建正则表达式树字典，然后使用 dictionary 表函数和 INTO OUTFILE 子句将此字典转储到 CSV 文件中。
SELECT * FROM dictionary(regexp_dict) INTO OUTFILE('regexp_dict.csv')
csv 文件的内容是
1,0,"Linux/(\d+[\.\d]*).+tlinux","['version','name']","['\\1','TencentOS']"
2,0,"(\d+)/tclwebkit(\d+[\.\d]*)","['comment','version','name']","['test $1 and $2','$1','Android']"
3,2,"33/tclwebkit","['version']","['13']"
4,2,"3[12]/tclwebkit","['version']","['12']"
5,2,"3[12]/tclwebkit","['version']","['11']"
6,2,"3[12]/tclwebkit","['version']","['10']"
转储文件的模式是
id UInt64：RegexpTree 节点的 ID。parent_id UInt64：节点的父节点的 ID。regexp String：正则表达式字符串。keys Array(String)：用户定义的属性的名称。values Array(String)：用户定义的属性的值。
要在 ClickHouse Cloud 中创建字典，首先使用以下表结构创建表 regexp_dictionary_source_table
CREATE TABLE regexp_dictionary_source_table
(
id UInt64,
parent_id UInt64,
regexp String,
keys Array(String),
values Array(String)
) ENGINE=Memory;
然后通过以下方式更新本地 CSV
clickhouse client \
--host MY_HOST \
--secure \
--password MY_PASSWORD \
--query "
INSERT INTO regexp_dictionary_source_table
SELECT * FROM input ('id UInt64, parent_id UInt64, regexp String, keys Array(String), values Array(String)')
FORMAT CSV" < regexp_dict.csv
您可以查看 插入本地文件 以获取更多详细信息。在初始化源表后，我们可以通过表源创建 RegexpTree
CREATE DICTIONARY regexp_dict
(
regexp String,
name String,
version String
PRIMARY KEY(regexp)
SOURCE(CLICKHOUSE(TABLE 'regexp_dictionary_source_table'))
LIFETIME(0)
LAYOUT(regexp_tree);
嵌入式字典
ClickHouse 包含用于处理地理数据库的内置功能。
这允许您
使用区域 ID 获取所需语言的区域名称。
使用区域 ID 获取城市、区域、联邦区、国家或大陆的 ID。
检查一个区域是否是另一个区域的一部分。
获取父区域链。
所有函数都支持“跨地域性”，即同时使用不同视角来查看区域所有权的能力。有关更多信息，请参阅“用于处理 Web 分析字典的函数”部分。
内部字典在默认包中被禁用。要启用它们，请在服务器配置文件中取消注释参数 path_to_regions_hierarchy_file 和 path_to_regions_names_files。
地理数据库从文本文件加载。
将 regions_hierarchy*.txt 文件放入 path_to_regions_hierarchy_file 目录中。此配置参数必须包含 regions_hierarchy.txt 文件的路径（默认区域层级结构），其他文件（regions_hierarchy_ua.txt）必须位于同一目录中。
将 regions_names_*.txt 文件放入 path_to_regions_names_files 目录。
你也可以自己创建这些文件。文件格式如下
regions_hierarchy*.txt: Tab 分隔 (无标题行)，列
区域 ID (UInt32)
父区域 ID (UInt32)
区域类型 (UInt8): 1 - 大陆, 3 - 国家, 4 - 联邦区, 5 - 地区, 6 - 城市; 其他类型没有值
人口 (UInt32) — 可选列
regions_names_*.txt: Tab 分隔 (无标题行)，列
区域 ID (UInt32)
区域名称 (String) — 不能包含制表符或换行符，即使是转义的也不行。
使用扁平数组在 RAM 中存储。因此，ID 不应超过一百万。
字典可以在不重启服务器的情况下更新。但是，可用字典的集合不会更新。为了更新，会检查文件的修改时间。如果文件已更改，则更新字典。检查更改的时间间隔在 builtin_dictionaries_reload_interval 参数中配置。字典更新（除了首次使用时的加载）不会阻塞查询。在更新期间，查询使用旧版本的字典。如果在更新过程中发生错误，错误将被写入服务器日志，查询将继续使用旧版本的字典。
我们建议定期使用 geobase 更新字典。在更新期间，生成新文件并将它们写入单独的位置。一切准备就绪后，将它们重命名为服务器使用的文件。
还有用于处理操作系统标识符和搜索引擎的功能，但不应该使用它们。
