1

I tried to set up a database with two tables in sqlite. Once of my table is having a timestamp column . I am trying to implement timestamp mode to capture incremental changes in the DB. Kafka connect is failing with the below error:

 ERROR Failed to get current time from DB using Sqlite and query 'SELECT 
CURRENT_TIMESTAMP' 
(io.confluent.connect.jdbc.dialect.SqliteDatabaseDialect:471)
java.sql.SQLException: Error parsing time stamp

Caused by: java.text.ParseException: Unparseable date: "2019-02-05 02:05:29" 
does not match (\p{Nd}++)\Q-\E(\p{Nd}++)\Q-\E(\p{Nd}++)\Q 
\E(\p{Nd}++)\Q:\E(\p{Nd}++)\Q:\E(\p{Nd}++)\Q.\E(\p{Nd}++)

Many thanks for the help

Config: 

name=test-query-sqlite-jdbc-autoincrement 
connector.class=io.confluent.connect.jdbc.JdbcSourceConnector 
tasks.max=1 
connection.url=jdbc:sqlite:employee.db 
query=SELECT users.id, users.name, transactions.timestamp, transactions.payment_type FROM users JOIN transactions ON (users.id = transactions.user_id) 
mode=timestamp 
timestamp.column.name=timestamp 
topic.prefix=test-joined

DDL: 

CREATE TABLE transactions(id integer primary key not null,
                          payment_type text not null,
                          timestamp DATETIME DEFAULT(STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW')),
                          user_id int not null, 
                          constraint fk foreign key(user_id) references users(id)
); 

CREATE TABLE users (id integer primary key not null,name text not null);
D Developer
  • 143
  • 1
  • 10
  • Can you share your table DDL and Connector config? – Robin Moffatt Feb 04 '19 at 16:49
  • The timestamp needs to include milllisecond precision, according to the error – OneCricketeer Feb 05 '19 at 00:13
  • @cricket_007 The error is getting generated from the internal query in the internal class of io.confluent.connect.jdbc.dialect.SqliteDatabaseDialect during bootstrapping. For custom query , I have included milliseconds precision. – D Developer Feb 05 '19 at 01:59
  • @RobinMoffatt DDL - CREATE TABLE transactions(id integer primary key not null,payment_type text not null,timestamp DATETIME DEFAULT(STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW')),user_id int not null, constraint fk foreign key(user_id) references users(id)); – D Developer Feb 05 '19 at 05:57
  • @DDeveloper please update your question with *both* DDL statements, and your Connector config. Thanks. – Robin Moffatt Feb 05 '19 at 09:46
  • @RobinMoffatt sharing the props file
    name=test-query-sqlite-jdbc-autoincrement connector.class=io.confluent.connect.jdbc.JdbcSourceConnector tasks.max=1 connection.url=jdbc:sqlite:employee.db query=SELECT users.id, users.name, transactions.timestamp, transactions.payment_type FROM users JOIN transactions ON (users.id = transactions.user_id) mode=timestamp timestamp.column.name=timestamp topic.prefix=test-joined     – D Developer Feb 05 '19 at 10:33
  • Help us to help you… you said there are TWO tables, I've asked twice… please can you provide the DDL for BOTH tables. Also edit your question instead of pasting the DDL as a comment, it makes it easier to view. – Robin Moffatt Feb 05 '19 at 10:56
  • @RobinMoffatt Update the same. Kindly suggest if you need any more information – D Developer Feb 05 '19 at 11:36
  • You have hours, minutes, seconds in `02:05:29`. Not milliseconds... – OneCricketeer Feb 05 '19 at 17:44

2 Answers2

1

The kafka connect jdbc connector easily detects the changes in the timestamp, if the values of the 'timestamp' column are in the format of the 'UNIX timestamp'.

sqlite> CREATE TABLE transact(timestamp TIMESTAMP DEFAULT (STRFTIME('%s', 'now')) not null,
   ...> id integer primary key not null,
   ...> payment_type text not null);
sqlite>

The values can be inserted as:

sqlite> INSERT INTO transact(timestamp,payment_type,id) VALUES (STRFTIME('%s', 'now'),'cash',1);

The timestamp related changes are then detected by the kafka jdbc source connector and the same can be consumed as follows:

kafka-console-consumer  --bootstrap-server localhost:9092 --topic jdbc-transact --from-beginning
{"timestamp":1562321516,"id":2,"payment_type":"card"}
{"timestamp":1562321790,"id":1,"payment_type":"online"}
Saba Naureen
  • 11
  • 1
0

I've reproduced this, and it is already logged as an issue for the JDBC Source connector. You can monitor it here: https://github.com/confluentinc/kafka-connect-jdbc/issues/219

Robin Moffatt
  • 30,382
  • 3
  • 65
  • 92