0

I try to create a source Kafka connect with timestamp mode in Windows. I have this table: 

CREATE TABLE "usu"."mytable" (  
"first_name" CHAR(8 BYTE) DEFAULT ' ',  
"last_name" CHAR(8 BYTE) DEFAULT ' ',  
"regist" TIMESTAMP (0) DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP)  
) TABLESPACE "temp" ;

oracle database properties:

name=jdbc-conector  
connector.class=io.confluent.connect.jdbc.JdbcSourceConnector  
connection.url=jdbc:oracle:thin:@localhost:1521:xe  
connection.user=usu  
connection.password=pwd  
query=select NDZ, to_timestamp(STRING_TIMESTAMP,'YYYY - MM - DD HH: MI: SS, FF6') AS TIMESTAMP_COLUMN FROM myTable
mode=timestamp+incrementing 
timestamp.column.name=TIMESTAMP_COLUMN
incrementing.column.name=NDZ
db.timezone=UTC
dialect.name=OracleDatabaseDialect
numeric.mapping=best_fit
table.types=TABLE  
poll.interval.ms=1000  
topic.prefix=input-mytable

INSERT INTO "usu"."mytable" (first_name,last_name) values("jake","tyler");
select output:
jake    tyler   2019-08-23 11:54:47:046

The console-consumer does not return any output: 

kafka-console-consumer --bootstrap-server localhost:9092 --topic input-mytable

Connector only working in bulk mode. I do not understand why.

checking connector: 

curl localhost:8083/connectors/jdbc-conector/status/  
{"name":"jdbc-conector","connector": 
{"state":"RUNNING","worker_id":"1.2.3.4:8083"},"tasks": 
[{"id":0,"state":"RUNNING","worker_id":"1.2.3.4:8083"}],"type":"source"}

Ed: In debug mode, my output is like this.

source kafka connect:

D:\kafka\bin\windows\connect-standalone.bat D:\kafka\config\connect-standalone.properties D:\kafka\config\connect-bbdd.properties

output:

DEBUG: prepared SQL query: 'select NDZ, to_timestamp(STRING_TIMESTAMP,'YYYY - MM - DD HH: MI: SS, FF6') AS TIMESTAMP_COLUMN FROM myTable) WHERE "TIMESTAMP_COLUMN" < ? AND(("TIMESTAMP_COLUMN" = ? AND "NDZ" > ? ) OR "TIMESTAMP_COLUMN" > ? ) ORDER BY "TIMESTAMP_COLUMN", "NDZ" ASC.  

[2019-08-23 14:01:36,150] DEBUG Executing prepared statement with start time value = 2019-08-23 11:40:47:046 end time = 2019-08-23 11:40:47:046 and incrementing value = 19 (io.confluent.connect.jdbc.source.TimestampIncrementingCriteria)

How can i controlled time difference?

Lanre
  • 91
  • 6
  • Are there are any errors in Kafka Connect logs? – Giorgos Myrianthous Aug 09 '19 at 14:58
  • Have a look a this: https://www.confluent.io/blog/kafka-connect-deep-dive-jdbc-source-connector#why-no-data – Robin Moffatt Aug 09 '19 at 15:53
  • In debug mode, I saw that the problem is the time is incorrect. I changed my field and used a timestamp without a timezone, but it is still different with kafka for more than half an hour. How can I control that difference? – Lanre Aug 23 '19 at 12:12

1 Answers1

0

For me the issue was related to the timezone of my kafka connector.My Kafka connector was GMT time , and the DB was GMT+3.So the all inserts\updates were ahead of time, hence the connector was not able to see them.

BlackBird87
  • 43
  • 1
  • 8