CREATE STREAM FOO AS
SELECT c.title, s.fromInstant, s.toInstant
FROM CATALOGUE_STREAM c
FULL OUTER JOIN SCHEDULE_STREAM s WITHIN 48 HOURS
ON s.ROWKEY = c.ROWKEY
EMIT CHANGES;
where SCHEDULE_STREAM and CATALOGUE_STREAM are streams based on my kafka topics.
and since I'd potentially get multiple messages from SCHEDULE_STREAM and CATALOGUE_STREAM for a given key, I tried the following to match it only with the latest update:
CREATE STREAM BAR AS
SELECT A1.*
FROM FOO AS F1 LEFT JOIN FOO AS F2
ON (F1.ROWKEY = F2.ROWKEY AND F1.ROWTIME < F2.ROWTIME)
WHERE F2.ROWTIME IS NULL
EMIT CHANGES;
see here for an explanation of the idea used here or see the following gist
but I get:
io.confluent.ksql.execution.expression.tree.LogicalBinaryExpression cannot be cast to io.confluent.ksql.execution.expression.tree.ComparisonExpression
Is this a conscious decision on the part of ksqldb?
What are the alternative ways of achieving this?
I further tried:
CREATE STREAM BAZ AS
SELECT c.title, s.fromInstant, s.toInstant
FROM CATALOGUE_TABLE c
FULL OUTER JOIN SCHEDULE_TABLE s
ON s.ROWKEY = c.ROWKEY
EMIT CHANGES;
where SCHEDULE_TABLE and CATALOGUE_TABLE are tables based on my kafka topics. Unfortunately I got:
Invalid result type. Your SELECT query produces a TABLE. Please use CREATE TABLE AS SELECT statement instead.
I have a stream join as below:
where
SCHEDULE_STREAM
andCATALOGUE_STREAM
are streams based on my kafka topics. and since I'd potentially get multiple messages fromSCHEDULE_STREAM
andCATALOGUE_STREAM
for a given key, I tried the following to match it only with the latest update:see here for an explanation of the idea used here or see the following gist
but I get:
where
SCHEDULE_TABLE
andCATALOGUE_TABLE
are tables based on my kafka topics. Unfortunately I got: