ClickHouse使用MySQL实现字典查询

文章介绍了如何在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 DICTIONARIESSHOW 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中的字典表实现,因为字典在内存中加载数据,查询效率更高。从技术上讲,字典的创建和使用包括以下几个步骤:

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

创建字典

上节已准备好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查询为字典调用,用户体验及查询性能都值得我们去学习和使用。外部字典还有其他类型,如层次、多边形等字典,未来继续学习并分享。

好的,这是一个比较复杂的问题,需要一定的时间来解决。在进行数据血缘分析之前,我们需要先了解一下这些数据库的基本信息和相关的Python库。其中,hivesql和clickhouse需要使用PyHive和clickhouse-driver库进行连接和操作,mysql需要使用pymysql库,sqlserver需要使用pyodbc库。 接下来,我们可以通过以下步骤进行数据血缘分析: 1. 连接数据库并获取元数据 我们可以使用相关的Python库连接到数据库,并获取每个表的元数据信息,包括表名、字段名、数据类型等。例如,使用PyHive连接到Hive数据库,可以通过以下代码获取表的元数据信息: ```python from pyhive import hive conn = hive.Connection(host='localhost', port=10000, username='user', database='default') cursor = conn.cursor() cursor.execute('DESCRIBE my_table') metadata = cursor.fetchall() ``` 2. 解析SQL语句 接下来,我们需要解析SQL语句,得到SQL语句中涉及的所有表和字段。可以使用Python库sqlparse对SQL语句进行解析。例如,对于以下SQL语句: ```sql SELECT a.id, b.name FROM my_table a JOIN another_table b ON a.id = b.id WHERE a.age > 18 ``` 可以使用以下代码解析得到表名和字段名: ```python import sqlparse sql = 'SELECT a.id, b.name FROM my_table a JOIN another_table b ON a.id = b.id WHERE a.age > 18' parsed_sql = sqlparse.parse(sql)[0] tables = set() fields = set() for token in parsed_sql.tokens: if isinstance(token, sqlparse.sql.IdentifierList): for identifier in token.get_identifiers(): fields.add(identifier.get_name()) elif isinstance(token, sqlparse.sql.Identifier): tables.add(token.get_name()) tables = list(tables) fields = list(fields) ``` 3. 进行数据血缘关系分析 有了表名和字段名之后,我们可以根据字段在SQL语句中的位置,判断它是源字段还是目标字段。例如,对于以下SQL语句: ```sql SELECT a.id, b.name FROM my_table a JOIN another_table b ON a.id = b.id WHERE a.age > 18 ``` 可以判断出a.id是源字段,b.name是目标字段。然后,我们可以根据表名和字段名,将源字段和目标字段进行映射,得到源表、目标表和字段映射关系。具体实现可以使用Python字典来存储映射关系,例如: ```python mapping = {} for i, field in enumerate(fields): if tables[i] == 'my_table': mapping[field] = { 'source_table': 'my_table', 'source_field': field, 'target_table': 'another_table', 'target_field': fields[i+1] } print(mapping) ``` 4. 输出Python代码 最后,我们可以根据得到的映射关系,生成Python代码。具体实现可以使用字符串拼接的方式,生成包含源表、目标表和字段映射关系的Python代码。例如,对于上面的映射关系,我们可以生成如下代码: ```python mapping = { 'id': { 'source_table': 'my_table', 'source_field': 'id', 'target_table': 'another_table', 'target_field': 'id' } } source_table = 'my_table' source_fields = ['id'] target_table = 'another_table' target_fields = ['name'] source_to_target = {} for field in source_fields: source_to_target[field] = mapping[field]['target_field'] print('SELECT {} FROM {} WHERE {} > 18'.format(', '.join(target_fields), target_table, source_to_target['id'])) ``` 以上就是一个简单的数据血缘分析的Python代码。由于每个数据库的语法和元数据信息都有所不同,需要根据实际情况进行调整和优化。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值