6

I'm a new bee to ksql. I'm just playing with read kafka topics to streams and it works great.

Also, trying to create a table from kafka topic and failed. Realized that I need to have a key set in kafka topic which is considered as primary key in ksql table. So I tried creating table from stream instead, but failed too. Query/Script: 

CREATE TABLE DETAILS_TABLE AS SELECT SEQ, Server1, ServerId, NumberUri, SERVERID2, SERVER2 FROM details_stream WINDOW TUMBLING (SIZE 1 MINUTES);
Invalid result type. Your SELECT query produces a STREAM. Please use CREATE STREAM AS SELECT statement instead.

Can someone explain if its possible or not? If yes, wher am I going wrong? Thanks.

Matthias J. Sax
  • 59,682
  • 7
  • 117
  • 137
srikanth
  • 958
  • 16
  • 37
  • 2
    If you want the query result to be a TABLE, you need to specify an aggregation query. – Matthias J. Sax May 05 '18 at 18:03
  • @MatthiasJ.Sax I tied this: CREATE TABLE DETAILS_TABLE AS SELECT SEQ, Server1, ServerId, NumberUri, SERVERID2, SERVER2, COUNT(*) AS TOTAL FROM details_stream WINDOW TUMBLING (SIZE 1 MINUTES) GROUP BY SEQ; the error is: Group by elements should match the SELECT expressions. – srikanth May 05 '18 at 18:29
  • I could succed with CREATE TABLE DETAILS_TABLE AS SELECT SEQ, COUNT(*) AS TOTAL FROM details_stream WINDOW TUMBLING (SIZE 1 MINUTES) GROUP BY SEQ; But the problem is i need other parameters/colums too. How do i proceed further. – srikanth May 05 '18 at 18:34

1 Answers1

8

As Matthias says, you need to specify a (valid) aggregate query.

So this would work: 

CREATE TABLE DETAILS_TABLE AS \
SELECT SEQ, Server1, ServerId, NumberUri, SERVERID2, SERVER2, COUNT(*) AS TOTAL \
FROM details_stream WINDOW TUMBLING (SIZE 1 MINUTES) \
GROUP BY SEQ, Server1, ServerId, NumberUri, SERVERID2, SERVER2;

Just as any SQL dialect, if you are doing an aggregation, you have to GROUP BY all of the fields, otherwise it makes no syntactical sense.

Robin Moffatt
  • 30,382
  • 3
  • 65
  • 92
  • We did, but couldn't pull off. The stream is able to read data from kafak-topic but the table isn't able to. Could read only 2 entries somehow. We are in a hurry to test our POC, so moved out and worked around. – srikanth May 08 '18 at 09:10
  • IS this this the only document https://docs.confluent.io/current/ksql/docs/tutorials/basics-docker.html to get more info? Can I get much organized doc explains 1)how long does a stream/table persist? 2) How to synchronize between two kafka_topics to use joins? and so on. Thanks. – srikanth May 08 '18 at 09:18
  • is this still the case? or is there an option of a stream.ToTable feature we have available in KSQL? – hec Aug 11 '23 at 06:05