PyArrow IPC and DuckDB Parquet bridges to MatrixOne

Route and installation

This route executed PyArrow IPC read/write and DuckDB SQL transformation, Parquet export and reread, followed by a DBAPI load into MatrixOne. It is an explicit SQL bridge, not evidence that MatrixOne natively scans every lake format. Reference versions are PyArrow 26.0.0 and DuckDB 1.5.6. Complete the shared connection and install pyarrow duckdb pymysql.

Define the file schema

The complete script first performs file round-trips with field comparisons, then loads example ID 201. Use microsecond timestamps for the SQL DATETIME range rather than a nanosecond type with a narrower range. SQL TIME is a duration: use signed microseconds in files. When representing decimal65 as exact text, retain precision, scale metadata and the destination SQL type. SQL DECIMAL limits do not define native Iceberg or DuckDB decimal limits.

from decimal import Decimal
from pathlib import Path
import duckdb
import pyarrow as pa
import pyarrow.ipc as ipc
from mo_common import connect

schema = pa.schema([("id", pa.int64()), ("amount", pa.decimal128(38,18)),
                    ("payload", pa.binary()), ("label", pa.string()), ("rev", pa.int64())])
t = pa.Table.from_pylist([dict(id=201, amount=Decimal("12345678901234567890.123456789012345678"),
                              payload=b"\x00\xff\x80", label="文件😀", rev=0)], schema=schema)
with pa.OSFile("rows.arrow", "wb") as f:
    with ipc.new_file(f, t.schema) as writer:
        writer.write_table(t)
with pa.memory_map("rows.arrow", "r") as f:
    restored = ipc.open_file(f).read_all()
assert restored.to_pylist() == t.to_pylist()
with duckdb.connect() as d:
    d.execute("SET threads=1")
    d.register("input_rows", restored)
    transformed = d.execute("SELECT id,amount,payload,label||'→ETL' AS label,rev+1 AS rev FROM input_rows").fetch_arrow_table()
    d.register("export_rows", transformed)
    path = str(Path("rows.parquet").resolve())
    d.execute("COPY export_rows TO ? (FORMAT PARQUET)", [path])
    scanned = d.execute("SELECT * FROM read_parquet(?)", [path]).fetch_arrow_table()
assert scanned.to_pylist() == transformed.to_pylist()
rows = [[r[k] for k in ("id","amount","payload","label","rev")] for r in scanned.to_pylist()]
with connect() as d:
    try:
        with d.cursor() as c:
            c.executemany("INSERT INTO lake_demo(id,amount,payload,label,rev) VALUES(%s,%s,%s,%s,%s)", rows)
        d.commit()
    except Exception:
        d.rollback()
        raise

Type boundaries and recovery

Archive the schema with the Parquet/IPC data. UInt64 needs uint64 or a sufficiently precise decimal representation, with no double intermediary. Document whether JSON, ENUM/SET and UUID string mappings preserve domain constraints. Check sign and total microseconds for -1 microsecond and TIME values exceeding24 hours.

File export and SQL commit are separate boundaries. Retain a file hash, batch ID, destination key range and SQL commit receipt. Read back an uncertain commit before replay. Incremental files do not automatically include deletes: supply a keyed tombstone dataset. Schema evolution must update both file schema and SQL DDL.

Each bridge completed 5000 combinations in the main plan; file and SQL effects remain attributed to the native component and explicit adapter. Historical mapping report: matrixone#29845.