字典

字典是一个映射（key -> attributes），方便各种类型的引用列表。

ClickHouse 支持用于查询的特殊函数。使用函数与引用表的 JOIN 相比，使用字典更容易、更有效。

ClickHouse 支持：

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

教程

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

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

ClickHouse：

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

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

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

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

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

tip

If you are using a dictionary with ClickHouse Cloud please use the DDL query option to create your dictionaries, and create your dictionary as user default. Also, verify the list of supported dictionary sources in the Cloud Compatibility guide.

使用 DDL 查询创建字典

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

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

使用配置文件创建字典

note

使用配置文件创建字典不适用于 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>

您可以配置同一文件中的任意数量的字典。

note

您可以通过在 SELECT 查询中描述它来将值转换为小型字典（请参见 transform 函数）。此功能与字典无关。

配置字典

tip

如果使用 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表中查看字典的列表和其状态。

tip

配置如下：

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

相应的DDL 查询：

CREATE DICTIONARY (...)
...
LAYOUT(LAYOUT_TYPE(param value)) -- layout settings
...

在 XML 字典中，没有 complex-key* 一词的字典具有 UInt64 类型的键，complex-key* 字典具有复合键（复杂键，具有任意类型）。

XML 字典中的 UInt64 键使用 <id> 标记定义。

配置示例（列 key_column 具有 UInt64 类型）：

...
<structure>
    <id>
        <name>key_column</name>
    </id>
...

复合 complex 键 XML 字典使用 <key> 标记定义。

复合键的配置示例（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>
    <!-- 如果 shards 大于 1（默认为 `1`），则字典将并行加载数据，如果字典中有大量元素，则这很有用。 -->
    <shards>10</shards>

    <!-- 并行队列中块的最大负载大小。

         由于并行加载的瓶颈是重新哈希，因此为了避免因线程正在进行重新哈希而导致停滞，需要有一些负载大小。

         10000 是内存和速度之间的良好平衡。
         即使对于 10e10 个元素，也可以处理所有负载而不会饿死。 -->
    <shard_load_queue_backlog>10000</shard_load_queue_backlog>

    <!-- 哈希表的最大负载因子，值越大，内存利用率越高（浪费的内存越少），但读取/性能可能会下降。

         有效值：[0.5, 0.99]
         默认值：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 而言更为重要，因为 sparse_hashed 比 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 │
└───────────────┴─────────────────────┴───────────────────┴────────┘

要使用日期范围的示例，请在structure中定义 range_min 和 range_max 元素。这些元素必须包含 name 和 type 元素（如果 type 是Date 或 DateTime，则还需要 time_zone 元素）。

配置示例：

<layout>
  <range_hashed>
    <range_min>
        <name>discount_start_date</name>
        <type>Date</type>
    </range_min>
    <range_max>
        <name>discount_end_date</name>
        <type>Date</type>
    </range_max>
  </range_hashed>
</layout>

或

LAYOUT(RANGE_HASHED([RANGE_MIN discount_start_date Date] [RANGE_MAX discount_end_date Date]))

complex_key_range_hashed

此存储类型用于复合键。类似于 range_hashed。

配置示例：

<layout>
  <complex_key_range_hashed>
    <range_min>
        <name>discount_start_date</name>
        <type>Date</type>
    </range_min>
    <range_max>
        <name>discount_end_date</name>
        <type>Date</type>
    </range_max>
  </complex_key_range_hashed>
</layout>

或

LAYOUT(COMPLEX_KEY_RANGE_HASHED([RANGE_MIN discount_start_date Date] [RANGE_MAX discount_end_date Date]))

cache

字典的缓存存储在内存中。字典可以包含任意数量的具有任意标识符的元素。在实践中，键的数量可以达到数千万个。

字典键具有 UInt64 类型。

支持所有类型的源。在更新时，会读取数据（从文件或表）的全部内容。

配置示例：

<layout>
  <cache>
    <!-- <max_size>1000000</max_size> -->
    <!-- <load_all>1</load_all> -->
  </cache>
</layout>

或

LAYOUT(CACHE([MAX_SIZE 1000000] [LOAD_ALL 1]))

complex_key_cache

此存储类型用于复合键。类似于 cache。

配置示例：

<layout>
  <complex_key_cache>
    <!-- <max_size>1000000</max_size> -->
    <!-- <load_all>1</load_all> -->
  </complex_key_cache>
</layout>

或

LAYOUT(COMPLEX_KEY_CACHE([MAX_SIZE 1000000] [LOAD_ALL 1]))

ssd_cache

类似于缓存，但将数据存储在固态硬盘上，索引存储在内存中。所有与更新队列相关的缓存字典设置也可以应用于固态硬盘缓存字典。

字典键具有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是完全下载的字典的更新间隔，也是缓存字典的失效间隔。

在更新期间，仍然可以查询字典的旧版本。字典更新（除了首次加载字典时）不会阻塞查询。如果在更新期间发生错误，错误将写入服务器日志，并且查询可以继续使用字典的旧版本。如果字典更新成功，旧版本的字典将被原子替换。

设置示例：

tip

<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将作为查询参数添加，上次更新时间作为参数值。
如果源是可执行文件，则update_field将作为可执行脚本参数添加，上次更新时间作为参数值。

如果源是ClickHouse、MySQL、PostgreSQL、ODBC，则将在WHERE的其他部分中检查update_field，其中update_field与上次更新时间进行比较。

默认情况下，此WHERE条件在SQL-Query的最高级别中检查。或者，可以使用{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))
...

字典源

tip

可以从许多不同的源连接到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

Types of sources (source_type):

Local file
Executable File
Executable Pool
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 – 文件格式。Formats中描述的所有格式都受支持。

当通过DDL命令（CREATE DICTIONARY ...）创建具有源FILE的字典时，源文件需要位于user_files目录中，以防止DB用户访问ClickHouse节点上的任意文件。

另请参阅

Dictionary function

可执行文件

与可执行文件的工作取决于字典存储在内存中的方式。如果使用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 — 可执行脚本应该包含一个主读写循环。在字典被销毁后，管道将被关闭，并且可执行文件将在 ClickHouse 发送 SIGTERM 信号给子进程之前有 command_termination_timeout 秒的时间来关闭。command_termination_timeout 以秒为单位指定。默认值为 10。可选参数。
command_read_timeout - 从命令标准输出读取数据的超时时间，以毫秒为单位。默认值为 10000。可选参数。
command_write_timeout - 将数据写入命令标准输入的超时时间，以毫秒为单位。默认值为 10000。可选参数。
implicit_key — 可执行源文件只能返回值，并且对于请求的键的对应关系是隐式确定的 —— 由结果中行的顺序决定。默认值为 false。
execute_direct - 如果 execute_direct = 1，那么 command 将在由 user_scripts_path 指定的 user_scripts 文件夹中搜索。额外的脚本参数可以使用空格分隔符指定。示例：script_name arg1 arg2。如果 execute_direct = 0，则 command 作为参数传递给 bin/sh -c。默认值为 0。可选参数。
send_chunk_header - 控制是否在发送数据块之前发送行数。可选。默认值为 false。

只能通过 XML 配置来配置该字典源。通过 DDL 创建具有可执行源的字典是被禁用的；否则，DB用户将能够在 ClickHouse 节点上执行任意二进制文件。

可执行池

可执行池允许从进程池中加载数据。该源不适用于需要从源加载所有数据的字典布局。如果字典使用 cache、complex_key_cache、ssd_cache、complex_key_ssd_cache、direct 或 complex_key_direct 布局进行存储，则可执行池可以工作。

可执行池将生成一个带有指定命令的进程池，并保持它们运行直到退出。程序在 STDIN 可用时应该从 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 — 文件格式。支持 “Formats” 中描述的所有格式。
pool_size — 池大小。如果将 pool_size 指定为 0，则没有池大小限制。默认值为 16。
command_termination_timeout — 可执行脚本应该包含一个主读写循环。在字典被销毁后，管道将被关闭，并且可执行文件将在 ClickHouse 发送 SIGTERM 信号给子进程之前有 command_termination_timeout 秒的时间来关闭。以秒为单位指定。默认值为 10。可选参数。
max_command_execution_time — 每个处理数据块的最大可执行脚本命令执行时间。以秒为单位指定。默认值为 10。可选参数。
command_read_timeout - 从命令标准输出读取数据的超时时间，以毫秒为单位。默认值为 10000。可选参数。
command_write_timeout - 将数据写入命令标准输入的超时时间，以毫秒为单位。默认值为 10000。可选参数。
implicit_key — 可执行源文件只能返回值，并且对于请求的键的对应关系是隐式确定的 —— 由结果中行的顺序决定。默认值为 false。可选参数。
execute_direct - 如果 execute_direct = 1，那么 command 将在由 user_scripts_path 指定的 user_scripts 文件夹中搜索。额外的脚本参数可以使用空格分隔符指定。示例：script_name arg1 arg2。如果 execute_direct = 0，则 command 作为参数传递给 bin/sh -c。默认值为 1。可选参数。
send_chunk_header - 控制是否在发送数据块之前发送行数。可选。默认值为 false。

该字典源只能通过 XML 配置进行配置。禁用通过 DDL 创建具有可执行源的字典，否则，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 – 文件格式。支持 “Formats” 中描述的所有格式。
credentials – 基本的 HTTP 认证。可选参数。
user – 认证所需的用户名。
password – 认证所需的密码。
headers – 用于 HTTP 请求的所有自定义 HTTP 头条目。可选参数。
header – 单个 HTTP 头条目。
name – 用于请求发送的标识符名称。
value – 为特定标识符名称设置的值。

在使用 DDL 命令 (CREATE DICTIONARY ...) 创建字典时，HTTP 字典的远程主机将根据配置文件中 remote_url_allow_hosts 部分的内容进行检查，以防止数据库用户访问任意的 HTTP 服务器。

DBMS

ODBC

你可以使用这种方法连接任何有ODBC驱动的数据库。

设置示例：

<source>
    <odbc>
        <db>DatabaseName</db>
        <table>ShemaName.TableName</table>
        <connection_string>DSN=some_parameters</connection_string>
        <invalidate_query>SQL_QUERY</invalidate_query>
        <query>SELECT id, value_1, value_2 FROM ShemaName.TableName</query>
    </odbc>
</source>

或

SOURCE(ODBC(
    db 'DatabaseName'
    table 'SchemaName.TableName'
    connection_string 'DSN=some_parameters'
    invalidate_query 'SQL_QUERY'
    query 'SELECT id, value_1, value_2 FROM db_name.table_name'
))

设置字段：

db – 数据库的名称。如果在 <connection_string> 参数中设置了数据库名称，则可以省略它。
table – 表的名称和模式（如果存在）。
connection_string – 连接字符串。
invalidate_query – 用于检查字典状态的查询。可选参数。在更新字典部分中了解更多。
query – 自定义查询。可选参数。

note

table 和 query 字段不能同时使用。table 和 query 字段中的任何一个必须声明。

ClickHouse 从 ODBC 驱动程序接收引用符号，并在查询驱动程序时引用所有设置，因此必须根据数据库中的表名情况设置表名。

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

ODBC 字典功能的已知漏洞

note

当通过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 中发送到 some-server.com 的值 USERNAME 和 PASSWORD。

连接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 – 用于检查字典状态的查询。可选参数。在更新字典部分中了解更多。
fail_on_connection_loss – 控制服务器在连接丢失时的行为。如果为 true，则在客户端和服务器之间的连接丢失时立即抛出异常。如果为 false，ClickHouse 服务器在抛出异常之前尝试三次执行查询。请注意，重试会导致响应时间增加。默认值：false。
query – 自定义查询。可选参数。

note

table 或 query 字段不能同时使用。table 或 query 字段中的任何一个必须声明。

note

没有显式参数 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 – 选择条件。条件的语法与 MySQL 中 WHERE 子句的语法相同，例如，id > 10 AND id < 20。可选参数。
invalidate_query – 用于检查字典状态的查询。可选参数。在更新字典部分中了解更多。
secure – 控制是否使用安全连接。可选参数。
query – 自定义查询。可选参数。

note

table 和 query 字段不能同时使用。table 和 query 字段中的任何一个必须声明。

MongoDB

设置示例：

``` xml
<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(
    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 – 用于连接的其他选项。可选参数。

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 中的顺序相同。默认值为 1（第一个键列是分区键，其他键列是聚簇键）。
consistency – 一致性级别。可能的值：One、Two、Three、All、EachQuorum、Quorum、LocalQuorum、LocalOne、Serial、LocalSerial。默认值为 One。
where – 可选的选择条件。
max_threads – 用于从组合键字典中的多个分区加载数据的最大线程数。
query – 自定义查询。可选参数。

note

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 – 用于检查字典状态的查询。可选参数。在更新字典部分中了解更多。
query – 自定义查询。可选参数。

note

table 或 where 字段不能同时使用。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);

字典键和字段

tip

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。

note

您不应将键描述为属性。

数字键

类型：UInt64。

配置示例：

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

配置字段：

name – 键列的名称。

对于 DDL 查询：

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

PRIMARY KEY – 键列的名称。

复合键

键可以是来自任何类型字段的 tuple。在这种情况下，布局必须是 complex_key_hashed 或 complex_key_cache。

tip

复合键可以由单个元素组成。这使得可以使用字符串作为键，例如。

键结构在元素 <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
)

配置字段：

Tag	Description	Required
`name`	Column name.	Yes
`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` 类型。	Yes
`null_value`	不存在元素的默认值。在这个例子中，它是一个空字符串。NULL 值只能用于 `Nullable` 类型（请参见上一行中的类型描述）。	Yes
`expression`	ClickHouse 对值执行的表达式。该表达式可以是远程 SQL 数据库中的列名。因此，您可以使用它来为远程列创建别名。默认值：没有表达式。	No
`hierarchical`	如果设置为 `true`，则该属性包含当前键的父键的值。参见分层字典。默认值：`false`。	No
`injective`	标志，指示 `id -> attribute` 映射是否是单射的。如果设置为 `true`，ClickHouse 可以自动将对具有注入功能的字典的请求放置在 `GROUP BY` 子句之后。通常这会显著减少这类请求的数量。默认值：`false`。	No
`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	Russia
2	1	Moscow
3	2	Center
4	0	Great Britain
5	4	London

此表包含一个列 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>

多边形字典

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

多边形字典配置示例：

tip

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

执行最后一个命令的结果是，在'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 的值由对正则表达式中的捕获组 (\d+[\.\d]*) 的反向引用 \1 组成。反向引用编号从 1 到 9，并且写为 $1 或 \1（对于数字 1）。在查询执行期间，反向引用将被匹配的捕获组替换。
child nodes：正则表达式树节点的子节点列表，每个子节点都有自己的属性和（可能）子节点。字符串匹配按深度优先的方式进行。如果字符串匹配了正则表达式节点，则字典将检查它是否还匹配了节点的子节点。如果是这样，将分配最深层匹配节点的属性。子节点的属性将覆盖父节点的同名属性。在 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](

收集属性值

有时，将匹配的多个正则表达式的值返回，而不仅仅是叶节点的值，是有用的。在这些情况下，可以使用专门的 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);

嵌套字典

note

This page is not applicable to ClickHouse Cloud. The feature documented here is not available in ClickHouse Cloud services. See the ClickHouse Cloud Compatibility guide for more information.

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​

字典键和字段​

键​

数字键​

复合键​

属性​

层次字典​

多边形字典​

正则表达式树字典​

在 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

字典键和字段

键

数字键

复合键

属性

层次字典

多边形字典

正则表达式树字典

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

收集属性值

匹配模式

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

嵌套字典