Skip to main content

Celonis Product Documentation

Real Time Extractions through Replication Cockpit
Setup and Configuration

The following real time extractions in JDBC are supported:

  • Azure SQL

  • Azure Synapse

  • Microsoft SQL Server

  • Oracle

  • Oracle 11g

  • SAP HANA

  • SAP HANA (Encrypted)

Overview

In general, the trigger-based real-time extractor setup includes the following actions:

  1. Activating the live extraction mode in the Celonis Platform

  2. Create the Changelog tables to store the changes in the source system

  3. Install triggers to monitor the changes in the source system

Note

Actions 2 & 3 are performed on the database and require involvement from the DB Admin.

Activate real-time extraction mode in Celonis Data Integration

The real-time extraction mode needs to be enabled in the Celonis Platform, at the Data Connection level.

To activate real-time extraction mode:

  1. Go to Data Integration => {Data Pool} => Data Connections.

  2. Open the required Database connection.

  3. Slide the Live data connection bar to the right.

The live connection allows you to use the new Replication Cockpit for more performant database extractions.

Adding Tables

The tables which should be extracted need to be added to the Replication Cockpit.

Database tables in the Replication cockpit have four options in the context menu:

  • Start: Starts the real-time extraction for that table

  • Stop: Stops the real-time extraction for that table

  • Delete: Deletes the table from the real-time cockpit

  • Trigger Code: This option will display the “Create Table” statement for a changelog table and a “Create Trigger” statement. The statement ist dynamically generated according to the table and database type and copy&paste of the statement should work without any modification.

Creating Log Tables and Triggers - Change Detection on Database

Initially, in order to start a real-time extraction for the table, users need to execute the trigger code that is supplied with the table in the Replication Cockpit.

Trigger code will have two separate statements:

  1. “Create Table” statement for a changelog table:

    • This table should not exist before there “create if not exists” should not be used.

    • This table will contain the changes for that specific table

    • This table will be named as CEL_CL_{TARGET_TABLE_NAME}

    • This table will have the following structure;

      • CEL_ID (varchar 40): Random UUID serves as the primary key for the table

      • ROW_ID (varchar 255): Unique identifier for the record being changed

      • CEL_CHANGE_TYPE (type enum(‘I’, ‘U’,’D’)): Defined type of the change either Insert, Update or Delete

      • CEL_CHANGE_DATE (type datetime): Timestamp of the change

      • CEL_EXTRACTED (type boolean): if it is already extracted or not

  2. “Trigger Table” statement to create the trigger:

    • This statement can be ignored if a trigger already exists with the same name

    • This trigger will insert the changes to the change log table for the corresponding table.

    • This trigger will be named as CEL_TR_{TARGET_TABLE_NAME}

Real Time Extraction

During the startup of the database extractor server, a request to integration will be made to get a list of real-time extractions that need to be executed.

  • For uplinked database servers: Only the real-time tables where the data sources uses that uplink, will be retrieved

  • For the cloud database servers: All the real-time tables where the data source connection type is direct

For the retrieved real-time tables, a continuous extraction will be created.

  • This extraction will query the corresponding change log table where CEL_EXTRACTED=false

  • Extracted data will be inserted into replicated tables using the real-time service

  • The inserted records are then streamed to the Transformation Service, where the records are processed according to the transformation logic of each table, as defined in EC.

  • The transformed records are inserted/merged into the transformed tables