As per the KSQLDB documentation, session window can be used to order the records as per timestamp and do aggregation. I have an use case where I want to insert records into MySQL in sequence. I have a timestamp field in my record that I used as ROWTIME and then tried session window over it and inserted into an output stream that will push into a topic and then to RDS. But in the output stream I was not able to reorder the messages as per the timestamp. Example - There are two records - Record 1 at 11:00AM and Record 2 at 11:01AM and both of them has same primary keys. These two records are getting ingested in Kafka in sequence - Record 2 , Record 1. But in MYSQL I need Record 1 and then Record 2 as the Record 1 has lower timestamp. I tried window session of 5 minutes in stream. But in output stream, it is always coming as Record 2, Record 1.
Is this scenario possible inside Kafka? Can I reorder the records inside Kafka and then push into a stream using INSERT INTO statement?
Currently I am trying to do using KSQL queries as I am using confluent Kafka.
Session windows do not change the order of records, they GROUP records together that have the same key and are within some time period of each other.
Hence session windows are not going to allow you to reorder messages.
Reordering messages is not a use-case ksqlDB is suited for at present. You may have better luck if you tried to write a Kafka Streams based application.
Kafka Streams would allow you to use a state-store to buffer input messages for some time to allow for out-of-order messages. You should be able to use punctuation to trigger outputting of cached messages after some time period. You will need to choose how long you are willing to buffer the input to allow for out-of-order messages.