作者:洪文丽
开源之夏2024“支持 External Dictionaries”项目参与者
东北大学软件工程专业云计算方向大二在读,喜欢挑战自我,尝试新鲜事物
背景介绍
在大型系统中,数据通常存储在多个不同的数据源中,例如 PostgreSQL、MySQL 和 Redis 负责存储在线数据,而 Databend 和 ClickHouse 则用于存储分析数据。传统的分析查询方法往往需要同时使用到多种不同的数据,通常通过 ETL 过程将数据导入一个系统后进行查询,但这种方式存在以下问题:
- 数据变动导致不一致性:
在多源数据系统中,数据可能会在不同的系统中以不同的频率和时间点进行更新。例如,在线数据源如 MySQL 和 Redis 可能会实时更新,而分析系统如 Databend 更新频率较低。这种时间差异可能导致数据不一致,分析结果可能与实际在线数据不符。
- 多表 join 操作性能低下:
在传统的 ETL 过程中,将数据从多个源导入到一个分析系统后,进行多表 join 操作时可能会遇到性能瓶颈。大规模的 join 操作需要对多个表进行复杂的匹配和计算,这可能导致查询响应时间变长,特别是在数据量大且 join 操作复杂的情况下。
- 数据系统管理复杂度高:
在大型系统中,管理多个数据源意味着需要处理不同的存储和查询机制。例如,MySQL 和 PostgreSQL 需要数据库管理和优化,Redis 需要确保缓存有效性,而 Databend 需要优化分析数据的存储和查询。每种系统都有不同的配置和维护要求,这增加了管理的复杂度和运维成本。数据备份、恢复、监控和性能调优等任务也需要分别在多个系统中进行。
为了解决这些问题,Databend 实现了字典功能。通过集成 MySQL、Redis 等外部数据源,Databend 实现了数据的实时查询与分析,不仅显著提升了查询性能,还确保了数据的一致性,减少了传统 ETL 流程的复杂性,特别适用于需要快速响应的实时分析场景。用户通过字典函数 dict_get
能够直接从外部数据源检索数据,简化了数据管理,并优化了大数据处理的效率。如图所示为字典功能示意图:
字典功能介绍
2.1 DDL 语法
- 基本语法
CREATE OR REPLACE DICTIONARY [db_name].dictionary_name
(
field1 type1 [DEFAULT expr1],
field2 type2 [DEFAULT expr2],
...
)
PRIMARY KEY primary_key
SOURCE(source_type [source_options]);
- 字典的配置参数
PRIMARY KEY
:字典的主键,用作查找数据的 key。SOURCE
:字典的数据源类型,如 MySQL、Redis 等。 - 数据源配置
目前支持 MySQL 和 Redis 作为数据源
MySQL 数据源
host (必填):MySQL 服务器的主机名或 IP 地址。
port (必填):MySQL 服务器的端口号,默认为 3306。
username (必填):用于连接 MySQL 服务器的用户名。
password (必填):用户的密码。
db (必填):要连接的数据库名称。
table (必填):数据库中的具体表名。
示例配置:
SOURCE(MYSQL(
host='your_host'
port='3306'
username='your_username'
password='your_password'
db='your_db'
table='your_table'
))
Redis数据源
host (必填):Redis 服务器的主机名或 IP 地址。
port (必填):Redis 服务器的端口号,默认为 6379。
username (选填):如果 Redis 服务器设置了用户认证,则提供用户名。
password (选填):用户的密码。
db_index (选填):指定要使用的 Redis 数据库,默认为 0,最大值为 15。
示例配置:
SOURCE(REDIS(
host='your_host'
port='6379'
username='your_username'
password='your_password'
db_index='db_index'
))
字段类型配置
- MySql数据源支持:
boolean
,string
,number
类型,其中number
类型涵盖了整型(如int
、bigint
)和浮点型(如float32
、float64
)。 - Redis数据源支持:
string
类型,用于键值对的简单存储和检索。 - 值得注意的是,在
dict_get
函数中,若无法从外部数据源检索到对应的键值,系统会返回用户指定的默认值,或者使用系统的默认值。这一机制确保即使在外部数据源中未找到匹配的键,Databend 依然能够返回合理的结果,从而避免数据缺失对后续操作的影响,提高了系统的鲁棒性和查询的稳定性。
- MySql数据源支持:
2.2 查询函数语法
Databend 支持使用 dict_get
函数查询字典数据。
dict_get([db_name].dict_name, 'attr_name', key_expr)
[db_name].dict_name
:外部字典的名称,可能包括数据库名(如果字典存储在特定的数据库中)和字典名。如果当前会话已经选择了一个特定的数据库,那么可以省略数据库名,只提供字典名。'attr_name'
:要查询的字典中字段的名称,必须为字符串。key_expr
:查询的 key 表达式,与字典中的PRIMARY KEY
类型相同。
使用示例
假设我们有一个学生成绩管理系统,我们需要存储和查询学生的成绩信息、课程信息等数据。这些信息分别存储在 Databend、MySQL 和 Redis 中。
Databend 中有一个学生成绩表
CREATE TABLE student_scores ( student_id INT, course_id INT, score INT );
插入一些测试数据
INSERT INTO student_scores VALUES (1, 1, 62),(1, 2, 75),(1, 3, 88),(2, 1, 93), (2, 2, 54),(2, 3, 99),(3, 1, 67),(3, 2, 80),
MySQL 中有一个课程信息表
CREATE TABLE courses ( course_id INT PRIMARY KEY, course_name VARCHAR(255),
插入一些测试数据
INSERT INTO courses
VALUES (1, 'math'),(2, 'english'),(3, 'chinese');
Redis 中存储了学生的姓名
127.0.0.1:6379> set 1 'Andy' 127.0.0.1:6379> set 2 'Nancy' 127.0.0.1:6379> set 3 'Lucy' 127.0.0.1:6379> set 4 'Jack'
在 Databend 中创建字典
CREATE DICTIONARY courses_dict ( course_id INT, course_name STRING ) PRIMARY KEY course_id SOURCE(MYSQL( host='localhost' port='3306' username='root' password='123456' db='test' table='courses' ));
CREATE DICTIONARY student_name_dict ( student_id string, student_name string ) PRIMARY KEY student_id SOURCE(REDIS( host='127.0.0.1' port='6379'
通过 Databend 的字典功能,可以轻松关联多个数据源并进行查询:
查询同学选课的信息,关联课程名称和学生姓名
SELECT student_id, dict_get(student_name_dict, 'student_name', to_string(student_id)) as student_name, course_id, dict_get(courses_dict, 'course_name', course_id) as course_name FROM student_scores;
+------------+--------------+-----------+-------------+ | student_id | student_name | course_id | course_name | +------------+--------------+-----------+-------------+ | 1 | Andy | 1 | math | | 1 | Andy | 2 | english | | 1 | Andy | 3 | chinese | | 2 | Nancy | 1 | math | | 2 | Nancy | 2 | english | | 2 | Nancy | 3 | chinese | | 3 | Lucy | 1 | math | | 3 | Lucy | 2 | english | | 3 | Lucy | 3 | chinese | | 4 | Jack | 1 | math | | 4 | Jack | 2 | english | | 4 | Jack | 3 | chinese | +------------+--------------+-----------+-------------+
查询同学平均成绩的排名
SELECT student_id, dict_get(student_name_dict, 'student_name', to_string(student_id)) as student_name, avg(score) as avg_score FROM student_scores GROUP BY student_id ORDER BY avg_score DESC;
+------------+--------------+-----------+ | student_id | student_name | avg_score | +------------+--------------+-----------+ | 2 | Nancy | 82.0 | | 4 | Jack | 80.0 | | 1 | Andy | 75.0 | | 3 | Lucy | 68.0 | +------------+--------------+-----------+
查询各门课程的平均分数
SELECT course_id, dict_get(courses_dict, 'course_name', course_id) as course_name, avg(score) as avg_score FROM student_scores
- ``` +-----------+-------------+-----------+ | course_id | course_name | avg_score | +-----------+-------------+-----------+ | 2 | english | 73.0 | | 1 | math | 72.0 | | 3 | chinese | 83.75 |
通过以上的示例,可以看到字典功能可以有效地结合 Databend、MySQL 和 Redis 处理学生成绩管理系统中的联合查询,提高查询效率和数据管理能力。
## 四、总结
在这篇文章中,我们介绍了字典功能的基本用法,并通过一个简单的例子展示了使用字典来查询多个数据源的数据。字典功能在很多应用场景都可以使用,特别是有外部数据源需要定期同步场景,例如,一个金融分析系统需要实时分析股票价格数据,通过为股票价格数据创建字典,可以从数据源实时读取最新的股票价格数据,结合其它数据进行实时的分析计算。字典功能在提高查询性能、简化数据管理、确保数据一致性等方面具有广泛的应用价值。
目前,Databend 字典已经支持了对 MySQL 和 Redis 数据源的访问,未来会支持更多数据源,包括 PostgreSQL、MongoDB、Sqlite、HTTP 接口等,满足更多数据处理和分析场景的需求。同时,字典查询的性能也会继续优化,通过引入缓存和批量查询机制,减少外部数据源的查询延迟,提升并发查询性能。
## 关于 Databend
Databend 是一款开源、弹性、低成本,基于对象存储也可以做实时分析的新式数仓。期待您的关注,一起探索云原生数仓解决方案,打造新一代开源 Data Cloud。 👨💻 Databend Cloud:[databend.cn](https://link.juejin.cn?target=https%3A%2F%2Fdatabend.cn "https://databend.cn")
📖 Databend 文档:[docs.databend.cn/](https://link.juejin.cn?target=https%3A%2F%2Fdocs.databend.cn%2F "https://docs.databend.cn/")
💻 Wechat:Databend
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。