字典

字典是一种映射（键 -> 属性），它对于各种类型的参考列表非常方便。

ClickHouse 支持用于处理字典的特殊函数，这些函数可以在查询中使用。使用字典和函数比使用带有参考表的 JOIN 更容易、更高效。

ClickHouse 支持

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

教程

如果您开始在 ClickHouse 中使用字典，我们有一个教程涵盖该主题。请查看此处。

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

ClickHouse

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

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

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

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

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

提示

如果您正在 ClickHouse Cloud 中使用字典，请使用 DDL 查询选项来创建字典，并将字典创建为用户 default。此外，请在Cloud 兼容性指南中验证支持的字典源列表。

使用 DDL 查询创建字典

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

• 不会向服务器配置文件添加额外的记录
• 字典可以像表或视图一样作为第一类实体进行处理
• 可以使用熟悉的 SELECT 而不是字典表函数直接读取数据
• 可以轻松重命名字典

使用配置文件创建字典

ClickHouse Cloud 中不支持

注意

使用配置文件创建字典不适用于 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。此外，请在Cloud 兼容性指南中验证支持的字典源列表。

如果使用 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 Cloud 中使用字典，请使用 DDL 查询选项来创建字典，并将字典创建为用户 default。此外，请在Cloud 兼容性指南中验证支持的字典源列表。

配置如下所示

<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。如果自将数据加载到单元格以来经过的时间超过 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))

设置足够大的缓存大小。您需要进行实验以选择单元格数量

1. 设置某个值。
2. 运行查询直到缓存完全满。
3. 使用 system.dictionaries 表评估内存消耗。
4. 增加或减少单元格数量，直到达到所需的内存消耗。

注意

不要使用 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);

该键必须只有一个 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。此外，请在Cloud 兼容性指南中验证支持的字典源列表。

<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、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 Cloud 中使用字典，请使用 DDL 查询选项来创建字典，并将字典创建为用户 default。此外，请在Cloud 兼容性指南中验证支持的字典源列表。

字典可以从许多不同的来源连接到 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
  • ODBC
  • MySQL
  • ClickHouse
  • MongoDB
  • Redis
  • Cassandra
  • PostgreSQL

本地文件

设置示例

<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 创建具有可执行源的字典；否则，DB 用户将能够在 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，则将在 user_scripts_path 指定的 user_scripts 文件夹中搜索 command。可以使用空格分隔符指定其他脚本参数。示例：script_name arg1 arg2。如果 execute_direct = 0，则 command 将作为 bin/sh -c 的参数传递。默认值为 1。可选参数。
• send_chunk_header - 控制在将数据块发送到处理之前是否发送行计数。可选。默认值为 false。

该字典源只能通过 XML 配置进行配置。禁用通过 DDL 创建具有可执行源的字典，否则，DB 用户将能够在 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 刷新字典数据 部分以了解更多信息。
• background_reconnect – 如果连接失败，则在后台重新连接到副本。可选参数。
• 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 – 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 主机。如果是本地主机，则在没有任何网络活动的情况下处理查询。为了提高容错能力，您可以创建一个 Distributed 表并在后续配置中输入它。
• 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 – 键空间（数据库）的名称。
• 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 字段之一。

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。此外，请在Cloud 兼容性指南中验证支持的字典源列表。

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>

配置字段

• 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* 函数的查询，tuple 作为键传递。示例：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	标志，指示是否为 MongoDB 文档按 ObjectID 执行查询。 默认值：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 Cloud 中使用字典，请使用 DDL 查询选项来创建字典，并将字典创建为用户 default。此外，请在Cloud 兼容性指南中验证支持的字典源列表。

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

配置多边形字典时，键必须具有以下两种类型之一

• 简单多边形。它是点的数组。
• MultiPolygon。它是多边形的数组。每个多边形是点的二维数组。此数组的第一个元素是多边形的外边界，随后的元素指定要从中排除的区域。

点可以指定为数组或其坐标的元组。在当前实现中，仅支持二维点。

用户可以上传 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 │
└─────────────────────────────────┴───────┘

正则表达式树字典

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

在 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 树的结构。例如

- 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）。在查询执行期间，反向引用将替换为匹配的捕获组。
• child nodes：regexp 树节点的子节点列表，每个子节点都有自己的属性和（可能）子节点。字符串匹配以深度优先的方式进行。如果字符串与 regexp 节点匹配，则字典将检查它是否也与节点的子节点匹配。如果是这种情况，则分配最深层匹配节点的属性。子节点的属性将覆盖父节点中同名的属性。YAML 文件中子节点的名称可以是任意的，例如上面示例中的 versions。

Regexp 树字典仅允许使用函数 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 配置文件，我们可以将 regexp 树字典用作用户代理字符串解析器。我们支持 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 中使用 regexp 树字典，首先在 ClickHouse 开源中从 YAML 文件本地创建一个 regexp 树字典，然后使用 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。此处记录的功能在 ClickHouse Cloud 服务中不可用。有关更多信息，请参阅 ClickHouse Cloud 兼容性指南。

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：TabSeparated（无标题行），列

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

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

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

平面数组用于存储在 RAM 中。因此，ID 不应超过一百万。

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

我们建议定期更新地理基础信息字典。在更新期间，生成新文件并将它们写入单独的位置。当一切准备就绪时，将它们重命名为服务器使用的文件。

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