Handling Default Values for NULL Data During MySQL to ClickHouse Replication

[simplecrm-github]

Question

How can I replace NULL values with specific default values (e.g., -1, 'NA', '1700-00-00 00:00:00') during the replication from MySQL to ClickHouse using mysql_ch_replicator?

Context

We are using mysql_ch_replicator to replicate data from MySQL to ClickHouse. During the replication process, we need to ensure that NULL values in certain columns from MySQL are replaced with specific default values when the data is inserted into ClickHouse. The data types involved are FLOAT, INT, VARCHAR, DATETIME, DATE, and DECIMAL.

The goal is to automatically replace NULL values with predefined default values for the following.

FLOAT / INT: Replace NULL with -1

VARCHAR: Replace NULL with 'NA'

DATETIME: Replace NULL with '1700-00-00 00:00:00'

DATE: Replace NULL with '1700-00-00'

DECIMAL: Replace NULL with -1.1

Kindly share the solution?

[bakwc]

Currently we don't support on-flight conversions of data types. There is a convert_record python function inside converter.py file, you could modify it to convert null records into some defined types.
I will implement a config mapping if there would be some other similar requests.