How to get window start or end time from kafka topic corresponding to KSQL windowed table?

Question

I want to get window start timestamp (here is 1530008520000) from kafka-console-consumer command.

It works with KSQL:

ksql> select * from DEV_MONITOR_RULE_2557_104782_233_2_TABLE;

1530008581051 | 2557 : Window{start=1530008520000 end=-} | 2557 | 2 | 1530008581051

But it does not work with kafka-console-consumer?

./bin/kafka-console-consumer --zookeeper 10.12.0.157:2181 --topic DEV_MONITOR_RULE_2557_104782_233_2_TABLE

{"HITCOUNTS":2,"TENANTID":2557,"HITTIME":1530008581051}

How to print window start time (here is 1530008520000) from kafka-console-consumer?

Thanks!

Sorry, what is your question? Your SELECT query already includes this information, i.e. 1530008581051? — Michael G. Noll
How to print window start time (here is 1530008520000) from kafka-console-consumer instead of HITTIME 1530008581051? — Casel Chen

Robin Moffatt Robin Moffatt · Accepted Answer · 2018-06-27T09:34:07

The window start time is reflected in the ROWTIME of the KSQL message, and in the timestamp of the Kafka message. This timestamp you can access using the standard APIs for working with Kafka messages.

AFAIK the kafka-console-consumer doesn't support showing timestamp. However something like kafkacat does.

Here's some aggregated data in KSQL, with the ROWTIME showing the start of the window (and TIMESTAMPTOSTRING being used to pretty-print it):

ksql> SELECT TIMESTAMPTOSTRING(ROWTIME, 'yyyy-MM-dd HH:mm:ss'), ROWTIME, STARS, STAR_COUNT FROM RATINGS_AGG2;
2018-06-27 09:30:00 | 1530091800000 | 1 | 2
2018-06-27 09:30:00 | 1530091800000 | 4 | 6
2018-06-27 09:30:00 | 1530091800000 | 2 | 2
2018-06-27 09:30:00 | 1530091800000 | 3 | 3

Now the same topic in kafkacat:

$ kafkacat -b localhost:9092 -C -K: \
  -f '\nTimestamp: %T\t\tValue (%S bytes): %s' \
  -t RATINGS_AGG2

Timestamp: 1530091800000                Value (26 bytes): {"STAR_COUNT":1,"STARS":1}
Timestamp: 1530091800000                Value (26 bytes): {"STAR_COUNT":2,"STARS":1}
Timestamp: 1530091800000                Value (26 bytes): {"STAR_COUNT":3,"STARS":3}
Timestamp: 1530091800000                Value (26 bytes): {"STAR_COUNT":5,"STARS":1}
Timestamp: 1530091800000                Value (26 bytes): {"STAR_COUNT":6,"STARS":3}
Timestamp: 1530091800000                Value (26 bytes): {"STAR_COUNT":7,"STARS":1}
Timestamp: 1530091800000                Value (26 bytes): {"STAR_COUNT":7,"STARS":3}
Timestamp: 1530091800000                Value (26 bytes): {"STAR_COUNT":9,"STARS":1}
Timestamp: 1530091800000                Value (26 bytes): {"STAR_COUNT":9,"STARS":3}

How to get window start or end time from kafka topic corresponding to KSQL windowed table?

2 Answers