Kafka Connect Query works with Join but not with Where clause for Mysql

[omkar....@gslab.com]

Hi, 

I am new to this platform. I am trying to use a simple query with Where clause in it. Query dosent work with where clause but it does work if i use join.

Following is my .prop file config:

name=mysql-whitelist-timestamp-source

connector.class=io.confluent.connect.jdbc.JdbcSourceConnector

tasks.max=1

connection.url=jdbc:mysql://localhost:3306/sys?user=root&password=rootadmin&ssl=true

query=select td.trade_id, ti.price, td.last_modified from trade_data td join trade_info ti on (td.trade_id=ti.trade_id)

mode=timestamp

timestamp.column.name=last_modified

topic.prefix=test-mysql-jdbc

----------------------------------------------

Following are my table definations in create table trade_data(

   trade_id INT NOT NULL ,

   incoming_sdr_data_id INT NOT NULL,

   action VARCHAR(40) ,

   event varchar(40),

   reporting_entity_lei varchar(40),

   submission_date timestamp,

   last_modified timestamp,

   PRIMARY KEY ( trade_id ));

   

  create table valuation_data(

   trade_id INT NOT NULL ,

   valuation_id INT NOT NULL,

   valuation_method VARCHAR(40) ,

   valuation_date timestamp,

   FOREIGN KEY trade_id(trade_id)

   REFERENCES trade_data(trade_id)

   ); 

   

   create table trade_info(

   trade_id INT NOT NULL ,

   price INT NOT NULL,

   mtm_value INT,

   mtm_ccy varchar(40),

   load_datetime timestamp,

   load_source varchar(40),

   FOREIGN KEY trade_id(trade_id)

   REFERENCES trade_data(trade_id)

   ); 

-------------------------------------------------------

Following is the query which is not working:

select td.trade_id, ti.price from trade_data td, trade_info ti where td.trade_id=ti.trade_id;

-------------------------------------------------------------

I am reffering to: http://docs.confluent.io/3.0.0/connect/connect-jdbc/docs/jdbc_connector.html

Can you please suggest how can i get query with Where clause in it working?

Thanks,

Omkar Sabane

[omkar....@gslab.com]

Hi,

I forgot to add error message in the above post.

Following is the error message i am getting when i use Where clause in the query.:

ERROR Failed to run query for table TimestampIncrementingTableQuerier{name='null', query='select td.trade_id, ti.price,td.last_modified from trade_data td, trade_info ti where td.trade_id=ti.trade_id', topicPrefix='test-mysql-jdbc', timestampColumn='last_modified', incrementingColumn='null'}: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE `last_modified` > '2016-08-18 13:26:06' AND `last_modified` < '2016-08-19 ' at line 1 (io.confluent.connect.jdbc.JdbcSourceTask:239)

Thanks,

Omkar Sabane

[Shikhar Bhushan]

Hi Omkar,

As documented in the configuration options, I'm afraid we don't currently support a custom query having a WHERE clause for any of the incremental polling modes (i.e. everything other than 'bulk').

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/37cc4436-4470-4bd4-9d69-53f83394682f%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Omkar Sabane]

Hi Shikhar,

Thanks for sharing valuable information. We now need to change our approach. I am right now working on just a sample project, but in actual practice our queries have WHERE clauses and INNER QUERIES as well.

Can you suggest any other alternative for this, so that we can use WHERE clause any how?

Can we use calling STORED PROCEDURES for the same? Is it supported?

Thanks,

Omkar Sabane

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

To view this discussion on the web visit https://groups.google.com/d/msgid/confluent-platform/37cc4436-4470-4bd4-9d69-53f83394682f%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "Confluent Platform" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/confluent-platform/G1gP-uoJlew/unsubscribe.
To unsubscribe from this group and all its topics, send an email to confluent-platform+unsub...@googlegroups.com.
To post to this group, send email to confluent-platform@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/confluent-platform/CAPXNJrW8R%2BRwOfAVGUm8HAhwryq3G6yuzkbb_ygrkboT7j5WJQ%40mail.gmail.com.

[Shikhar Bhushan]

Hi Omkar,

The restriction should not be hard to fix (perhaps you'd like to contribute a patch?). I have opened https://github.com/confluentinc/kafka-connect-jdbc/issues/112 for it.

Regarding stored procedures, I'm not sure. What does the query look like in that case? Perhaps the description in that issue of why WHERE leads to invalid SQL, gives you some insight.

Best,

Shikhar

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/37cc4436-4470-4bd4-9d69-53f83394682f%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "Confluent Platform" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/confluent-platform/G1gP-uoJlew/unsubscribe.

To unsubscribe from this group and all its topics, 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/CAPXNJrW8R%2BRwOfAVGUm8HAhwryq3G6yuzkbb_ygrkboT7j5WJQ%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

--

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/CA%2BT0afmsT4hbvX0f0SUSQ90thca-8egddYtWnFMmhgEDh82SEg%40mail.gmail.com.

[omkar....@gslab.com]

Hi Shikhar,

I tried stored procedures approach as well. As per my understanding that also leads us to the same SQLSyntaxError same as above. I wrote query in a different manner and for now it solved my problem.

I modified my query like: 

query=select * from (select td.trade_id, ti.price,td.last_modified from trade_data td, trade_info ti where td.trade_id=ti.trade_id) as DataTable

So, even if because of timestamp or incremental mode if another where clause gets appended, query will work fine. But this will cost me if performance is considered.

I would like to contribute, and i will try for that.

I appreciate your help and thank you for your valuable guidance.

Thanks,

Omkar Sabane

To unsubscribe from this group and stop receiving emails from it, send an email to confluent-platform+unsub...@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/37cc4436-4470-4bd4-9d69-53f83394682f%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "Confluent Platform" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/confluent-platform/G1gP-uoJlew/unsubscribe.

To unsubscribe from this group and all its topics, send an email to confluent-platform+unsub...@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/CAPXNJrW8R%2BRwOfAVGUm8HAhwryq3G6yuzkbb_ygrkboT7j5WJQ%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

--
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.

[Gwen Shapira]

Regarding the performance issue of the work-around... if you are using
Oracle, they have a predicate-pushdown hint that can be used to get
Oracle to try to merge the subquery before optimizing. It may help.

Gwen

>>>>> 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/37cc4436-4470-4bd4-9d69-53f83394682f%40googlegroups.com.
>>>>> For more options, visit https://groups.google.com/d/optout.
>>>>
>>>> --
>>>> You received this message because you are subscribed to a topic in the
>>>> Google Groups "Confluent Platform" group.
>>>> To unsubscribe from this topic, visit
>>>> https://groups.google.com/d/topic/confluent-platform/G1gP-uoJlew/unsubscribe.
>>>> To unsubscribe from this group and all its topics, 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/CAPXNJrW8R%2BRwOfAVGUm8HAhwryq3G6yuzkbb_ygrkboT7j5WJQ%40mail.gmail.com.
>>>>
>>>>
>>>> For more options, visit https://groups.google.com/d/optout.
>>>
>>> --
>>> 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/CA%2BT0afmsT4hbvX0f0SUSQ90thca-8egddYtWnFMmhgEDh82SEg%40mail.gmail.com.
>>> For more options, visit https://groups.google.com/d/optout.
>

> --
> 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/cb353bae-24d0-4a4c-b287-d727fa3462ec%40googlegroups.com.

>
> For more options, visit https://groups.google.com/d/optout.



--

Gwen Shapira
Product Manager | Confluent
650.450.2760 | @gwenshap
Follow us: Twitter | blog

[omkar....@gslab.com]

Hi Gwen,

I will explore this area further. Thank you.

Thanks,

Omkar Sabane

>>>>> an email to confluent-platform+unsub...@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/37cc4436-4470-4bd4-9d69-53f83394682f%40googlegroups.com.
>>>>> For more options, visit https://groups.google.com/d/optout.
>>>>
>>>> --
>>>> You received this message because you are subscribed to a topic in the
>>>> Google Groups "Confluent Platform" group.
>>>> To unsubscribe from this topic, visit
>>>> https://groups.google.com/d/topic/confluent-platform/G1gP-uoJlew/unsubscribe.
>>>> To unsubscribe from this group and all its topics, send an email to

>>>> confluent-platform+unsub...@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/CAPXNJrW8R%2BRwOfAVGUm8HAhwryq3G6yuzkbb_ygrkboT7j5WJQ%40mail.gmail.com.
>>>>
>>>>
>>>> For more options, visit https://groups.google.com/d/optout.
>>>
>>> --
>>> 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.

>>> 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/CA%2BT0afmsT4hbvX0f0SUSQ90thca-8egddYtWnFMmhgEDh82SEg%40mail.gmail.com.
>>> For more options, visit https://groups.google.com/d/optout.
>
> --
> 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.