作者:洪文丽

开源之夏2024“支持 External Dictionaries”项目参与者

东北大学软件工程专业云计算方向大二在读,喜欢挑战自我,尝试新鲜事物

背景介绍

在大型系统中,数据通常存储在多个不同的数据源中,例如 PostgreSQL、MySQL 和 Redis 负责存储在线数据,而 Databend 和 ClickHouse 则用于存储分析数据。传统的分析查询方法往往需要同时使用到多种不同的数据,通常通过 ETL 过程将数据导入一个系统后进行查询,但这种方式存在以下问题:

  1. 数据变动导致不一致性:

在多源数据系统中,数据可能会在不同的系统中以不同的频率和时间点进行更新。例如,在线数据源如 MySQL 和 Redis 可能会实时更新,而分析系统如 Databend 更新频率较低。这种时间差异可能导致数据不一致,分析结果可能与实际在线数据不符。

  1. 多表 join 操作性能低下:

在传统的 ETL 过程中,将数据从多个源导入到一个分析系统后,进行多表 join 操作时可能会遇到性能瓶颈。大规模的 join 操作需要对多个表进行复杂的匹配和计算,这可能导致查询响应时间变长,特别是在数据量大且 join 操作复杂的情况下。

  1. 数据系统管理复杂度高:

在大型系统中,管理多个数据源意味着需要处理不同的存储和查询机制。例如,MySQL 和 PostgreSQL 需要数据库管理和优化,Redis 需要确保缓存有效性,而 Databend 需要优化分析数据的存储和查询。每种系统都有不同的配置和维护要求,这增加了管理的复杂度和运维成本。数据备份、恢复、监控和性能调优等任务也需要分别在多个系统中进行。

为了解决这些问题,Databend 实现了字典功能。通过集成 MySQL、Redis 等外部数据源,Databend 实现了数据的实时查询与分析,不仅显著提升了查询性能,还确保了数据的一致性,减少了传统 ETL 流程的复杂性,特别适用于需要快速响应的实时分析场景。用户通过字典函数 dict_get 能够直接从外部数据源检索数据,简化了数据管理,并优化了大数据处理的效率。如图所示为字典功能示意图:

字典功能介绍

2.1 DDL 语法

  1. 基本语法
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]);
  1. 字典的配置参数 PRIMARY KEY:字典的主键,用作查找数据的 key。 SOURCE:字典的数据源类型,如 MySQL、Redis 等。
  2. 数据源配置

目前支持 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'
        ))
  1. 字段类型配置

    1. MySql数据源支持:booleanstringnumber类型,其中 number 类型涵盖了整型(如 intbigint)和浮点型(如 float32float64)。
    2. Redis数据源支持:string类型,用于键值对的简单存储和检索。
    3. 值得注意的是,在 dict_get 函数中,若无法从外部数据源检索到对应的键值,系统会返回用户指定的默认值,或者使用系统的默认值。这一机制确保即使在外部数据源中未找到匹配的键,Databend 依然能够返回合理的结果,从而避免数据缺失对后续操作的影响,提高了系统的鲁棒性和查询的稳定性。

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

databend
20 声望9 粉丝

Databend 旨在成为一个 开源、弹性、可靠 的无服务器数仓,查询快如闪电,与 弹性、简单、低成本 的云服务有机结合。数据云的构建,从未如此简单!