confluent JDBC connector - DATATYPE timestamp issue

737 views
Skip to first unread message

sunil sharma

unread,
Jul 26, 2017, 9:05:22 AM7/26/17
to Confluent Platform
 I'm able to fetch data from sql server to kafka topic :
using JDBC connector -----

connection.url=jdbc:sqlserver://100.100.100.100:1521;database=report;user=sa;password=asd@123
mode=timestamp+incrementing
query=SELECT name,logtime,logid from test
incrementing.column.name=logid
timestamp.column.name=logtime
topic.prefix=test-sqlserver-jdbc-team
schema.registry=dbo

Data in sql server database is :
Name | logtime | logid
sunil | 2017-07-17 23:59:59:49.999 | 1
sam | 2017-07-16 23:59:59:49.999 | 2
julie | 2017-07-15 23:59:59:49.999 | 3
max | 2017-07-14 23:59:59:49.999 | 4

Everything is coming as its their in database except date data coming in kafka topic when I used avro consumer to view the data:
{ [name ="sunil" type ="string"], logtime ="50134567876",logid ="1"}

NOTE :

Date format is getting changed, how do it keep it in the same format as it is SQL server table



no of command run :

Zookeeper server :
bin/zookeeper-server-start etc/kafka/zookeeper.properties

Kafka server :
bin/kafka-server-start etc/kafka/server.properties

ClassPath :
export CLASSPATH=/opt/confluent/*

Schema-registry :
bin/schema-registry-start etc/schema-registry/schema-registry.properties

ClassPath :

export CLASSPATH=/opt/confluent/etc/kafka-connect-jdbc/sqljdbc4.jar

Standalone Kafka Connect Worker

bin/connect-standalone etc/schema-registry/connect-avro-standalone.properties etc/kafka-connect-jdbc/sqlservertime.properties

sunil sharma

unread,
Jul 27, 2017, 1:23:06 AM7/27/17
to Confluent Platform
Someone please help me out

Ewen Cheslack-Postava

unread,
Jul 27, 2017, 2:49:29 AM7/27/17
to Confluent Platform
I assume you're referring to the logtime field. Dates/timestamps are converted to their logical types *if* the connector can determine the type. If the connector can't, or you're just seeing the underlying type downstream, you'll see the "raw" type. "Logical" types are types that are represented by a simple underlying type but have more context that give them more meaning, for example unix timestamps can be represented as 32-bit (or for future compatibility 64-bit) integers. With the additional info that they represent a specific type, we might be able to compute a different representation, but if not we might just "render" them as the raw type. That's probably why you are seeing logtime as an int64 rather than formatted as the timestamp you might expect.

If you're not seeing the format you expect, it'd be helpful to see the end-to-end configs so we can sort out where information about the type/format is being lost and fix it so you'll see the expected output.

-Ewen

--
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-platform@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/confluent-platform/ab2e0f1c-5aeb-406a-a09e-7cda6ef9daf4%40googlegroups.com.

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

sunil sharma

unread,
Jul 27, 2017, 4:10:32 AM7/27/17
to Confluent Platform


Zookeeper server :
bin/zookeeper-server-start etc/kafka/zookeeper.properties

Kafka server :
bin/kafka-server-start etc/kafka/server.properties

ClassPath :
export CLASSPATH=/opt/confluent/*

Schema-registry :
bin/schema-registry-start etc/schema-registry/schema-

registry.properties

ClassPath :

export CLASSPATH=/opt/confluent/etc/kafka-connect-jdbc/sqljdbc4.jar

Standalone Kafka Connect Worker

bin/connect-standalone etc/schema-registry/connect-avro-standalone.properties etc/kafka-connect-jdbc/sqlservertime.properties



using JDBC connector -----


//100.100.100.100:1521;
database=report;user=sa;password=asd@123
mode=timestamp+incrementing
query=SELECT name,logtime,logid from test
incrementing.column.name=logid
timestamp.column.name=logtime
topic.prefix=test-sqlserver-jdbc-team
schema.registry=dbo


connection.url=jdbc:sqlserver:




But this result is coming in kafka topic with unix date format :







HOW Do I fix this issue ?? Becasue this date will be used by Elasticsearch but it is not recognising it...!!













__________________________________________________________________________________________________________________________________
To post to this group, send email to confluent...@googlegroups.com.

Robin Moffatt

unread,
Jul 27, 2017, 10:05:38 AM7/27/17
to confluent...@googlegroups.com
One option would be to define a mapping template in Elasticsearch so that the column named is interpreted as a date (IIRC epoch is a default so you don't have to specify explicitly).

This gist gives an example of a mapping template -- you'd need to update it for your index and field name wildcards etc

 

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

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

sunil sharma

unread,
Jul 28, 2017, 1:07:59 AM7/28/17
to Confluent Platform
Hi Robin,

I have updated the elasticsearch index mapping to epoch and now its been correctly handled by elasticsearch and data is coming as expected in kibana.
Thank you for your help. :) :)

 I have one more doubt, help me out in clearing it:

Currently, I'm running all this confluent steps ( starting zookeeper,kafka,schema registry,JDBC connector, elasticsearch connector) in ubuntu linux terminal separately.
Its more like, its running in standalone mode.
My Question here is , how to make this whole thing in one entity or one project....so once i execute/run that project, all the confluent steps run all together and same hierarchal order.

Kindly suggest,......!!!!





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

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

Robin Moffatt

unread,
Jul 28, 2017, 3:13:45 AM7/28/17
to confluent...@googlegroups.com
Do you mean running this in production? Or just an easier way to run it all in a sandbox environment? 

For the latter, stay tuned for Confluent Platform 3.3, there's a nice treat :) 


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

--
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+unsubscribe@googlegroups.com.

To post to this group, send email to confluent...@googlegroups.com.

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

sunil sharma

unread,
Jul 28, 2017, 6:28:38 AM7/28/17
to Confluent Platform
Thank you Robin...
you have been a great help :)

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

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

--
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.
Reply all
Reply to author
Forward
0 new messages