如何新建、修改和使用ClickHouse外部扩展字典 - 云数据库 ClickHouse

在数仓开发和数据分析过程中，当业务中有常量数据或者业务数据可以划分为业务事实表和业务维度表时，您可以通过使用数据字典来替代维度表实现，避免使用Join语句，从而提升查询效率。本文为您介绍云数据库ClickHouse如何新建、修改和使用外部扩展字典。

前提条件

数据源与目标集群使用了相同的VPC，且在同一地域下，并将彼此的IP添加到了对方白名单中。如果不满足此条件，请先解决网络问题。具体操作，请参见如何解决目标集群与数据源网络互通问题。

说明

您可以通过SELECT * FROM system.clusters;命令查看云数据库ClickHouse集群的IP地址。

ClickHouse如何添加白名单，请参见设置白名单。

注意事项

21.8及以上版本的云数据库ClickHouse集群不支持通过控制台管理数据字典。您可以通过SQL查看和创建数据字典。具体操作，请参见创建字典。

新建字典配置

登录云数据库ClickHouse控制台。
在页面左上角，选择目标集群所在的地域。
在集群列表页面，选择默认实例列表，单击目标集群ID。
在左侧导航栏，单击字典管理，进入字典列表页面。
单击右上角新增字典配置。

在弹出的新增字典配置窗口编辑模板填写字典内容。

此处只介绍字典配置的主要参数，更多参数，请参见Dictionaries。

参数	描述
<name>	自定义字典名称，必须全局唯一。
<source>	配置字典的数据源，表示字典中的数据来源。目前ClickHouse支持的数据源为： MySQL ClickHouse
<lifetime>	字典中数据的更新频率，单位：秒。
<layout>	内存中的数据格式类型，目前扩展字典共拥有7种类型。单数值key flat hashed range_hashed cache 复合key complex_key_hashed complex_key_cache ip_trie
<structure>	字典的数据结构。说明单次只允许添加一个节点。

填写完毕后单击确定，数据字典创建完成。
创建成功后，在数据字典对应的操作列，可进行查看、删除和修改。

字典表DDL

云数据库ClickHouse集群20.8之后的版本（您可以登录ClickHouse控制台-集群信息-查看版本号）增加了数据字典的DDL，您可以直接使用DDL语句新建扩展字典表。

CREATE DICTIONARY [IF NOT EXISTS] [db.]dictionary_name [ON CLUSTER cluster]
(
    key1 type1  [DEFAULT|EXPRESSION expr1] [HIERARCHICAL|INJECTIVE|IS_OBJECT_ID],
    key2 type2  [DEFAULT|EXPRESSION expr2] [HIERARCHICAL|INJECTIVE|IS_OBJECT_ID],
    attr1 type2 [DEFAULT|EXPRESSION expr3],
    attr2 type2 [DEFAULT|EXPRESSION expr4]
)
PRIMARY KEY key1, key2
SOURCE(SOURCE_NAME([param1 value1 ... paramN valueN]))
LAYOUT(LAYOUT_NAME([param_name param_value]))
LIFETIME([MIN val1] MAX val2)

数据字典的使用

字典表查询

SELECT
    name,
    type,
    key,
    attribute.names,
    attribute.types,
    bytes_allocated,
    element_count,
    source
FROM system.dictionaries

查询字典数据

使用dictGet函数获取数据字典中的数据。dictGet函数的更多详情，请参见ClickHouse官方文档。

dictGet(<dict_name>, <attr_name>, <id_expr>)
dictGetOrDefault(<dict_name>, <attr_name>, <id_expr>, <default_value_expr>)