CDC data sources (Debezium)

This article provides an introduction to how Upsolver works with CDC (Change Data Capture) data sources.
What is Debezium?
Debezium is an open source distributed platform for change data capture. To use it, start it up, point it at your databases, and your apps can start responding to all of the inserts, updates, and deletes that other apps commit to your databases. Upsolver currently uses Debezium v1.4.
CDC Data Sources
Upsolver supports ingesting CDC data from relational databases such as MySQL, MariaDB and PostgreSql. Upsolver provides CDC capabilities by running a Debezium Engine under the hood to connect to database journals. The connectors automatically detect and ingest any change.
Event Format
Upsolver will read Debezium Change events with the following fields:
before -The state of the row before the change that was applied in the current event. This can be null if this row is new.
after - The state of the row after the change that was applied in the current event.
source - Information about the change event. Including things such as what binlog file it came from, and it's sequence number or position within the file. The Source Table and Database will also be here.
op - The change type. The options are:
r - Read events (when loading the initial data)
c - Create
u - Update
d - Delete
Example 1:
{
    "ts_ms": 1617525879250,
    "op": "c",
    "after": {
        "id": "188283-21202",
        "cost": 4.2,
        "item_id": 10
    },
    "source": {
        "name": "debezium",
        "db": "prod",
        "row": 0,
        "server_id": 0,
        "snapshot": "true",
        "table": "sales",
        "version": "1.4.2.Final",
        "ts_ms": 0,
        "file": "mysql-bin-changelog.008019",
        "pos": 156,
        "connector": "mysql"
    }
}
This example event represents a new row being added to the table. You can tell this by the op type being c. In this case we added a new sale to the sales table in the prod database (this can be seen in the source information).
Example 2:
{
    "ts_ms": 1617525879252,
    "op": "u",
    "before": {
        "id": "188283-21202",
        "cost": 4.2,
        "item_id": 10
    },
    "after": {
        "id": "188283-21202",
        "cost": 5,
        "item_id": 10
    },
    "source": {
        "name": "debezium",
        "db": "prod",
        "row": 0,
        "server_id": 0,
        "snapshot": "true",
        "table": "sales",
        "version": "1.4.2.Final",
        "ts_ms": 0,
        "file": "mysql-bin-changelog.008019",
        "pos": 157,
        "connector": "mysql"
    }
}
In this case we received an updated event for the event in Example 1. We can see the old values in before and the new updated values in after.
Supported Databases
Currently the following databases and versions are supported:
Database
Version
AWS RDS Supported?
MySQL
5.6+
Yes
PostgreSQL
10, 11, and 12
Yes
Some databases may require specific journal configurations to be used. See the documentation page for creating a CDC data source for your database for info.
MySQL insert example
/{
  "time": "2021-11-18 10:33:45",
  "data": {
    "operation": "insert",
    "database_name": "prod",
    "table_name": "sales",
    "full_table_name": "prod.sales",
    "primary_key": "188283-21202",
    "row": {
      "id": "188283-21202",
      "cost": 5,
      "item_id": 10
    },
    "metadata": {
      "binlog_file_name": "mysql-bin-changelog.030565",
      "binlog_file_position": 749,
      "binlog_row": 0,
      "from_snapshot": false,
      "binlog_timestamp": 1637224425000,
      "is_delete": false
    }
  }
}
MySQL update example
{
  "time": "2021-11-18 10:33:45",
  "data": {
    "operation": "update",
    "database_name": "prod",
    "table_name": "sales",
    "full_table_name": "prod.sales",
    "primary_key": "188283-21202",
    "row": {
      "id": "188283-21202",
      "cost": 5,
      "item_id": 10
    },
    "old_row": {
      "id": "188283-21202",
      "cost": 3,
      "item_id": 10
    },
    "metadata": {
      "binlog_file_name": "mysql-bin-changelog.030565",
      "binlog_file_position": 749,
      "binlog_row": 0,
      "from_snapshot": false,
      "binlog_timestamp": 1637224425000,
      "is_delete": false
    }
  }
}
Postgres insert example
{
  "time": "2021-11-18 09:38:00",
  "data": {
    "operation": "insert",
    "database_name": "postgres",
    "schema_name": "prod",
    "table_name": "sales",
    "full_table_name": "postgres.prod.sales",
    "primary_key": "188283-21202",
    "row": {
      "id": "188283-21202",
      "cost": 5,
      "item_id": 10
    },
    "metadata": {
      "lsn": 2032660385960,
      "from_snapshot": false,
      "binlog_timestamp": 1637221080616,
      "is_delete": false,
      "is_heartbeat": false
    }
  }
}
Postgres update example
{
  "time": "2021-11-18 09:38:00",
  "data": {
    "operation": "update",
    "database_name": "postgres",
    "schema_name": "prod",
    "table_name": "sales",
    "full_table_name": "postgres.prod.sales",
    "primary_key": "188283-21202",
    "row": {
      "id": "188283-21202",
      "cost": 5,
      "item_id": 10
    },
    "old_row": {
      "id": "188283-21202",
      "cost": 3,
      "item_id": 10
    },
    "metadata": {
      "lsn": 2032660385960,
      "from_snapshot": false,
      "binlog_timestamp": 1637221080616,
      "is_delete": false,
      "is_heartbeat": false
    }
  }
}
Supported Data Outputs
Upsolver currently supports the following data outputs (for database replications).
Ahana 
Amazon Athena 
Dremio 
Hive Metastore 
PrestoDB 
Qubole 
Redshift Spectrum 
Starburst 
Upsolver Query
Connecting data sources - Previous
File upload data source
MySQL CDC data source
Last modified 1yr ago
Database	Version	AWS RDS Supported?
MySQL	5.6+	Yes
PostgreSQL	10, 11, and 12	Yes