什么是字典表

• Clickhouse字典是一种映射，把表数据映射成key/value字典表

使用场景

• 简化某些场景下的join语句，提高查询速度

使用

分类（LAYOUT）

• hashed： hash表形式，所有数据保存到内存中；需要注意内存大小
• sparse_hashed：和hashed相似，但占用会比较少
• complex_key_hashed：联合键的hashed
• ssd_cache：数据保存到ssd, 索引保存到RAM中
• direct：直接查询表数据
• complex_key_direct：联合键的直接查询
• 更多 clickhouse.com/docs/en/sql…

语法

CREATE DICTIONARY dict_name
(
    ... -- attributes
)
PRIMARY KEY    -- 字典键
SOURCE(...)    -- 数据源
LAYOUT(...)    -- 类型
LIFETIME(...)  -- 字典更新频率

示例

-- hashed
CREATE DICTIONARY business.t_install_dict
(
    `did` String,
    `inst` DateTime
)
PRIMARY KEY did
SOURCE(CLICKHOUSE(HOST 'localhost' PORT 9000 USER 'default' TABLE 't_inst_date' DB 'business'))
LAYOUT(HASHED())
LIFETIME(MIN 0 MAX 10);


-- COMPLEX_KEY_DIRECT
CREATE DICTIONARY t_install_dict
(
    `pkg` String,
    `did` String,
    `inst` DateTime
)
PRIMARY KEY pkg, did
SOURCE(CLICKHOUSE(HOST 'localhost' PORT 9000 USER 'default' TABLE 't_inst_date' DB 'default'))
LAYOUT(COMPLEX_KEY_DIRECT());

使用

-- 查询
-- 查询函数 https://clickhouse.com/docs/en/sql-reference/functions/ext-dict-functions/
select dictGetString('t_install_dict', 'inst', tuple('pkg', 'did')) as install_date;

-- 注：dictGetDateTime 会变为utc时间
select toDate(dictGetDateTime('t_install_dict', 'inst', tuple('pkg', 'did'))) as install_date;
select toDate(dictGetDateTime('t_install_dict', 'inst', tuple('pkg', 'did')) + Interval 8 HOUR) as install_date;

-- 删除
drop dictionary t_install_dict;

-- 查看表结构
show create table t_install_dict;

-- 查看表大小
select formatReadableSize(bytes_allocated) from system.dictionaries where name = 't_install_dict';

参考

• 建议看英文文档，中英文档会有缺失
• ext-dict-functions | ClickHouse Docs
• Dictionary Table Engine | ClickHouse Docs
• External Dictionaries | ClickHouse Docs