Kafka Connect JDBC connector table.whitelist questions

[Jim Malone]

Hi, we are currently using the JDBC connector's query functionality to pull data from sql server into Kafka. This requires 1 connector per table/query, which is getting unmanageable. So we are looking to instead move to a combination of sql Views and the table whitelist functionality.  

I've been looking around at the docs and forums but unfortunately have not found what I was looking for. It looks like what we want isn't available yet unless we build it ourselves.  Just in case can someone please answer these questions for me?:

Can the JDBC connector table whitelist functionality...

1. pick up sql views, or can it only handle tables?
   
2. look only at a specific schema?
   
3. use wildcards so that we don't have to update the connector every time a new view gets added?
   

Basically we'd like to setup a connector that uses:

"table.whitelist": ["schema.view_prefix_*"]

Alternately, just a "schema.whitelist" would be usable as long as it picked up all tables AND views in the given schema(s).

If this functionality is not available yet, are there any plans to add it in the near future?  Is any of it available in version 3 that's not available in version 2 (we will be doing the upgrade soon).

Thank you for your time,

Jim

[Shikhar Bhushan]

Hi Jim,

1. Currently we are filtering table metadata retrieval for tables. We could potentially allow for views, probably behind an option.

2. There is a PR open for that https://github.com/confluentinc/kafka-connect-jdbc/pull/109 

3. The whitelist is matching on exact table names.

So it seems like 1 & 2 would met your use-case as you said.

The problem you initially mentioned though was needing a connector per query. Would this solve your problem: https://github.com/confluentinc/kafka-connect-jdbc/issues/113 

Best,

Shikhar

--
You received this message because you are subscribed to the Google Groups "Confluent Platform" group.
To unsubscribe from this group and stop receiving emails from it, send an email to confluent-platf...@googlegroups.com.
To post to this group, send email to confluent...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/confluent-platform/887ad0bb-949f-4bff-bd3e-2db770f60154%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Jim Malone]

Yeah for 1 I think we are going to try adding "VIEW" to that DEFAULT_TABLE_TYPES set to see if that works.  I've seen that suggested elsewhere as well.  And 2 will be great once that's merged in.

For 3 it looks like we'll have to update the TableMonitorThread's whitelist functionality to handle the use of a wildcard.  I don't anticipate that being too tricky, but we'll see.

And for your last comment, the issue you linked to could potentially solve our problem with needing one connector per query, but it looks like your use case is different than ours.  We only need 1 query per table, but it can't just read in a full table since it needs to utilize DB functions and re-cast some columns.  Also each query needs to go to a separate topic, whereas you seem to request they share a topic.  I will add a comment to the issue anyways to help campaign for supporting multiple custom queries.

Thanks for the response,

Jim

On Tuesday, August 23, 2016 at 2:06:44 PM UTC-4, Shikhar Bhushan wrote:

Hi Jim,

1. Currently we are filtering table metadata retrieval for tables. We could potentially allow for views, probably behind an option.

2. There is a PR open for that https://github.com/confluentinc/kafka-connect-jdbc/pull/109 

3. The whitelist is matching on exact table names.

So it seems like 1 & 2 would met your use-case as you said.

The problem you initially mentioned though was needing a connector per query. Would this solve your problem: https://github.com/confluentinc/kafka-connect-jdbc/issues/113 

Best,

Shikhar

On Tue, Aug 23, 2016 at 7:03 AM Jim Malone <malo...@gmail.com> wrote:

Hi, we are currently using the JDBC connector's query functionality to pull data from sql server into Kafka. This requires 1 connector per table/query, which is getting unmanageable. So we are looking to instead move to a combination of sql Views and the table whitelist functionality.  

I've been looking around at the docs and forums but unfortunately have not found what I was looking for. It looks like what we want isn't available yet unless we build it ourselves.  Just in case can someone please answer these questions for me?:

Can the JDBC connector table whitelist functionality...

1. pick up sql views, or can it only handle tables?
   
2. look only at a specific schema?
   
3. use wildcards so that we don't have to update the connector every time a new view gets added?
   

Basically we'd like to setup a connector that uses:

"table.whitelist": ["schema.view_prefix_*"]

Alternately, just a "schema.whitelist" would be usable as long as it picked up all tables AND views in the given schema(s).

If this functionality is not available yet, are there any plans to add it in the near future?  Is any of it available in version 3 that's not available in version 2 (we will be doing the upgrade soon).

Thank you for your time,

Jim

--
You received this message because you are subscribed to the Google Groups "Confluent Platform" group.

To unsubscribe from this group and stop receiving emails from it, send an email to confluent-platform+unsub...@googlegroups.com.