How to format Timestamp value to Date Format in Confluent Kafka connect - JDBC Oracle Source Connector

1
votes

I have setup a JDBC Oracle Source Connector in Kafka Connect and I have a timestamp column in Oracle table whose value is set as date format "21-MAR-18 05.39.44.0194990 AM".

All the data from the Oracle table is fetched into the Kafka topics, but the value of column with date format (timestamp column) in oracle table is converted to milliseconds and displayed in the topic as milliseconds value. I would like to have the date format received as it is in Oracle table.

{
  "name":"JDBC_CONN_1",
  "config": {
  "connector.class": "io.confluent.connect.jdbc.JdbcSourceConnector",
  "connection.url": "jdbc:oracle:thin:@<hostname>:1521/<servicename>",
  "connection.user": "User",
  "connection.password": "Password",
  "table.whitelist": "Table_Name",
  "catalog.pattern": "",
  "schema.pattern": "Schema_Name",
  "mode": "bulk",
  "poll.interval.ms": "1000",
  "numeric.mapping":"best_fit",
  "topic.prefix": "JDBC_CONN_1"
  }
}

DB Table column:

UPDATED_ON  TIMESTAMP(6)    Not Null
column value - 27-MAR-18 05.39.44.0194490 AM

Value in Topic:1522129184019 (equivalent milli second format)

2
jdbcapache-kafkaapache-kafka-connect

2 Answers

1
votes

You can use the SMT TimeStampConverter

https://docs.confluent.io/current/connect/transforms/timestampconverter.html#timestampconverter

The exemple seam to resolve your problem :

"transforms": "TimestampConverter",
"transforms.TimestampConverter.type": "org.apache.kafka.connect.transforms.TimestampConverter$Value",
"transforms.TimestampConverter.format": "yyyy-MM-dd"
"transforms.TimestampConverter.target.type": "string"

Before: 1556204536000

After: "2014-04-25"

0
votes

A simple and elegant way to resolve this is using this property on MySQL Source:

"time.precision.mode":"connect"

This will treat the dates using the Kafka Connect Built-in date types:

connect represents time and timestamp values using Kafka Connect’s built-in representations for Time, Date, and Timestamp. It uses millisecond precision regardless of database column precision.

The default value for this property is "adaptive_time_microseconds":

adaptive_time_microseconds (the default) which captures the date, datetime and timestamp values exactly as they are in the database. It uses either millisecond, microsecond, or nanosecond precision values that are are based on the database column’s type. An exception to this are TIME type fields, which are always captured as microseconds.

For more details, see the Debezium MySQL source documentation