Kafka-connect-jdbc: How to only publish new rows for existing large table?

[sunshineo]

We have a huge table. We do not want to publish everything from the beginning of time to Kafka, we want to start the connector and start to publish only newly inserted rows.
How to do that?

[scottg88]

I would suggest sourcing from a view rather than a table. You can add a WHERE clause to the view to limit it to data after a certain point (be it an ID or a date).
Once the process has begun you no longer require the WHERE as the connector will keep track of where it is up to.
I find views to be useful in general for sources if you are using Avro schemas. If someone for example adds a row to the backend table it will not be brought in until you update the view (and have therefore followed steps to ensure you have updated your Avro schema etc. before updating the view). They are also useful to allow you to rename columns where appropriate.

[ewencp]

@scottg88's solution seems like the best workaround currently (and a nice little hack). It would be nice if we had a way to initialize the "offset" we use to track where we are in the table. The point about schemas is also great (and a good way to customize your query in other ways without the limitations of using custom query mode).

[sunshineo]

How to source from a view? Did I miss a big chunk of the documentation?

[sunshineo]

I'm guessing SQL view? http://www.w3schools.com/sql/sql_view.asp

[sunshineo]

@ewencp Can we add initial offset to backlog?

[sunshineo]

@scottg88 What do you mean by 'not be brought in until you update the view'? I have to have something constantly updating the view?

[ewencp]

@sunshineo Sure, I'll reopen and this ticket can serve that purpose.

@scottg88 Was saying that if you create a view that only uses a specific set of columns from the original table, then if someone alters the original table by adding a column the view will be unaffected. He's saying that this allows you to decouple when a schema change on a table occurs from when you start including the new fields in the data delivered to Kafka.

[scottg88]

@ewencp - that's exactly what I meant. When different people manage your connect datasource and your source database, this is very important (as is my case) - the connector will bomb out if the source changes in an incompatible way with the Avro schema for the topic.

Regarding initial offset, I looked at adding this specifically for timestamp mode in the changes I've made which are referenced in #181.
Some pitfalls I see with setting an initial offset is that it can be restrictive when sourcing multiple tables in same source connector, if you need the initial offset to be different for each table.
This can be worked around by adding them one-by-one in the whitelist and updating the initial offset config each time. If this is implemented it is worth documenting this workaround.

If I have some time on the weekend I'll look at these changes as they should be easy changes considering I've essentially already done it.

[sunshineo]

Thanks for the help! I wonder why some of the settings for the connector affect whole database instead of single table. Such as the incrementing.column.name and offset as @scottg88 mentioned above. I'm ending up creating multiple connectors and each with a white list of one table

[scottg88]

I think it's down to how difficult it would be to architect the configuration options - in a perfect world where table names are only letters and number, nothing special, you could do e.g. incrementing.column.name.tablename - but in reality it's not as easy as that.

[250690392]

I use where clause to this query mode by kafka-connect-jdbc, but I facing a mistake :the connector task log hint "java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended"

[wicknicks]

Closing this ticket as it is a year old now, and regarding properties that affect the whole database instead, we have issue #36 that discusses the same problem.