Kafka-connect-jdbc: Support WHERE clauses in custom queries in incremental mode

[shikhar]

Currently TimestampIncrementingTableQuerier appends aWHERE clause which causes invalid SQL if there is already one in the SQL.

One approach could be to detect if there is already a WHERE, but that seems error prone.

I propose we allow for the query to contain placeholder text like "${incrementalClause}". If that is present that gets replaced with the timestmap/ID conditions. If it is absent, we can append a "WHERE .. conditions .. " as currently.

[ewencp]

Yeah, this seems like a reasonable solution if we go with string interpolation more generally across the framework/connectors. I had avoided handling this so far because I didn't really want to try to robustly handle the SQL syntax/parsing.

[teq0]

I'd be very keen for this to go ahead, it would open up a lot of possibilities that just aren't possible right now. Can I suggest that maybe a few variables could be exposed, not just the ${incrementalClause}, which would only work for straight queries. Exposing, say, ${currentIncrementValue} and ${currentTimestampValue} would allow them to be passed to stored procedures as well.

[yangfeiran]

https://github.com/confluentinc/kafka-connect-jdbc/pull/191, this is my pull request for the change I made to solve your problem.
You have to use lower case "where" if you use my change.

[teq0]

Could you please make it case-insensitive? It only gets called once for any task, the overhead shouldn't matter.

This doesn't solve the stored procedure problem though, that requires using the templated variables discussed above. I'll have a go at it myself when I get a minute.

[miketzian]

Another way would be to add a 'query.condition' config parameter, which would replace the normal where clause generated by TimestampIncrementingTableQuerier, (in this case for a stored proc):

query.condition=@from_ts = ?, @to_ts = ?

The developer could then write whatever where clause they need based on their use of incrementing and/or timestamp columns.

[kgeis]

There are (limited) workarounds for this, depending on the level of SQL support in the underlying database. Examples:

SELECT * FROM (SELECT... WHERE...)

WITH a AS
   SELECT * FROM b
    WHERE ...
SELECT * FROM a

[yangfeiran]

you are absolutely right, but the right syntax is " SELECT * FROM ( SELECT * FROM table WHERE ...) as a"

[kgeis]

@yangfeiran, either I'm absolutely right or I'm not. No buts! :)

The alias is necessary on some databases (like PostgreSQL) but not on others (like Oracle).

[yangfeiran]

@kgeis cool

[wicknicks]

Workaround is to escape the nested query as described above by @kgeis. Another alternative is to create logical views on the database so that as far as the connector is concerned it is hitting a table.

Closing this issue for now.

[sohaibfarooqi]

This workaround doesn't work for PostgreSQL 9.6.xx. Connect script works fine but no topic created.