Articles in this section

See more

Connectors for Change Data Capture (CDC)

  • Updated
Follow
  • Startup
  • Business
  • Enterprise
  • On-Premise
  • Add-on

When to use change data capture connectors

Use the change data capture connector to configure the source in CDC flows.

Change data capture (CDC) is an approach to data integration that is based on the identification, capture, and delivery of the changes made to the source database and stored in the database redo log (also called transaction log). 

Etlworks supports native log-based change data capture for PostgreSQL, SQL Server, MySQL, Oracle, DB2, MongoDB and AS400.

We use heavily modified embedded Debezium engine for CDC.

Read about configuring the CDC pipelines in Etlworks.

What you need to know

Enable CDC for the source database

Learn about enabling CDC for Microsoft SQL Server, MySQL, Oracle, PostgreSQL, DB2,  MongoDB, AS400.

Read more

Create a Connection

Create a Connection in four easy steps.

Read more

Connection parameters

Learn about the different parameters for all databases, authentication, serialization, and others.

Read more

Database specific parameters

Learn about the different parameters for SQL, MySQL, Oracle, Postgres, DB2, MongoDB, AS400.

Read more

 

Enable CDC for the source database

Here are some helpful articles about enabling CDC for Microsoft SQL Server, MySQL, Oracle, PostgreSQL, DB2, MongoDB and AS400 (IBMI).

Microsoft SQL Server
MySQL
Oracle
PostgreSQL
DB2
MongoDB
AS400

 

Create a Connection

Here's how you can create a Connection in four easy steps:

Step 1. In the Connections window, click +, and type in cdc.

Step 2. Select the source database.

Step 3. Enter Connection parameters.

Step 4. Test the Connection.

Connection parameters

Below are the different parameters for all databases, authentication, serialization, and others.

All databases 

  • Logical Connector Name: the required logical connector name. The connector uses the logical name as a unique identifier when recording a position in the log and storing the metadata. You should give each connection a unique logical name that meaningfully describes the source system. If you change the value of this property, after a restart, the connector will be unable to recover its database schema history as it is stored under diffrent name. It will automatically trigger the snapshot instead of continuing to emit change events.  
  • Database server logical name: an optional server logical name. Database server logical name provides a namespace for the particular SQL Server database server in which connector is capturing changes. If not provided the Database server logical name is set to the URL of the connection (host:port). If provided this parameter should be unique across all other connectors. Only alphanumeric characters, hyphens, dots and underscores must be used in the database server logical name. If you change the value of this property, after a restart, the connector will be unable to recover its database schema history as it is stored under dirrent name. It will automatically trigger the snapshot instead of continuing to emit change events
  • Include Table(s): a comma-separated list of regular expressions that match fully-qualified table identifiers for tables to be monitored; any table not included in the whitelist will be excluded from monitoring. Each identifier is of the form databaseName.tableName. By default, the connector will monitor every non-system table in each monitored database. This parameter will be overridden in the TO field of the CDC Flow.
  • Excluded Columns: an optional comma-separated list of regular expressions that match the fully-qualified names of columns that should be excluded from change event message values. Fully qualified names for columns are of the form databaseName.tableName.columnName or databaseName.schemaName.tableName.columnName.

CDC Key

Authentication

  • User: the user name.
  • Password: the password.

Metadata (used for retrieving tables and views in the UI)

The parameters in this group are used exclusively when populating tables that can be selected in UI to configure the list of monitored objects.  Setting the schema and enabling the filter can significantly improve the speed at which UI renders the list of tables. Changing any of these parameters has no effect on CDC.

Schema: database schema.

Current Database and Schema only: if this parameter is enabled, the connector will populate tables for the current database and schema only.

Serialization

The parameters in this group are used to configure the Format and the fields of the CDC stream. The parameters in this group are ignored when the connector streams events into a message queue.

  • Add Event Type and Unique Sequence: if this option is enabled the connector adds two extra columns to the CDC stream:
    • debezium_cdc_op: event type VARCHAR(1), c for create, u for update, and d for delete.
    • debezium_cdc_timestamp: the unique sequential number of the CDC event LONG. 
  • This parameter must be enabled when loading data into an online data warehouse, such as Amazon Redshift or Snowflake.
  • Extra columns: the columns specified in this field will be added to the end of the stream. Read more about extra columns. Extra columns can be also used to implement the soft delete.
  • Preprocessor: the optional JavaScript that can be used to change the CDC event before saving it to the CSV file. The following variables are available in the preprocessor code:
  • Flatten CDC events: if this option is enabled the CDC connector will flatten CDC events, encoded using Extended JSON Format.  Only the following data types are decoded: $oid,$symbol,$numberInt,$numberLong,$numberDouble,$numberDecimal,$code,$date,$minKey,$maxKey,$undefined). It will also transform [arrays] to strings.
  • Convert JSON array to comma-separated list: if this option is enabled the CDC connector  converts JSON arrays to the comma-separated list of strings: [1,2,3]->1,2,3.
  • : if this option is enabled and Convert JSON array to comma-separated list is also enabled the CDC connector will preserve closing and opening brackets ([]) in the JSON array which stores objects:  [{"first": "joe"}, {"first": "jane"}]->[{"first": "joe"}, {"first": "jane"}] .

Load CDC events into queue

The parameters in this group are used to configure the CDC connector to stream CDC events to a message queue, such as Kafka, Azure Events Hubs, etc. The parameters in this group are ignored when the connector creates files.

  • Structure of the CDC Event: a typical CDC event emitted by Debezium contains the following elements:
      • before: an optional field that specifies the state of the row before the event occurred.
      • after: an optional field that specifies the state of the row after the event occurred.
      • source: a mandatory field that describes the source metadata for the event.
      • op: a mandatory string that describes the type of operation that caused the connector to generate the event.
      • ts_ms: optional field that displays the time at which the connector processed the event,
      • transaction: transaction metadata,
      • schema: the schema, which describes the structure of the payload.
      • fields: the JDBC schema.
  • By default, the connector only retains the state of the event after the update and converts it to the flat JSON. The flat JSON is then added to the message queue. You can change the structure of the event added to the queue by enabling parts of the original event emitted by Debezium. This option is ignored if the connector creates files.
  • Update fields for each event: if this option is enabled the connector will update the list of fields for each CDC event, even when the CDC Key hasn't changed. Enable this option if you are planning to stream events from all tables into a single topic in a message queue.  

  • The Partition Key is used to send records to the specific Kafka or Azure Event Hub partition.

    If the Partition Key is not set the record will be sent to the random partition so we strongly recommend configuring it. 

    Example of the partition key: [table].

    mceclip0.png

    The same connector-specific [tokens] that can be used as a part of the CDC Key are available for the Partition Key.

Create files

The parameters in this group are used to configure the CDC connector to extract CDC events and create files.  

  • Serialize CDC events as CSV files: if this option is enabled, the CDC connector saves the CDC events as CSV files into the local (server) or cloud storage. The filename for each generated file is going to be [cdc_key]_uuid.csv, where uuid is an automatically generated globally unique identifier.  
  • : if this option is enabled, the connector creates an XML file with the same name as the CSV file. The XML file contains information about the actual data types of the columns and which columns belong to the primary key. If present this information will be used during the load to set the data types of the columns in the destination. Read more about preserving the exact data types of the columns in the source table when creating a destination table.
  • : By default, all columns are captured as nullable. If this option is enabled (it is disabled by default) the connector will capture NOT NULL constraints. Note that this option works only when is enabled. Read more.
  • Columns in CSV File: the columns specified in this field will be used to create the CSV file. Other columns, except extra columns and debezium_cdc_timestamp/ debezium_cdc_op will be ignored. This field is case insensitive. You can enter columns in any CaSe and the system will match them regardless. 
  • Serialize CDC events as JSON files: if this option is enabled, the CDC connector saves the CDC events as JSON files into the local (server) or cloud storage. The filename for each generated file is going to be [cdc_key]_uuid.json, where uuid is an automatically generated globally unique identifier.  This option can be enabled together with Serialize CDC events as CSV files .
  • Use Unique Sequence as a suffix: if this option is enabled, the connector will create CSV and JSON files with the Unique Sequence in a filename: test_dbo_account_1234.csv. If this option is disabled (default) the connector will use UUID as a suffix: test_dbo_account_123e4567-e89b-12d3-a456-426614174000.csv

  • Convert Geometry WKB to JSON: if this option is enabled, the connector will convert WKB to human-readable JSON. This is specifically useful when streaming data from Postgres tables with geometry columns. Other databases support geometry data types as well so it is not limited to Postgres only. 

  • Max Rows in CSV file: the maximum number of rows in CSV file. When the file reaches the max number of rows the connector closes the file.
  • Max File Size in Bytes: if this parameter is set the connector will close the file when the size of the file reaches the threshold.
  • Keep file open (ms): maximum wait time in milliseconds before closing the CSV file if not updated. The connector waits for a configured number of milliseconds and if there are no new updates for the specific database.table closes the file.  
  • Close CSV file if header is different: if this option is enabled, the CDC connector will close the CSV file and will create a new one if the new header is different from the current header for the file with the same CDC key. Enable it if you expect that the source table structure can be changed or when it is possible to have a different number of columns in the CDC stream for the specific object (typically MongoDB collection).
  • Close File After (ms): the wait time in milliseconds before closing the CSV file even if it is still getting updated or has not reached the maximum number of rows.
  • Escape double-quotes: is this option is enabled, a double-quote appearing inside a field will be escaped by preceding it with another double quote
  • Convert null to empty: if this option is enabled, the connector will convert SQL NULL values to an empty string when creating the CSV file. Read more about handling SQL NULLs and empty strings.
  • Do not enclose null in double quotes: if this option is enabled the connector will not enclose null values in double quotes ("") when creating CSV files. Note that by default this option is disabled, so all values, even nulls, are enclosed in "".
  • Convert boolean true/false to numeric 1/0: if this option is enabled the connector will convert values in columns with BOOLEAN or BIT datatypes from true/false to 1/0. 
  • Remove EOL characters: if this option is enabled, the system will remove end-of-line (EOL) characters from the field's values when creating CSV files.
  • Encoding: the character encoding of the generated CSV files. 

Storage

The parameters in this group are used to configure where to store the generated CSV and JSON files with CDC events.

It is possible (also recommended) to override the parameters in this group when creating a CDC flow.

  • Storage type: CDC events serialized as files can be stored in the attached  File system (default) or in any of the supported cloud storage systems:
    • S3 SDK (S3 is deprecated)
    • Azure Storage SDK (Azure Storage is deprecated), 
    • Google Storage
  • Archive files: if the particular archive Format is selected, the generated files will be archived using the selected Format before copying to cloud storage. This parameter is ignored if the File system is selected as a storage type.
  • Location of the CDC events: the location of the CDC events serialized as files. If this field is empty the system will use the default location, which is {app.data}/debezium_data/events for File system storage type. When configuring the location you can use tokens, for example {app.data} as a part of the field's value. You can also use any part of the CDC key, specifically [db]/[schema]/[table]. NOTE: This field is required when cloud storage is selected. Possible values are:
    • S3: bucket/[optional folders].
    • Azure Storage: container/[optional folders].
    • Google Storage: bucket/[optional folders].
  • AWS Region: the AWS cloud region. This parameter is required if Storage type is set to S3 SDK.
  • Azure Authentication Type: the authentication type for the Azure storage account. The possible values are Access Key and SAS token.
  • Access Key, IAM Role, or Account Name: the key (username) for the cloud storage account:
    • Amazon S3: Access Key ID or IAM Role.
    • Azure Storage: Storage account.
    • Google Storage: Access Key.
  • Secret Key or SAS Token: the secret (password for the cloud storage account:
    • Amazon S3: Secret Access Key or empty if IAM Role authentication is used.
    • Azure Storage: Access Key.
    • Google Storage: Secret.

Offset parameters

Active CDC Connection records the latest offset in the transaction log to the file {app.data}/debezium_data/offeset_file_name.dat. You can override the default file name which is calculated using the unique name of the Connection.

  • Offset Commit Interval (ms): interval at which connector will be committing offsets. The default is 1 minute.
  • Offset Flush Timeout (ms): the maximum number of milliseconds to wait for records to flush and partition offset data to be committed to offset storage before canceling the process and restoring the offset data to be committed in a future attempt. The default is 5 seconds.
  • Offset File Name: the offset file name. If this field is empty the offset file name will be calculated automatically using the unique name of the Connection. The offset file is located in {app.data}/debezium_data.
  • DDL History File Name: the DDL history file name. If this field is empty the DDL file name will be calculated automatically using the unique name of the Connection. The DDL history file is located in {app.data}/debezium_data.

CDC Stream

  • Maximum Queue Size: a positive integer value that specifies the maximum size of the blocking queue into which change events read from the database log are placed before they are written to the stream. This queue can provide backpressure to the transactional log reader when, for example, writes to the stream are slower. Events that appear in the queue are not included in the offsets periodically recorded by this connector. Defaults to 8192, and should always be larger than the maximum batch size.
  • Maximum Batch Size: a positive integer value that specifies the maximum size of each batch of events that should be processed during each iteration of this connector. Defaults to 2048.
  • Poll Interval in Milliseconds: a positive integer value that specifies the number of milliseconds the connector should wait during each iteration for new change events to appear. Defaults to 500 milliseconds. 

Snapshot

  • Snapshot Mode: specifies the criteria for running a snapshot upon startup of the connector. It is different for each database (see below).
  • Snapshot Max Threads: specifies the number of threads that the connector uses when performing an initial and ah-hoc snapshots. To enable parallel initial and ah-hoc snapshots, set the property to a value greater than 1. In a parallel snapshot, the connector processes multiple tables concurrently
  • Automatically trigger ad-hoc snapshot for new tables: if this option is enabled, the initial snapshot has been successfully completed, and the Snapshot Mode is set to ad-hoc initial, ad-hoc or ad-hoc schema onlythe connector will automatically trigger the ad-hoc snapshot for the tables added since the last run. 
  • Signal Data Collection: the name of the signaldata collection which can be a database table name or a file name. Read more.
  • Sleep (milliseconds) between attempting to read signals:  the sleep time in milliseconds between attempts to read the signal. The default is 10000milliseconds or 10 seconds. 
  • Snapshot Locking Mode: the optional locking mode for the specific database when performing the snapshot. It is different for each database (see below).

How to stop CDC Stream

The Flow will stop if any of the above reaches the configured threshold. 

  • Number of retries before giving up: the number of retries before giving up if poll returns no records. The default is 100.
  • Retry N minutes before giving up: the number of minutes to retry before giving up if the poll returns no records.
  • Always stop after N minutes: the number of minutes to run the connector before stopping. The connector will stop even if there are new records in the queue. Note that the connector will NOT stop during the snapshot

If none of the parameters are set the CDC stream will run forever until manually stopped.

To stop CDC Flow, manually click Stop/ Cancel .

mceclip1.png

Transactions

This group of parameters enables transaction markers.

  • Provide Transaction Metadata parameter: when available and enabled the connector will automatically create the transaction maker for each transaction.

Other parameters

  • Other Parameters: other parameters as key=value pairs. The parameters are specific for the different connectors.
  • Connection Timeout in Milliseconds: a positive integer value that specifies the maximum time in milliseconds this connector should wait after trying to connect to the database server before timing out. Defaults to 30 seconds.

How to handle errors

Auto-retries

  • Maximum number of retries on connection errors: the maximum number of retries on connection errors before failing (-1 = no limit, 0 = disabled, > 0 = num of retries).

When there are multiple connectors configured to run on the same database (for example, each connector could be extracting data from a different group of tables) it is possible that the error in one connector (for example, when the connector losing the connectivity to the source database) could cause cascading errors in other running connectors. These types of errors could cause a temporary spike in CPU/RAM utilization when multiple connectors are trying to recover from the error at the same time. In this case, it makes sense to shut down all the connectors running on the same database and let the Flows restart automatically.   

  • Errors to trigger stop for all CDC Flows: a semicolon-separated list of errors which will trigger stop for all CDC connectors running for the database with the same host:port. The list can contain the entire error or the part of the error. Example: Failed to deserialize data; EOFException in order to trigger the stop the exception stack trace must contain Failed to deserialize data and EOFException. 

Formats and Templates

  • Template: use the template to populate columns for mapping without actually connecting to the source database. Example: {\"first\":\"string\",\"last\":\"string\"}
  • Date and Time Format: Format for the fields with a TIMESTAMP type. If not entered the TIMESTAMP fields will be serialized as Linux epoch time (in milliseconds).
  • Date Format: Format for the fields with a DATE type. If not entered the DATE fields will be serialized as Linux epoch time (in milliseconds).
  • Time Format: Format for the fields with TIME type. If not entered the TIME fields will be serialized as Linux epoch time (in milliseconds).
  • Timezone: the timezone used when converting date/time fields into Strings. If the value of this parameter is empty, the timezone of the instance running Etlworks is used. In most cases, it is UTC.

Connect to a database over SSH tunnel

If you need to access a database that can only be accessed via an SSH tunnel, you need to specify additional information in the Connect over SSH tunnel section of the database Connection screen.

  • SSH Host: the name or IP address for the host accepting SSH Connections.
  • SSH Port: the port accepting SSH Connections. The default value is 22.
  • SSH User : the user name.
  • SSH Password: the optional password.
  • Private Key File: the private key file in the pem or ppk Format used for SSH authentication. Click Upload SSH key to manage SSH keys using GUI. You can also upload the private key file manually and use the token {app.data} as a part of the filename, for example, {app.data}/keys/secret.pem. This parameter is optional.

ssh_key.png

Database specific parameters

Here's a list of SQL, MySQL, Oracle, Postgres, BD2, and MongoDB paramaters.

SQL Server parameters

SQL Server CDC connector parameters

  • Host: the host or IP address of the source database.
  • Port: the port the source database is listening to.
  • Database: the name of the database to connect on startup.
  • Include Databases(s): an optional, comma-separated list of database names or regular expressions that match the names of the databases for which to capture changes. The connector does not capture changes in any database whose name is not in. If this parameter is empty the connector will use the value of the parameter Database. Setting the value of this property to more than one database will make it impossible to add tables to snapshot on the fly (ad-hoc snapshots).
  • Include Table(s): a comma-separated list of regular expressions that match fully-qualified table identifiers for tables to be monitored; any table not included in the whitelist will be excluded from monitoring. Each identifier is of the form databaseName.tableName. By default, the connector will monitor every non-system table in each monitored database. This parameter will be overridden in the TO field of the CDC Flow.
  • Excluded Columns(s): an optional comma-separated list of regular expressions that match the fully-qualified names of columns that should be excluded from change event message values. Fully-qualified names for columns are of the form databaseName.tableName.columnName, or databaseName.schemaName.tableName.columnName.
  • CDC Key: a template for CDC key. The CDC key is used as a file name when streaming CDC events and creating files and as a topic name when streaming CDC events to a message queue such as Kafka. Example of the CDC key: testDB_dbo_customers_cdc_stream. Available tokens:
    • [db]: database name.
    • [schema]: schema name.
    • [table]: table name.
    • [op]: the CDC operation: c (create), d (delete), u (update).
    • [timestamp]: the timestamp of the event in ms. 
    • [version]: the connector version.
    • [connector]: the connector class name.
    • [name]: the connector name.
    • [ts_ms]: the timestamp of the event in ms.
    • [snapshot]: true for a snapshot.
    • [change_lsn]: change lns.
    • [commit_lsn]: commit lns.
    • [event_serial_no]: event serial number.
  • JDBC Fetch Size: the JDBC Fetch Size field can be used to set the maximum number of records which will be retrieved in one database call when fetching the data in a snapshot mode, thus limiting the memory consumption within the JVM. The default is 10000.
  • Max Rows to Read: the maximum number of rows to read when fetching the data in a snapshot mode. The default is empty, hence no limit.
  • Max Field Size: sets the limit for the maximum number of bytes that can be returned for character and binary column values in a ResultSet object produced by this Statement object. The default is empty, hence there is no limit.

SQL Server snapshot

  • Snapshot Mode: a mode for taking an initial snapshot of the structure and optional data of captured tables. Supported values are:
    • ad-hoc initial: the connector takes a snapshot of the structure and data of captured tables, then starts streaming change events from the captured tables. This mode allows adding new tables and triggering incremental snapshots at runtime.
    • ad-hoc schema only: the connector takes a snapshot of the structure of captured tables only, then starts streaming change events from the captured tables. This mode allows adding new tables and triggering incremental snapshots at runtime. 
    • initial: the connector takes a snapshot of the structure and data of captured tables, then starts streaming change events from the captured tables. It is useful if a complete representation of the data from the included tables should be captured. 
    • initial_schema_only: the connector takes a snapshot of the structure of captured tables only. It is useful if only changes happening from now onwards should be propagated downstream. Once the snapshot is complete, the connector will continue reading change events from the database’s redo logs.
    • initial only: the connector performs a database snapshot and stops before streaming any change event records.
  • Snapshot Isolation Node: mode to control which transaction isolation level is used and how long the connector locks the monitored tables. There are five possible values: read_uncommitted, read_committed, repeatable_read, snapshot, and exclusive ( in fact, exclusive mode uses repeatable read isolation level, however, it takes the exclusive lock on all tables to be read). It is worth documenting that snapshot, read_committed, and read_uncommitted modes do not prevent other transactions from updating table rows during the initial snapshot, while exclusive and repeatable_read do. Another aspect is data consistency. Only exclusive and snapshot modes guarantee full consistency, that is, initial snapshot and streaming logs constitute a linear history. In the case of repeatable_read and read_committed modes, it might happen that, for instance, a record added appears twice –– once in the initial snapshot and once in the streaming phase. Nonetheless, that consistency level should do for data mirroring. For read_uncommitted there are no data consistency guarantees at all (some data might be lost or corrupted).

Debezium SQL Server properties

Read about all properties available for Debezium SQL Server connector. The properties can be set as key-value pairs in Other Parameters. 

mceclip3.png

MySQL parameters

MySQL CDC connector parameters

  • Host: the host or IP address of the source database.
  • Port: the port the source database is listening to.
  • Database: the name of the database to connect on startup.
  • Include Databases(s): optional comma-separated list of regular expressions that match database names to be monitored; any database name not included in the whitelist will be excluded from monitoring. By default, all databases will be monitored.
  • Include Table(s): a comma-separated list of regular expressions that match fully-qualified table identifiers for tables to be monitored; any table not included in the whitelist will be excluded from monitoring. Each identifier is of the form databaseName.tableName. By default, the connector will monitor every non-system table in each monitored database. This parameter will be overridden in the TO field of the CDC Flow.
  • Excluded Columns(s): an optional comma-separated list of regular expressions that match the fully-qualified names of columns that should be excluded from change event message values. Fully-qualified names for columns are of the form databaseName.tableName.columnName, or databaseName.schemaName.tableName.columnName.
  • Case sensitive matching: by default, the system will use the case insensitive pattern to whitelist the db.table to stream. For example, if there is a database test and the tables abc and Abc the stream will include records from test.abc and test.Abc. By enabling this option you can enforce the case-sensitive matching of the db.table defined for the source.
  • CDC Key: a template for CDC key. The CDC key is used as a file name when streaming CDC events and creating files and as a topic name when streaming CDC events to a message queue such as Kafka. Example of the CDC key: inventory_customers_cdc_stream. Available tokens:
    • [db]: database name.
    • [table]: table name.
    • [op]:  the CDC operation: c (create), d (delete), u (update).
    • [timestamp]: the timestamp of the event in ms. 
    • [version]: the connector version.
    • [connector]: the connector class name.
    • [name]: the connector name.
    • [ts_ms]: the timestamp of the event in ms.
    • [snapshot]: true for a snapshot.
    • [server_id]: the MySQL server ID.
    • [gtid]: the GTID.
    • [file]: the binlog filename.
    • [pos]: the position in the binlog.
    • [row]: the row number.
  • JDBC Fetch Size: the JDBC Fetch Size field can be used to set the maximum number of records which will be retrieved in one database call when fetching the data in a snapshot mode, thus limiting the memory consumption within the JVM. The default is 10000.
  • Max Rows to Read: the maximum number of rows to read when fetching the data in a snapshot mode. The default is empty, hence no limit.
  • Max Field Size: sets the limit for the maximum number of bytes that can be returned for character and binary column values in a ResultSet object produced by this Statement object. The default is empty, hence no limit.

MySQL snapshot

  • Snapshot Mode: specifies the criteria for running a snapshot upon startup of the connector.
    • ad-hoc initial: the connector runs a snapshot upon startup whenever it deems it necessary (when no offsets are available, or when a previously recorded offset specifies a binlog location or GTID that is not available in the server), then starts streaming changes from a binlog. This mode allows adding new tables and triggering incremental snapshots at runtime.
    • ad-hoc schema only: use it if you don’t need a consistent snapshot of the data to be captured on startup but only need the changes since the connector was started. This mode allows adding new tables and triggering incremental snapshots at runtime.
    • initial: the connector runs a snapshot only when no offsets have been recorded for the logical server name, then starts streaming changes from the binlog.
    • initial_only: the connector runs a snapshot only when no offsets have been recorded for the logical server name and then stops, i.e. it will not read change events from the binlog.
    • schema_only: use it if you don’t need a consistent snapshot of the data but only need the changes since the connector was started.
    • when_needed: the connector runs a snapshot upon startup whenever it deems it necessary (when no offsets are available, or when a previously recorded offset specifies a binlog location or GTID that is not available in the server), then starts streaming changes from the binlog.
    • schema_only_recovery:  is a recovery mode for an existing connector to recover a corrupted or lost database history, or to periodically clean up a database history that may be growing unexpectedly.
    • always_recover_schema: this mode is similar to when_needed but it always triggers the schema refresh on startup so it can be used to add new tables.
    • schema_only_always_recover_schema: this mode is similar to schema_only but it always triggers the schema refresh on startup so it can be used to add new tables.
  • Snapshot Locking Node: controls if and how long the connector holds onto the global MySQL read lock (preventing any updates to the database) while it is performing a snapshot. There are three possible values minimal, extended, and none.
    • minimal: the connector holds the global read lock for just the initial portion of the snapshot while the connector reads the database schemas and other metadata. The remaining work in a snapshot involves selecting all rows from each table, and this can be done in a consistent fashion using the REPEATABLE READ transaction even when the global read lock is no longer held and while other MySQL clients are updating the database.
    • extended: in some cases where clients are submitting operations that MySQL excludes from REPEATABLE READ semantics, it may be desirable to block all writes for the entire duration of the snapshot.
    • none: will prevent the connector from acquiring any table locks during the snapshot process. This value can be used with all snapshot modes but it is safe to use if and only if no schema changes are happening while the snapshot is taken. Note that for tables defined with MyISAM engine, the tables would still be locked despite this property being set as MyISAM acquires a table lock. This behavior is unlike InnoDB engine which acquires row-level locks.
  • Starting binlog filename for Snapshot: the binlog filename to start pulling data from when switching from snapshot to binlog. This parameter is only used with initial and schema_only snapshot modes.
  • Starting binlog position for Snapshot: the position in the binlog file to start pulling data from when switching from snapshot to binlog snapshot. This parameter is only used with initial and scehma_only snapshot modes.
  • Starting GTID: the GTID to start pulling data from after snapshot. This parameter is only used with 'initial', 'when_needed', and 'schema_only' snapshot modes.

Debezium MySQL properties

Read about all properties available for Debezium MySQL connector. The properties can be set as key-value pairs in Other Parameters.

mceclip2.png

Oracle parameters

Oracle CDC connector parameters

  • Host: the host or IP address of the source database.
  • Port: the port that the source database is listening to.
  • Database: the name of the database to connect on startup. Must be the CDB name when working with the CDB + PDB model.
  • Name of the PDB: name of the PDB to connect to, when working with the CDB + PDB model.
  • Database URL: the raw database JDBC URL. This property can be used when more flexibility is needed and can support raw TNS names or RAC connection strings.
  • Include Table(s): a comma-separated list of regular expressions that match fully-qualified table identifiers for tables to be monitored; any table not included in the whitelist will be excluded from monitoring. Each identifier is of the form databaseName.tableName. By default, the connector will monitor every non-system table in each monitored database. This parameter will be overridden in the TO field of the CDC Flow.
  • Excluded Columns(s): an optional comma-separated list of regular expressions that match the fully-qualified names of columns that should be excluded from change event message values. Fully-qualified names for columns are of the form databaseName.tableName.columnName, or databaseName.schemaName.tableName.columnName.
  • CDC Key: a template for CDC key. The CDC key is used as a file name when streaming CDC events and creating files and as a topic name when streaming CDC events to a message queue such as Kafka. Example of the CDC key: server1_DEBEZIUM_CUSTOMERS_Envelope_cdc_stream. Available tokens:
      • [source]: fully qualified event source.
      • [op]: the CDC operation: c (create), d (delete), u (update).
      • [timestamp]: the timestamp of the event in ms .
      • [version]: the connector version.
      • [connector]: the connector class name.
      • [name]: the connector name.
      • [ts_ms]: the timestamp of the event in ms.
      • [snapshot]: true for a snapshot.
      • [txId]: the transaction id.
      • [scn]: the scn.
  • CDC Adapter: the adapter implementation to use. xstream uses the Oracle XStreams API. logminer uses the native Oracle LogMiner API. olr uses OpenLogReplicator. The default is logminer.
  • Run connector in read-only mode: if this option is enabled and CDC Adapter set to logminer, the connector will not attempt to flush the LGWR buffer to disk, allowing connecting to read-only databases.
  • Name of the XStream outbound server: name of the XStream outbound server configured in the database. This parameter is ignored if CDC Adapter set to logminer.
  • OpenLogReplicator source: the logical name of the configured source.name element in the OpenLogReplicator JSON configuration.
  • OpenLogReplicator host: the host name or IP address of the OpenLogReplicator network service.
  • OpenLogReplicator port: The port number of the OpenLogReplicator network service.
  • JDBC Fetch Size: the JDBC Fetch Size field can be used to set the maximum number of records which will be retrieved in one database call when fetching the data in a snapshot mode, thus limiting the memory consumption within the JVM. The default is 10000.
  • Max Rows to Read: the maximum number of rows to read when fetching the data in a snapshot mode. The default is empty, hence there is no limit.
  • Max Field Size: sets the limit for the maximum number of bytes that can be returned for character and binary column values in a ResultSet object produced by this Statement object. The default is empty, hence there is no limit.

Oracle snapshot

  • Snapshot Mode: mode for taking an initial snapshot of the structure and optional data of captured tables. Supported values are:
    • ad-hoc initial: the connector takes a snapshot of the structure and data of captured tables, then starts reading change events from the database’s redo logs. This mode allows adding new tables and triggering incremental snapshots at runtime.
    • ad-hoc schema only: the connector takes a snapshot of the structure of captured tables only, then starts reading change events from the database’s redo logs. This mode allows adding new tables and triggering incremental snapshots at runtime.
    • initial: the connector takes a snapshot of the structure and data of captured tables, then starts reading change events from the database’s redo logs.
    • schema_only: the connector takes a snapshot of the structure of captured tables only. It is useful if only changes happening from now onwards should be propagated downstream. Once the snapshot is complete, the connector will continue reading change events from the database’s redo logs.
    • schema_only_recovery: set this option to restore a database history that is lost or corrupted. After a restart, the connector runs a snapshot that rebuilds the database history.
    • always_recover_schema: This mode is similar to initial but it always triggers the schema refresh on startup so it can be used to add new tables.
    • schema_only_always_recover_schema: This mode is similar to schema_only but it always triggers the schema refresh on startup so it can be used to add new tables. 
  • Disable Flashback Query (AS OF) when creating snapshot: When the Oracle CDC connector starts a snapshot it reads the current system change number (SCN) position from the server’s redo log. It then scans all of the relevant database tables and schemas as valid at the SCN position that was read in the previous step (SELECT * FROM … AS OF SCN 123) and generates a READ event for each row. Flashback queries (AS OF) can be slow because Oracle needs to 'play' the DML events (insert/update/delete) with reference to a specific point in time. Enable this option to disable the flashback queries during the snapshot. This could improve the performance of the snapshot in some environments.

Embedded Infinispan Cache for Log Mining Buffer

Oracle writes all changes to the redo logs in the order in which they occur, including changes that are later discarded by a rollback. As a result, concurrent changes from separate transactions are intertwined. When the connector first reads the stream of changes, because it cannot immediately determine which changes are committed or rolled back, it temporarily stores the change events in an internal buffer. By default, the connector uses the heap memory of the JVM process to allocate and manage buffered event records which can cause OutOfMemory error for long-running and large transactions. The connector can also be configured to use Infinispan as its cache provider, supporting cache stores both locally with embedded mode or remotely on a server cluster. Enable property Enable Embedded Infinispan Cache for Log Mining Buffer to configure the connector to use an embedded cache store for Log Mining Buffer. This can significantly reduce the memory footprint of the connector. 

Debezium Oracle properties

Read about all properties available for Debezium Oracle connector. The properties can be set as key-value pairs in Other Parameters.

mceclip0.png

Postgres parameters

Postgres CDC connector parameters

  • Host: the host or IP address of the source database.
  • Port: the port the source database is listening to.
  • Database: the name of the database to connect on startup.
  • Include Schemas(s): an optional, comma-separated list of regular expressions that match names of schemas for which you want to capture changes. Any schema name not included in schema.include.list is excluded from having its changes captured. By default, all non-system schemas have their changes captured. Do not also set the schema.exclude.list property.
  • Include Table(s):a comma-separated list of regular expressions that match fully-qualified table identifiers for tables to be monitored; any table not included in the whitelist will be excluded from monitoring. Each identifier is of the form databaseName.tableName. By default, the connector will monitor every non-system table in each monitored database. This parameter will be overridden in the TO field of the CDC Flow.
  • Excluded Columns(s): an optional comma-separated list of regular expressions that match the fully-qualified names of columns that should be excluded from change event message values. Fully-qualified names for columns are of the form databaseName.tableName.columnName, or databaseName.schemaName.tableName.columnName.
  • Server: logical name that identifies and provides a namespace for the particular PostgreSQL database server/cluster being monitored. The logical name should be unique across all other connectors, since it is used as a prefix for all Kafka topic names coming from this connector. Defaults to host:port/dbname, though we recommend using an explicit and meaningful logical name.
  • Output Plugin: PostgreSQL’s logical decoding feature was first introduced in version 9.4 and is a mechanism that allows the extraction of the changes which were committed to the transaction log and the processing of these changes in a user-friendly manner via the help of an output plugin. This output plugin must be installed prior to running the PostgreSQL server and enabled together with a replication slot in order for clients to be able to consume the changes.
  • Replication Slot Name: the name of the PostgreSQL logical decoding slot that was created for streaming changes from a particular plug-in for a particular database/schema. The server uses this slot to stream events to the connector that you are configuring.
  • Append Included Tables to Replication Slot Name: if this option is enabled the system will append the 'included tables' to the replication slot name. This will create a new replication slot if the list has changed, therefore triggering the new snapshot for all included tables.
  • Propagate default values: if this option is enabled and a default value is specified for a column in the database schema, the PostgreSQL connector will attempt to propagate this value to the stored schema whenever possible. 
  • CDC Key: a template for CDC key. The CDC key is used as a file name when streaming CDC events and creating files and as a topic name when streaming CDC events to a message queue such as Kafka. Example of the CDC key: postgres_public_customers_cdc_stream.  Available tokens: 
      • [db: database name.
      • [schema]: schema name.
      • [table]: table name.
      • [op]: the CDC operation: c (create), d (delete), u (update).
      • [timestamp]: the timestamp of the event in ms. 
      • [version]: the connector version.
      • [connector]: the connector class name.
      • [name]:the connector name.
      • [ts_ms]: the timestamp of the event in ms.
      • [snapshot]:true for a snapshot.
      • [txId]: the transaction ID.
      • [lsn]: the lsn.
      • [xmin]: the xmin.
  • JDBC Fetch Size: the JDBC Fetch Size field can be used to set the maximum number of records which will be retrieved in one database call when fetching the data in a snapshot mode, thus limiting the memory consumption within the JVM. The default is 10000.
  • Max Rows to Read: the maximum number of rows to read when fetching the data in a snapshot mode. The default is empty, hence there is no limit.
  • Max Field Size: sets the limit for the maximum number of bytes that can be returned for character and binary column values in a ResultSet object produced by this Statement object. The default is empty, hence there is no limit.

Postgres snapshot

  • Snapshot Mode: specifies the criteria for running a snapshot upon startup of the connector.
    • ad-hoc initial: the connector performs an initial snapshot only when no offsets have been recorded for the logical server name. This mode allows adding new tables and triggering incremental snapshots at runtime.
    • ad-hoc: the connect never runs a snapshot and upon the first startup with a logical server name the connector reads data either from where it last left off (last LSN position) or starts from the beginning from the point of the view of the logical replication slot. This mode allows adding new tables and triggering incremental snapshots at runtime.
    • initial: the connector performs a snapshot only when no offsets have been recorded for the logical server name.
    • initial_only: the connector only takes an initial snapshot and then stops, without processing any subsequent changes.
    • never: the connect never runs a snapshot and upon the first startup with a logical server name the connector reads data either from where it last left off (last LSN position) or starts from the beginning from the point of the view of the logical replication slot.
    • always: the connector runs a snapshot each time on startup.
    • exported: deprecated and equals to initial.
    • custom: this snapshot mode lets you inject your own implementation of the io.debezium.connector.postgresql.spi.Snapshotter interface.

Debezium PostgreSQL properties

Read about all properties available for Debezium PostgreSQL connector. The properties can be set as key-value pairs in Other Parameters. 

mceclip1.png

DB2 parameters

DB2 CDC connector parameters

  • Host: the host or IP address of the source database.
  • Port: the port the source database is listening to.
  • Database: the name of the database to connect on startup.
  • Include Table(s): a comma-separated list of regular expressions that match fully-qualified table identifiers for tables to be monitored; any table not included in the whitelist will be excluded from monitoring. Each identifier is of the form databaseName.tableName. By default, the connector will monitor every non-system table in each monitored database. This parameter will be overridden in the TO field of the CDC Flow.
  • Excluded Columns(s): an optional comma-separated list of regular expressions that match the fully-qualified names of columns that should be excluded from change event message values. Fully-qualified names for columns are of the form databaseName.tableName.columnName, or databaseName.schemaName.tableName.columnName.
  • CDC Key: a template for CDC key. The CDC key is used as a file name when streaming CDC events and creating files and as a topic name when streaming CDC events to a message queue such as Kafka. Example of the CDC key: testDB_testSchema_customers_cdc_stream. This template defines how CDC Key is generated. Available tokens: [db] - database name, [schema] - schema name, [table] - table name. Also: [op], [timestamp], [version], [connector], [name], [ts_ms], [snapshot], [change_lsn], [commit_lsn].
  • JDBC Fetch Size: the JDBC Fetch Size field can be used to set the maximum number of records which will be retrieved in one database call when fetching the data in a snapshot mode, thus limiting the memory consumption within the JVM. The default is 10000.
  • Max Rows to Read: the maximum number of rows to read when fetching the data in a snapshot mode. The default is empty, hence no limit.
  • Max Field Size: sets the limit for the maximum number of bytes that can be returned for character and binary column values in a ResultSet object produced by this Statement object. The default is empty, hence there is no limit.

DB2 snapshot

  • Snapshot Mode: specifies the criteria for performing a snapshot when the connector starts:
    • ad-hoc initial: for tables in capture mode, the connector takes a snapshot of the schema for the table and the data in the table. After the snapshot is completed, the connector continues by reading change events from the database’s redo logs. This mode allows adding new tables and triggering incremental snapshots at runtime.
    • ad-hoc schema only: for tables in capture mode, the connector takes a snapshot of only the schema for the table. After the schema snapshot is completed, the connector continues by reading change events from the database’s redo logs. This mode allows adding new tables and triggering incremental snapshots at runtime.
    • initial: for tables in capture mode, the connector takes a snapshot of the schema for the table and the data in the table.
    • schema_only: for tables in capture mode, the connector takes a snapshot of only the schema for the table. This is useful when only the changes that are happening from now on need to be emitted. After the snapshot is completed, the connector continues by reading change events from the database’s redo logs.
  • Snapshot Isolation Node: during a snapshot, controls the transaction isolation level and how long the connector locks the tables that are in capture mode. The possible values are:
    • read_uncommitted: does not prevent other transactions from updating table rows during an initial snapshot. This mode has no data consistency guarantees; some data might be lost or corrupted.
    • read_committed: does not prevent other transactions from updating table rows during an initial snapshot. It is possible for a new record to appear twice: once in the initial snapshot and once in the streaming phase. However, this consistency level is appropriate for data mirroring.
    • repeatable_read: prevents other transactions from updating table rows during an initial snapshot. It is possible for a new record to appear twice: once in the initial snapshot and once in the streaming phase. However, this consistency level is appropriate for data mirroring.
    • exclusive: uses repeatable read isolation level but takes an exclusive lock for all tables to be read. This mode prevents other transactions from updating table rows during an initial snapshot. Only exclusive mode guarantees full consistency; the initial snapshot and streaming logs constitute a linear history.

Debezium DB2 properties

Read about all properties available for Debezium DB2 connector. The properties can be set as key-value pairs in Other Parameters. 

mceclip3.png

MongoDB parameters

MongoDB CDC connector parameters

  • Cluster URL: the required Connection string. Read about the MongoDB Connection string.

  • Capture modes: the capture mode used by the connector when pulling changes from the MongoDB:

    • change_streams_update_full: enable to capture via MongoDB Change Streams mechanism, update message contains the full message. This is a default method.
    • change_streams: enbale to capture via MongoDB Change Streams mechanism, update message does not contain the full message.
    • oplog: enable to capture changes from oplog. This is the method is depricated.
  • Include Databases(s): optional comma-separated list of regular expressions that match database names to be monitored; any database name not included in the whitelist is excluded from monitoring. By default, all databases are monitored. 
  • Include Collection(s): optional comma-separated list of regular expressions that match database names to be excluded from monitoring; any database name not included in the blacklist is monitored.
  • CDC Key: a template for CDC key. The CDC key is used as a file name when streaming CDC events and creating files and as a topic name when streaming CDC events to a message queue such as Kafka. Example of the CDC key: inventory_customers_cdc_stream. Available tokens:
    • [db]: the database name.
    • [collection]: the collection name.
    • [op]: the CDC operation: c (create), d (delete), u (update).
    • [timestamp]: the timestamp of the event in ms.
    • [version]: the connector version.
    • [connector]: the connector class name.
    • [name]: the connector name.
    • [ts_ms]: the timestamp of the event in ms.
    • [snapshot]: true for a snapshot.
    • [order]: the order of the event. 
    • [h]: the h of the event.
  • Snapshot Mode: Specifies the criteria for running a snapshot (eg. initial sync) upon startup of the connector. Supported values are:
    • initial - the connector reads a snapshot when no offset is found. Uppon completion of the snapshot the connector begins to stream change events.
    • ad-hoc initial - same as 'initial' but this mode allows adding new collections and triggering ad-hoc snapshot of new collections on startup and/or on-demand;
    • never - the connector never snapshots the data and on startup immediately begins to stream change events; ad-hoc - sames as 'never' but this mode allows adding new collections and triggering ad-hoc snapshot of new collections on startup and/or on-demand
  • New name of the Object ID (_id) field: each record in MongoDB collection has an _id field. Typically it is some sort of nested JSON object, often generated automatically. The CDC Flow will automatically flatten the value of the _id field and transform it into a unique string. E "_id": ObjectId("54759eb3c090d83494e2d804") to "_id": "54759eb3c090d83494e2d804". It will also rename the _id field. The default new name is mongo_id.
  • Number of Documents in Explorer: the maximum number of the MongoDB documents that can be displayed in the Etlworks Explorer. The default value is 10, the maximum possible value is 9999. MongoDB can contain millions of documents, so displaying all of them can kill the server.

Debezium MongoDB properties

Read about all properties available for Debezium MongoDB connector. The properties can be set as key-value pairs in Other Parameters.  

 

mceclip0.png

AS400 parameters

Etlworks AS400 CDC connector is based on community driven open source project Debezium connector for IBMI. It uses the IBM I journal as a source of CDC events.

AS400 CDC connector parameters

  • Host: the host or IP address of the source database.
  • Port: the port the source database is listening to.
  • Database: the name of the database to connect on startup.
  • Include Table(s): a comma-separated list of regular expressions that match fully-qualified table identifiers for tables to be monitored; any table not included in the whitelist will be excluded from monitoring. Each identifier is of the form databaseName.tableName. By default, the connector will monitor every non-system table in each monitored database. This parameter will be overridden in the TO field of the CDC Flow.
  • Excluded Columns(s): an optional comma-separated list of regular expressions that match the fully-qualified names of columns that should be excluded from change event message values. Fully-qualified names for columns are of the form databaseName.tableName.columnName, or databaseName.schemaName.tableName.columnName.
  • CDC Key: a template for CDC key. The CDC key is used as a file name when streaming CDC events and creating files and as a topic name when streaming CDC events to a message queue such as Kafka. Example of the CDC key: testDB_testSchema_customers_cdc_stream. This template defines how CDC Key is generated. Available tokens: [db] - database name, [schema] - schema name, [table] - table name. Also: [op], [timestamp], [version], [connector], [name], [ts_ms], [snapshot], [change_lsn], [commit_lsn].
  • JDBC Fetch Size: the JDBC Fetch Size field can be used to set the maximum number of records which will be retrieved in one database call when fetching the data in a snapshot mode, thus limiting the memory consumption within the JVM. The default is 10000.
  • Max Rows to Read: the maximum number of rows to read when fetching the data in a snapshot mode. The default is empty, hence no limit.
  • Max Field Size: sets the limit for the maximum number of bytes that can be returned for character and binary column values in a ResultSet object produced by this Statement object. The default is empty, hence there is no limit.

AS400 snapshot

  • Snapshot Mode: specifies the criteria for performing a snapshot when the connector starts:
    • initial: for tables in capture mode, the connector takes a snapshot of the schema for the table and the data in the table.
    • schema_only: for tables in capture mode, the connector takes a snapshot of only the schema for the table. This is useful when only the changes that are happening from now on need to be emitted. After the snapshot is completed, the connector continues by reading change events from the database’s redo logs.
    • never: never perform as snapshot and start steaming immediately ion startup.
  • Size of buffer: the size of buffer for fetching journal entries default 131072 (should not be smaller).
  • Maximum number of journal entries to process server side: maximum number of journal entries to process server side when filtering. Default is 1000000.
  • AS400 Date Format: default date format is 2 digit date 1940->2039 set this to 'iso' or make sure you only have dates in this range, performance is ambysmal if you don't not to mention lots of missing data.
  • Error reporting: JDBC level of detail to include in log.

AS400 properties

Read about all properties available for AS400 connector. The properties can be set as key-value pairs in Other Parameters. 

mceclip3.png

 



etlworks.com_-_free_trial_banners_2.png

Related articles

Comments

0 comments

Please sign in to leave a comment.