DateTime conversion

[saty...@telmate.com]

Hi Randall,

Is there a way i can convert datetime column type from mysql, into io.debezium.time.ZonedTimestamp?

Currently they are getting converted into long int or number of milliseconds, is there a way to convert it into datetime formatted string or timestamp format string or ZonedTimestamp, to make sure i get the proper timestamp and not int values. Example (2017-08-07 15:13:01).

From the documentation i see, both the option adaptive and connective types for the property time.precision.mode , converts the datetime format into int64 type, is there any way i can change it to io.debezium.time.ZonedTimestamp?.

or a string type of timestamp format and not int64.

Regards,

Satyajit.

[Randall Hauch]

There's no way to do that out of the box, since MySQL's DATETIME does not have any notion of timezone and will not perform any timezone conversions. 

If you want to use ZonedTimestamp instead, you could use a custom SMT that converts from `io.debezium.time.Timestamp` into a `io.debezium.time.ZonedTimestamp` by providing the missing timezone information.

--
You received this message because you are subscribed to the Google Groups "debezium" group.
To unsubscribe from this group and stop receiving emails from it, send an email to debezium+unsubscribe@googlegroups.com.
To post to this group, send email to debe...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/9ef9159b-ba2e-4789-8c95-06a30bd2b8ae%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[saty...@telmate.com]

Hi Randall,

What if , i don't care about the timezone, but would need just the timestamp format() and not the long number or int64 number for the column value?

Regards,

Satyajit.

On Monday, August 7, 2017 at 3:31:40 PM UTC-7, Randall Hauch wrote:

There's no way to do that out of the box, since MySQL's DATETIME does not have any notion of timezone and will not perform any timezone conversions. 

If you want to use ZonedTimestamp instead, you could use a custom SMT that converts from `io.debezium.time.Timestamp` into a `io.debezium.time.ZonedTimestamp` by providing the missing timezone information.

On Mon, Aug 7, 2017 at 3:26 PM, <saty...@telmate.com> wrote:

Hi Randall,

Is there a way i can convert datetime column type from mysql, into io.debezium.time.ZonedTimestamp?

Currently they are getting converted into long int or number of milliseconds, is there a way to convert it into datetime formatted string or timestamp format string or ZonedTimestamp, to make sure i get the proper timestamp and not int values. Example (2017-08-07 15:13:01).

From the documentation i see, both the option adaptive and connective types for the property time.precision.mode , converts the datetime format into int64 type, is there any way i can change it to io.debezium.time.ZonedTimestamp?.

or a string type of timestamp format and not int64.

Regards,

Satyajit.

--
You received this message because you are subscribed to the Google Groups "debezium" group.

To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.

[Randall Hauch]

The connector doesn't do anything for this out of the box. All I can suggest is to use a custom SMT to convert from the data that's there to what you want.

Best regards,

Randall

On Mon, Aug 7, 2017 at 6:17 PM, <saty...@telmate.com> wrote:

Hi Randall,

What if , i don't care about the timezone, but would need just the timestamp format() and not the long number or int64 number for the column value?

Regards,

Satyajit.

On Monday, August 7, 2017 at 3:31:40 PM UTC-7, Randall Hauch wrote:

There's no way to do that out of the box, since MySQL's DATETIME does not have any notion of timezone and will not perform any timezone conversions. 

If you want to use ZonedTimestamp instead, you could use a custom SMT that converts from `io.debezium.time.Timestamp` into a `io.debezium.time.ZonedTimestamp` by providing the missing timezone information.

On Mon, Aug 7, 2017 at 3:26 PM, <saty...@telmate.com> wrote:

Hi Randall,

Is there a way i can convert datetime column type from mysql, into io.debezium.time.ZonedTimestamp?

Currently they are getting converted into long int or number of milliseconds, is there a way to convert it into datetime formatted string or timestamp format string or ZonedTimestamp, to make sure i get the proper timestamp and not int values. Example (2017-08-07 15:13:01).

From the documentation i see, both the option adaptive and connective types for the property time.precision.mode , converts the datetime format into int64 type, is there any way i can change it to io.debezium.time.ZonedTimestamp?.

or a string type of timestamp format and not int64.

Regards,

Satyajit.

--
You received this message because you are subscribed to the Google Groups "debezium" group.
To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
To post to this group, send email to debe...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/9ef9159b-ba2e-4789-8c95-06a30bd2b8ae%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "debezium" group.

To unsubscribe from this group and stop receiving emails from it, send an email to debezium+unsubscribe@googlegroups.com.

To post to this group, send email to debe...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/2c16d88a-e09a-43b4-9115-b47e37483d69%40googlegroups.com.

[saty...@telmate.com]

Thank you Randall.

Appreciate your quick response.

[Gunnar Morling]

Using an SMT is viable for sure.

Although I'm wondering whether we shouldn't provide an option in Debezium itself to serialize DATETIME columns as a formatted String alternatively. Transmitting them optionally as "2017-08-07 15:13:01" seems like a very useful approach as it makes messages more human-readable.

Any thoughts?

To unsubscribe from this group and stop receiving emails from it, send an email to debezium+unsubscribe@googlegroups.com.

To post to this group, send email to debe...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/cef193d6-b4a1-4396-9a95-44425a4f42ed%40googlegroups.com.

[Randall Hauch]

I think an SMT to do this would be great, especially if it were well written and easy to use on 1+ fields (rather than require a separate SMT for each field). And maybe contribute to Kafka Connect for the fall release.

I would strongly favor limiting the variability in the connectors. Every config option makes a connector more difficult to use and more difficult to test. If SMTs would have been available earlier, I have done a number of the existing MySQL options via SMTs rather than connector configs.

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/CADGJaX-KLuCYJdQOEBsTp15JRuYSMkK0X-wfO%3D%3DvHj4-EJ33Pg%40mail.gmail.com.

[Gunnar Morling]

I hear what you're saying about complexity of configuration. But then, it seems less complex overall to just emit the value in the intended format to begin with instead of applying SMTs to them.

Also, is there a known perf overhead to using SMTs? Finding the field(s) to apply the SMT to seems like it could be costly potentially. I guess it depends on the message structure and format, too.

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/CALYgK0GqF9LB_ZkdVv%3DtVNVCbfGorokO3erOM2AuqP9hmPdejw%40mail.gmail.com.

[Randall Hauch]

The problem is that a stringified timestamp is no longer a "timestamp", it's a string. Imagine what the documentation at http://debezium.io/docs/connectors/mysql/#temporal-values would look like if we added an *orthogonal* configuration to convert timestamps (and dates? and times? and datetimes?) to equivalent ISO formatted strings.

Then someone is going to want a different format, and this cycle never ends. SMTs are extremely powerful, and if we create one for this purpose we can make it easy to configure and apply to multiple fields.

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/CADGJaX85v1XaJYZHBL37cj1ckGs0JzqRaAy11h0CxkCvAGBGwQ%40mail.gmail.com.

[satyajit vegesna]

Hi Randall/Gunnar,

I would be interested in taking this task, may be we could leverage an existing SMT timestampconverter(https://github.com/apache/kafka/blob/trunk/connect/transforms/src/main/java/org/apache/kafka/connect/transforms/TimestampConverter.java).

But i might need little guidance for it, and if so, i can create a ticket for the same and start working on it.

Regards,

Satyajit.

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/cef193d6-b4a1-4396-9a95-44425a4f42ed%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "debezium" group.

To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
To post to this group, send email to debe...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/CADGJaX-KLuCYJdQOEBsTp15JRuYSMkK0X-wfO%3D%3DvHj4-EJ33Pg%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "debezium" group.

To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
To post to this group, send email to debe...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/CALYgK0GqF9LB_ZkdVv%3DtVNVCbfGorokO3erOM2AuqP9hmPdejw%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "debezium" group.

To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
To post to this group, send email to debe...@googlegroups.com.

[Gunnar Morling]

Hi Satyajit,

Awesome, let's give the SMT approach a try then, we always can re-consider and make it an option within the connector itself, should we at some point conclude that this is better. The embedded engine btw. is one area which may benefit from having the option in the connector. Although, perhaps we could even have support for SMTs in the embedded engine, too.

I suggest you move forward, create an issue, add a comment you are working on it and start exploring it. If you run into any problems, we can discuss them and try and help as they occur.

Thanks a lot!

--Gunnar

To unsubscribe from this group and stop receiving emails from it, send an email to debezium+unsubscribe@googlegroups.com.

To post to this group, send email to debe...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/a592142c-5908-49ce-ab90-c3004c1c5013%40googlegroups.com.

[satyajit vegesna]

Hi Randall/Gunnar,

I have a question wrt SMT datetime conversion,

i was able to enhance the Timestampconverter SMT to support debezium , but the problem is, at one point when i convert the "io.debezium.time.Timestamp" , i have to put the new value into the struct ,like the below code (https://github.com/apache/kafka/blob/trunk/connect/transforms/src/main/java/org/apache/kafka/connect/transforms/TimestampConverter.java#L372), which internally validates the value (https://github.com/apache/kafka/blob/e31c0c9bdbad432bc21b583bd3c084f05323f642/connect/api/src/main/java/org/apache/kafka/connect/data/ConnectSchema.java#L221L229) , the problem is that io.debezium.time.timestamp is not a part of the SCHEMA_TYPE_CLASSES or LOGICAL_TYPE_CLASSES.

So it throws an error, can anyone of you suggest the best possible way to deal with the situation or any clue on how to handle the above scenario.

Regards,

Satyajit.

Hi Satyajit,

[Jiri Pechanec]

Hi,

correct me if I am wrong but you must convert to a datatype that is supported by Connect converter. So effectivelly you should provide a SMT that converts from epoch time into a predefined string with TZ info. See ZonedTimestamp.builder() that it really maps into schema type string.

Looking into the converter code I think it is good start and it needs only one thing - it needs a support for enrichment with custom TZ so instead of always using UTC as it is now it would be possible  to provide an arbitrary TZ as one parameter and then execute conversion you need. I'd say this would be great PR to a Connect code.

A better solution (not available now) would be to enhance Connect with support of Java 1.8 time types like ZonedDateTime that would make the handling consistent accross all connectors - but it is probably a bigger task and Randall should probably write a KIP for it ;-).

J

[satyajit vegesna]

Hi Jiri,

It would be great to have TZ support, but my question was about a small issue that i was facing wrt to Timestamp conversion for debezium, which i think i figured out.

@Gunnar/Randall will keep you updated on the progress.

Regards,

Satyajit.

[Jim Glennon]

Hi Randall,

I'm curious why the Kafka Connect data types (i.e. from org.apache.kafka.connect.data) were not used by the Debezium Connector. It seems it would make integration with other connectors (e.g. JDBC Sink Connector) more straightforward.

Thanks.

~jim

On Monday, August 7, 2017 at 6:31:40 PM UTC-4, Randall Hauch wrote:

There's no way to do that out of the box, since MySQL's DATETIME does not have any notion of timezone and will not perform any timezone conversions. 

If you want to use ZonedTimestamp instead, you could use a custom SMT that converts from `io.debezium.time.Timestamp` into a `io.debezium.time.ZonedTimestamp` by providing the missing timezone information.

On Mon, Aug 7, 2017 at 3:26 PM, <saty...@telmate.com> wrote:

Hi Randall,

Is there a way i can convert datetime column type from mysql, into io.debezium.time.ZonedTimestamp?

Currently they are getting converted into long int or number of milliseconds, is there a way to convert it into datetime formatted string or timestamp format string or ZonedTimestamp, to make sure i get the proper timestamp and not int values. Example (2017-08-07 15:13:01).

From the documentation i see, both the option adaptive and connective types for the property time.precision.mode , converts the datetime format into int64 type, is there any way i can change it to io.debezium.time.ZonedTimestamp?.

or a string type of timestamp format and not int64.

Regards,

Satyajit.

--
You received this message because you are subscribed to the Google Groups "debezium" group.

To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.

[Randall Hauch]

Debezium defines a number of additional data types: Bits, Json, Uuid, VariableScaleDecimal, Xml, plus lots of variations of date-time types, including year, zoned time, zoned timestamp, etc. The Kafka Connect logical data type were simply incapable of accurately representing the various quantities that MySQL, PostgreSQL, and MongoDB support. For example, Connect's date and timestamp logical types use Java's Calendar to compute the timestamp in millis, so it can't represent microprecision without truncation. They also can't represent timestamps with timezones. Connect's time logical type also is in terms of millis, so it can't represent times with more precision.

You do have some flexibility. For example, the MySQL connector has a "time.precision.mode" configuration that, if set to "connect", will use the predefined Kafka Connect logical types (potentially with loss of precision if the MySQL columns use microsecond precision). See http://debezium.io/docs/connectors/mysql/#data-types for details.

To unsubscribe from this group and stop receiving emails from it, send an email to debezium+unsubscribe@googlegroups.com.

To post to this group, send email to debe...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/c8213eac-8d95-407e-981e-dd87295db654%40googlegroups.com.

[saty...@telmate.com]

Hi Gunnar/Randall,

I have completed the SMT to convert timestamp types to String.

PF _https://github.com/Satyajitv/DebeziumTimestampConvertor.

Not really sure how to upload the code as pull request to the ticket.(DBZ-335).

Regards,

Satyajit.

[Gunnar Morling]

Hi Satyajit,

You'd first create a fork of the Debezium project (https://github.com/debezium/debezium), then commit your change to your fork and finally create a pull request against the upstream repo as described here: https://help.github.com/articles/creating-a-pull-request/

Hth,

--Gunnar

To unsubscribe from this group and stop receiving emails from it, send an email to debezium+unsubscribe@googlegroups.com.

To post to this group, send email to debe...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/47a2eb6f-7464-451e-800e-e231df523862%40googlegroups.com.

[Randall Hauch]

Also see https://github.com/debezium/debezium/blob/master/CONTRIBUTE.md for step-by-step instructions, which is referenced from 

http://debezium.io/docs/contribute/

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/CADGJaX-LkF3frsaxOb9EKPBGG4kcO9Ok%2BwBhFuwTY78CbNCWTQ%40mail.gmail.com.

[Peter Goransson]

Hi Randall,

What about an optional DateTimeFormatter per each temporal MySQL field? Ex. "yyyy-MM-dd"

If the formatter is specified, the connector would use it and write the String version, otherwise epochX as it does today.

Perhaps the simpler/easier solution that does not open the door to runaway cycle of formatting requests.

-Peter

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/cef193d6-b4a1-4396-9a95-44425a4f42ed%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "debezium" group.

To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
To post to this group, send email to debe...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/CADGJaX-KLuCYJdQOEBsTp15JRuYSMkK0X-wfO%3D%3DvHj4-EJ33Pg%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "debezium" group.

To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
To post to this group, send email to debe...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/CALYgK0GqF9LB_ZkdVv%3DtVNVCbfGorokO3erOM2AuqP9hmPdejw%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "debezium" group.

To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
To post to this group, send email to debe...@googlegroups.com.

[Gunnar Morling]

Hi Peter,

The current idea is to do these transformations after the fact using an SMT rather than having formatting options in the connector itself. You can track the progress in this PR:

    https://github.com/debezium/debezium/pull/306

I still think we may add connector options eventually if that makes more sense, but the SMT route seems reasonable for the time being and I Satyajit has been making some progress there already.

--Gunnar