ClickHouse 数据字典介绍

数据字典

一个字典是一个映射 (key -> attributes) ，能够作为函数被用于查询。你可以认为它可以作为更便捷和高效的 JOIN 类型，作为维度表。

数据字典有两种，一个是内置字典，另一个是外置字典。

外部字典

通过外部字典，你可以从不同的数据源添加自己的字典。对于一个字典，数据源可以是本地文件系统的一个文件，在ClickHouse服务器上，或者在MySQL服务器上。一个字典可以完全存储在内存中，或者部分在内存中，可以动态加载未命中的数据。

外部字典的配置是在一个单独文件中，或者文件的字典配置参数部分dictionaries_config。此参数包含了文件的绝对或相对路径，带有字典配置。相对路径是相对于带有服务器配置文件的字典。路径能够包含掩码和?, 通过掩码发现所有匹配的文件. 例如: dictionaries/.xml。

词典配置，也是带有此配置的文件集合，可以在不重启服务器的情况下进行更新。服务器每5秒中检查一遍更新，因此字典能够被随时动态地启用。

当启动服务器时，字典能够被创建，或者初次使用。通过dictionaries_lazy_load参数被定义，在主服务器的配置文件中。这个参数是可选的，默认情况是’true’，每个字典初次使用时被创建。如果词典创建失败，正在使用词典的函数抛出一个异常。如果是’false’，当服务器启动时，所有的字典被创建，如果有一个错误，那么服务器将停机。

词典配置文件如下所示：

<comment>Optional element with any content; completely ignored.</comment>

<!–You can set any number of different dictionaries. -->

<dictionary>

<!-- Dictionary name. The dictionary will be accessed for use by this name. -->

<name>os</name>

<!-- Data source. -->

<source>

<!-- Source is a file in the local file system. -->

<file>

<!-- Path on the local file system. -->

<path>/opt/dictionaries/os.tsv</path>

<!-- Which format to use for reading the file. -->

<format>TabSeparated</format>

</file>

<!-- or the source is a table on a MySQL server.

<mysql>

<!- - These parameters can be specified outside (common for all replicas) or inside a specific replica - ->

<port>3306</port>

<user>clickhouse</user>

<password>qwerty</password>

<!- - Specify from one to any number of replicas for fault tolerance. - ->

<replica>

<host>example01-1</host>

<priority>1</priority> <!- - The lower the value, the higher the priority. - ->

</replica>

<replica>

<host>example01-2</host>

<priority>1</priority>

</replica>

<db>conv_main</db>

<table>counters</table>

</mysql>

–>

<!-- or the source is a table on the ClickHouse server.

<clickhouse>

<host>example01-01-1</host>

<port>9000</port>

<user>default</user>

<password></password>

<db>default</db>

<table>counters</table>

</clickhouse>

<!- - If the address is similar to localhost, the request is made without network interaction. For fault tolerance, you can create a Distributed table on localhost and enter it. - ->

–>

<!-- or the source is a executable. If layout.complex_key_cache - list of needed keys will be written in STDIN of program -->

<executable>

<!-- Path on the local file system or name located in one of env PATH dirs. -->

<command>cat /opt/dictionaries/os.tsv</command>

<!-- Which format to use for reading/writing stream. -->

<format>TabSeparated</format>

</executable>

<!-- or the source is a http server. If layout.complex_key_cache - list of needed keys will be sent as POST  -->

<http>

<!-- Host. -->

<url>http://[::1]/os.tsv</url>

<!-- Which format to use for reading answer and making POST. -->

<format>TabSeparated</format>

</http>

</source>

<!-- Update interval for fully loaded dictionaries. 0 - never update. -->

<lifetime>

<min>300</min>

<max>360</max>

<!-- The update interval is selected uniformly randomly between min and max, in order to spread out the load when updating dictionaries on a large number of servers. -->

</lifetime>

<!-- or <!- - The update interval for fully loaded dictionaries or invalidation time for cached dictionaries. 0 - never update. - ->

<lifetime>300</lifetime>

–>

<layout><!-- Method for storing in memory. -->

<flat/>

<!-- or <hashed />

or

<cache>

<!- - Cache size in number of cells; rounded up to a degree of two. - ->

<size_in_cells>1000000000</size_in_cells>

</cache>

or

<ip_trie />

–>

</layout>

<!-- Structure. -->

<structure>

<!-- Description of the column that serves as the dictionary identifier (key). -->

<id>

<!-- Column name with ID. -->

<name>Id</name>

</id>

<attribute>

<!-- Column name. -->

<name>Name</name>

<!-- Column type. (How the column is understood when loading. For MySQL, a table can have TEXT, VARCHAR, and BLOB, but these are all loaded as String) -->

<type>String</type>

<!-- Value to use for a non-existing element. In the example, an empty string. -->

<null_value></null_value>

</attribute>

<!-- Any number of attributes can be specified. -->

<attribute>

<name>ParentID</name>

<type>UInt64</type>

<null_value>0</null_value>

<!-- Whether it defines a hierarchy - mapping to the parent ID (by default, false). -->

<hierarchical>true</hierarchical>

<!-- The mapping id -> attribute can be considered injective, in order to optimize GROUP BY. (by default, false) -->

<injective>true</injective>

</attribute>

</structure>

</dictionary>

</dictionaries>

字典ID(Key 属性)将是一个数字，数据类型一般为UInt64。你也能够使用任意的tuples作为 keys。你也能够使用由多个元素组合的复杂的keys。它允许使用字符串作为字典 keys。

有6种不同的方法在内存中存储字典。

flat

flat是最有效的方法。如果所有的 keys 小于500,000，它正常工作。如果一个更大的 key被发现，当创建一个字典，一个异常将抛出，此字典不会被创建。字典被整体加载进入到内存中。字典使用内存量与最大的 key value 相称。限制为500,000，内存消耗不会太高。所有类型的资源都被支持。当更新时，数据(文件或者表)被整体读取。

Hashed

此方法比第一个效果差一点。字典也被加载到内存中，能够包含任意的条目数量（带有任意 ID）。在实际情况下，它可以用到数百万个条目，如果有足够大的内存。所有源的类型都被支持。当更新时，数据(文件或者表)被整体读取。

Cache

最无效的方法. 如果字典不适合在内存，它是合适的. 它是确定数据槽位数量的缓存，可将频繁访问的数据放在这. MySQL, ClickHouse, Executable, HTTP 资源都被支持, 但是文件资源不被支持. 当在一个词典中搜索时, 缓存是第一个被搜索的. 对于每一个数据块, 所有的键没有在缓存中找到的 (或者已经超时) 被收集在一个包中, 被发送到带有查询的源端 SELECT attrs…FROM db.table WHERE id IN(k1,k2,…). 接收的数据被写入到缓存中.

Range_hashed

此表为每个键列出一些数据. 为给定 key，给定date下抽取数据。

示例: 在表中为每个广告商列出一些折扣:

advertiser id discount start date end date value

123                2015-01-01                    2015-01-15    0.15

123                2015-01-16                    2015-01-31    0.25

456                2015-01-01                    2015-01-15    0.05添加 layout = range_hashed. 当使用这个时, 结构将有元素 range_min, range_max.

<structure>

<id>

<name>Id</name>

</id>

<range_min>

<name>first</name>

</range_min>

<range_max>

<name>last</name>

</range_max>

这些列必须是 Date 类型. 其他的类型都暂时不支持. 此列提示了一个关闭日期范围.

为了与这些字典工作，dictGetT函数必须获得多个参数—日期：

dictGetT(‘dict_name’,‘attr_name’,id,date)此函数为 id 和 date 取出这个值, 包括转换日期. 如果没有 id 或范围被找到, 则将返回默认值.

如果有重叠的范围，任何合适的都可以被使用。

如果范围边界是NULL或者是一个不正确的 Date(1900-01-01, 2039-01-01),则此范围将考虑被打开。次范围在两边都打开。

在内存中, 数据被显示成为一个哈希表，以一个顺序数组范围 和对应的值.

范围字典的示例：

<dictionary>

<name>xxx</name>

<source>

<mysql>

<password>xxx</password>

<port>3306</port>

<user>xxx</user>

<replica>

<host>xxx</host>

<priority>1</priority>

</replica>

<db>dicts</db>

<table>xxx</table>

</mysql>

</source>

<lifetime>

<min>300</min>

<max>360</max>

</lifetime>

<layout>

<range_hashed/>

</layout>

<structure>

<id>

<name>Abcdef</name>

</id>

<range_min>

<name>StartDate</name>

</range_min>

<range_max>

<name>EndDate</name>

</range_max>

<attribute>

<name>XXXType</name>

<type>String</type>

<null_value/>

</attribute>

</structure>

</dictionary>

</dictionaries>ip_trie

此表为每个 IP 地址存储 IP 前缀, 它使映射 IP 地址到元数据称为可能，例如 ASN 或攻击评分.

例如: 在表中有前缀匹配 AS 号和国家:

prefix asn cca2

202.79.32.0/20    17501    NP

2620:0:870::/48  3856      US

2a02:6b8:1::/48  13238    RU

2001:db8::/32    65536    ZZ当使用这个时, 此结构应该有 “key” 元素.

例如:

<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>这些键只有一个属性类型 String,包含了有效的 IP 前缀。其他类型不被支持。

对于查询，相同的函数(dictGetT with tuple) 对于复杂 Key 字典来说必须用到。

dictGetT(‘dict_name’,‘attr_name’,tuple(ip))此函数接受UInt32(对于 IPv4地址)或者 FixedString(16)(对于IPv6地址)，wire 格式为：

dictGetString(‘prefix’,‘asn’,tuple(IPv6StringToNum(‘2001:db8::1’)))没有其他类型被支持。此函数对于给定的 IP 地址前缀匹配返回属性。如果有重叠前缀，返回一个。

此数据被保存在bitwise trie中，内存是适合的。

complex_key_hashed

对于复杂的 keys，与hashed是相同的，

complex_key_cache

对于复杂的 keys，与 cache 是相同的。

备注

我们推荐使用flat 方法，或者 hashed。词典的速度在内存存储下是非常快的。

只有当不可避免时，使用缓存方法. 缓存的速度严重依赖于正确的设置和应用场景。一个缓存类型只为高命中率工作(推荐99% 和更高) 。在系统字典表中你可以查看平均命中率. 设置一个足够大的缓存大小. 你需要不断试验来找到正确的值 - 选择一个值, 使用一个查看来获得缓存完全的值, 看一下内存消耗 (在系统字典表中 system.dictionaries), 然后增加数值，因此合理的内存数被消耗。我们推荐 MySQL 为缓存作为数据源, 因为ClickHouse并不能很好地处理随机读操作(Read)的请求。

在所有的情况下，如果你调用与字典相关的函数，在GROUPBY之后，那么性能是更好的。如果属性查询被标记作为单映射函数。对于一个字典缓存，如果在LIMIT后调用此函数，则性能会提升。为了做这个你能够使用带有LIMIT的子查询，同时调用带有字典的函数，字典在外部。

一个属性被称为单映射函数，如果不同的属性值对应着不同的键。当GROUP BY使用一个函数，通过一个键查询一个属性值时，此函数将自动取出GROUP BY。

当从一个文件中更新字典时，初次文件修改时间被检查，同时只要文件已经修改，则被加载。当从 MySQL 更新时，对于flat 和 hashed字典，SHOW TABLE STATUS查询被执行，表更新时间被检查。如果它不为 NULL，它比较存储时间，它能够工作在MyISAM表之上，但是对于InnoDB表，更新时间是未知的，从InnoDB中加载在每个更新中被执行。

对于缓存字典，在缓存中的数据超时可以被设置。如果大于生命周期的时间被传入，当加载数据进入数据格时，数据格中的值不能被用，同时下一次，当需要被使用时，它被重新请求。

如果一个字典不能被加载，尝试使用它将抛出异常。在请求过程中，如果一个错误发生在一个缓存的数据源，将抛出一个异常。字典更新不会阻塞查询。当更新时，一个字典的旧版本将被使用。如果一个错误在更新中发生，一个错误被写入到服务器日志，同时查询继续使用旧版本的字典。

你能够查看外部字典的列表和他们的状态，在system.dictionaries表中。为了使用外部字典，请查看章节 “Function函数如何在外表中运行”。

通过制定字典中的全部内容你能够为一个小的字典转换这个值，在SELECT查询中。这个函数不与外部字典相关。

带有复杂Keys的字典

你能够使用tuples，由任意类型的数据域作为 keys 组成。配置你的字典-带有complex_key_hashed或者complex_key_cache配置。

Key 结构被配置不在<id>元素中，而在<key>元素中。key tuple 的数据域被配置与字典属性类似。例如：

<structure>

<key>

<attribute>

<name>field1</name>

<type>String</type>

</attribute>

<attribute>

<name>field2</name>

<type>UInt32</type>

</attribute>

当使用这些字典时, 使用 Tuple 域值作为 一个 key，在 dictGet* 函数中. Example:dictGetString(‘dict_name’,‘attr_name’,tuple(‘field1_value’,123)).

内部字典

ClickHouse 包含了一个内置的特性，即 geobase.

它可以允许你：

使用一个 Region 的 ID 在所需要的语言中获得它的名字。

使用一个 Region 的 ID获得 一个城市、区域、行政区、国家的 ID。

检查是否一个 Region 是另外一个 Region 的一部分。

获得父 Region 的链条。

所有函数支持“translocality”，在 region 上同时使用不同的视图的能力。更多信息，可查看章节"运行在Yandex.Metrica字典的函数"。

内部字典在默认包中是禁用的。为了启用他们，取消批注参数path_to_regions_hierarchy_file和path_to_regions_names_files，在服务器配置文件中。

来源：http://www.clickhouse.com.cn/topic/5a3bb6862141c2917483556c