Debezium SQL Connector - handle with timestamp in Payload output

[Tiago Quaresma]

Hi all!

need help on translating the value for column EventDate that are timestamp on the source but in payload output, they are in int64 format... 

This is the connector configuration that I use:

{

    "name": "SQL_CDC_ODS_2",

    "config": {

    "connector.class": "io.debezium.connector.sqlserver.SqlServerConnector",

    "database.hostname": "GIRASOL",

    "database.port": "1433",

    "database.user": "debeziumuser",

    "database.password": "*********",

    "database.dbname": "ODS",

    "database.server.name": "odseventhub",

    "snapshot.isolation.mode": "snapshot",

    "snapshot.mode":"schema_only",

    "table.include.list": "dbo.EventHubTesting",

    "database.history":"io.debezium.relational.history.MemoryDatabaseHistory",

    "time.precision.mode":"connect"

    }

}

payload exemple:

"payload": {

      "before": null,

      "after": {

        "id": 699828,

        "EventType": 600682,

        "EventName": "EVENTO GERADO POR ROTINA - 600682",

        "TicketNumber": "ABC-600682",

        "EventDate": 1649341314540

      },

      "source": {

        "version": "1.8.0.Final",

        "connector": "sqlserver",

        "name": "odseventhub",

        "ts_ms": 1649341314543,

        "snapshot": "false",

        "db": "ODS",

        "sequence": null,

        "schema": "dbo",

        "table": "EventHubTesting",

        "change_lsn": "0000007d:00011f08:0002",

        "commit_lsn": "0000007d:00011f08:0003",

        "event_serial_no": 1

      },

      "op": "c",

      "ts_ms": 1649341315778,

      "transaction": null

    },

    "EventProcessedUtcTime": "2022-04-08T09:38:05.1108225Z",

    "PartitionId": 0,

    "EventEnqueuedUtcTime": "2022-04-07T14:21:56.4940000Z"

  }

The connector configuration "time.precision.mode":"connect" dont resolve this?!

Thanks!

TQuaresma

[jiri.p...@gmail.com]

Hi,

please see https://debezium.io/documentation/reference/1.9/connectors/sqlserver.html#sql-server-temporal-values

Connect's mapping is to milliseconds too

J.

[Tiago Quaresma]

Hi,

I had already seen these settings. However, just setting the time.precision.mode to "Connect" or "adaptive" doesn't solve it... I still have the EventDate column in INT64 format.

"type":"struct","fields":[{"type":"struct","fields":[{"type":"int64","optional":0,"field":"id"},{"type":"int64","optional":0,"field":"EventType"},{"type":"string","optional":1,"field":"EventName"},{"type":"string","optional":1,"field":"TicketNumber"},{"type":"int64","optional":1,"name":"io.debezium.time.Timestamp","version":1,"field":"EventDate"}],"optional":1,"name":"odseventhub.dbo.EventHubTesting.Value","field":"before"},{"type":"struct","fields":[{"type":"int64","optional":0,"field":"id"},{"type":"int64","optional":0,"field":"EventType"},{"type":"string","optional":1,"field":"EventName"},{"type":"string","optional":1,"field":"TicketNumber"},{"type":"int64","optional":1,"name":"io.debezium.time.Timestamp","version":1,"field":"EventDate"}],"optional":1,"name":"odseventhub.dbo.EventHubTesting.Value","field":"after"},{"type":"struct","fields":[{"type":"string","optional":0,"field":"version"},{"type":"string","optional":0,"field":"connector"},{"type":"string","optional":0,"field":"name"},{"type":"int64","optional":0,"field":"ts_ms"},{"type":"string","optional":1,"name":"io.debezium.data.Enum","version":1,"parameters":{"allowed":"true,last,false,incremental"},"default":"false","field":"snapshot"},{"type":"string","optional":0,"field":"db"},{"type":"string","optional":1,"field":"sequence"},{"type":"string","optional":0,"field":"schema"},{"type":"string","optional":0,"field":"table"},{"type":"string","optional":1,"field":"change_lsn"},{"type":"string","optional":1,"field":"commit_lsn"},{"type":"int64","optional":1,"field":"event_serial_no"}],"optional":0,"name":"io.debezium.connector.sqlserver.Source","field":"source"},{"type":"string","optional":0,"field":"op"},{"type":"int64","optional":1,"field":"ts_ms"},{"type":"struct","fields":[{"type":"string","optional":0,"field":"id"},{"type":"int64","optional":0,"field":"total_order"},{"type":"int64","optional":0,"field":"data_collection_order"}],"optional":1,"field":"transaction"}],"optional":0,"name":"odseventhub.dbo.EventHubTesting.Envelope"}

{"before":null,"after":{"id":700568,"EventType":372,"EventName":"EVENTO GERADO POR ROTINA - 372","TicketNumber":"ABC-372","EventDate":1650455774063},"source":{"version":"1.8

Thanks

TQuaresma

[Chris Cranford]

Hi Tiago,

What Jiri was pointing out was that in either situation, the EventDate's value will be INT64 representing the number of milliseconds since epoch.  Depending on your pipeline, you would simply need to interpret the INT64 as such. 

If you wanted to convert this value to a LocalDateTime in Java, it would be something like:

    LocalDateTime ldt = Instant.ofEpochMillis( value ).atZone( ZoneId.systemDefault() ).toLocalDateTime();

If you wanted to convert this value to a DATETIME column type for a sink JDBC database, you would use database temporal functions to do this.  In SQL Server, this would be something like DATEDIFF or DATEADD paired with the epoch of '1970-01-01T00:00:00'.

Is there something specific you want to do with this value that you are unsure what steps to take to convert it?

HTH,
Chris

--
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 view this discussion on the web visit https://groups.google.com/d/msgid/debezium/6d6836b3-bd94-45c3-80f9-85a517403ed0n%40googlegroups.com.