开源利器DuckDB实测:把MongoDB全量数据导入MySQL

作者介绍

贺春旸，dbaplus社群金牌专家，凡普金科和爱钱进DBA团队负责人，《MySQL管理之道：性能调优、高可用与监控》第一&二版、《MySQL运维进阶指南》作者，曾任职于中国移动飞信、安卓机锋网。五次荣获dbaplus年度MVP，致力于MariaDB、MongoDB等开源技术的研究，主要负责数据库性能调优、监控和架构设计。

一、DuckDB 是什么

2024 年 6 月 3 日，经过六年打磨，开源高性能分析型数据库 DuckDB 正式发布了 1.0.0 版本。

DuckDB 是一款功能强大的嵌入式分析型数据库，常被誉为 SQLite 的升级版。它不仅具备 SQLite 的轻量级、易用性，还支持更复杂的 SQL 查询和分析功能。

在数据迁移场景中，DuckDB 可以充当一个高效的 ETL 工具，帮助我们快速将 MongoDB 中的全量数据导入到 MySQL 数据库中。

二、DuckDB 的优势

• 简单易用：DuckDB 无需繁琐的安装过程，仅需一个启动文件即可运行。
• 灵活适配：支持直接映射 MySQL 数据库，实现本地读写 MySQL 表数据。
• 数据兼容：支持读取本地 JSON 文件，方便处理各种格式的数据。
• 高效处理：采用列式存储，擅长处理大规模数据集的分析查询。
• 本地运行：无需设置端口号，降低了部署和管理的复杂性。

三、迁移步骤示例

1、导出 MongoDB 数据

使用 mongoexport 工具将 MongoDB 中的目标集合导出为 JSON 文件。

shell> mongoexport -u admin -p 123456 -h 192.168.137.131:27017 -d test -c students 
--authenticationDatabase admin 
-o /mnt/mongo_bak/student.json

student.json文件内容：

shell> jq  .  /mnt/mongo_bak/students.json
{
  "_id": {
    "$oid": "66cfd71e67a1f9c596bdae5e"
  },
  "id": 1,
  "name": "张伟",
  "age": 20,
  "courses": [
    {
      "course_id": "CS101",
      "course_name": "计算机基础",
      "credits": 3
    },
    {
      "course_id": "MATH202",
      "course_name": "高等数学",
      "credits": 4
    }
  ],
  "address": {
    "street": "北京市朝阳区幸福路123号",
    "city": "北京",
    "state": "北京市",
    "zip": "100000"
  }
}
{
  "_id": {
    "$oid": "66cfd71e67a1f9c596bdae5f"
  },
  "id": 2,
  "name": "李娜",
  "age": 22,
  "courses": [
    {
      "course_id": "BIO301",
      "course_name": "生物学基础",
      "credits": 3
    }
  ],
  "address": {
    "street": "上海市浦东新区花园路456号",
    "city": "上海",
    "state": "上海市",
    "zip": "200000"
  }
}

2、加载到 DuckDB

shell> 修改duckdb提示符
shell> cat prompt.sql
.prompt 'duckdb> '


shell> -- 连接到me数控库里，me.duckdb为数据文件       
shell> ./duckdb me.duckdb -init prompt.sql

使用 DuckDB 的 SQL 语句将导出的 JSON 文件加载到一个 DuckDB 表中。

duckdb> -- 转换为MySQL输出形式
duckdb> .mode table


duckdb> -- 安装扩展MySQL和JSON
duckdb> INSTALL json;
duckdb> INSTALL mysql;


duckdb> create table student as 
SELECT * FROM read_json('/mnt/mongo_bak/students.json', columns = {'id': 'INTEGER', 'name':'VARCHAR', 'age':'INTEGER',   'courses': 'JSON', 'address': 'JSON'});

3、得到 DuckDB 表结构

duckdb> WITH table_columns AS (
    SELECT 
        name,
        type,
        CASE WHEN "notnull" = 1 THEN 'NOT NULL' ELSE '' END AS not_null
    FROM pragma_table_info('student')
)
SELECT 
    'CREATE TABLE student (' || 
    string_agg(name || ' ' || type || ' ' || not_null, ', ') || 
    ');' AS create_table_sql
FROM table_columns;
+------------------------------------------------------------------------------------------------+
|                                        create_table_sql                                        |
+------------------------------------------------------------------------------------------------+
| CREATE TABLE student (id INTEGER , name VARCHAR , age INTEGER , courses JSON , address JSON ); |
+------------------------------------------------------------------------------------------------+

4、创建 MySQL 表

在 MySQL 中创建一个与 DuckDB 表结构相同的表。

CREATE TABLE student (
    id int,
    name VARCHAR(255),
    age int,
    courses JSON,
    address JSON
);

5、数据迁移

使用 DuckDB 的 SQL 语句将数据从 DuckDB 表中插入到 MySQL 表中。

duckdb> ATTACH 'host=192.168.137.131 user=admin password=123456 port=6666 database=test' AS mysql_test (TYPE mysql_scanner);
duckdb> insert into mysql_test.student SELECT * FROM me.student;

四、DuckDB 在迁移过程中的作用

• 中间缓存：DuckDB 作为中间层，可以将 MongoDB 的数据加载到内存中，加速数据处理。
• 数据清洗：在 DuckDB 中，可以对数据进行清洗、转换和筛选，以满足 MySQL 的导入要求。
• 性能优化：DuckDB 的列式存储和高效查询引擎，可以显著提升数据迁移的性能。

五、总结

DuckDB 凭借其易用性、灵活性、高效性，为 MongoDB 到 MySQL 的数据迁移提供了便捷而强大的解决方案。通过合理利用 DuckDB，我们可以快速、准确地完成大规模数据的迁移任务。