{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "c00-ea460751",
   "metadata": {},
   "source": [
    "# Sinks, errors and dead letters in Connect — practical\n",
    "\n",
    "Finance wants Brightlane's tariffs in its reporting database. A CDC connector reads them from the\n",
    "pricing database, a JDBC sink connector writes them into the reporting database. Then a bad\n",
    "record stops the sink, and you make it park such records instead, and operate the connectors\n",
    "through their REST API.\n",
    "\n",
    "**Lab services:** kafka-1..3, schema-registry, postgres, connect (`docker compose --profile connect up -d --wait`)\n",
    "· **Lesson:** `modules/06-connect-and-cdc/02-sinks-errors-and-dead-letters.md`"
   ]
  },
  {
   "cell_type": "code",
   "id": "c01-aff2141e",
   "metadata": {},
   "source": [
    "# Setup: find the lab's shared helpers and check the lab is running\n",
    "import sys, pathlib\n",
    "lab_root = next(p for p in [pathlib.Path.cwd(), *pathlib.Path.cwd().parents] if (p / \"common\").is_dir())\n",
    "sys.path.insert(0, str(lab_root))\n",
    "\n",
    "import json, time\n",
    "from decimal import Decimal\n",
    "from common import settings, lab\n",
    "\n",
    "lab.require_kafka()\n",
    "for name, url in [(\"Schema Registry\", settings.SCHEMA_REGISTRY_URL + \"/subjects\"), (\"Kafka Connect\", settings.CONNECT_URL)]:\n",
    "    lab.require_http(name, url, start_hint=\"docker compose --profile connect up -d --wait\")\n",
    "lab.print_versions()"
   ],
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "id": "c02-2a814b65",
   "metadata": {},
   "source": [
    "## Step 1: The source: tariff changes from the pricing database\n",
    "The same CDC connector as in lesson 6.1, with its own names."
   ]
  },
  {
   "cell_type": "code",
   "id": "c03-63ace380",
   "metadata": {},
   "source": [
    "lab.remove_connectors(\"m06l02-\")\n",
    "lab.reset_pricing_db()\n",
    "lab.pg(\"DROP TABLE IF EXISTS tariffs\", db=\"reporting\")\n",
    "TOPIC = \"m06l02_pricing.public.tariffs\"\n",
    "lab.connect_api(\"PUT\", \"/connectors/m06l02-pricing-cdc/config\", {\n",
    "    \"connector.class\": \"io.debezium.connector.postgresql.PostgresConnector\", \"plugin.name\": \"pgoutput\",\n",
    "    \"database.hostname\": \"postgres\", \"database.port\": \"5432\", \"database.dbname\": \"pricing\",\n",
    "    \"database.user\": \"brightlane\", \"database.password\": \"brightlane-lab\",\n",
    "    \"topic.prefix\": \"m06l02_pricing\", \"table.include.list\": \"public.tariffs\",\n",
    "    \"slot.name\": \"m06l02_pricing_cdc\", \"publication.name\": \"m06l02_pricing_cdc\",\n",
    "    \"publication.autocreate.mode\": \"filtered\", \"time.precision.mode\": \"connect\",\n",
    "    \"topic.creation.default.replication.factor\": \"3\", \"topic.creation.default.partitions\": \"1\"})\n",
    "print(\"source:\", lab.wait_connector(\"m06l02-pricing-cdc\")[\"connector\"][\"state\"])"
   ],
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "id": "c04-552628cc",
   "metadata": {},
   "source": [
    "## Step 2: The sink: a JDBC connector into the reporting database\n",
    "The Debezium JDBC sink understands change events directly: it upserts each `after` row by the\n",
    "record's key, and deletes the row for a delete event. `schema.evolution=basic` lets it create the\n",
    "table from the Avro schema."
   ]
  },
  {
   "cell_type": "code",
   "id": "c05-5c598416",
   "metadata": {},
   "source": [
    "sink = {\n",
    "    \"connector.class\": \"io.debezium.connector.jdbc.JdbcSinkConnector\",\n",
    "    \"topics\": TOPIC,\n",
    "    \"connection.url\": \"jdbc:postgresql://postgres:5432/reporting\",\n",
    "    \"connection.username\": \"brightlane\", \"connection.password\": \"brightlane-lab\",\n",
    "    \"insert.mode\": \"upsert\",                # insert a new key, update an existing one\n",
    "    \"delete.enabled\": \"true\",               # a delete event deletes the row\n",
    "    \"primary.key.mode\": \"record_key\",       # the record key (tariff_id) is the primary key\n",
    "    \"schema.evolution\": \"basic\",            # create the table, add new columns\n",
    "    \"collection.name.format\": \"tariffs\",    # the table's name\n",
    "}\n",
    "lab.connect_api(\"PUT\", \"/connectors/m06l02-reporting-tariffs/config\", sink)\n",
    "lab.wait_connector(\"m06l02-reporting-tariffs\")\n",
    "\n",
    "count = lambda db: lab.pg(\"SELECT count(*) FROM tariffs\", db=db)[0][0]\n",
    "lab.wait_until(lambda: lab.pg(\"SELECT to_regclass('tariffs')\", db=\"reporting\")[0][0] and count(\"reporting\") == 36,\n",
    "               timeout=60, what=\"the reporting table\")\n",
    "print(\"reporting columns:\", lab.pg(\"SELECT column_name, data_type FROM information_schema.columns \"\n",
    "                                   \"WHERE table_name = 'tariffs' ORDER BY ordinal_position\", db=\"reporting\"))\n",
    "print(\"rows: pricing\", count(\"pricing\"), \"| reporting\", count(\"reporting\"))"
   ],
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "id": "c06-0bf61326",
   "metadata": {},
   "source": [
    "## Step 3: A price change, end to end\n",
    "How long does a change take from the pricing database to the reporting database?"
   ]
  },
  {
   "cell_type": "code",
   "id": "c07-9e909942",
   "metadata": {},
   "source": [
    "def reporting_price(tariff_id):\n",
    "    rows = lab.pg(\"SELECT price_per_kwh FROM tariffs WHERE tariff_id = %s\", (tariff_id,), db=\"reporting\")\n",
    "    return rows[0][0] if rows else None\n",
    "\n",
    "started = time.monotonic()\n",
    "lab.pg(\"UPDATE tariffs SET price_per_kwh = 0.5800 WHERE tariff_id = 'tar-site-C05-1'\")\n",
    "lab.wait_until(lambda: reporting_price(\"tar-site-C05-1\") == Decimal(\"0.5800\"), timeout=60, interval=0.1,\n",
    "               what=\"the new price in reporting\")\n",
    "print(f\"price visible in reporting after {time.monotonic() - started:.1f} s\")\n",
    "lab.pg(\"DELETE FROM tariffs WHERE tariff_id = 'tar-site-C06-1'\")\n",
    "lab.wait_until(lambda: count(\"reporting\") == 35, timeout=60, what=\"the delete\")\n",
    "print(\"rows: pricing\", count(\"pricing\"), \"| reporting\", count(\"reporting\"))"
   ],
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "id": "c08-b4567269",
   "metadata": {},
   "source": [
    "## Break it: a record the sink can't read\n",
    "A quick script writes one tariff as plain JSON into the topic, without the Avro serializer (as in\n",
    "lesson 5.1). Predict first: what does the sink do, and what happens to the next real price change?"
   ]
  },
  {
   "cell_type": "code",
   "id": "c09-4e8403a2",
   "metadata": {},
   "source": [
    "from confluent_kafka import Producer\n",
    "\n",
    "producer = Producer(settings.PRODUCER_DEFAULTS)\n",
    "producer.produce(TOPIC, key=b\"tar-site-N01-1\", value=b'{\"tariff_id\": \"tar-site-N01-1\", \"price_per_kwh\": 0.39}')\n",
    "producer.flush(10)\n",
    "time.sleep(5)\n",
    "lab.pg(\"UPDATE tariffs SET price_per_kwh = 0.5000 WHERE tariff_id = 'tar-site-N01-1'\")\n",
    "time.sleep(10)\n",
    "status = lab.connect_api(\"GET\", \"/connectors/m06l02-reporting-tariffs/status\")\n",
    "print(\"connector:\", status[\"connector\"][\"state\"], \"| task:\", status[\"tasks\"][0][\"state\"])\n",
    "print(\"\\n\".join(line for line in status[\"tasks\"][0][\"trace\"].splitlines() if line.startswith(\"Caused by\"))[:400])\n",
    "print(\"tar-site-N01-1 in reporting:\", reporting_price(\"tar-site-N01-1\"), \"(pricing has 0.5000)\")"
   ],
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "id": "c10-ab6eb568",
   "metadata": {},
   "source": [
    "The task stopped at the bad record, and every change after it waits. That's Connect's default,\n",
    "`errors.tolerance=none`.\n",
    "\n",
    "## Step 4: Tolerate bad records, park them in a dead-letter topic\n",
    "Update the sink's configuration, then restart the failed task."
   ]
  },
  {
   "cell_type": "code",
   "id": "c11-b7e6b0ed",
   "metadata": {},
   "source": [
    "DLQ = \"m06l02-reporting-tariffs.dlq\"\n",
    "sink.update({\n",
    "    \"errors.tolerance\": \"all\",                                  # don't stop on a bad record ...\n",
    "    \"errors.deadletterqueue.topic.name\": DLQ,                   # ... park it here\n",
    "    \"errors.deadletterqueue.topic.replication.factor\": \"3\",\n",
    "    \"errors.deadletterqueue.context.headers.enable\": \"true\",    # with headers saying why\n",
    "    \"errors.log.enable\": \"true\"})\n",
    "lab.connect_api(\"PUT\", \"/connectors/m06l02-reporting-tariffs/config\", sink)\n",
    "lab.connect_api(\"POST\", \"/connectors/m06l02-reporting-tariffs/restart?includeTasks=true&onlyFailed=true\")\n",
    "lab.wait_connector(\"m06l02-reporting-tariffs\")\n",
    "lab.wait_until(lambda: reporting_price(\"tar-site-N01-1\") == Decimal(\"0.5000\"), timeout=60, what=\"the waiting change\")\n",
    "print(\"tar-site-N01-1 in reporting:\", reporting_price(\"tar-site-N01-1\"))"
   ],
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "id": "c12-cb857c2a",
   "metadata": {},
   "source": [
    "The waiting price change arrived. The bad record is in the dead-letter topic, with Connect's\n",
    "headers:"
   ]
  },
  {
   "cell_type": "code",
   "id": "c13-0edc8fd8",
   "metadata": {},
   "source": [
    "from confluent_kafka import Consumer, TopicPartition\n",
    "\n",
    "consumer = Consumer({\"bootstrap.servers\": settings.KAFKA_BOOTSTRAP, \"group.id\": \"m06l02-inspect\",\n",
    "                     \"enable.auto.commit\": False})\n",
    "consumer.assign([TopicPartition(DLQ, 0, 0)])\n",
    "parked = consumer.poll(10)\n",
    "consumer.close()\n",
    "print(\"value:\", parked.value())\n",
    "for key, value in parked.headers():\n",
    "    if \"stacktrace\" not in key:\n",
    "        print(f\"{key:<42} {value.decode()[:70]}\")"
   ],
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "id": "c14-7580dc1d",
   "metadata": {},
   "source": [
    "## Step 5: Operate the connectors\n",
    "The REST API shows both connectors at once, their positions, and lets you stop and resume them."
   ]
  },
  {
   "cell_type": "code",
   "id": "c15-ae65ad92",
   "metadata": {},
   "source": [
    "time.sleep(6)                                     # Connect saves positions every 5 s in the lab\n",
    "for name, info in lab.connect_api(\"GET\", \"/connectors?expand=status\").items():\n",
    "    s = info[\"status\"]\n",
    "    print(f\"{name:<26} {s['type']:<7} {s['connector']['state']:<8} tasks: {[t['state'] for t in s['tasks']]}\")\n",
    "print(json.dumps(lab.connect_api(\"GET\", \"/connectors/m06l02-pricing-cdc/offsets\")[\"offsets\"][0][\"offset\"]))\n",
    "print(json.dumps(lab.connect_api(\"GET\", \"/connectors/m06l02-reporting-tariffs/offsets\")[\"offsets\"][0]))"
   ],
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "id": "c16-44fefa86",
   "metadata": {},
   "source": [
    "The source connector's position is a place in PostgreSQL's log (`lsn`); the sink's is an offset in\n",
    "the topic. `stop` shuts a connector down but keeps its configuration and position; `resume`\n",
    "starts it again from there:"
   ]
  },
  {
   "cell_type": "code",
   "id": "c17-79e20f53",
   "metadata": {},
   "source": [
    "lab.connect_api(\"PUT\", \"/connectors/m06l02-reporting-tariffs/stop\")\n",
    "lab.wait_until(lambda: lab.connect_api(\"GET\", \"/connectors/m06l02-reporting-tariffs/status\", echo=False)\n",
    "               [\"connector\"][\"state\"] == \"STOPPED\", timeout=30, what=\"the stop\")\n",
    "lab.pg(\"UPDATE tariffs SET price_per_kwh = 0.4800 WHERE tariff_id = 'tar-site-N01-1'\")\n",
    "time.sleep(5)\n",
    "print(\"while stopped:\", reporting_price(\"tar-site-N01-1\"))\n",
    "lab.connect_api(\"PUT\", \"/connectors/m06l02-reporting-tariffs/resume\")\n",
    "lab.wait_until(lambda: reporting_price(\"tar-site-N01-1\") == Decimal(\"0.4800\"), timeout=60, what=\"the catch-up\")\n",
    "print(\"after resume:\", reporting_price(\"tar-site-N01-1\"))"
   ],
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "id": "c18-c7c3f9fb",
   "metadata": {},
   "source": [
    "## Clean up\n",
    "Delete the connectors, slot and publication, the topics and schemas, the reporting table, and\n",
    "reset the pricing database. If anything gets stuck, `docker compose down -v` in the `code/` folder\n",
    "resets the lab."
   ]
  },
  {
   "cell_type": "code",
   "id": "c19-73a62d31",
   "metadata": {},
   "source": [
    "from confluent_kafka.admin import AdminClient\n",
    "from confluent_kafka.schema_registry import SchemaRegistryClient\n",
    "\n",
    "lab.remove_connectors(\"m06l02-\")\n",
    "admin = AdminClient({\"bootstrap.servers\": settings.KAFKA_BOOTSTRAP})\n",
    "lab.delete_topics(admin, [t for t in admin.list_topics(timeout=10).topics if t.startswith(\"m06l02\")])\n",
    "admin.delete_consumer_groups([\"m06l02-inspect\", \"connect-m06l02-reporting-tariffs\"])\n",
    "registry = SchemaRegistryClient({\"url\": settings.SCHEMA_REGISTRY_URL})\n",
    "for subject in [s for s in registry.get_subjects() if s.startswith(\"m06l02\")]:\n",
    "    registry.delete_subject(subject)\n",
    "    registry.delete_subject(subject, permanent=True)\n",
    "lab.pg(\"DROP TABLE IF EXISTS tariffs\", db=\"reporting\")\n",
    "lab.reset_pricing_db()\n",
    "print(\"cleaned up\")"
   ],
   "execution_count": null,
   "outputs": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "pygments_lexer": "ipython3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
