Transformation is not working

[Dwija D]

Hi

I have a debezium connector(Oracle) running and is generating change events. There are two transformation i am trying to accomplish one is for the column ts_ms  and another is for the column LAST_UPDATE_DATE. The  datatype for the column LAST_UPDATE_DATE in the table definition is Date.

I am applying following transformation in the connector:

"transforms": "convert1,convert2",

    "transforms.convert1.type": "org.apache.kafka.connect.transforms.TimestampConverter$Value",

    "transforms.convert1.target.type": "string",

    "transforms.convert1.field": "ts_ms",

    "transforms.convert1.format": "dd-MMM-yyyy HH:mm:ss",

    "transforms.convert2.type": "org.apache.kafka.connect.transforms.TimestampConverter$Value",

    "transforms.convert2.target.type": "string",

    "transforms.convert2.field": "LAST_UPDATE_DATE",

    "transforms.convert2.format": "dd-MMM-yyyy HH:mm:ss"

However, the connector transforms only ts_ms column leaving LAST_UPDATE_DATE column without any transformation.

One sample JSON message for change events is like:

{"before":null,"after":{"USER_NAME":"FEDERAL","LAST_UPDATE_DATE":1150795738000,"ASSIGNMENT_REASON":null},"source":{"version":"1.6.1.Final","connector":"oracle","name":"VIS_R1227","ts_ms":1628351315650,"snapshot":"true","db":"VIS","sequence":null,"schema":"APPLSYS","table":"WF_USER_ROLE_ASSIGNMENTS","txId":null,"scn":"12204814242730","commit_scn":null,"lcr_position":null},"op":"r","ts_ms":"07-Aug-2021 15:48:35","transaction":null}

I assume, the debezium connector transforms all the Date/Timestamp related columns  to milliseconds(epoch).   Is that correct ?

I am using Oracle 12c with Debezium  1.6.1.Final.

Any suggestions  to get past this issue ?

Thanks and regards

[Dwija D]

I think  standard transformation org.apache.kafka.connect.transforms.*  can only be applied to the fields of first level, e.g. before or after, but the transformation can't be applied to nested fields, e.g. after.LAST_UPDATE_DATE or before.LAST_UPDATE_DATE

Therefore, The debezium connector able to transform outer ts_ms column but the inner ts_ms column could not be transformed along with LAST_UPDATE_DATE

What are the options available to retain all the nested columns inside before and after and also to apply transformation to select fields in a source connector ?

 Thanks

[Gunnar Morling]

Dwija,

I think your best (or probably only) option is to implement a custom SMT which supports timestamp conversion for nested fields. Or, you could gauge interest in the upstream Kafka community whether they'd be open for a PR adding this capability to TimestampConverter. Alternatively, you could use Debezium's flattening SMT (https://debezium.io/documentation/reference/configuration/event-flattening.html), if only propagating the "after" part of change events (note you can fold selected fields from the "source" part into the emitted flat row).

--Gunnar

[Dwija D]

Hi

Gunner,

Many thanks for confirming and suggesting possible ways to implement the issue.

Thanks