Exact event loading with Redpanda Connect and kafka-python¶
Route and prerequisites¶
The reference is Redpanda Connect4.113.0: real kafka_franz input → sql_raw output → fallback Kafka DLQ, with actual kafka-python producer/consumer APIs. Checks covered primary-key idempotence, deletes, restart, offline changes and coordinated added columns. This does not establish automatic DDL or cross-system exactly-once.
Complete the shared connection. Prepare accessible input topic lake-events and DLQ lake-dlq, recording partitions/retention, and install Redpanda Connect plus kafka-python. This page writes lake_demo using example key 501; do not replay into a business database. Inject MO_DSN from a secret manager using your MySQL driver’s DSN format (shape user:password@tcp(host:6001)/doc_lake) and check special-character handling separately.
Runtime configuration¶
Save as redpanda.yaml. Represent 64-bit integers/decimals as strings and binary as hex; NULL remains JSON null. Bind SQL parameters rather than concatenating values. Start with one thread/in-flight request; same-key multiple partitions or threads need separate ordering checks.
input:
kafka_franz:
seed_brokers: ["${KAFKA_BOOTSTRAP}"]
topics: [lake-events]
consumer_group: lake-demo-sink
start_offset: earliest
commit_period: 1s
checkpoint_limit: 1
pipeline:
threads: 1
output:
fallback:
- switch:
cases:
- check: 'this.op == "delete"'
output:
sql_raw:
driver: mysql
dsn: "${MO_DSN}"
query: 'DELETE FROM lake_demo WHERE id=?'
args_mapping: 'root = [this.id]'
max_in_flight: 1
- output:
sql_raw:
driver: mysql
dsn: "${MO_DSN}"
query: 'INSERT INTO lake_demo(id,amount,payload,label,rev) VALUES(?,?,UNHEX(?),?,?) ON DUPLICATE KEY UPDATE amount=VALUES(amount),payload=VALUES(payload),label=VALUES(label),rev=VALUES(rev)'
args_mapping: 'root = [this.id,this.amount,this.binary_hex,this.label,this.rev]'
max_in_flight: 1
- kafka_franz:
seed_brokers: ["${KAFKA_BOOTSTRAP}"]
topic: lake-dlq
key: '${! this.id }'
shutdown_timeout: 5s
redpanda-connect lint redpanda.yaml
redpanda-connect run redpanda.yaml
Send with kafka-python and verify¶
Run the producer in another terminal. Expect id 501, amount -1e-18, binary 00FF80 and the Unicode label. Resend the same keyed event and expect one row; sending {"op":"delete","id":"501"} should remove it. Retain original topic events and send receipts.
import os, json
from kafka import KafkaProducer
p = KafkaProducer(bootstrap_servers=os.environ["KAFKA_BOOTSTRAP"],acks="all")
try:
event = {"op":"upsert","id":"501","amount":"-0.000000000000000001",
"binary_hex":"00ff80","label":"Kafka😀","rev":"1"}
receipt = p.send("lake-events",key=event["id"].encode(),
value=json.dumps(event,ensure_ascii=False).encode()).get(timeout=10)
print(receipt.topic,receipt.partition,receipt.offset)
p.flush()
finally:
p.close()
DLQ, schema and recovery¶
Send an overlong label as an invalid record, followed by a valid one. Check the complete original record in DLQ, valid-record progress, absence of the bad SQL row and committed next offset. Fallback acceptance into DLQ can advance offsets, so a committed offset does not mean every row reached SQL. Audit and repair/replay the DLQ separately.
Commit new events while the component is stopped and verify offline catch-up after resume. Migrate SQL DDL before updating args_mapping, event schema and old-event compatibility. Protect replay with version conditions or event deduplication; final-value UPSERT alone does not protect against reordering. A custom kafka-python SQL consumer must implement commit/offset ordering, rebalance, DLQ and deduplication itself. Producer acks=all is not an external database exactly-once guarantee.