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

字典

字典是一个映射 (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 查询具有以下结构:

在内存中存储字典

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

我们推荐flathashedcomplex_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_minrange_max元素。这些元素必须包含nametype元素(如果未指定type,将使用默认类型 - Date)。type可以是任何数值类型(Date / DateTime / UInt64 / Int32 / 其他)。

备注

range_minrange_max 的值应符合 Int64 类型。

示例:

或者

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

查询示例:

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

算法细节:

  • 如果未找到 id 或未为 id 找到范围,则返回属性类型的默认值。
  • 如果存在重叠范围且 range_lookup_strategy=min,则返回带有最小 range_min 的匹配范围,如果找到多个范围,则返回带有最小 range_max 的范围,如果再次找到多个范围(多个范围具有相同的 range_minrange_max),则返回它们中的一个随机范围。
  • 如果存在重叠范围且 range_lookup_strategy=max,则返回带有最大 range_min 的匹配范围,如果找到多个范围,则返回带有最大 range_max 的范围,如果再次找到多个范围(多个范围具有相同的 range_minrange_max),则返回它们中的一个随机范围。
  • 如果 range_maxNULL,则该范围是开放的。NULL 被视为最大可能值。对于 range_min 可以使用 1970-01-010(-MAX_INT)作为开放值。

配置示例:

或者

配置示例,带有重叠范围和开放范围:

complex_key_range_hashed

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

配置示例:

cache

字典存储在具有固定数量单元的缓存中。这些单元包含经常使用的元素。

字典的键为UInt64类型。

查询字典时,首先会从缓存中查找。对于每个数据块,所有未在缓存中找到或已过期的键都通过 SELECT attrs... FROM db.table WHERE id IN (k1, k2, ...) 从源请求。收到的数据然后写入缓存。

如果字典中未找到键,则会创建更新缓存任务并将其添加到更新队列中。更新队列的属性可以通过设置 max_update_queue_sizeupdate_queue_push_timeout_millisecondsquery_wait_timeout_millisecondsmax_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> 字段。

设置示例:

或者

对于 CacheComplexKeyCacheSSDCacheSSDComplexKeyCache 字典,支持同步和异步更新。

同样,FlatHashedComplexKeyHashed 字典也可以只请求自上次更新后发生变化的数据。如果在字典源配置中指定了 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_lagupdate_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 节点上的任意文件。

另见

可执行文件

与可执行文件的工作取决于字典在内存中的存储方式。如果字典使用 cachecomplex_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 = 0command 作为 bin/sh -c 的参数传递。默认值为 0。可选参数。
  • send_chunk_header - 控制是否在发送数据块进行处理之前发送行数。可选。默认值为 false

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

可执行池

可执行池允许从进程池加载数据。此源不适用于需要从源加载所有数据的字典布局。可执行池在字典 cachecomplex_key_cachessd_cachecomplex_key_ssd_cachedirectcomplex_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 = 0command 作为 bin/sh -c 的参数传递。默认值为 1。可选参数。
  • send_chunk_header - 控制是否在发送数据块进行处理之前发送行数。可选。默认值为 false

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

HTTP(S)

与 HTTP(S) 服务器的交互取决于字典在内存中的存储方式。如果字典使用 cachecomplex_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 – 自定义查询。可选参数。
备注

tablequery 字段不能一起使用。并且必须声明 tablequery 字段之一。

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

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

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

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

不安全使用示例

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

如果您随后执行类似于

则 ODBC 驱动程序会将 odbc.ini 中的 USERNAMEPASSWORD 的值发送到 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 – 自定义查询。可选参数。

备注

tablewhere 字段不能与 query 字段一起使用。并且必须声明 tablequery 字段之一。

备注

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

MySQL 可以通过本地套接字连接。要实现此目的,请设置 hostsocket

设置示例:

或者

ClickHouse

设置示例:

或者

设置字段:

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

tablewhere 字段不能与 query 字段一起使用。并且必须声明 tablequery 字段之一。

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 – 一致性级别。可选值:OneTwoThreeAllEachQuorumQuorumLocalQuorumLocalOneSerialLocalSerial。默认值为 One
  • where – 可选选择标准。
  • max_threads – 从多个分区加载数据时使用的最大线程数,适用于复合键字典。
  • query – 自定义查询。可选参数。
备注

column_familywhere 字段不能与 query 字段一起使用。必须声明 column_familyquery 字段中的一个。

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

tablewhere 字段不能与 query 字段一起使用。必须声明 tablequery 字段中的一个。

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_hashedcomplex_key_cache

提示

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

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

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

Attributes

配置示例:

配置字段:

标签描述必需
name列名称。
typeClickHouse 数据类型:UInt8UInt16UInt32UInt64Int8Int16Int32Int64Float32Float64UUIDDecimal32Decimal64Decimal128Decimal256DateDate32DateTimeDateTime64StringArray
ClickHouse 尝试将字典中的值转换为指定的数据类型。例如,对于 MySQL,该字段在 MySQL 源表中可能是 TEXTVARCHARBLOB,但可以在 ClickHouse 中上传为 String
Nullable 当前支持 FlatHashedComplexKeyHashedDirectComplexKeyDirectRangeHashed、Polygon、CacheComplexKeyCacheSSDCacheSSDComplexKeyCache 字典。在 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:用户定义的字典属性的列表。在此示例中,有两个属性:nameversion。第一个节点定义这两个属性。第二个节点仅定义属性 name。属性 version 由第二个节点的子节点提供。
    • 属性的值可以包含 反向引用,引用匹配正则表达式的捕获组。在此示例中,第一个节点的属性 version 的值包含了对捕获组 (\d+[\.\d]*) 的反向引用 \1。反向引用数字范围从 1 到 9,并写为 $1\1(针对数字 1)。反向引用在查询执行期间被匹配的捕获组替换。
  • child nodes:正则表达式树节点的子列表,每个都有其自己的属性和(可能的)子节点。字符串匹配以深度优先的方式进行。如果字符串与正则表达式节点匹配,则字典检查它是否也与节点的子节点匹配。如果是这样,则分配最深匹配节点的属性。子节点的属性会覆盖同名的父节点属性。YAML 文件中子节点的名称可以是任意的,例如上面的例子中的 versions

正则表达式树字典仅允许使用 dictGetdictGetOrDefaultdictGetAll 函数访问。

示例:

结果:

在此情况下,我们首先匹配顶层第二个节点中的正则表达式 \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_filepath_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 参数配置。字典更新(在首次使用时加载除外)不会阻塞查询。在更新期间,查询使用旧版本的字典。如果更新期间发生错误,错误将写入服务器日志,查询将继续使用旧版本的字典。

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

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