Getting confused with ksqlDB events and materialized views. I want to be notified in a dedicated topic / stream when a new high stock price occurs as determined in a table aggregate / materialized view but I'm getting the high price reported for every event rather than only when there is a new high price event.
Here is my working example / setup.
Create underlying stream and topic for stocks.
ksql> create stream stocks (symbol VARCHAR KEY, company VARCHAR, price DECIMAL(9, 2))
> WITH (KAFKA_TOPIC='stocks', PARTITIONS=1, VALUE_FORMAT='json');
Message
----------------
Stream created
----------------
add some initial data on stock prices for Acme Corp
ksql> insert into stocks (symbol, company, price) values ('ACME', 'Acme Corp', 111.11);
ksql> insert into stocks (symbol, company, price) values ('ACME', 'Acme Corp', 111.12);
ksql> insert into stocks (symbol, company, price) values ('ACME', 'Acme Corp', 111.13);
Print underlying topic to prove data is there.
ksql> print 'stocks' from beginning limit 3;
Key format: KAFKA_INT or KAFKA_STRING
Value format: JSON or KAFKA_STRING
rowtime: 2021/03/21 14:26:57.169 Z, key: 1094929733, value: {"COMPANY":"Acme Corp","PRICE":111.11}
rowtime: 2021/03/21 14:27:01.717 Z, key: 1094929733, value: {"COMPANY":"Acme Corp","PRICE":111.12}
rowtime: 2021/03/21 14:27:04.546 Z, key: 1094929733, value: {"COMPANY":"Acme Corp","PRICE":111.13}
Topic printing ceased
Create aggregate / materialized view to show highest stock prices.
ksql> create table stock_highs as
> select symbol, max(price) as high
> from stocks
> group by symbol
> emit changes;
Message
--------------------------------------------
Created query with ID CTAS_STOCK_HIGHS_115
--------------------------------------------
Query it for visual inspection.
ksql> select * from stock_highs where symbol = 'ACME';
+--------------+------------------------+
|SYMBOL |HIGH |
+--------------+------------------------+
|ACME |111.13 |
Query terminated
Use consumer (aka print STOCK_HIGHS) in a separate terminal (terminal 2) of to watch for changes to the stock high price.
ksql> print STOCK_HIGHS from beginning;
Key format: KAFKA_INT or KAFKA_STRING
Value format: JSON or KAFKA_STRING
rowtime: 2021/03/21 14:27:04.546 Z, key: 1094929733, value: {"HIGH":111.13}
Back in original terminal (terminal 1) with ksql client insert more data to force the stock high to be updated.
ksql> insert into stocks (symbol, company, price) values ('ACME', 'Acme Corp', 111.20);
ksql> insert into stocks (symbol, company, price) values ('ACME', 'Acme Corp', 111.15);
The above inserts should give a new high price of 111.20 and ignore the 111.15 price so, in my thinking and use case I'd want to get an event / message from the STOCK_HIGHS topic showing a new high stock price of 111.20 but not the 111.15 price. However, I get two new events in the consumer (terminal 2).
ksql> print STOCK_HIGHS from beginning;
Key format: KAFKA_INT or KAFKA_STRING
Value format: JSON or KAFKA_STRING
rowtime: 2021/03/21 14:27:04.546 Z, key: 1094929733, value: {"HIGH":111.13}
rowtime: 2021/03/21 14:37:51.234 Z, key: 1094929733, value: {"HIGH":111.20}
rowtime: 2021/03/21 14:39:03.301 Z, key: 1094929733, value: {"HIGH":111.20}
The problem is I really only want to be notified or "evented" when only new high prices are occuring that way I can have a consumer going off and doing something meaningful when there is a new high price established.
