【kafka】JDBCconnector进行表数据增量同步过程中的源表与目标表时间不一致问题解决

〇、参考资料

1、Oracle源表数据

2、PG同步后的表数据

3、现象

时间不一致，差了8个小时

4、查看对应的connector信息

（1）source

{
"connector.class": "io.confluent.connect.jdbc.JdbcSourceConnector",
"mode": "timestamp",
"timestamp.column.name": "UPDDATTIM_0",
"topic.prefix": "connector_topic_",
"connection.password": "system",
"connection.user": "system",
"db.timezone": "Asia/Shanghai",
"name": "source_connector_Test_TimeFormat_Order",
"connection.url": "jdbc:oracle:thin:@//192.168.0.2:1521/helowin",
"table.whitelist": "TEST.Test_TimeFormat_Order"
}

（2）sink

{
"connector.class": "io.confluent.connect.jdbc.JdbcSinkConnector",
"table.name.format": "wxf_test.Test_TimeFormat_Order",
"connection.password": "qaz123",
"tasks.max": "1",
"topics": "connector_topic_Test_TimeFormat_Order",
"delete.enabled": "false",
"auto.evolve": "true",
"connection.user": "postgres",
"name": "sink_connector_Test_TimeFormat_Order",
"auto.create": "true",
"connection.url": "jdbc:postgresql://ip:5432/bigdata",
"insert.mode": "upsert",
"pk.mode": "record_value",
"pk.fields": "Order_ID"
}
二、解决方式

1、方案

即sink和source都加

 "db.timezone": "Asia/Shanghai",

并需要保持一直

 2、最终source

{
"connector.class": "io.confluent.connect.jdbc.JdbcSourceConnector",
"mode": "timestamp",
"timestamp.column.name": "UPDDATTIM_0",
"topic.prefix": "connector_topic_",
"connection.password": "system",
"connection.user": "system",
"db.timezone": "Asia/Shanghai",
"name": "source_connector_Test_TimeFormat_Order",
"connection.url": "jdbc:oracle:thin:@//192.168.0.2:1521/helowin",
"table.whitelist": "TEST.TEST_TIMEFORMAT_ORDER"
}

3、最终sink

{
"connector.class": "io.confluent.connect.jdbc.JdbcSinkConnector",
"table.name.format": "wxf_test.TEST_TIMEFORMAT_ORDER",
"connection.password": "qaz123",
"tasks.max": "1",
"topics": "connector_topic_TEST_TIMEFORMAT_ORDER",
"delete.enabled": "false",
"auto.evolve": "true",
"connection.user": "postgres",
"db.timezone": "Asia/Shanghai",
"name": "sink_connector_Test_TimeFormat_Order",
"auto.create": "true",
"connection.url": "jdbc:postgresql://192.168.0.2:5432/bigdata",
"insert.mode": "upsert",
"pk.mode": "record_value",
"pk.fields": "ORDER_ID"
}

本文来自博客园，作者：哥们要飞，转载请注明原文链接：https://www.cnblogs.com/liujinhui/p/16672162.html