{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "c00-9b589165",
   "metadata": {},
   "source": [
    "# Change data capture from PostgreSQL — practical\n",
    "\n",
    "Brightlane's tariffs live in PostgreSQL. You register a Debezium connector with Kafka Connect,\n",
    "read the snapshot and the change events it writes, flatten them into a compacted \"latest tariff\"\n",
    "topic, and then watch a replication slot hold on to the database's log while nothing seems wrong.\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/01-change-data-capture.md`"
   ]
  },
  {
   "cell_type": "code",
   "id": "c01-eeb065d1",
   "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 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-2eb2c3b1",
   "metadata": {},
   "source": [
    "## Step 1: The Connect worker\n",
    "Kafka Connect has a REST API. Ask the worker for its version and the connector plug-ins it has."
   ]
  },
  {
   "cell_type": "code",
   "id": "c03-70b396aa",
   "metadata": {},
   "source": [
    "print(lab.connect_api(\"GET\", \"/\"))\n",
    "for plugin in lab.connect_api(\"GET\", \"/connector-plugins\"):\n",
    "    if \"debezium\" in plugin[\"class\"]:\n",
    "        print(f\"{plugin['type']:<7} {plugin['class']}  {plugin['version']}\")"
   ],
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "id": "c04-e214997c",
   "metadata": {},
   "source": [
    "## Step 2: The pricing database\n",
    "36 tariffs, one per site. The notebook resets the database to its starting state first."
   ]
  },
  {
   "cell_type": "code",
   "id": "c05-da7cfd63",
   "metadata": {},
   "source": [
    "lab.remove_connectors(\"m06l01-\")\n",
    "lab.reset_pricing_db()\n",
    "for row in lab.pg(\"SELECT tariff_id, site_id, price_per_kwh, idle_fee_per_min FROM tariffs ORDER BY tariff_id LIMIT 3\"):\n",
    "    print(row)\n",
    "print(lab.pg(\"SELECT count(*) FROM tariffs\")[0][0], \"tariffs\")"
   ],
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "id": "c06-38f1f999",
   "metadata": {},
   "source": [
    "## Step 3: Register a CDC connector\n",
    "A connector is created by sending its configuration to the REST API. The topics it writes are\n",
    "named `<topic.prefix>.<schema>.<table>`. The brokers don't create topics automatically, so the\n",
    "connector creates them itself (`topic.creation.default.*`)."
   ]
  },
  {
   "cell_type": "code",
   "id": "c07-d84b8ca2",
   "metadata": {},
   "source": [
    "PREFIX = \"m06l01_pricing\"\n",
    "cdc = {\n",
    "    \"connector.class\": \"io.debezium.connector.postgresql.PostgresConnector\",\n",
    "    \"plugin.name\": \"pgoutput\",                         # PostgreSQL's built-in logical decoding\n",
    "    \"database.hostname\": \"postgres\", \"database.port\": \"5432\", \"database.dbname\": \"pricing\",\n",
    "    \"database.user\": \"brightlane\", \"database.password\": \"brightlane-lab\",\n",
    "    \"topic.prefix\": PREFIX,\n",
    "    \"table.include.list\": \"public.tariffs\",\n",
    "    \"slot.name\": \"m06l01_pricing_cdc\", \"publication.name\": \"m06l01_pricing_cdc\",\n",
    "    \"publication.autocreate.mode\": \"filtered\",\n",
    "    \"time.precision.mode\": \"connect\",                  # dates as Avro dates\n",
    "    \"topic.creation.default.replication.factor\": \"3\", \"topic.creation.default.partitions\": \"1\",\n",
    "}\n",
    "lab.connect_api(\"PUT\", \"/connectors/m06l01-pricing-cdc/config\", cdc)\n",
    "status = lab.wait_connector(\"m06l01-pricing-cdc\")\n",
    "print(status[\"connector\"][\"state\"], [t[\"state\"] for t in status[\"tasks\"]])"
   ],
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "id": "c08-24941db0",
   "metadata": {},
   "source": [
    "## Step 4: Read the snapshot\n",
    "The first thing the connector does is a **snapshot**: it reads every existing row and writes one\n",
    "event per row. Keys and values are Avro, so the module 5 deserializer reads them."
   ]
  },
  {
   "cell_type": "code",
   "id": "c09-a5950b6f",
   "metadata": {},
   "source": [
    "from confluent_kafka import Consumer, TopicPartition\n",
    "from confluent_kafka.schema_registry import SchemaRegistryClient\n",
    "from confluent_kafka.schema_registry.avro import AvroDeserializer\n",
    "from confluent_kafka.serialization import SerializationContext, MessageField\n",
    "\n",
    "TOPIC = f\"{PREFIX}.public.tariffs\"\n",
    "registry = SchemaRegistryClient({\"url\": settings.SCHEMA_REGISTRY_URL})\n",
    "deserialize = AvroDeserializer(registry)\n",
    "\n",
    "def read(topic, start=0):\n",
    "    \"\"\"Every record of a 1-partition topic from `start`, as (offset, key, value) with Avro decoded.\"\"\"\n",
    "    consumer = Consumer({\"bootstrap.servers\": settings.KAFKA_BOOTSTRAP, \"group.id\": \"m06l01-reader\",\n",
    "                         \"enable.auto.commit\": False})\n",
    "    end = consumer.get_watermark_offsets(TopicPartition(topic, 0), timeout=10)[1]\n",
    "    consumer.assign([TopicPartition(topic, 0, start)])\n",
    "    records = []\n",
    "    while start + len(records) < end:\n",
    "        msg = consumer.poll(10)\n",
    "        if msg is None:\n",
    "            break\n",
    "        decode = lambda data, field: deserialize(data, SerializationContext(topic, field)) if data else None\n",
    "        records.append((msg.offset(), decode(msg.key(), MessageField.KEY), decode(msg.value(), MessageField.VALUE)))\n",
    "    consumer.close()\n",
    "    return records\n",
    "\n",
    "def show(event):\n",
    "    \"\"\"The parts of a change event worth looking at.\"\"\"\n",
    "    source = {k: event[\"source\"][k] for k in (\"snapshot\", \"table\", \"lsn\", \"txId\")}\n",
    "    return {\"op\": event[\"op\"], \"before\": event[\"before\"], \"after\": event[\"after\"], \"source\": source}\n",
    "\n",
    "lab.wait_until(lambda: len(read(TOPIC)) >= 36, timeout=60, what=\"the snapshot\")\n",
    "snapshot = read(TOPIC)\n",
    "print(len(snapshot), \"events; the first one:\")\n",
    "offset, key, value = snapshot[0]\n",
    "print(\"key:\", key)\n",
    "print(json.dumps(show(value), indent=1, default=str))"
   ],
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "id": "c10-32fe33f5",
   "metadata": {},
   "source": [
    "## Step 5: Change a price, delete a tariff\n",
    "Now the pricing team raises the price at site-N03 and closes site-V10's tariff."
   ]
  },
  {
   "cell_type": "code",
   "id": "c11-87bd618e",
   "metadata": {},
   "source": [
    "lab.pg(\"UPDATE tariffs SET price_per_kwh = 0.5900 WHERE tariff_id = 'tar-site-N03-1'\")\n",
    "lab.pg(\"DELETE FROM tariffs WHERE tariff_id = 'tar-site-V10-1'\")\n",
    "lab.wait_until(lambda: len(read(TOPIC)) >= 39, timeout=30, what=\"the change events\")\n",
    "for offset, key, value in read(TOPIC, start=36):\n",
    "    print(f\"offset {offset}, key {key}:\")\n",
    "    print(\"  \", json.dumps(show(value), default=str) if value else \"None  (a tombstone)\")"
   ],
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "id": "c12-93effa5d",
   "metadata": {},
   "source": [
    "## Change it: the latest tariff per id, in a compacted topic\n",
    "Billing (module 7) only wants each tariff's current row, not the change history. A second\n",
    "connector applies two **single message transforms**: `ExtractNewRecordState` keeps only the\n",
    "`after` row (and turns deletes into tombstones), and `RegexRouter` renames the topic."
   ]
  },
  {
   "cell_type": "code",
   "id": "c13-b504c5b1",
   "metadata": {},
   "source": [
    "from confluent_kafka.admin import AdminClient\n",
    "\n",
    "STATE_TOPIC = f\"{PREFIX}.tariffs\"\n",
    "admin = AdminClient({\"bootstrap.servers\": settings.KAFKA_BOOTSTRAP})\n",
    "lab.recreate_topic(admin, STATE_TOPIC, partitions=1, config={\"cleanup.policy\": \"compact\"})\n",
    "state = {**cdc,\n",
    "         \"topic.prefix\": \"m06l01_state\", \"slot.name\": \"m06l01_pricing_state\", \"publication.name\": \"m06l01_pricing_state\",\n",
    "         \"transforms\": \"unwrap,route\",\n",
    "         \"transforms.unwrap.type\": \"io.debezium.transforms.ExtractNewRecordState\",\n",
    "         \"transforms.route.type\": \"org.apache.kafka.connect.transforms.RegexRouter\",\n",
    "         \"transforms.route.regex\": r\"m06l01_state\\.public\\.(.*)\",\n",
    "         \"transforms.route.replacement\": f\"{PREFIX}.$1\"}\n",
    "lab.connect_api(\"PUT\", \"/connectors/m06l01-pricing-state/config\", state)\n",
    "lab.wait_connector(\"m06l01-pricing-state\")\n",
    "lab.wait_until(lambda: len(read(STATE_TOPIC)) >= 35, timeout=60, what=\"the state snapshot\")\n",
    "lab.pg(\"UPDATE tariffs SET price_per_kwh = 0.6100 WHERE tariff_id = 'tar-site-N03-1'\")\n",
    "lab.pg(\"DELETE FROM tariffs WHERE tariff_id = 'tar-site-V09-1'\")\n",
    "lab.wait_until(lambda: len(read(STATE_TOPIC)) >= 37, timeout=30, what=\"the flattened changes\")\n",
    "for offset, key, value in [read(STATE_TOPIC)[0]] + read(STATE_TOPIC, start=35):\n",
    "    print(offset, key, value)"
   ],
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "id": "c14-18ec7462",
   "metadata": {},
   "source": [
    "## Break it: a busy table nobody captures\n",
    "The pricing service also keeps an audit table. It's busy, and no connector captures it. Predict\n",
    "first: the connectors are running and healthy. How much log does PostgreSQL keep for them?\n",
    "\n",
    "A **replication slot** is PostgreSQL's bookmark for a connector: the database keeps every part of\n",
    "its write-ahead log (WAL) the slot's reader hasn't confirmed yet."
   ]
  },
  {
   "cell_type": "code",
   "id": "c15-d2afe91e",
   "metadata": {},
   "source": [
    "def slots():\n",
    "    return lab.pg(\"SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) \"\n",
    "                  \"FROM pg_replication_slots WHERE slot_name LIKE 'm06l01%' ORDER BY slot_name\")\n",
    "\n",
    "lab.pg(\"CREATE TABLE price_audit (id bigserial PRIMARY KEY, entry text)\")\n",
    "lab.pg(\"INSERT INTO price_audit (entry) SELECT repeat('price check ', 40) FROM generate_series(1, 100000)\")\n",
    "time.sleep(15)\n",
    "print(\"connectors:\", {n: lab.connect_api(\"GET\", f\"/connectors/{n}/status\", echo=False)[\"connector\"][\"state\"]\n",
    "                      for n in (\"m06l01-pricing-cdc\", \"m06l01-pricing-state\")})\n",
    "for slot in slots():\n",
    "    print(slot)"
   ],
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "id": "c16-fb0d96b9",
   "metadata": {},
   "source": [
    "Both slots hold on to the WAL the audit inserts wrote, although both connectors run. They only\n",
    "confirm a position when an event from a captured table reaches them, and none does. Debezium's\n",
    "fix is a **heartbeat**: every few seconds the connector writes to a small table that it does\n",
    "capture, and confirms the position that event carries. Add it to the first connector only:"
   ]
  },
  {
   "cell_type": "code",
   "id": "c17-4e890cbf",
   "metadata": {},
   "source": [
    "lab.pg(\"CREATE TABLE debezium_heartbeat (id integer PRIMARY KEY, ts timestamptz NOT NULL)\")\n",
    "cdc.update({\n",
    "    \"table.include.list\": \"public.tariffs,public.debezium_heartbeat\",\n",
    "    \"heartbeat.interval.ms\": \"5000\",\n",
    "    \"heartbeat.action.query\": \"INSERT INTO debezium_heartbeat VALUES (1, now()) \"\n",
    "                              \"ON CONFLICT (id) DO UPDATE SET ts = excluded.ts\"})\n",
    "lab.connect_api(\"PUT\", \"/connectors/m06l01-pricing-cdc/config\", cdc)\n",
    "lab.wait_connector(\"m06l01-pricing-cdc\")\n",
    "time.sleep(30)\n",
    "for slot in slots():\n",
    "    print(slot)"
   ],
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "id": "c18-a08399a4",
   "metadata": {},
   "source": [
    "## Clean up\n",
    "Delete the connectors, their slots and publications, the topics and the schemas, and reset the\n",
    "database. If anything gets stuck, `docker compose down -v` in the `code/` folder resets the lab."
   ]
  },
  {
   "cell_type": "code",
   "id": "c19-3406c085",
   "metadata": {},
   "source": [
    "lab.remove_connectors(\"m06l01-\")\n",
    "topics = [t for t in admin.list_topics(timeout=10).topics if t.startswith((\"m06l01\", \"__debezium-heartbeat.m06l01\"))]\n",
    "lab.delete_topics(admin, topics)\n",
    "for subject in [s for s in registry.get_subjects() if s.startswith(\"m06l01\")]:\n",
    "    registry.delete_subject(subject)\n",
    "    registry.delete_subject(subject, permanent=True)\n",
    "lab.pg(\"DROP TABLE IF EXISTS price_audit, debezium_heartbeat\")\n",
    "lab.reset_pricing_db()\n",
    "admin.delete_consumer_groups([\"m06l01-reader\"])\n",
    "print(\"cleaned up:\", topics)"
   ],
   "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
}
