6

I am trying to convert all timestamp fields to a string type with the format yyyy-MM-dd HH:mm:ss.

To transform multiple fields, I have to create a transform for each one individually.

...
"transforms":"tsFormat1,tsFormat2,...,tsFormatN",
"transforms.tsFormat1.type": "org.apache.kafka.connect.transforms.TimestampConverter$Value",
"transforms.tsFormat1.target.type": "string",
"transforms.tsFormat1.field": "ts_col1",
"transforms.tsFormat1.format": "yyyy-MM-dd HH:mm:ss",
"transforms.tsFormat2.type": "org.apache.kafka.connect.transforms.TimestampConverter$Value",
"transforms.tsFormat2.target.type": "string",
"transforms.tsFormat2.field": "ts_col2",
"transforms.tsFormat2.format": "yyyy-MM-dd HH:mm:ss",
...
"transforms.tsFormatN.type": "org.apache.kafka.connect.transforms.TimestampConverter$Value",
"transforms.tsFormatN.target.type": "string",
"transforms.tsFormatN.field": "ts_colN",
"transforms.tsFormatN.format": "yyyy-MM-dd HH:mm:ss",
...

 

Is there any way to apply a single transform on all timestamp columns?

I have tried,

...
"transforms":"tsFormat",
"transforms.tsFormat.type": "org.apache.kafka.connect.transforms.TimestampConverter$Value",
"transforms.tsFormat.target.type": "string",
"transforms.tsFormat.field": "ts_col1, ts_col2,..., ts_colN",
"transforms.tsFormat.format": "yyyy-MM-dd HH:mm:ss",
...

and

...
"transforms":"tsFormat",
"transforms.tsFormat.type": "org.apache.kafka.connect.transforms.TimestampConverter$Value",
"transforms.tsFormat.target.type": "string",
"transforms.tsFormat.field": "ts_col1",
"transforms.tsFormat.field": "ts_col2",
...
"transforms.tsFormat.field": "ts_colN",
"transforms.tsFormat.format": "yyyy-MM-dd HH:mm:ss",
...

 

What would be even better is something like numeric type matching "numeric.mapping": "best_fit". Just like how numeric.mapping applies to all numeric fields (without having to manually specify the field names) to try and find the best numeric type, is there something like this that can apply a transform or string format for all timestamp fields?

A. Saunders
  • 815
  • 1
  • 6
  • 19
  • `.field` is singular... `numeric.mapping` isn't part of the Connect API, so which connector are you using? – OneCricketeer Apr 01 '19 at 22:40
  • I understand that `.field` is singular. That is why I am asking if there is some other way to accomplish this. I am using the `io.confluent.connect.jdbc.JdbcSourceConnector` connector. Is there a way to format all timestamp columns other than by creating a transform for each individually? – A. Saunders Apr 02 '19 at 15:40
  • 2
    There is not... Option 1) Write the data with the correct timestamp initially. Option 2) Use Kafka Streams or KSQL ahead of Connect's consumer. Option 3) Let the data land in the database as-is, then use database functions to parse it later when you actually display the data in applications – OneCricketeer Apr 02 '19 at 16:26

2 Answers2

0

I made a little tweaks base on the original TimestampConverter, works as perfect as what you want: https://github.com/howareyouo/kafka-connect-timestamp-converter

backflow
  • 11
  • 2
0

There is a Github repo providing custom converter to convert all timestamp messages into string datetime format.

So your code should be like this

"converters": "timestampConverter",
"timestampConverter.type": "oryanmoshe.kafka.connect.util.TimestampConverter",
"timestampConverter.format.datetime": "YYYY-MM-dd HH:mm:ss"

Don't forgot to add Timestampconverter.jar into plugin.path variable of kafka-connect.

Achyut Vyas
  • 471
  • 1
  • 6
  • 18