ClickHouse使用MySQL实现字典查询

原创已于 2023-03-18 16:41:11 修改 · 1.9k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #clickhouse #数据库 #外部字典

于 2023-03-18 15:47:48 首次发布

ClickHouse 专栏收录该内容

32 篇文章

订阅专栏

文章介绍了如何在ClickHouse中利用外部字典功能，特别是通过连接MySQL数据源来提高查询性能。ClickHouse的字典是内存中的键值对结构，用于高效替代SQL中的JOIN操作。通过CREATEDICTIONARY语句，可以从MySQL表中创建字典并在内存中缓存数据，以实现低延迟的查询。文章提供了一个示例，展示如何在ClickHouse的事实表和MySQL的维度表之间使用字典进行数据关联，强调了字典在查询优化中的作用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

字典是ClickHouse高效动态丰富数据的强大方法, 字典将标识符与之对应的附加属性映射到一起。字典数据存储在内存中，从而可以极大地提升查询性能。MySQL为实现通用业务的流行数据库，本文介绍ClickHouse如何使用MySql表实现字典功能。

ClickHouse字典

ClickHouse外部字典是内存键值对结构，用于代替SQL中join查询。ClickHouse支持多表联合查询，字典是实现高效多表查询的解决方案。字典可以连接外部数据源，如：ClickHouse，MySQL，或通用ODBC，文件或web服务。ClickHouse可以自动刷新外部数据源字典至最新状态，避免了繁琐的ETL过程，实现低延迟、高性能查询应用。

在ClickHouse 20.1版本开始支持DDL(CREATE DICTIONARY)方式定义字典，不再需要XML编辑字典。已定义字典可通过SHOW DICTIONARIES 及 SHOW CREATE DICTIONARY ndict语句进行检查。也可以使用ON CLUSTER语法在集群节点上创建。

字典虽然是内存表，但不要通过join一起使用，而是通过字典函数才能发挥其优势。
如：dictGet(‘default.ndict’, ‘third_column’, number) ，还有其他几个重载函数，用于赋默认值：

dictGet('dict_name', attr_names, id_expr)
dictGetOrDefault('dict_name', attr_names, id_expr, default_value_expr)
dictGetOrNull('dict_name', attr_name, id_expr)

下面通过一个完整示例进行说明。

ClickHouse 事实表

假设ClickHouse中有一个事实表，事件源，包括id、发生日期和度量值。

创建表语句如下：

CREATE TABLE log
(
    `user_id` UInt64,
    `datetime` Datetime,
    `value` UInt32
)
ENGINE = MergeTree
ORDER BY datetime

这里 user_id 为用户ID，用户其他属性存储在MySQL中。

MySQL 维度表

这里使用简单的表进行示例：

CREATE TABLE `names` (
  `id` SERIAL PRIMARY KEY,
  `name` char(3) DEFAULT NULL
)

用户仅包括name属性，id与ClickHouse中user_id对应。下面介绍如何从ClickHouse中获取MySQL数据。

字典实现机制

显然，我们需要从MySQL移动数据到ClickHouse，然后关联查询log表，从而获得所有必要的属性。ClickHouse提供了简单方式实现：我们可以创建MYSQL引擎表，可以直接引用MySQL数据库中的表，然后在ClickHouse中进行连接查询。

但可以使用ClickHouse中的字典表实现，因为字典在内存中加载数据，查询效率更高。从技术上讲，字典的创建和使用包括以下几个步骤:

定义需要从中加载数据的数据源
调整设置，如缓存时间，确保更好的性能与数据相关性
从查询中直接通过函数获取字典数据

创建字典

上节已准备好Mysql表，下面在ClickHouse中创建字典：

CREATE DICTIONARY dict_users
(
    `id` UInt64,
    `name` String
)
PRIMARY KEY id
SOURCE(MYSQL(HOST '127.0.0.1' PORT 3306 USER 'test' PASSWORD 'test' DB 'test' TABLE 'names'))
LIFETIME(MIN 300 MAX 360)
LAYOUT(HASHED())

上面要求ClickHouse创建字典，名称为dict_users, 字典属性与MySql表属性一样。SOURCE块配置MySQL服务参数，HASHED()指定载入所有数据至内存hash表。因为MySql数据有可能改变，如有新增加的数据，因此使用lifetime块配置缓存参数。

ClickHouse在第一次使用字典时加载数据至字典（不是创建后加载），使用字典的语法如下：

SELECT dictGet('dict_names', 'name', 123)

首先从MySql加载所有数据至ClickHouse，然后返回id为123用户对应的name，其等效查询语句为：

SELECT name FROM names WHERE id = 123 -- equivalent Mysql query

为了检查字典占用内存情况，可以查询system表：

SELECT formatReadableSize(bytes_allocated)
FROM system.dictionaries
WHERE name = 'dict_users'

用户可以在httpUI中查询：http://localhost:8123/play, 返回结果：

formatReadableSize(bytes_allocated)
10.23 KiB

通常字典和ID属性一起丰富查询结果，log表没有name，使用字典进行转换：

SELECT dictGet('dict_users', 'name', user_id), datetime, val
FROM log WHERE ...

其他字典函数

dictHas(‘dict_name’, id_expr) ：检查id_expr在字典dict_name中是否存在

对于字典包括联合主键（多个字段联合作为主键）则使用dictGetString函数，语法如下：

dictGetString(‘dict_name’, ‘attr_name’, tuple(‘string for field1’, num_for_field2)).

总结

外部字典是ClickHouse的一等公民，是Schema的一部分，我们可以使用本地DDL或 ON CLUSTER语句进行创建。实现细节多用户是透明的，它自动转换join查询为字典调用，用户体验及查询性能都值得我们去学习和使用。外部字典还有其他类型，如层次、多边形等字典，未来继续学习并分享。