Stream Oracle changes to ClickHouse in real time with Redpanda Connect

TL;DR

• Redpanda Connect now has a native oracledb_cdc input that captures inserts, updates, and deletes from Oracle using LogMiner. No Debezium, no Kafka Connect runtime, no JVM.
• We will build a two-stage pipeline: Oracle to a durable Redpanda topic, then from that topic to a ClickHouse table, both as single-binary YAML configs.
• oracledb_cdc is an enterprise connector. You can unlock it in two minutes with a free 30-day trial license.
• Everything here runs locally with Docker and a couple of rpk connect run commands.

Why Oracle to ClickHouse, and why now

Most teams I talk to have an Oracle database sitting at the center of an operational system that nobody wants to touch, and a separate analytics stack where they actually want to ask questions. ClickHouse is a fantastic destination for that second job: it is fast, columnar, and built for the kind of aggregations that make Oracle sweat. The hard part has always been getting data from one to the other without hammering the source database with batch exports or standing up a fleet of Kafka Connect workers and Debezium plugins.

That is the gap the new oracledb_cdc input closes. It reads Oracle's redo logs through LogMiner, turns every row-level change into a message, and hands it to the rest of your Redpanda Connect pipeline. Because it is just another input in the same single binary you already use for the 300+ other connectors, there is no separate Connect cluster to babysit and no plugin JARs to manage. You write some YAML, you run it, and changes start flowing.

In this post, I will wire Oracle straight through Redpanda into ClickHouse so you can follow along and have a working pipeline by the end. If you'd rather see it all in action, you can watch our Tech Talk on demand below.

{{featured-event}}

Stream changes from Oracle to ClickHouse with Redpanda Connect

We will run two short pipelines:

1. Oracle to Redpanda. The oracledb_cdc input captures changes and an optional initial snapshot, and the redpanda output writes each change to a topic. Putting a topic in the middle gives us durability, replay, and the freedom to add more consumers later without touching Oracle again.
2. Redpanda to ClickHouse. A second pipeline reads that topic and uses the sql_insert output with the ClickHouse driver to land every change event as a row.

Splitting it this way is deliberate. CDC is precious data, and a Redpanda topic is the right place to keep it safe before fanning out to ClickHouse, a data lake, or whatever comes next.

Prerequisites

You will need:

• Docker, for the Oracle, Redpanda, and ClickHouse containers.
• rpk with Redpanda Connect installed. See the Redpanda Connect quickstart if you do not have it yet.
• An Oracle database you can enable ARCHIVELOG mode on (any recent XE/EE image works).
• A running ClickHouse instance.

export REDPANDA_LICENSE="<your-license-string>"

If you would rather not use an environment variable, you can save the key to /etc/redpanda/redpanda.license, point REDPANDA_LICENSE_FILEPATH at a file, or pass it inline with rpk connect run --redpanda-license <license-string> ./pipeline.yaml. When the pipeline starts, Connect logs a line confirming the license loaded and when it expires:

INFO Successfully loaded Redpanda license  expires_at="..." license_type="enterprise"

Step 2: Prepare Oracle for CDC

LogMiner requires the database to be in ARCHIVELOG mode with supplemental logging enabled, and a user with the appropriate grants. Connect as a privileged user and run:

-- Put the database in ARCHIVELOG mode (skip if already enabled)
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

-- Enable supplemental logging at the database and table level
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER TABLE sales.orders ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

-- Create the CDC user and grant LogMiner access
CREATE USER cdc_user IDENTIFIED BY cdc_password;
GRANT CREATE SESSION TO cdc_user;
GRANT SELECT ANY TABLE TO cdc_user;
GRANT SELECT_CATALOG_ROLE TO cdc_user;
GRANT EXECUTE_CATALOG_ROLE TO cdc_user;
GRANT SELECT ANY TRANSACTION TO cdc_user;
GRANT LOGMINING TO cdc_user;
GRANT SELECT ON sales.orders TO cdc_user;

-- Create the rpcn schema used for the checkpoint cache
CREATE USER rpcn IDENTIFIED BY rpcn_password;
GRANT CREATE TABLE TO rpcn;
GRANT CREATE PROCEDURE TO rpcn;
GRANT UNLIMITED TABLESPACE TO rpcn;

The rpcn schema matters: by default the connector creates a small checkpoint table there to track the last System Change Number (SCN) it delivered, so it can resume after a restart instead of replaying the entire redo log.

For this walkthrough, assume a simple sales.orders table:

CREATE TABLE sales.orders (
  id          NUMBER PRIMARY KEY,
  customer_id NUMBER,
  amount      NUMBER(10,2),
  status      VARCHAR2(20)
);

Step 3: Stream Oracle changes into Redpanda

Here is the first pipeline, oracle-to-redpanda.yaml:

input:
  oracledb_cdc:
    connection_string: ${ORACLE_DSN}
    stream_snapshot: true          # also load existing rows on first run
    include:
      - "SALES\\.ORDERS"

pipeline:
  processors:
    # Fold the CDC metadata into the message body so it survives the topic
    - mapping: |
        root = this
        root._op = metadata("operation")
        root._scn = metadata("scn")
        meta kafka_key = this.ID.string()

output:
  redpanda:
    seed_brokers: [ "${REDPANDA_BROKERS}" ]
    topic: oracle.sales.orders
    key: ${! meta("kafka_key") }

A few things worth calling out about this pipeline:

• stream_snapshot: true emits the current contents of the table as read events before tailing the redo log, so ClickHouse ends up with full state, not just changes from this moment forward.
• include takes regular expressions in SCHEMA\.TABLE form.
• operation metadata tells you whether each event was a read, insert, update, or delete.
• scn is Oracle's monotonic change number, which we lean on in ClickHouse to keep only the latest version of each row.

I make a copy of operation and scn into the message body so they travel through the topic, and I set the Kafka key to the primary key so all changes for a given order land on the same partition, in order.

Set your connection details and run it:

export ORACLE_DSN="oracle://cdc_user:cdc_password@localhost:1521/XEPDB1"
export REDPANDA_BROKERS="localhost:9092"

rpk connect run ./oracle-to-redpanda.yaml

You should see the snapshot rows flow through, and the pipeline then sits and waits for new changes. Confirm the topic is filling up:

rpk topic consume oracle.sales.orders --num 5

Step 4: Land the stream in ClickHouse

Now the second pipeline, redpanda-to-clickhouse.yaml:

input:
  redpanda:
    seed_brokers: [ "${REDPANDA_BROKERS}" ]
    topics: [ "oracle.sales.orders" ]
    consumer_group: clickhouse-sink

output:
  sql_insert:
    driver: clickhouse
    dsn: ${CLICKHOUSE_DSN}
    table: orders
    columns: [ id, customer_id, amount, status, _op, _scn ]
    args_mapping: |
      root = [
        this.ID,
        this.CUSTOMER_ID,
        this.AMOUNT,
        this.STATUS,
        this._op,
        this._scn.number(),
      ]
    init_statement: |
      CREATE TABLE IF NOT EXISTS orders (
        id          UInt64,
        customer_id UInt64,
        amount      Decimal(10,2),
        status      String,
        _op         String,
        _scn        UInt64
      ) ENGINE = ReplacingMergeTree(_scn)
      ORDER BY id;
    batching:
      count: 1000
      period: 1s

The init_statement runs once on the first connection and creates the target table if it does not exist, so there is no separate DDL step to remember. I am using a ReplacingMergeTree(_scn) engine ordered by id: ClickHouse keeps the row with the highest SCN per primary key during background merges, which means every update collapses to the latest version automatically. To read the deduplicated state at query time, add the FINAL modifier:

SELECT id, customer_id, amount, status FROM orders FINAL ORDER BY id;

The args_mapping leverages the power of Bloblang: it maps each field from the message into the column list, in order. The batching block groups inserts so ClickHouse gets healthy batch sizes instead of one round trip per row, which it strongly prefers.

Run it in a second terminal:

export CLICKHOUSE_DSN="clickhouse://default:@localhost:9000/default"
export REDPANDA_BROKERS="localhost:9092"

rpk connect run ./redpanda-to-clickhouse.yaml

Step 5: Watch it work end to end

With both pipelines running, make a change in Oracle:

INSERT INTO sales.orders VALUES (1001, 42, 199.99, 'PENDING');
UPDATE sales.orders SET status = 'SHIPPED' WHERE id = 1001;

Then query ClickHouse:

SELECT * FROM orders FINAL WHERE id = 1001;

You will see order 1001 with status SHIPPED, the higher SCN from the update having won. The insert and the update both arrived as separate change events, flowed through the Redpanda topic, and ReplacingMergeTree kept the newest one. That round trip, from an Oracle UPDATE to a queryable row in ClickHouse, is happening in real time with two YAML files.

What next?

This is a small demo example, but the same pattern scales in a few directions. You an add more tables to the include list and route them to per-table topics with a dynamic topic: oracle.${! meta("table_name").lowercase() }. Tune the logminer and checkpoint_limit settings as throughput grows. And, because the change stream lives in a Redpanda topic, you can attach a second sink, say an Iceberg table or a search index, without going anywhere near Oracle again.

If you want to dig into every field, the oracledb_cdc reference and the Oracle CDC cookbook are the places to go. And if you hit something rough or have a pattern you want supported, find me in the Redpanda Community Slack.

Happy streaming!