开源利器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，我们可以快速、准确地完成大规模数据的迁移任务。