How to Migrate Data from MongoDB to MySQL Using DuckDB

DuckDB 1.0.0 Release

DuckDB 1.0.0 was released on June 3, 2024. It is an embedded analytical database that combines SQLite‑like lightweight deployment with columnar storage and full SQL support for large‑scale analytics.

Why Use DuckDB in a Migration Pipeline

Single‑file deployment : No server installation; the engine runs from a single executable.

Direct MySQL integration : DuckDB can attach to a MySQL instance and read/write MySQL tables.

JSON compatibility : Built‑in JSON extension reads local JSON files, simplifying ingestion of semi‑structured data.

Columnar storage : Optimized for analytical queries on big datasets.

Local execution : No network ports or external services are required.

Step‑by‑Step Migration Example

1. Export MongoDB collection to JSON

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

Sample document in student.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" }
}

2. Load JSON into DuckDB

Start DuckDB and install the required extensions. INSTALL json;</code><code>INSTALL mysql; Create a DuckDB table from the JSON file, explicitly specifying column types to avoid type inference errors.

CREATE TABLE student AS 
SELECT * FROM read_json('/mnt/mongo_bak/student.json', 
    columns = {
        'id': 'INTEGER',
        'name': 'VARCHAR',
        'age': 'INTEGER',
        'courses': 'JSON',
        'address': 'JSON'
    });

3. Verify DuckDB table schema

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;

Resulting DDL:

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

4. Create the target MySQL table

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

5. Transfer data from DuckDB to MySQL

Attach the MySQL instance as a foreign data source and insert the rows.

ATTACH 'host=192.168.137.131 user=admin password=123456 port=6666 database=test' 
AS mysql_test (TYPE mysql_scanner);
INSERT INTO mysql_test.student SELECT * FROM student;

Role of DuckDB in the Migration Pipeline

Intermediate cache : Loads the entire MongoDB export into memory, enabling fast downstream processing.

Data cleaning & transformation : Allows SQL‑based filtering, column renaming, or type conversion before loading into MySQL.

Performance boost : Columnar storage and vectorized execution reduce the time required for large‑scale data movement.