Kafka Connect JDBC sink - store aggregated data from KSQL

Question

I'm trying to store a result of ksql query (windowed table) to database using kafka connect.

What I want to do now is (using kafka jdbc connect) store them in database but the rows being updated (as kafka table currently updates the values) instead of just stream of messages;

So although the messages will be like this:

1558042958867 | User_9 : Window{start=1558042920000 end=-} | User_9 | 20
1558042961348 | User_9 : Window{start=1558042920000 end=-} | User_9 | 21
1558042962141 | User_9 : Window{start=1558042920000 end=-} | User_9 | 22
1558042965552 | User_9 : Window{start=1558042920000 end=-} | User_9 | 23
1558042968275 | User_9 : Window{start=1558042920000 end=-} | User_9 | 24
1558042969668 | User_9 : Window{start=1558042920000 end=-} | User_9 | 25
1558042973915 | User_9 : Window{start=1558042920000 end=-} | User_9 | 26
1558042976235 | User_9 : Window{start=1558042920000 end=-} | User_9 | 27
1558042980197 | User_9 : Window{start=1558042980000 end=-} | User_9 | 1
1558042980635 | User_9 : Window{start=1558042980000 end=-} | User_9 | 2
1558042982969 | User_9 : Window{start=1558042980000 end=-} | User_9 | 3
1558042983511 | User_9 : Window{start=1558042980000 end=-} | User_9 | 4
1558042986352 | User_9 : Window{start=1558042980000 end=-} | User_9 | 5
1558042986863 | User_9 : Window{start=1558042980000 end=-} | User_9 | 6
1558042988328 | User_9 : Window{start=1558042980000 end=-} | User_9 | 7
1558042988863 | User_9 : Window{start=1558042980000 end=-} | User_9 | 8

In database I would only like to have:

User_9 : Window{start=1558042920000 end=-} | User_9 | 27
User_9 : Window{start=1558042980000 end=-} | User_9 | 8

Something like this. Is there some magic around ksql/kafka-connect that would allow me to do that?

For clarification - last field is this aggregate which counts how many time x happened so far during window time.

I'd assume I could get window start + key as database key and do updates on those, but I'm not sure how to achieve that in KSQL. Maybe that would be possible with Kafka Streams?

@Edit:

Ok, so I kind of managed to do that by adding those properties to sink configuration:

pk.mode=record_key
pk.fields=rowkey
insert.mode=upsert

Now the rows are updated but the window data is some jibberish, it looks like that:

TOTAL   USERID  rowkey
32      User_9  User_9jİ�
31      User_9  User_9jı�`
22      User_9  User_9jĲ��
1       User_9  User_9jĳ�

So the window is there, but binary encoded? Not sure what's going on there. I still need to get this date in some format, that is readable

score 1 · Accepted Answer · answered May 19 '19 at 18:40

Ok, found a solution. So, first of all what I needed was in query to create window_start/window_end fields, like that:

SELECT [...], WINDOWSTART() AS window_start, WINDOWEND() AS window_end, [...]

After that I had to add those params to sink:

pk.mode=record_value
pk.fields=[...],WINDOW_START
insert.mode=upsert

That works.

Kafka Connect JDBC sink - store aggregated data from KSQL

1 Answers1