在数仓开发和数据分析过程中，当业务中有常量数据或者业务数据可以划分为业务事实表和业务维度表时，您可以通过使用数据字典来替代维度表实现，避免使用Join语句，从而提升查询效率。本文为您介绍云数据库ClickHouse如何新建、修改和使用外部扩展字典。
前提条件
数据源与目标集群使用了相同的VPC，且在同一地域下，并将彼此的IP添加到了对方白名单中。如果不满足此条件，请先解决网络问题。具体操作，请参见如何解决目标集群与数据源网络互通问题。
您可以通过SELECT * FROM system.clusters;命令查看云数据库ClickHouse集群的IP地址。
ClickHouse如何添加白名单，请参见设置白名单。
注意事项
以下版本的云数据库ClickHouse集群不支持通过控制台管理数据字典。如需创建，您可以通过SQL查看和创建数据字典。具体操作，请参见创建字典。
21.8及以上版本的云数据库ClickHouse社区兼容版集群。
云数据库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)创建示例如下：
源端为当前云数据库ClickHouse
数据准备。
创建源表。
CREATE TABLE default.cities ( id UInt64, city_name String)ENGINE = Memory;导入测试数据。
INSERT INTO default.cities (id, city_name) VALUES(1, 'Beijing'),(2, 'Shanghai'),(3, 'Guangzhou'),(4, 'Shenzhen');
创建字典表。
CREATE DICTIONARY default.city_dict ( id UInt64, city_name String ) PRIMARY KEY id SOURCE(CLICKHOUSE( USER 'username' PASSWORD 'password' TABLE 'cities' DB 'default' )) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 360);
源端为其他云数据库ClickHouse
数据准备。
创建源表。
CREATE TABLE default.cities ( id UInt64, city_name String)ENGINE = Memory;导入测试数据。
INSERT INTO default.cities (id, city_name) VALUES(1, 'Beijing'),(2, 'Shanghai'),(3, 'Guangzhou'),(4, 'Shenzhen');
创建字典表。
CREATE DICTIONARY default.city_dict ( id UInt64, city_name String ) PRIMARY KEY id SOURCE(CLICKHOUSE( HOST 'cc-xxx.clickhouse.ads.aliyuncs.com' PORT 3306 USER 'username' PASSWORD 'password' DB 'default' TABLE 'cities' )) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 360);
源端为RDS MySQL实例
RDS Mysql侧数据准备。
创建数据库与源表。
CREATE DATABASE testdb; CREATE TABLE testdb.cities ( id INT PRIMARY KEY, city_name VARCHAR(255));导入测试数据。
INSERT INTO testdb.cities (id, city_name) VALUES(1, 'Beijing'),(2, 'Shanghai'),(3, 'Guangzhou'),(4, 'Shenzhen');
创建字典表。
CREATE DICTIONARY default.city_dict ( id UInt64, city_name String ) PRIMARY KEY id SOURCE(MYSQL( HOST 'rm-xxx.mysql.rds.aliyuncs.com' PORT 3306 USER 'username' PASSWORD 'password' DB 'testdb' TABLE 'cities' )) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 360);
带有query语句的字典表
数据准备。
创建源表。
CREATE TABLE default.cities ( id UInt64, city_name String)ENGINE = Memory;导入测试数据。
INSERT INTO default.cities (id, city_name) VALUES(1, 'Beijing'),(2, 'Shanghai'),(3, 'Guangzhou'),(4, 'Shenzhen');
创建字典表。
CREATE DICTIONARY default.my_dict ( id UInt64, city_name String ) PRIMARY KEY id SOURCE(CLICKHOUSE( USER 'username' PASSWORD 'password' DB 'default' QUERY 'SELECT id, city_name FROM default.cities where id<2' )) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);
数据字典的使用
字典表查询
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>)