Notebook · code/module-06/02-sinks-errors-and-dead-letters.ipynb

Download 02-sinks-errors-and-dead-letters.ipynb

Open it in JupyterLab or VS Code from the lab folder to run it. This page is a read-only view.

Sinks, errors and dead letters in Connect — practical

Finance wants Brightlane's tariffs in its reporting database. A CDC connector reads them from the pricing database, a JDBC sink connector writes them into the reporting database. Then a bad record stops the sink, and you make it park such records instead, and operate the connectors through their REST API.

Lab services: kafka-1..3, schema-registry, postgres, connect (docker compose --profile connect up -d --wait) · Lesson: modules/06-connect-and-cdc/02-sinks-errors-and-dead-letters.md

# Setup: find the lab's shared helpers and check the lab is running
import sys, pathlib
lab_root = next(p for p in [pathlib.Path.cwd(), *pathlib.Path.cwd().parents] if (p / "common").is_dir())
sys.path.insert(0, str(lab_root))

import json, time
from decimal import Decimal
from common import settings, lab

lab.require_kafka()
for name, url in [("Schema Registry", settings.SCHEMA_REGISTRY_URL + "/subjects"), ("Kafka Connect", settings.CONNECT_URL)]:
    lab.require_http(name, url, start_hint="docker compose --profile connect up -d --wait")
lab.print_versions()

Step 1: The source: tariff changes from the pricing database

The same CDC connector as in lesson 6.1, with its own names.

lab.remove_connectors("m06l02-")
lab.reset_pricing_db()
lab.pg("DROP TABLE IF EXISTS tariffs", db="reporting")
TOPIC = "m06l02_pricing.public.tariffs"
lab.connect_api("PUT", "/connectors/m06l02-pricing-cdc/config", {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector", "plugin.name": "pgoutput",
    "database.hostname": "postgres", "database.port": "5432", "database.dbname": "pricing",
    "database.user": "brightlane", "database.password": "brightlane-lab",
    "topic.prefix": "m06l02_pricing", "table.include.list": "public.tariffs",
    "slot.name": "m06l02_pricing_cdc", "publication.name": "m06l02_pricing_cdc",
    "publication.autocreate.mode": "filtered", "time.precision.mode": "connect",
    "topic.creation.default.replication.factor": "3", "topic.creation.default.partitions": "1"})
print("source:", lab.wait_connector("m06l02-pricing-cdc")["connector"]["state"])

Step 2: The sink: a JDBC connector into the reporting database

The Debezium JDBC sink understands change events directly: it upserts each after row by the record's key, and deletes the row for a delete event. schema.evolution=basic lets it create the table from the Avro schema.

sink = {
    "connector.class": "io.debezium.connector.jdbc.JdbcSinkConnector",
    "topics": TOPIC,
    "connection.url": "jdbc:postgresql://postgres:5432/reporting",
    "connection.username": "brightlane", "connection.password": "brightlane-lab",
    "insert.mode": "upsert",                # insert a new key, update an existing one
    "delete.enabled": "true",               # a delete event deletes the row
    "primary.key.mode": "record_key",       # the record key (tariff_id) is the primary key
    "schema.evolution": "basic",            # create the table, add new columns
    "collection.name.format": "tariffs",    # the table's name
}
lab.connect_api("PUT", "/connectors/m06l02-reporting-tariffs/config", sink)
lab.wait_connector("m06l02-reporting-tariffs")

count = lambda db: lab.pg("SELECT count(*) FROM tariffs", db=db)[0][0]
lab.wait_until(lambda: lab.pg("SELECT to_regclass('tariffs')", db="reporting")[0][0] and count("reporting") == 36,
               timeout=60, what="the reporting table")
print("reporting columns:", lab.pg("SELECT column_name, data_type FROM information_schema.columns "
                                   "WHERE table_name = 'tariffs' ORDER BY ordinal_position", db="reporting"))
print("rows: pricing", count("pricing"), "| reporting", count("reporting"))

Step 3: A price change, end to end

How long does a change take from the pricing database to the reporting database?

def reporting_price(tariff_id):
    rows = lab.pg("SELECT price_per_kwh FROM tariffs WHERE tariff_id = %s", (tariff_id,), db="reporting")
    return rows[0][0] if rows else None

started = time.monotonic()
lab.pg("UPDATE tariffs SET price_per_kwh = 0.5800 WHERE tariff_id = 'tar-site-C05-1'")
lab.wait_until(lambda: reporting_price("tar-site-C05-1") == Decimal("0.5800"), timeout=60, interval=0.1,
               what="the new price in reporting")
print(f"price visible in reporting after {time.monotonic() - started:.1f} s")
lab.pg("DELETE FROM tariffs WHERE tariff_id = 'tar-site-C06-1'")
lab.wait_until(lambda: count("reporting") == 35, timeout=60, what="the delete")
print("rows: pricing", count("pricing"), "| reporting", count("reporting"))

Break it: a record the sink can't read

A quick script writes one tariff as plain JSON into the topic, without the Avro serializer (as in lesson 5.1). Predict first: what does the sink do, and what happens to the next real price change?

from confluent_kafka import Producer

producer = Producer(settings.PRODUCER_DEFAULTS)
producer.produce(TOPIC, key=b"tar-site-N01-1", value=b'{"tariff_id": "tar-site-N01-1", "price_per_kwh": 0.39}')
producer.flush(10)
time.sleep(5)
lab.pg("UPDATE tariffs SET price_per_kwh = 0.5000 WHERE tariff_id = 'tar-site-N01-1'")
time.sleep(10)
status = lab.connect_api("GET", "/connectors/m06l02-reporting-tariffs/status")
print("connector:", status["connector"]["state"], "| task:", status["tasks"][0]["state"])
print("\n".join(line for line in status["tasks"][0]["trace"].splitlines() if line.startswith("Caused by"))[:400])
print("tar-site-N01-1 in reporting:", reporting_price("tar-site-N01-1"), "(pricing has 0.5000)")

The task stopped at the bad record, and every change after it waits. That's Connect's default, errors.tolerance=none.

Step 4: Tolerate bad records, park them in a dead-letter topic

Update the sink's configuration, then restart the failed task.

DLQ = "m06l02-reporting-tariffs.dlq"
sink.update({
    "errors.tolerance": "all",                                  # don't stop on a bad record ...
    "errors.deadletterqueue.topic.name": DLQ,                   # ... park it here
    "errors.deadletterqueue.topic.replication.factor": "3",
    "errors.deadletterqueue.context.headers.enable": "true",    # with headers saying why
    "errors.log.enable": "true"})
lab.connect_api("PUT", "/connectors/m06l02-reporting-tariffs/config", sink)
lab.connect_api("POST", "/connectors/m06l02-reporting-tariffs/restart?includeTasks=true&onlyFailed=true")
lab.wait_connector("m06l02-reporting-tariffs")
lab.wait_until(lambda: reporting_price("tar-site-N01-1") == Decimal("0.5000"), timeout=60, what="the waiting change")
print("tar-site-N01-1 in reporting:", reporting_price("tar-site-N01-1"))

The waiting price change arrived. The bad record is in the dead-letter topic, with Connect's headers:

from confluent_kafka import Consumer, TopicPartition

consumer = Consumer({"bootstrap.servers": settings.KAFKA_BOOTSTRAP, "group.id": "m06l02-inspect",
                     "enable.auto.commit": False})
consumer.assign([TopicPartition(DLQ, 0, 0)])
parked = consumer.poll(10)
consumer.close()
print("value:", parked.value())
for key, value in parked.headers():
    if "stacktrace" not in key:
        print(f"{key:<42} {value.decode()[:70]}")

Step 5: Operate the connectors

The REST API shows both connectors at once, their positions, and lets you stop and resume them.

time.sleep(6)                                     # Connect saves positions every 5 s in the lab
for name, info in lab.connect_api("GET", "/connectors?expand=status").items():
    s = info["status"]
    print(f"{name:<26} {s['type']:<7} {s['connector']['state']:<8} tasks: {[t['state'] for t in s['tasks']]}")
print(json.dumps(lab.connect_api("GET", "/connectors/m06l02-pricing-cdc/offsets")["offsets"][0]["offset"]))
print(json.dumps(lab.connect_api("GET", "/connectors/m06l02-reporting-tariffs/offsets")["offsets"][0]))

The source connector's position is a place in PostgreSQL's log (lsn); the sink's is an offset in the topic. stop shuts a connector down but keeps its configuration and position; resume starts it again from there:

lab.connect_api("PUT", "/connectors/m06l02-reporting-tariffs/stop")
lab.wait_until(lambda: lab.connect_api("GET", "/connectors/m06l02-reporting-tariffs/status", echo=False)
               ["connector"]["state"] == "STOPPED", timeout=30, what="the stop")
lab.pg("UPDATE tariffs SET price_per_kwh = 0.4800 WHERE tariff_id = 'tar-site-N01-1'")
time.sleep(5)
print("while stopped:", reporting_price("tar-site-N01-1"))
lab.connect_api("PUT", "/connectors/m06l02-reporting-tariffs/resume")
lab.wait_until(lambda: reporting_price("tar-site-N01-1") == Decimal("0.4800"), timeout=60, what="the catch-up")
print("after resume:", reporting_price("tar-site-N01-1"))

Clean up

Delete the connectors, slot and publication, the topics and schemas, the reporting table, and reset the pricing database. If anything gets stuck, docker compose down -v in the code/ folder resets the lab.

from confluent_kafka.admin import AdminClient
from confluent_kafka.schema_registry import SchemaRegistryClient

lab.remove_connectors("m06l02-")
admin = AdminClient({"bootstrap.servers": settings.KAFKA_BOOTSTRAP})
lab.delete_topics(admin, [t for t in admin.list_topics(timeout=10).topics if t.startswith("m06l02")])
admin.delete_consumer_groups(["m06l02-inspect", "connect-m06l02-reporting-tariffs"])
registry = SchemaRegistryClient({"url": settings.SCHEMA_REGISTRY_URL})
for subject in [s for s in registry.get_subjects() if s.startswith("m06l02")]:
    registry.delete_subject(subject)
    registry.delete_subject(subject, permanent=True)
lab.pg("DROP TABLE IF EXISTS tariffs", db="reporting")
lab.reset_pricing_db()
print("cleaned up")