跳到主要内容
跳到主要内容

字典

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

ClickHouse 支持用于处理字典的特殊函数，这些函数可以在查询中使用。使用函数与字典进行操作比使用与参考表的 JOIN 更加简单和高效。

ClickHouse 支持：

教程

如果您是 ClickHouse 字典的初学者，我们有一份教程涵盖这一主题。请查看这里。

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

ClickHouse：

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

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

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

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

  • 字典的状态。
  • 配置参数。
  • 例如为字典分配的 RAM 数量或自字典成功加载以来的查询次数等度量。
提示

如果您在使用 ClickHouse Cloud 的字典，请使用 DDL 查询选项来创建您的字典，并以用户 default 的身份创建您的字典。 此外，请在 Cloud Compatibility guide 中验证支持的字典源列表。

使用 DDL 查询创建字典​

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

  • 不会向服务器配置文件中添加额外记录
  • 字典可以作为一类实体（如表或视图）进行操作
  • 可以直接使用熟悉的 SELECT 进行数据读取，而不是使用字典表函数
  • 字典可以很容易地重命名

使用配置文件创建字典​

Not supported in ClickHouse Cloud
备注

使用配置文件创建字典在 ClickHouse Cloud 中不可用。请使用 DDL（见上文），并以用户 default 创建字典。

字典配置文件的格式如下：

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

备注

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

配置字典​

提示

如果您在使用 ClickHouse Cloud 的字典，请使用 DDL 查询选项来创建您的字典，并以用户 default 的身份创建您的字典。 此外，请在 Cloud Compatibility guide 中验证支持的字典源列表。

如果字典是使用 xml 文件配置的，那么字典配置具有以下结构：

相应的DDL 查询具有以下结构：

在内存中存储字典​

有多种方法可以在内存中存储字典。

我们推荐flat、hashed 和 complex_key_hashed，这些方法提供最佳的处理速度。

由于可能会造成性能不佳及在选择最佳参数时的困难，因此不推荐使用缓存。请在cache部分中了解更多信息。

有几种方法可以提升字典性能：

  • 在 GROUP BY 后调用处理字典的函数。
  • 将要提取的属性标记为可注入。如果不同的密钥对应不同的属性值，则该属性称为可注入。因此，当 GROUP BY 使用一个通过键提取属性值的函数时，该函数会自动被排除在 GROUP BY 之外。

ClickHouse 会对字典处理错误产生异常。错误示例：

  • 无法加载所访问的字典。
  • 查询cached字典时出错。

您可以在system.dictionaries表中查看字典及其状态的列表。

提示

如果您在使用 ClickHouse Cloud 的字典，请使用 DDL 查询选项来创建您的字典，并以用户 default 的身份创建您的字典。 此外，请在 Cloud Compatibility guide 中验证支持的字典源列表。

配置看起来如下：

相应的DDL 查询：

没有“complex-key*”字样的字典在布局中具有UInt64类型的键，“complex-key*”字典则具有复合键（复杂，带任意类型）。

XML 字典中的UInt64键通过 <id> 标签进行定义。

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

复合 complex 键的 XML 字典通过 <key> 标签定义。

复合键（键有一个String类型元素）的配置示例：

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

flat​

字典完全以平面数组的形式存储在内存中。字典使用多少内存？该量与最大密钥的大小（所占空间）成比例。

字典键为UInt64类型，值被限制为 max_array_size（默认为 500,000）。如果在创建字典时发现更大的密钥，ClickHouse 会抛出异常并且不会创建字典。字典平面数组的初始大小由 initial_array_size 设置控制（默认为1024）。

所有类型的源都被支持。在更新时，数据（来自文件或表）会被完全读取。

此方法提供了在所有可用字典存储方法中最佳的性能。

配置示例：

或者

hashed​

字典完全以哈希表的形式存储在内存中。字典可以包含任意数量的元素和任意标识符。在实际应用中，键的数量可以达到数千万。

字典的键为UInt64类型。

所有类型的源都被支持。在更新时，数据（来自文件或表）会被完全读取。

配置示例：

或者

配置示例：

或者

sparse_hashed​

类似于 hashed，但使用更少的内存，换取更多的 CPU 使用。

字典的键为UInt64类型。

配置示例：

或者

也可以为这种类型的字典使用 shards，而且这对 sparse_hashed 比对 hashed 更为重要，因为 sparse_hashed 较慢。

complex_key_hashed​

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

配置示例：

或者

complex_key_sparse_hashed​

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

配置示例：

或者

hashed_array​

字典完全存储在内存中。每个属性存储在数组中。键属性存储为哈希表，其中值是属性数组的索引。字典可以包含任意数量的元素和任意标识符。在实际应用中，键的数量可以达到数千万。

字典的键为UInt64类型。

所有类型的源都被支持。在更新时，数据（来自文件或表）会被完全读取。

配置示例：

或者

complex_key_hashed_array​

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

配置示例：

或者

range_hashed​

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

字典的键为UInt64类型。 这种存储方法的单独部分也适用于其他任意数值范围。

示例：表中包含每个广告主的折扣，格式如下：

要使用日期范围的样本，请在结构中定义range_min和range_max元素。这些元素必须包含name和type元素（如果未指定type，将使用默认类型 - Date）。type可以是任何数值类型（Date / DateTime / UInt64 / Int32 / 其他）。

备注

range_min 和 range_max 的值应符合 Int64 类型。

示例：

或者

要处理这些字典，需要将额外参数传递给 dictGet 函数，以选择范围：

查询示例：

此函数返回指定 ids 和包含所传日期的日期范围的值。

算法细节：

  • 如果未找到 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）作为开放值。

配置示例：

或者

配置示例，带有重叠范围和开放范围：

complex_key_range_hashed​

字典以哈希表的方式存储在内存中，具有有序的范围数组及其对应的值（见range_hashed）。这种存储类型用于复合键。

配置示例：

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 的子查询，并在外部调用字典函数。

所有类型的源都被支持。

设置示例：

或者

设置一个足够大的缓存大小。您需要尝试选择单元数量：

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

不要将 ClickHouse 作为源，因为处理随机读取查询时速度较慢。

complex_key_cache​

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

ssd_cache​

类似于 cache，但将数据存储在 SSD 上并将索引存储在 RAM 中。对于 SSD 缓存字典，也可以应用与更新队列相关的缓存字典设置。

字典的键为UInt64类型。

或者

complex_key_ssd_cache​

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

direct​

字典不存储在内存中，直接在处理请求时访问源。

字典的键为UInt64类型。

支持所有类型的源，但本地文件除外。

配置示例：

或者

complex_key_direct​

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

ip_trie​

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

示例

假设我们在 ClickHouse 中有一个包含我们的 IP 前缀及其映射的表：

让我们为此表定义一个 ip_trie 字典。ip_trie 布局要求使用复合键：

或者

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

语法为：

该函数接受 UInt32 作为 IPv4，或 FixedString(16) 作为 IPv6。例如：

其他类型目前不被支持。该函数返回与此 IP 地址相应的前缀的属性。如果存在重叠前缀，将返回最具体的前缀。

数据必须完全适合内存。

使用生存时间刷新字典数据​

ClickHouse 会根据 LIFETIME 标签定期更新字典（以秒为单位定义）。 LIFETIME 是完全下载字典的更新间隔以及缓存字典的失效间隔。

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

设置示例：

提示

如果您在使用 ClickHouse Cloud 的字典，请使用 DDL 查询选项来创建您的字典，并以用户 default 的身份创建您的字典。 此外，请在 Cloud Compatibility guide 中验证支持的字典源列表。

或者

设置 <lifetime>0</lifetime> (LIFETIME(0)) 将阻止字典更新。

您可以为更新设置一个时间间隔，ClickHouse 将在此范围内选择一个均匀随机的时间。这是为了在多个服务器上更新时分散字典源的负载。

设置示例：

或者

如果 <min>0</min> 和 <max>0</max>，ClickHouse 不会因超时而重新加载字典。 在这种情况下，如果字典配置文件发生变化或执行了 SYSTEM RELOAD DICTIONARY 命令，ClickHouse 可以更早地重新加载字典。

在更新字典时，ClickHouse 服务器根据源类型应用不同的逻辑：

  • 对于文本文件，它会检查修改时间。如果时间与先前记录的时间不同，则更新字典。
  • 默认情况下，来自其他源的字典每次都更新。

对于其他源（ODBC、PostgreSQL、ClickHouse 等），您可以设置一个查询，仅在字典确实发生变化时才会更新，而不是每次都更新。为此，请按照以下步骤操作：

  • 字典表必须具有一个字段，该字段在源数据更新时始终会发生变化。
  • 源的设置必须指定一个查询，该查询检索变化的字段。ClickHouse 服务器将查询结果解释为一行，如果这行与其先前状态不同，字典会被更新。在源的设置中指定查询为 <invalidate_query> 字段。

设置示例：

或者

对于 Cache、ComplexKeyCache、SSDCache 和 SSDComplexKeyCache 字典，支持同步和异步更新。

同样，Flat、Hashed、ComplexKeyHashed 字典也可以只请求自上次更新后发生变化的数据。如果在字典源配置中指定了 update_field，更新数据请求时将把上次更新时间的值（以秒为单位）添加到请求中。根据源类型（可执行文件、HTTP、MySQL、PostgreSQL、ClickHouse 或 ODBC），在从外部源请求数据之前，将对 update_field 应用不同的逻辑。

  • 如果源是 HTTP，则将 update_field 作为查询参数添加，参数值为上次更新时间。
  • 如果源是可执行文件，则将 update_field 作为可执行脚本参数添加，参数值为上次更新时间。
  • 如果源是 ClickHouse、MySQL、PostgreSQL、ODBC，则将有一个附加的 WHERE 部分，其中 update_field 被比较为大于或等于上次更新时间。
    • 默认情况下，此 WHERE 条件在 SQL 查询的最高级别进行检查。或者，可以在查询中的任何其他 WHERE 子句中使用 {condition} 关键字检查条件。示例：

如果设置了 update_field 选项，可以设置附加选项 update_lag。 update_lag 选项的值在请求更新数据之前将从上次更新时间中减去。

设置示例：

或者

字典源​

提示

如果您在使用 ClickHouse Cloud 的字典，请使用 DDL 查询选项来创建您的字典，并以用户 default 的身份创建您的字典。 此外，请在 Cloud Compatibility guide 中验证支持的字典源列表。

字典可以从许多不同的源连接到 ClickHouse。

如果通过 xml 文件配置字典，配置看起来像这样：

在 DDL 查询 的情况下，上述配置将如下所示：

源在 source 部分中配置。

对于 本地文件、可执行文件、HTTP(S)、ClickHouse 源类型，可以使用可选设置：

或者

源类型 (source_type) 包括：

本地文件​

设置示例：

或者

设置字段：

  • path – 文件的绝对路径。
  • format – 文件格式。支持格式中描述的所有格式。

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

另见

可执行文件​

与可执行文件的工作取决于字典在内存中的存储方式。如果字典使用 cache 和 complex_key_cache 存储，ClickHouse 通过向可执行文件的 STDIN 发送请求来请求所需的键。否则，ClickHouse 启动可执行文件，并将其输出视为字典数据。

设置示例：

设置字段：

  • command — 可执行文件的绝对路径或文件名（如果命令目录在 PATH 中）。
  • format — 文件格式。支持格式中描述的所有格式。
  • command_termination_timeout — 可执行脚本应包含主要的读写循环。在字典被销毁后，管道关闭，而可执行文件将有 command_termination_timeout 秒的时间以进行关闭，之后 ClickHouse 将向子进程发送 SIGTERM 信号。command_termination_timeout 以秒为单位指定。默认值为 10。可选参数。
  • command_read_timeout - 从命令标准输出读取数据的超时时间（以毫秒为单位）。默认值为 10000。可选参数。
  • command_write_timeout - 向命令标准输入写入数据的超时时间（以毫秒为单位）。默认值为 10000。可选参数。
  • implicit_key — 可执行源文件只能返回值，与请求的键之间的对应关系是根据结果中的行顺序隐式确定的。默认值为 false。
  • execute_direct - 如果 execute_direct = 1，则将在由 user_scripts_path 指定的 user_scripts 文件夹内搜索 command。可以使用空格分隔指定附加脚本参数。例如：script_name arg1 arg2。如果 execute_direct = 0，command 作为 bin/sh -c 的参数传递。默认值为 0。可选参数。
  • send_chunk_header - 控制是否在发送数据块进行处理之前发送行数。可选。默认值为 false。

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

可执行池​

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

可执行池将生成一个包含指定命令的进程池，并保持它们处于运行状态直到它们退出。该程序应在 STDIN 可用时读取数据并将结果输出到 STDOUT。它可以在 STDIN 上等待下一个数据块。ClickHouse 在处理数据块后不会关闭 STDIN，但在需要时会传送另一块数据。可执行脚本应就此数据处理方式做好准备——它应轮询 STDIN 并提前刷新数据到 STDOUT。

设置示例：

设置字段：

  • command — 可执行文件的绝对路径或文件名（如果程序目录已写入 PATH）。
  • format — 文件格式。支持"格式"中描述的所有格式。
  • pool_size — 池的大小。如果指定 pool_size 为 0，则没有池大小限制。默认值为 16。
  • command_termination_timeout — 可执行脚本应包含主要的读写循环。在字典被销毁后，管道关闭，而可执行文件将有 command_termination_timeout 秒的时间进行关闭，然后 ClickHouse 将发送 SIGTERM 信号给子进程。以秒为单位指定。默认值为 10。可选参数。
  • max_command_execution_time — 处理数据块期间可执行脚本命令的最大执行时间。以秒为单位指定。默认值为 10。可选参数。
  • command_read_timeout - 从命令标准输出读取数据的超时时间，以毫秒为单位。默认值为 10000。可选参数。
  • command_write_timeout - 向命令标准输入写入数据的超时时间，以毫秒为单位。默认值为 10000。可选参数。
  • implicit_key — 可执行源文件只能返回值，与请求的键之间的对应关系是根据结果中的行顺序隐式确定的。默认值为 false。可选参数。
  • execute_direct - 如果 execute_direct = 1，则将在由 user_scripts_path 指定的 user_scripts 文件夹内搜索 command。可以使用空格分隔指定附加脚本参数。例如：script_name arg1 arg2。如果 execute_direct = 0，command 作为 bin/sh -c 的参数传递。默认值为 1。可选参数。
  • send_chunk_header - 控制是否在发送数据块进行处理之前发送行数。可选。默认值为 false。

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

HTTP(S)​

与 HTTP(S) 服务器的交互取决于字典在内存中的存储方式。如果字典使用 cache 和 complex_key_cache 存储，ClickHouse 通过 POST 方法发送请求以请求必要的键。

设置示例：

或者

为了让 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 驱动程序的数据库。

设置示例：

或者

设置字段：

  • db – 数据库的名称。如果数据库名称在 <connection_string> 参数中设置，则省略它。
  • table – 表和模式的名称（如果存在）。
  • connection_string – 连接字符串。
  • invalidate_query – 检查字典状态的查询。可选参数。更多信息请查看使用生存时间刷新字典数据部分。
  • background_reconnect – 如果连接失败则在后台重新连接到副本。可选参数。
  • query – 自定义查询。可选参数。
备注

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

ClickHouse 从 ODBC 驱动程序接收引号符号，并对所有设置在查询到驱动程序中引用，因此必须根据数据库中表名的大小写相应地设置表名。

如果在使用 Oracle 时遇到编码问题，请查看相应的常见问题条目。

ODBC 字典功能的已知漏洞​
备注

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

不安全使用示例

让我们为 PostgreSQL 配置 unixODBC。/etc/odbc.ini 的内容：

如果您随后执行类似于

则 ODBC 驱动程序会将 odbc.ini 中的 USERNAME 和 PASSWORD 的值发送到 some-server.com。

连接 PostgreSQL 的示例​

Ubuntu 操作系统。

安装 unixODBC 和 PostgreSQL 的 ODBC 驱动程序：

配置 /etc/odbc.ini（或 ~/.odbc.ini，如果您以运行 ClickHouse 的用户身份登录）：

在 ClickHouse 中的字典配置：

或者

您可能需要编辑 odbc.ini 以指定带有驱动程序的库的完整路径 DRIVER=/usr/local/lib/psqlodbcw.so。

连接 MS SQL Server 的示例​

Ubuntu 操作系统。

安装用于连接 MS SQL 的 ODBC 驱动程序：

配置驱动程序：

备注：

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

在 ClickHouse 中配置字典：

或者

MySQL​

设置示例：

或者

设置字段：

  • 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 – 自定义查询。可选参数。

备注

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

备注

没有显式的 secure 参数。在建立 SSL 连接时，安全性是强制性的。

MySQL 可以通过本地套接字连接。要实现此目的，请设置 host 和 socket。

设置示例：

或者

ClickHouse​

设置示例：

或者

设置字段：

  • host – ClickHouse 主机。如果是本地主机，则查询在没有网络活动的情况下处理。为了提高容错性，您可以创建一个分布式表，并在后续配置中输入它。
  • port – ClickHouse 服务器的端口。
  • user – ClickHouse 用户的名称。
  • password – ClickHouse 用户的密码。
  • db – 数据库的名称。
  • table – 表的名称。
  • where – 选择标准。可以省略。
  • invalidate_query – 检查字典状态的查询。可选参数。更多信息请查看使用生存时间刷新字典数据部分。
  • secure - 在连接时使用 ssl。
  • query – 自定义查询。可选参数。
备注

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

MongoDB​

设置示例：

或者

或者

设置字段：

  • host – MongoDB 主机。
  • port – MongoDB 服务器的端口。
  • user – MongoDB 用户的名称。
  • password – MongoDB 用户的密码。
  • db – 数据库的名称。
  • collection – 集合的名称。
  • options - MongoDB 连接字符串选项（可选参数）。

或者

设置字段：

  • uri - 建立连接的 URI。
  • collection – 集合的名称。

有关引擎的更多信息

或

设置字段：

  • host – Redis 主机。
  • port – Redis 服务器上的端口。
  • storage_type – 内部 Redis 存储结构，用于处理键。simple 用于简单源和哈希单键源，hash_map 用于具有两个键的哈希源。范围源和复杂键的缓存源不支持。可省略，默认值为 simple。
  • db_index – Redis 逻辑数据库的特定数字索引。可省略，默认值为 0。

Cassandra​

设置示例如下：

设置字段：

  • 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​

设置示例如下：

或

设置字段：

  • 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 – 用于检查字典状态的查询。可选参数。有关更多信息，请参见 使用生存时间刷新字典数据 部分。
  • background_reconnect – 如果连接失败，则在后台重新连接到副本。可选参数。
  • query – 自定义查询。可选参数。
备注

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

Null​

一个特殊源，可用于创建虚拟（空）字典。这种字典可以用于测试，或在使用划分表的节点上与分离的数据和查询节点的设置一起使用。

Dictionary Key and Fields​

提示

如果您在使用 ClickHouse Cloud 的字典，请使用 DDL 查询选项来创建您的字典，并以用户 default 的身份创建您的字典。 此外，请在 Cloud Compatibility guide 中验证支持的字典源列表。

structure 子句描述字典键及可用于查询的字段。

XML 描述：

属性在以下元素中描述：

  • <id> — 键列
  • <attribute> — 数据列：可以有多个属性。

DDL 查询：

属性在查询主体中描述：

  • PRIMARY KEY — 键列
  • AttrName AttrType — 数据列。可以有多个属性。

Key​

ClickHouse 支持以下类型的键：

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

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

备注

不得将键描述为属性。

Numeric Key​

类型：UInt64。

配置示例：

配置字段：

  • name – 键列的名称。

对于 DDL 查询：

  • PRIMARY KEY – 键列的名称。

Composite Key​

键可以是任何字段类型的 tuple。此时的 layout 必须为 complex_key_hashed 或 complex_key_cache。

提示

复合键可以包含单个元素。这使得可以将字符串用作键，例如。

键结构在元素 <key> 中设置。键字段以与字典 attributes 相同的格式指定。示例：

或

对于 dictGet* 函数的查询，tuple 作为键被传递。例如：dictGetString('dict_name', 'attr_name', tuple('string for field1', num_for_field2))。

Attributes​

配置示例：

或

配置字段：

标签描述必需
name列名称。是
typeClickHouse 数据类型：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 类型（请参见前面的类型描述行）。		是
expressionClickHouse 在值上执行的 表达式。
该表达式可以是远程 SQL 数据库中的列名称。因此，您可以使用它为远程列创建别名。

默认值：没有表达式。		否
hierarchical如果为 true，则属性包含当前键的父键值。参见 层次字典。

默认值：false。		否
injective显示 id -> 属性 映像是否是 一一映射 的标志。
如果为 true，ClickHouse 可以在 GROUP BY 子句之后自动放置 injection 请求。通常，这会显著减少此类请求的数量。

默认值：false。		否
is_object_id显示查询是否通过 ObjectID 为 MongoDB 文档执行的标志。

默认值：false。

Hierarchical Dictionaries​

ClickHouse 支持具有 数值键 的层次字典。

下面是层次结构的例子：

这个层次可以表示为以下字典表。

region_idparent_regionregion_name
10俄罗斯
21莫斯科
32中心
40英国
54伦敦

此表包含一列 parent_region，其中包含该元素最近父元素的键。

ClickHouse 支持外部字典属性的层次属性。此属性允许您配置层次字典，类似于上面所述。

dictGetHierarchy 函数允许您获取元素的父链。

针对我们的示例，字典的结构可以如下：

多边形字典​

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

多边形字典配置示例：

提示

如果您在使用 ClickHouse Cloud 的字典，请使用 DDL 查询选项来创建您的字典，并以用户 default 的身份创建您的字典。 此外，请在 Cloud Compatibility guide 中验证支持的字典源列表。

对应的 DDL-query：

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

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

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

用户可以以 ClickHouse 支持的所有格式上传自己的数据。

有 3 种类型的 内存存储 可用：

  • POLYGON_SIMPLE。这是一个简单的实现，对于每个查询，将对所有多边形进行线性遍历，并在不使用额外索引的情况下检查每个多边形的成员关系。

  • POLYGON_INDEX_EACH。为每个多边形建立一个单独的索引，这在大多数情况下可以快速检查它是否属于（针对地理区域进行了优化）。此外，在考虑的区域上叠加了一个网格，这大大缩小了考虑的多边形数量。网格通过递归将单元格划分为 16 个相等部分创建，并使用两个参数进行配置。当递归深度达到 MAX_DEPTH 或当单元格交叉不超过 MIN_INTERSECTIONS 的多边形时，划分停止。响应查询时，会对应到一个单元格，并交替访问存储在其中的多边形索引。

  • POLYGON_INDEX_CELL。此放置也创建上述描述的网格。可用相同的选项。对于每个工作表单元格，建立一个索引，涵盖所有落入其中的多边形片段，这使得可以快速响应请求。

  • POLYGON。等同于 POLYGON_INDEX_CELL。

字典查询是使用标准的 函数 来执行字典操作。一个重要的区别在于，这里键将是您希望找到包含这些点的多边形的点。

示例

与上述定义的字典一起工作示例：

执行最后命令的结果是，对于 'points' 表中的每个点，都会找到一个包含该点的最小区域多边形，并输出请求的属性。

示例

您可以通过 SELECT 查询从多边形字典中读取列，只需在字典配置或相应的 DDL-query 中启用 store_polygon_key_column = 1。

查询：

结果：

正则表达式树字典​

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

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

正则表达式树字典在 ClickHouse 开源中使用 YAMLRegExpTree 源定义，该源提供了包含正则表达式树的 YAML 文件的路径。

字典源 YAMLRegExpTree 表示正则表达式树的结构。例如：

此配置由正则表达式树节点的列表组成。每个节点具有以下结构：

  • regexp：节点的正则表达式。
  • attributes：用户定义的字典属性的列表。在此示例中，有两个属性：name 和 version。第一个节点定义这两个属性。第二个节点仅定义属性 name。属性 version 由第二个节点的子节点提供。
    • 属性的值可以包含 反向引用，引用匹配正则表达式的捕获组。在此示例中，第一个节点的属性 version 的值包含了对捕获组 (\d+[\.\d]*) 的反向引用 \1。反向引用数字范围从 1 到 9，并写为 $1 或 \1（针对数字 1）。反向引用在查询执行期间被匹配的捕获组替换。
  • child nodes：正则表达式树节点的子列表，每个都有其自己的属性和（可能的）子节点。字符串匹配以深度优先的方式进行。如果字符串与正则表达式节点匹配，则字典检查它是否也与节点的子节点匹配。如果是这样，则分配最深匹配节点的属性。子节点的属性会覆盖同名的父节点属性。YAML 文件中子节点的名称可以是任意的，例如上面的例子中的 versions。

正则表达式树字典仅允许使用 dictGet、dictGetOrDefault 和 dictGetAll 函数访问。

示例：

结果：

在此情况下，我们首先匹配顶层第二个节点中的正则表达式 \d+/tclwebkit(?:\d+[\.\d]*)。字典接着继续查找子节点，并发现该字符串也匹配 3[12]/tclwebkit。结果，属性 name 的值为 Android（在第一层定义），属性 version 的值为 12（在子节点中定义）。

借助强大的 YAML 配置文件，我们可以使用正则表达式树字典作为用户代理字符串解析器。我们支持 uap-core，并演示如何在功能测试中使用它 02504_regexp_dictionary_ua_parser

收集属性值​

有时，返回多个正则表达式匹配的值比仅返回叶节点的值更有用。在这些情况下，可以使用专业的 dictGetAll 函数。如果节点具有类型为 T 的属性值，dictGetAll 将返回一个 Array(T)，其中包含零个或多个值。

默认情况下，每个键返回的匹配数量是没有上限的。可以通过将可选的第四个参数传递给 dictGetAll 来设置上限。该数组按 拓扑顺序 填充，意味着子节点在父节点之前，兄弟节点按照源中的顺序跟随。

示例：

结果：

匹配模式​

模式匹配行为可以通过某些字典设置进行修改：

  • 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 文件。

CSV 文件的内容是：

导出文件的模式为：

  • id UInt64：正则表达式树节点的 ID。
  • parent_id UInt64：节点的父节点 ID。
  • regexp String：正则表达式字符串。
  • keys Array(String)：用户定义的属性名称。
  • values Array(String)：用户定义属性的值。

要在 ClickHouse Cloud 中创建字典，首先创建结构如下的表 regexp_dictionary_source_table：

然后通过以下方式更新本地 CSV：

您可以参见 插入本地文件 获取更多细节。初始化源表后，我们可以通过表源创建 RegexpTree：

嵌入字典​

Not supported in ClickHouse Cloud
备注

此页面不适用于 ClickHouse Cloud。此处文档记录的功能在 ClickHouse Cloud 服务中不可用。 有关更多信息，请参阅 ClickHouse 的 Cloud Compatibility 指南。

ClickHouse 包含用于处理地理基准的内置功能。

这允许您：

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

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

内部字典在默认软件包中禁用。 要启用它们，请在服务器配置文件中取消注释参数 path_to_regions_hierarchy_file 和 path_to_regions_names_files。

地理基准从文本文件加载。

将 regions_hierarchy*.txt 文件放入 path_to_regions_hierarchy_file 目录。此配置参数必须包含到 regions_hierarchy.txt 文件（默认区域层次）的路径，其他文件（regions_hierarchy_ua.txt）必须位于同一目录中。

将 regions_names_*.txt 文件放入 path_to_regions_names_files 目录。

您还可以自己创建这些文件。文件格式如下：

regions_hierarchy*.txt：制表符分隔（无标题），列：

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

regions_names_*.txt：制表符分隔（无标题），列：

  • 区域 ID (UInt32)
  • 区域名称 (String) — 不能包含制表符或换行符，连转义字符也不可以。

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

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

我们建议定期更新地理基准字典。在更新期间，生成新文件并将其写入不同位置。准备好后，将它们重命名为服务器使用的文件。

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