Unable to create a TABLE from a STREAM

[Alessio Bernesco Làvore]

Hello everyone,

i've derived a STREAM called "temperature" from an IoT Kafka topic, containing generic messages with an attribute "messagetype", possibly "ERROR" or "DATA".

Now i'm trying to create a TABLE selecting only the messages with attribute "messagetype"="ERROR", usign this syntax:

CREATE TABLE errors AS SELECT

messagetimestamp,device,messagename,messagestringvalue 

FROM temperature

WHERE messagetype='ERROR'

EMIT CHANGES;

But i cannot complete the operation, with the error:

"Invalid result type. Your SELECT query produces a STREAM. Please use CREATE STREAM AS SELECT statement instead."

I'm using the examples at: https://docs.ksqldb.io/en/latest/developer-guide/create-a-table/ but i'm clearly missing something.

Thanks,

Alessio

[Michael Drogalis]

A materialized table (a table that is derived from a stream) needs an aggregation to compound the data it is receiving. These links might help:

1. How materialized views work

2. All aggregation functions

The reason this is a bit confusing is that base tables (tables declared straight onto a topic that don't derive from a stream) don't need an aggregation. The ksqlDB language overloads the word "table"—we're working to change that to make it less confusing.

--
You received this message because you are subscribed to the Google Groups "ksqldb-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ksql-users+...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/ksql-users/CAAK%2B_NQ43z3gMFnbYn%2BEH2YcwoKixTL%3DXk9_Z%2BJp2pPz0_LDTA%40mail.gmail.com.

[Alessio Bernesco Làvore]

Thank you Michael, working smoothly! Having the need to treat each error as a single element in the table I have build a different composite aggregation key for each message.

Just to have your opinion:having the use case of creating two tables to split the messages on the "temperature" topic, dividing the messages with "data" and "error" 

Thank you,

Alessio

[Michael Drogalis]

That's perfectly okay to do.

[Alessio Bernesco Làvore]

Thank you Michael,

Also thanks for your 10/13 article, missed it, great contents and really useful insight. 

To view this discussion on the web, visit https://groups.google.com/d/msgid/ksql-users/CAB47CADyQ8UzdPMhfcTYHKrP4gBLJTK%2BrXjHR3gATrAvJTHVPw%40mail.gmail.com.