how to make ksql print only latest records

Question

I am running following query on ksql. but I want only last 20 records.

SELECT MAX(ROWTIME),TIMESTAMPTOSTRING(ROWTIME, 'yyyy-MM-dd HH:mm:ss'),SERIAL,COUNT FROM MY_STREAM WHERE TIMESTAMPTOSTRING(ROWTIME, 'yyyy-MM-dd HH:mm:ss') >= DATE AND TIMESTAMPTOSTRING(ROWTIME, 'yyyy-MM-dd HH:mm:ss') <= DATE2 GROUP BY SERIAL,COUNT LIMIT 20;

score 3 · Answer 1 · answered Dec 06 '18 at 11:59

3

You can't do that in KSQL currently. Since KSQL is dealing with unbounded data, the 'last' records don't make so much sense, because the data is (potentially) always arriving.

Depending on what you're trying to do you could use ROWTIME predicate to filter data based on a time range.

Possibly you will need to use Kafka Streams instead to access the data in the pattern you're describing.

You might want to also check for existing issue to upvote here: https://github.com/confluentinc/ksql/issues/ and if there isn't one, create one https://github.com/confluentinc/ksql/issues/new

answered Dec 06 '18 at 11:59

Robin Moffatt

30,382
3
65
92

1

Is there ORDER BY clause support ? or TOPKDISTINCT – Nanobrains Dec 07 '18 at 08:44
You can see all the currently supported syntax here: https://docs.confluent.io/current/ksql/docs/developer-guide/syntax-reference.html – Robin Moffatt Dec 07 '18 at 09:38
if I use count(*), it returns only 1 – Nanobrains Dec 07 '18 at 11:27
If I use TOPKDISTINCT(item1,5) it returns [value1,null,null,null,null] – Nanobrains Dec 07 '18 at 11:28
Just adding a correction to this: `LIMIT 20` is supported by now, which displays to the *first* 20 records that would appear in the result. – kopaka Sep 10 '21 at 14:14

how to make ksql print only latest records

1 Answers1