kakfa jdbc sqlserver connect timestamp mode

[sujatha mysore]

hi,

I am trying to use kakfka connect sqlserver.

this is my configuration:

I want to use mode=timestamp

with query

case 1-:  for this combination:

=======================

table.whitelist: ngv_task

query=select * from (SELECT taskid, uowid, taskstatus, endtime FROM ngv_task where taskstatus in ('SKIP','SHORT','CUT')) o

mode=timestamp

timestamp.column.name=endtime

validate.non.null=false

tasks.max=1

topic=sqlconnect-test

table.types: TABLE

poll.interval.ms: 10000

schema.ignore: true

   query = select * from (SELECT taskid, uowid, taskstatus, endtime FROM ngv_task where taskstatus in ('SKIP','SHORT','CUT')) o

        query.suffix =

        quote.sql.identifiers = ALWAYS

        schema.pattern = null

        table.blacklist = []

        table.poll.interval.ms = 60000

        table.types = [TABLE]

        table.whitelist = [ngv_task]

        timestamp.column.name = [endtime]

        timestamp.delay.interval.ms = 0

        timestamp.initial = null

        topic.prefix =

        validate.non.null = false

 (io.confluent.connect.jdbc.source.JdbcSourceConnectorConfig:354)

[2021-04-12 16:31:12,565] INFO Attempting to open connection #1 to SqlServer (io.confluent.connect.jdbc.util.CachedConnectionProvider:82)

[2021-04-12 16:31:12,986] ERROR WorkerConnector{id=source-jdbc-connector} Error while starting connector (org.apache.kafka.connect.runtime.WorkerConnector:193)

org.apache.kafka.connect.errors.ConnectException: query may not be combined with whole-table copying settings.

        at io.confluent.connect.jdbc.JdbcSourceConnector.start(JdbcSourceConnector.java:108)

        at org.apache.kafka.connect.runtime.WorkerConnector.doStart(WorkerConnector.java:185)

        at org.apache.kafka.connect.runtime.WorkerConnector.start(WorkerConnector.java:210)

        at org.apache.kafka.connect.runtime.WorkerConnector.doTransitionTo(WorkerConnector.java:349)

        at org.apache.kafka.connect.runtime.WorkerConnector.doTransitionTo(WorkerConnector.java:332)

        at org.apache.kafka.connect.runtime.WorkerConnector.doRun(WorkerConnector.java:140)

        at org.apache.kafka.connect.runtime.WorkerConnector.run(WorkerConnector.java:117)

        at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)

        at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)

        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)

        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)

        at java.base/java.lang.Thread.run(Thread.java:834)

[2021-04-12 16:31:12,991] ERROR Failed to create job for /mnt/c/kafkaDocker/code/source/demo-4/JdbcWorker.properties (org.apache.kafka.connect.cli.ConnectStandalone:110)

[2021-04-12 16:31:12,992] ERROR Stopping after connector error (org.apache.kafka.connect.cli.ConnectStandalone:121)

java.util.concurrent.ExecutionException: org.apache.kafka.connect.errors.ConnectException: Failed to transition connector source-jdbc-connector to state STARTED

case2: tried without giving specific query

and I get this error

   table.whitelist = [ngv_task]

        tables = ["vlp"."dbo"."ngv_task"]

        timestamp.column.name = [endtime]

        timestamp.delay.interval.ms = 0

        timestamp.initial = null

        topic.prefix =

        validate.non.null = false

 (io.confluent.connect.jdbc.source.JdbcSourceTaskConfig:354)

[2021-04-12 16:33:48,756] INFO Using JDBC dialect SqlServer (io.confluent.connect.jdbc.source.JdbcSourceTask:102)

[2021-04-12 16:33:48,980] INFO Started JDBC source task (io.confluent.connect.jdbc.source.JdbcSourceTask:261)

[2021-04-12 16:33:48,981] INFO WorkerSourceTask{id=source-jdbc-connector-0} Source task finished initialization and start (org.apache.kafka.connect.runtime.WorkerSourceTask:233)

[2021-04-12 16:33:48,983] INFO Attempting to open connection #1 to SqlServer (io.confluent.connect.jdbc.util.CachedConnectionProvider:82)

[2021-04-12 16:33:49,223] INFO Begin using SQL query: SELECT * FROM "vlp"."dbo"."ngv_task" WHERE "vlp"."dbo"."ngv_task"."endtime" > ? AND "vlp"."dbo"."ngv_task"."endtime" < ? ORDER BY "vlp"."dbo"."ngv_task"."endtime" ASC (io.confluent.connect.jdbc.source.TableQuerier:164)

[2021-04-12 16:33:49,394] INFO Closing resources for JDBC source task (io.confluent.connect.jdbc.source.JdbcSourceTask:324)

[2021-04-12 16:33:49,396] INFO Closing connection #1 to SqlServer (io.confluent.connect.jdbc.util.CachedConnectionProvider:108)

[2021-04-12 16:33:49,398] INFO WorkerSourceTask{id=source-jdbc-connector-0} Committing offsets (org.apache.kafka.connect.runtime.WorkerSourceTask:478)

[2021-04-12 16:33:49,399] INFO WorkerSourceTask{id=source-jdbc-connector-0} flushing 0 outstanding messages for offset commit (org.apache.kafka.connect.runtime.WorkerSourceTask:495)

[2021-04-12 16:33:49,405] ERROR WorkerSourceTask{id=source-jdbc-connector-0} Task threw an uncaught and unrecoverable exception (org.apache.kafka.connect.runtime.WorkerTask:187)

org.apache.kafka.connect.errors.DataException: endtime is not a valid field name

can you help me find what am I doing wrong with this configuration.

I would really need a query and timestamp, so can get i=changed data, based on time stamp.

appreciate your help.

thanks

sujatha

[sujatha mysore]

issue resolved