NiFi Record flows and MatrixOne JDBC writes¶
Versions, installation and source role¶
Reference versions are NiFi 2.4.0 and JDK 21, with a real processor flow and MySQL JDBC. Deploy using the NiFi administration guide, install a JDBC driver readable by the worker and complete the shared fixtures. The earlier baseline read MySQL Records into MatrixOne. Configure separate source and destination DBCP services; passing writes do not certify every source.
Start with a one-shot INSERT baseline and probe UPSERT separately. Parameterized assignment of an unchanged primary key in MySQL UPSERT historically failed, reported in matrixone#29850. Keep explicit PutSQL/outbox adapter success separate from native PutDatabaseRecord UPSERT results.
Controller Services¶
Create these services in one process group. Use parameter contexts/sensitive properties for credentials and keep secrets out of exported flows. Configure TLS for the JDBC release; the example URL is for the local experiment.
Service |
Configuration |
|---|---|
Source DBCPConnectionPool |
MySQL source URL, |
MO DBCPConnectionPool |
|
AvroRecordSetWriter |
Inherit the Record schema and retain logical types |
AvroReader |
Use the Avro embedded schema |
Enable the services before creating the flow. Initialize DDL in the dedicated MatrixOne database. Read the two fixed fixture keys for the first source batch.
CREATE DATABASE IF NOT EXISTS doc_lake;
USE doc_lake;
CREATE TABLE IF NOT EXISTS nifi_demo (id BIGINT PRIMARY KEY, amount DECIMAL(38,18), payload VARBINARY(256), label VARCHAR(256), rev BIGINT);
SELECT id,CAST(amount AS CHAR),HEX(payload),label,rev FROM nifi_demo ORDER BY id;
Processors and connections¶
Processor |
Property/action |
|---|---|
ExecuteSQLRecord |
Source DBCP; AvroRecordSetWriter; SQL Query |
PutDatabaseRecord |
AvroReader; MO DBCP; Table Name= |
Connect ExecuteSQLRecord success → PutDatabaseRecord. Route success to a completion queue and failure/retry to retained troubleshooting/retry queues. Do not auto-terminate failures. Set explicit back pressure and bounded batch sizes. Use Run Once initially to avoid repeated polling causing INSERT conflicts.
Check VALID status, bulletins, queues/provenance and fresh JDBC readback. Expect id 9007199254740993, the complete decimal and 00FF80, plus the NULL/empty values for id 2.
UPSERT, deletes, state and schema¶
After the baseline, switch Statement Type to UPSERT, define Update Keys, change source label/rev and replay once. On affected releases, retain generated SQL and the failed FlowFile. Recheck the native route after a fix; passing custom SQL is a separate result. Explicit PutSQL routes need parameter binding and versioned mappings, not payload-based SQL concatenation.
A QueryDatabaseTableRecord maximum-value cursor only tracks selected columns; it does not reliably discover UPDATE/DELETE on old keys. Use a transactional outbox/CDC with op/key/version and explicit DELETE/UPDATE/INSERT effects. Check ordering for the same key and old-key deletion during primary-key changes.
Record queues, processor state and database commits. The gap between SQL commit and FlowFile session ACK can cause replay and needs idempotence or event deduplication. Added fields require matching Record schema, destination DDL and writer mapping. A functional NiFi restart/replay check is not replicated repository durability or end-to-end exactly-once.