字典

字典是一种映射 (key -> attributes)，方便用于各种类型的参考列表。

ClickHouse 支持使用字典的特殊函数，这些函数可以在查询中使用。与使用参考表的 JOIN 相比，使用函数与字典更容易且更高效。

ClickHouse 支持

带有一组函数的字典。
带有特定一组函数的嵌入式字典。

教程

如果您是 ClickHouse 字典的初学者，我们提供了一个涵盖该主题的教程。请查看此处。

您可以从各种数据源添加自己的字典。字典的来源可以是 ClickHouse 表格、本地文本或可执行文件、HTTP(s) 资源或其他 DBMS。有关更多信息，请参阅“字典来源”。

ClickHouse

完全或部分地将字典存储在 RAM 中。
定期更新字典并动态加载缺失的值。换句话说，字典可以动态加载。
允许使用 xml 文件或DDL 查询创建字典。

字典的配置可以位于一个或多个 xml 文件中。路径到配置在dictionaries_config 参数中指定。

字典可以在服务器启动时或首次使用时加载，具体取决于dictionaries_lazy_load 设置。

该dictionaries系统表包含有关服务器上配置的字典的信息。对于每个字典，您可以在其中找到

字典的状态。
配置参数。
指标，例如为字典分配的 RAM 量或字典成功加载后的查询次数。

提示

如果您使用 ClickHouse 云服务，请使用 DDL 查询选项创建字典，并以用户default身份创建字典。此外，请在云兼容性指南中验证支持的字典源列表。

使用 DDL 查询创建字典

字典可以使用DDL 查询创建，这是推荐的方法，因为使用 DDL 创建的字典

不会向服务器配置文件添加其他记录
可以像表格或视图一样将字典作为一等实体进行操作
可以使用熟悉的 SELECT 而不是字典表函数直接读取数据
字典可以轻松重命名

使用配置文件创建字典

注意

使用配置文件创建字典不适用于 ClickHouse 云服务。请使用 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 云服务，请使用 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 查询具有以下结构

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 云服务，请使用 DDL 查询选项创建字典，并以用户default身份创建字典。此外，请在云兼容性指南中验证支持的字典源列表。

配置如下所示

<clickhouse>
    <dictionary>
        ...
        <layout>
            <layout_type>
                <!-- layout settings -->
            </layout_type>
        </layout>
        ...
    </dictionary>
</clickhouse>

相应的DDL 查询

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>
...

在内存中存储字典的方法

flat
hashed
sparse_hashed
complex_key_hashed
complex_key_sparse_hashed
hashed_array
complex_key_hashed_array
range_hashed
complex_key_range_hashed
cache
complex_key_cache
ssd_cache
complex_key_ssd_cache
direct
complex_key_direct
ip_trie

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())

配置示例

<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

字典以哈希表的形式存储在内存中，该哈希表包含一个有序的范围数组及其对应值。

字典键的类型为UInt64。此存储方法的工作方式与 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>

或

LAYOUT(DIRECT())

complex_key_direct

这种存储类型用于复合键。类似于direct。

ip_trie

这种存储类型用于将网络前缀（IP 地址）映射到元数据（如 ASN）。

示例

假设我们在 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);

键必须只有一个包含允许的 IP 前缀的String类型属性。其他类型尚不支持。

语法为

dictGetT('dict_name', 'attr_name', ip)

该函数采用 IPv4 的UInt32或 IPv6 的FixedString(16)。例如

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 云服务，请使用 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 服务器会根据源的类型应用不同的逻辑。

对于文本文件，它会检查修改时间。如果时间与之前记录的时间不同，则更新字典。
对于 MySQL 源，使用SHOW TABLE STATUS查询检查修改时间（对于 MySQL 8，您需要通过set global information_schema_stats_expiry=0禁用 MySQL 中的元信息缓存）。
默认情况下，来自其他源的字典每次都会更新。

对于其他源（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、ComplexKeyHashed字典，也可以只请求上次更新后更改的数据。如果在字典源配置中指定了update_field，则上次更新时间的值（以秒为单位）将添加到数据请求中。根据源类型（Executable、HTTP、MySQL、PostgreSQL、ClickHouse 或 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 云服务，请使用 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）

本地文件
可执行文件
可执行文件池
HTTP(S)
DBMS

本地文件

设置示例

<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目录中，以防止 DB 用户访问 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 - 从命令标准输出读取数据的超时时间，以毫秒为单位。默认值为10000。可选参数。
command_write_timeout - 向命令标准输入写入数据的超时时间，以毫秒为单位。默认值为10000。可选参数。
implicit_key — 可执行源文件只能返回值，并且与请求键的对应关系由结果中行的顺序隐式确定。默认值为false。
execute_direct - 如果execute_direct = 1，则将在user_scripts_path指定的user_scripts文件夹中搜索command。可以使用空格分隔符指定其他脚本参数。例如：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 - 从命令标准输出读取数据的超时时间，以毫秒为单位。默认值为10000。可选参数。
command_write_timeout - 向命令标准输入写入数据的超时时间，以毫秒为单位。默认值为10000。可选参数。
implicit_key — 可执行源文件只能返回值，并且与请求键的对应关系由结果中行的顺序隐式确定。默认值为false。可选参数。
execute_direct - 如果execute_direct = 1，则将在user_scripts_path指定的user_scripts文件夹中搜索command。可以使用空格分隔符指定其他脚本参数。例如：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 ...）创建字典时，会根据配置中remote_url_allow_hosts部分的内容检查HTTP字典的远程主机，以防止数据库用户访问任意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刷新字典数据部分中了解更多信息。
query – 自定义查询。可选参数。

注意

table和query字段不能一起使用。并且必须声明table或query字段中的一个。

ClickHouse从ODBC驱动程序接收引号符号，并在查询中将所有设置都引用到驱动程序，因此需要根据数据库中表名的案例设置表名。

如果您在使用Oracle时遇到编码问题，请参阅相应的FAQ条目。

ODBC字典功能的已知漏洞

注意

通过ODBC驱动程序连接到数据库时，可以替换连接参数Servername。在这种情况下，odbc.ini中的USERNAME和PASSWORD的值将发送到远程服务器，并且可能被泄露。

不安全用法的示例

让我们为PostgreSQL配置unixODBC。/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（如果您以运行ClickHouse的用户身份登录，则配置~/.odbc.ini）

    [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"

备注

要确定特定SQL Server版本支持的最早TDS版本，请参阅产品文档或查看MS-TDS产品行为

在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` – The MySQL host.
  - `replica/priority` – The replica priority. When attempting to connect, ClickHouse traverses the replicas in order of priority. The lower the number, the higher the priority.

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://127.0.0.1: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://127.0.0.1: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 – 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刷新字典数据部分中了解更多信息。
query – 自定义查询。可选参数。

注意

table或where字段不能与query字段一起使用。并且必须声明table或query字段中的一个。

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 云服务，请使用 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 — 数据列。可以有多个属性。

Key

ClickHouse 支持以下类型的键

数字键。UInt64。在 <id> 标签中或使用 PRIMARY KEY 关键字定义。
复合键。不同类型值的集合。在标签 <key> 或 PRIMARY KEY 关键字中定义。

xml 结构可以包含 <id> 或 <key>。DDL 查询必须包含单个 PRIMARY KEY。

注意

您不能将键描述为属性。

数值键

类型：UInt64。

配置示例

<id>
    <name>Id</name>
</id>

配置字段

name – 带有键的列的名称。

对于 DDL 查询

CREATE DICTIONARY (
    Id UInt64,
    ...
)
PRIMARY KEY Id
...

PRIMARY KEY – 带有键的列的名称。

复合键

键可以是来自任何类型的字段的 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 String
    ...
)
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
)

配置字段

标签	描述	必需
`name`	列名。	是
`type`	ClickHouse 数据类型：UInt8、UInt16、UInt32、UInt64、Int8、Int16、Int32、Int64、Float32、Float64、UUID、Decimal32、Decimal64、Decimal128、Decimal256、Date、Date32、DateTime、DateTime64、String、Array。 ClickHouse 尝试将值从字典转换为指定的数据类型。例如，对于 MySQL，字段在 MySQL 源表中可能是 `TEXT`、`VARCHAR` 或 `BLOB`，但它可以在 ClickHouse 中作为 `String` 上传。 Nullable 当前支持 Flat、Hashed、ComplexKeyHashed、Direct、ComplexKeyDirect、RangeHashed、Polygon、Cache、ComplexKeyCache、SSDCache、SSDComplexKeyCache 字典。在 IPTrie 字典中，不支持 `Nullable` 类型。	是
`null_value`	不存在元素的默认值。在示例中，它是一个空字符串。NULL 值只能用于 `Nullable` 类型（请参见上一行中的类型描述）。	是
`expression`	ClickHouse 在值上执行的表达式。表达式可以是远程 SQL 数据库中的列名。因此，您可以使用它为远程列创建别名。默认值：无表达式。	否
`hierarchical`	如果为 `true`，则属性包含当前键的父键的值。请参阅分层字典。默认值：`false`。	否
`injective`	标志，指示 `id -> attribute` 映像是否单射。如果为 `true`，则 ClickHouse 可以在 `GROUP BY` 子句之后自动放置对具有注入的字典的请求。通常，它会显着减少此类请求的数量。默认值：`false`。	否
`is_object_id`	标志，指示查询是否通过 `ObjectID` 为 MongoDB 文档执行。默认值：`false`。

分层字典

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>

多边形字典

多边形字典允许您有效地搜索包含指定点的多边形。例如：通过地理坐标定义城市区域。

多边形字典配置示例

提示

如果您使用 ClickHouse 云服务，请使用 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 │
└─────────────────────────────────┴───────┘

正则表达式树字典

正则表达式树字典是一种特殊的字典类型，它使用正则表达式树表示从键到属性的映射。有一些用例，例如解析用户代理字符串，可以用正则表达式树字典优雅地表达。

在 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的值包含一个反向引用\1，该引用指向正则表达式中捕获组(\d+[\.\d]*)。反向引用编号范围从 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函数。如果节点具有类型为T的属性值，则dictGetAll将返回一个包含零个或多个值的Array(T)。

默认情况下，每个键返回的匹配次数是不受限制的。可以将边界作为可选的第四个参数传递给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 Cloud。此处记录的功能在 ClickHouse Cloud 服务中不可用。有关更多信息，请参阅 ClickHouseCloud Compatibility指南。

ClickHouse 包含一个用于处理地理数据库的内置功能。

这允许您

使用区域的 ID 获取其所需语言的名称。
使用区域的 ID 获取城市、地区、联邦区、国家或大陆的 ID。
检查某个区域是否属于另一个区域。
获取父区域链。

所有函数都支持“跨区域性”，即能够同时使用区域所有权的不同视角。有关更多信息，请参阅“用于处理网络分析字典的函数”部分。

默认软件包中禁用了内部字典。要启用它们，请取消服务器配置文件中参数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：TabSeparated（无标题），列

区域 ID（UInt32）
父区域 ID（UInt32）
区域类型（UInt8）：1 - 大陆，3 - 国家，4 - 联邦区，5 - 地区，6 - 城市；其他类型没有值
人口（UInt32）— 可选列

regions_names_*.txt：TabSeparated（无标题），列

区域 ID（UInt32）
区域名称（String）— 不能包含制表符或换行符，即使是转义的也不能。

使用平面数组进行内存存储。因此，ID 不应超过一百万。

可以在不重启服务器的情况下更新字典。但是，可用字典的集合不会更新。对于更新，会检查文件修改时间。如果文件已更改，则更新字典。检查更改的时间间隔在builtin_dictionaries_reload_interval参数中配置。字典更新（除了首次使用时的加载）不会阻塞查询。在更新期间，查询使用字典的旧版本。如果更新期间发生错误，则错误将写入服务器日志，并且查询将继续使用字典的旧版本。

我们建议定期使用地理数据库更新字典。在更新期间，生成新文件并将其写入单独的位置。一切准备就绪后，将其重命名为服务器使用的文件。

还有一些用于处理操作系统标识符和搜索引擎的函数，但不要使用它们。

字典

使用 DDL 查询创建字典​

使用配置文件创建字典​

配置字典​

在内存中存储字典​

在内存中存储字典的方法​

flat​

hashed​

sparse_hashed​

complex_key_hashed​

complex_key_sparse_hashed​

hashed_array​

complex_key_hashed_array​

range_hashed​

complex_key_range_hashed​

cache​

complex_key_cache​

ssd_cache​

complex_key_ssd_cache​

direct​

complex_key_direct​

ip_trie​

使用 LIFETIME 刷新字典数据​

字典源​

本地文件​

可执行文件​

可执行池​

HTTP(S)​

DBMS​

ODBC​

ODBC字典功能的已知漏洞​

连接Postgresql的示例​

连接MS SQL Server的示例​

Mysql​

ClickHouse​

MongoDB​

Redis​

Cassandra​

PostgreSQL​

Null​

字典键和字段​

Key​

数值键​

复合键​

属性​

分层字典​

多边形字典​

正则表达式树字典​

在 ClickHouse 开源版中使用正则表达式树字典​

收集属性值​

匹配模式​

在 ClickHouse Cloud 中使用正则表达式树字典​

嵌入式字典​

使用 DDL 查询创建字典

使用配置文件创建字典

配置字典

在内存中存储字典

在内存中存储字典的方法

flat

hashed

sparse_hashed

complex_key_hashed

complex_key_sparse_hashed

hashed_array

complex_key_hashed_array

range_hashed

complex_key_range_hashed

cache

complex_key_cache

ssd_cache

complex_key_ssd_cache

direct

complex_key_direct

ip_trie

使用 LIFETIME 刷新字典数据

字典源

本地文件

可执行文件

可执行池

HTTP(S)

DBMS

ODBC

ODBC字典功能的已知漏洞

连接Postgresql的示例

连接MS SQL Server的示例

Mysql

ClickHouse

MongoDB

Redis

Cassandra

PostgreSQL

Null

字典键和字段

Key

数值键

复合键

属性

分层字典

多边形字典

正则表达式树字典

在 ClickHouse 开源版中使用正则表达式树字典

收集属性值

匹配模式

在 ClickHouse Cloud 中使用正则表达式树字典

嵌入式字典