Native Spark JDBC reads, writes, types and partitions¶
Pin versions and partitions¶
This round used PySpark 3.5.7, Temurin Java 17 and Connector/J 8.4.0. Twenty native tinyint append cases passed with two JDBC writer partitions and batchsize=17. That window covers append at limited volume; temporal values, wide decimals, reads and retries require separate acceptance.
Prepare actual tables¶
Use a dedicated test database and the connection fixtures. The source is MySQL and the destination is MatrixOne. Create this table on both; insert the fixture only on the source. Set SRC_JDBC_URL/SRC_USER/SRC_PASSWORD and MO_JDBC_URL/MO_USER/MO_PASSWORD for the respective databases, fixing UTF-8 and UTC. Local plaintext examples do not qualify production TLS.
CREATE TABLE native_demo (
id BIGINT PRIMARY KEY,
amount DECIMAL(38,18), biguint BIGINT UNSIGNED,
payload VARBINARY(256), ts DATETIME(6), elapsed TIME(6), label VARCHAR(256)
);
INSERT INTO native_demo VALUES
(1,12345678901234567890.123456789012345678,18446744073709551615,
X'00FF80','2024-02-29 12:00:00.123456','-00:00:00.000001','中文😀'),
(2,NULL,NULL,NULL,NULL,NULL,NULL),
(3,0,9007199254740993,X'','1970-01-01 00:00:00','37:00:00.999999','');
Read, transform and write¶
Set MO_JDBC_JAR to the verified driver. This uses an actual JDBC reader, two read partitions and two writer partitions; CAST and Base64 are explicit adapters. DecimalType precision is limited to 38, so qualify SQL DECIMAL(65,30) reflection separately. Negative or over-24-hour TIME is not ordinary time of day.
import os
from pyspark.sql import SparkSession, functions as F
spark = (SparkSession.builder.appName("matrixone-native-scan")
.config("spark.jars", os.environ["MO_JDBC_JAR"])
.config("spark.sql.session.timeZone", "UTC").getOrCreate())
try:
query = """SELECT id, CAST(amount AS CHAR) AS amount,
CAST(biguint AS CHAR) AS biguint,
REPLACE(TO_BASE64(payload), CHAR(10), '') AS payload,
CAST(ts AS CHAR) AS ts, CAST(elapsed AS CHAR) AS elapsed, label
FROM native_demo WHERE id BETWEEN 1 AND 3"""
frame = (spark.read.format("jdbc")
.option("url", os.environ["SRC_JDBC_URL"])
.option("dbtable", "(" + query + ") fixture")
.option("driver", "com.mysql.cj.jdbc.Driver")
.option("user", os.environ["SRC_USER"])
.option("password", os.environ["SRC_PASSWORD"])
.option("partitionColumn", "id")
.option("lowerBound", "1").option("upperBound", "4")
.option("numPartitions", "2").option("fetchsize", "17").load())
frame = frame.withColumn("payload", F.unbase64("payload"))
(frame.repartition(2).write.format("jdbc")
.option("url", os.environ["MO_JDBC_URL"]).option("dbtable", "native_demo")
.option("driver", "com.mysql.cj.jdbc.Driver")
.option("user", os.environ["MO_USER"]).option("password", os.environ["MO_PASSWORD"])
.option("batchsize", "17").option("numPartitions", "2")
.option("isolationLevel", "READ_COMMITTED").mode("append").save())
finally:
spark.stop()
Verify coverage and fidelity¶
Read lowerBound/upperBound calculate partition strides; they do not replace a WHERE filter. The subquery explicitly fixes the range. Qualify NULL partition keys, boundary keys and concurrent source updates separately. Compare these values through fresh connections and preserve stages and partitions committed before failure.
SELECT id, CAST(amount AS CHAR), CAST(biguint AS CHAR), HEX(payload),
CAST(ts AS CHAR), CAST(elapsed AS CHAR), label
FROM native_demo ORDER BY id;
Writer partitions commit independently; a failed partition does not imply the whole append rolled back. Task retries and speculation can replay writes. Partition success or final key deduplication is not exactly-once. Preserve failed default-mapping attempts before recording separate adapter cases. Deletion and target schema changes need their own protocol; a DataFrame batch write is not CDC.
Measured scope of this round¶
On 2026-10-13, MatrixOne commit 652c643666428f1c434e03369834f2a9bead7c27 was tested against an isolated MySQL 8.4 source/control. Each scan case used 256 rows, batch=17 and long Unicode labels, with original all-field oracles, fresh-connection reads and typed archives. Each table entry covers both MatrixOne and MySQL destinations.
Type |
Path |
Result at both destinations |
|---|---|---|
int64 |
text_adapter |
Passed |
uint64 |
text_adapter |
Passed |
decimal38_18 |
raw |
Passed |
decimal38_18 |
text_adapter |
Passed |
decimal65_30 |
raw |
Execution failed |
decimal65_30 |
text_adapter |
Passed |
varchar_utf8 |
text_adapter |
Passed |
varbinary |
text_adapter |
Passed |
datetime6 |
raw |
Passed |
datetime6 |
text_adapter |
Passed |
time6 |
raw |
Execution failed |
time6 |
text_adapter |
Passed |
Of 24 real read/write cases, 20 passed and four failed. DECIMAL(65,30) exceeds Spark DecimalType precision in default mapping; extended TIME fails Connector/J’s default time-of-day conversion. Source reads blocked both destinations. Corresponding explicit text paths passed, as did this round’s native DATETIME(6) boundary values. Failed attempts remain separate from adapter passes.
Temporary-filesystem, bounded-window results do not certify disk throughput, long soak, HA, distributed scale or cross-system EOS. Evidence and documentation work are tracked in documentation issue #428.