CODEWITHSUNDEEP
apache-kafkaksqldb
chubao
chubao

Reputation: 6011

ksql: create table and filter today's data based on timestamp

I am quite new to KSQL and just wonder how to extract today's data for aggregation? say I have the stream of count data with schema below:

Field     | Type
-----------------------------
 ROWTIME   | BIGINT
 ROWKEY    | VARCHAR(STRING)
 TS        | BIGINT
 COUNT     | BIGINT

How to create a table which would output the snapshot of todays sum of COUNT where TS is the UNIX timestamp using KSQL?

Upvotes: 0

Views: 3288

Answers (1)

Hojjat
Hojjat

Reputation: 684

You can use TIMESTAMPTOSTRING udf to change the TS value to your desired date format and then perform the sum. Here is one way to do this:

CREATE STREAM mystream1 AS SELECT TIMESTAMPTOSTRING(TS, 'yyyy-MM-dd') AS TS, COUNT_VAL FROM mystream;

SELECT TS, sum(COUNT_VAL) FROM mystream1 GROUP BY TS;

You may want to rename COUNT column in your stream since it is function name in KSQL. You can also add WINDOW if you want to perform this over window.

Upvotes: 3

Related Questions